Internal language transformations

The Derby SQL parser sometimes transforms SQL statements internally for performance reasons. This section describes those transformations.

Understanding the internal language transformations can help you analyze and tune performance. Understanding the internal language transformations is not necessary for the general user.

This chapter uses some specialized terms. Here are some definitions:

base table

A real table in a FROM list. In queries that involve "virtual" tables such as views and derived tables, base tables are the underlying tables to which virtual tables correspond.

derived table

A virtual table, such as a subquery given a correlation name or a view. For example:

SELECT derivedtable.c1 FROM (VALUES ('a','b'))
AS derivedtable(c1,c2)

equality predicate

A predicate in which one value is compared to another value using the = operator.

equijoin predicate

A predicate in which one column is compared to a column in another table using the = operator.

optimizable

A predicate is optimizable if it provides a starting or stopping point and allows use of an index. Optimizable predicates use only simple column references and =, <, >, +, >=, and IS NULL operators. For complete details, see What's optimizable?. A synonym for optimizable is indexable.

predicate

A WHERE clause contains boolean expressions that can be linked together by AND or OR clauses. Each part is called a predicate. For example, the following clause contains two predicates:

WHERE c1 =2 AND c2 = 5

sargable

Sargable predicates are a superset of optimizable predicates; not all sargable predicates are optimizable, because sargable predicates also include the <> operator. (Sarg stands for "search argument.") Predicates that are sargable but not optimizable nevertheless improve performance and allow the optimizer to use more accurate costing information.

In addition, sargable predicates can be pushed down (see Predicates pushed into views or derived tables).

simple column reference

A reference to a column that is not part of an expression. For example, c1 is a simple column reference, but c1+1, max(c1), and lower(c1) are not.

• Predicate transformations
  WHERE clauses with predicates joined by OR are usually not optimizable. WHERE clauses with predicates joined by AND are optimizable if at least one of the predicates is optimizable.
• Transitive closure
  The transitive property of numbers states that if A = B and B = C, then A = C.
• View transformations
  When Derby evaluates a statement that references a view, it transforms the reference to a view into a derived table. It might make additional transformations to improve performance.
• Subquery processing and transformations
  Subqueries are notoriously expensive to evaluate. This section describes some of the transformations that Derby makes internally to reduce the cost of evaluating them.
• Outer join transformations
  Derby transforms OUTER to INNER joins when the predicate filters out all nulls on the join column. This transformation can allow more potential query plans and thus better performance.
• Sort avoidance
  Sorting is an expensive process. Derby tries to eliminate unnecessary sorting steps where possible.
• Aggregate processing