Background documentationSearch Strategies for Search Conditions Linked with OR Locate this document in the navigation structure

 

For SQL statements that contain search conditions linked with OR, the SQL optimizer proceeds as follows.

  1. It initially analyzes each search condition individually.

    Example Example

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

    The SQL optimizer individually analyzes the expressions b1, b2 and (b3 OR (b4 AND b5)).

    End of the example.

  2. If the SQL 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, because the search range cannot be effectively restricted any further.

    If the SQL optimizer does not find any equality condition for key columns, it continues to analyze the search conditions linked with OR, which it has not considered until now. To do this, it proceeds as follows:

    1. It converts the search condition to 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. It analyzes the new expression, analyzing the newly-formed parenthetical expressions separately.

    3. It determines the costs. For this, it sums up the costs of the various search strategies for the parenthesized expressions of the disjunctive normal form. If the sum is lower than the initial cost determined for the search strategy that was determined without considering the search conditions linked with OR, the database system uses this search strategy.

      If the total costs of (b1 AND b2 AND b3) and (b1 AND b2 AND b4 AND b5) are smaller than the costs for the search strategy for b1 and b2, the database system uses the strategies for (b1 AND b2 AND b3) and (b1 AND b2 AND b4 AND b5).

Example

The following example uses the demo database DEMODB with the complete demo data in the schema HOTEL, see Concepts of the Database System, Objects in the Schema HOTEL.

EXPLAIN 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 CUSTOMER table: cno

  • Indexes via qualified columns: FULL_NAME_INDEX (name, firstname)

  • Search strategy for the first predicate: EQUAL CONDITION FOR INDEX

  • Search strategy for the second predicate: EQUAL CONDITION FOR 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

Multiple AND terms are linked to one OR condition. The AND condition has a higher priority than the OR condition.

The SQL Optimizer determines the best search strategy for each AND term. Both AND terms qualify the two index columns with an equality condition. This means that the database system can use the EQUAL CONDITION FOR INDEX strategy for each of these. ADDNL. QUALIFICATION ON INDEX is displayed because the columns linked with OR are also checked in the index.

More Information

Analyzing Search Strategies Using the EXPLAIN Statement

Search Strategies

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