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 search 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, 1^st predicate: EQUAL CONDITION FOR INDEX
Search strategy used, 2^nd 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