Search Conditions Linked with OR

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:

It converts the search condition to the disjunctive normal form.

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

Search Conditions Linked with OR

Example

More Information