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
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.
A virtual table, such as a subquery given a correlation name or a view.
For example: SELECT derivedtable.c1 FROM (VALUES ('a','b'))
A predicate in which one value is
compared to another value using the = operator.
A predicate in which one column is compared to a column in another table
using the = operator.
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.
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 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