SQL Processing - Relational Database Index Design and the Optimizers - page 30

PREDICATES

A WHERE clause consists of one or more predicate s (search arguments). Three
simple predicates are shown in SQL 3.1. These are:

SQL 3.1

WHERE SEX = 'M'

AND
(WEIGHT > 90
OR
HEIGHT > 190)

ž SEX = 'M'
ž WEIGHT > 90
ž HEIGHT > 190
They can also be considered as two compound predicates :

ž WEIGHT > 90 OR HEIGHT > 190
ž SEX = 'M' AND (WEIGHT > 90 OR HEIGHT > 190)
Predicates are the primary starting points for index design. When an index sup-
ports all the predicates of a SELECT statement, the optimizer is likely to build
an efficient access path.

Comment
In his numerous topics, Chris Date uses the term predicate reluctantly (4):
We follow conventional database usage here in referring to conditions as
predicates. Strictly speaking, however, this usage is incorrect. A more accurate
term would be conditional expression or truth-valued expression. (page 79)

OPTIMIZERS AND ACCESS PATHS

One of the long-standing advantages of relational databases has been that data is
requested with little or no thought for the way in which the data is to be accessed.
This decision is made by a component of the DBMS called the optimizer .These
have varied widely across different relational systems and probably always will,
but they all try to access the data in the most effective way possible, using
statistics stored by the system collected on the data. The optimizer is, of course,
at the heart of SQL processing and is also central to this topic as well.
Before an SQL statement can be executed, the optimizer must first decide
how to access the data; which index, if any, should be used; how the index should
be used; should assisted random read be used; and so forth. All of this information