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

 

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

Example Example

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 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 since the search range cannot be effectively restricted any further.

End of the example.

If the SQL Optimizer does not find any equality condition for key columns, it continues to analyze the search conditions linked with OR it has not looked at so far. To do this, it proceeds as follows:

  1. It converts the search condition to the disjunctive normal form.

    Example Example

    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)

    End of the example.
  2. It analyzes the new expression, analyzing the newly-formed parenthetical expressions separately.

  3. It determines the costs. This, 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 cost determined for the search strategy, without considering the search conditions linked with OR, the database system uses the different search strategies.

    Example Example

    If the total costs of (b1 AND b2 AND b3) and (b1 AND b2 AND b4 AND b5) are less 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).

    End of the example.
  4. The database system uses the most cost-effective search strategy to execute the SQL statement.

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

Example Example

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

End of the example.

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

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 can find additional simple example SQL statements in the SQL Tutorial.