The Derby SQL parser sometimes transforms SQL statements internally for performance reasons. This appendix 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: WHERE c1 =2 AND c2 = 5 contains two predicates.
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.
