Search Conditions Linked with OR

For SQL statements that contain search conditions linked with OR, the SQL Optimizer initially analyzes each search condition individually.

b1 AND b2 AND (b3 OR (b4 AND b5))

The expressions b1, b2 and (b3 OR (b4 AND b5)) are initially analyzed individually. If the Optimizer finds an equality condition for key columns for b1 or b2, then it does not have to consider (b3 OR (b4 AND b5)) when selecting the search strategy since the search range cannot be effectively restricted any further.

If the system does not find any equality conditions for key columns, then the other search conditions linked with OR (not yet considered by the system) are analyzed. The SQL Optimizer proceeds as follows:

..

       1.      Transformation into the disjunctive normal form

b1 AND b2 AND (b3 OR (b4 AND b5))

is transformed into the disjunctive normal form

(b1 AND b2 AND b3) OR (b1 AND b2 AND b4 AND b5)

       2.      Analysis of the new expression
The new parenthesized expressions are analyzed separately.

       3.      Determine costs
The costs of the various search strategies for the parenthesized expressions of the disjunctive normal form are totaled. If this  total is lower than the initial determined cost for the search strategy, without considering the search conditions linked with OR, then these various s
earch strategies are used.

If the total costs of (b1 AND b2 AND b3) and (b1 AND b2 AND b4 AND b5) are lower than the costs for the search strategy for b1 and b2, then the strategies for (b1 AND b2 AND b3) and (b1 AND b2 AND b4 AND b5) are used.

       4.      The most cost-effective search strategy is used to execute the SQL statement.

Examples

The examples used to explain the SQL Optimizer are based on the demo database DEMODB and its complete demo data in the schema HOTEL.

Concepts of the Database System, Objects in the Schema HOTEL

SELECT * FROM hotel.customer
  WHERE name = 'Smith' AND firstname = 'Sally'
     OR name = 'Miller' AND firstname = 'Sally'

Qualification: name = 'Smith' AND firstname = 'Sally'
       OR name = 'Miller' AND firstname = 'Sally'

Primary key of the table CUSTOMER: cno
Indexes of qualified columns:
FULL_NAME_INDEX (name, firstname)
Search strategy used, 1st predicate: EQUAL CONDITION FOR INDEX
Search strategy used, 2nd predicate: EQUAL CONDITION FOR INDEX

Multiple AND terms are linked to one OR condition. The AND condition has a higher priority than the OR condition. For each AND term, the best access strategy is determined. Both AND terms qualify the two index columns with an equality condition. They can thus each be processed with an EQUAL CONDITION FOR INDEX. The ADDL. QUALIFICATION ON INDEX is output because the OR-linked columns are also checked in the index.

Result of the EXPLAIN Statement

TABLENAME

COLUMN_OR_INDEX

STRATEGY

PAGECOUNT

CUSTOMER

 

DIFFERENT STRATEGIES FOR OR TERMS

34

 

FULL_NAME_INDEX

EQUAL CONDITION FOR INDEX

 

 

 

ADDNL. QUALIFICATION ON INDEX

 

 

NAME

(USED INDEX COLUMN))

 

 

FIRSTNAME

(USED INDEX COLUMN))

 

 

FULL_NAME_INDEX

EQUAL CONDITION FOR INDEX

 

 

 

ADDNL. QUALIFICATION ON INDEX

 

 

NAME

(USED INDEX COLUMN))

 

FIRSTNAME

(USED INDEX COLUMN))

 

 

 

RESULT IS COPIED,
COSTVALUE IS

14

You will find additional simple example SQL statements in the SQL Tutorial.

See also:

EXPLAIN Statement

List of All Search Strategies