Search Strategies for Search Conditions Linked with OR

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

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

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

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).

The following example uses the demo database DEMODB with the complete demo data in the schema HOTEL (ee 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

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.

Displaying the Search Strategies Used by SQL Statements (EXPLAIN)

Search Strategies

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