An equality condition is a search condition with a comparison predicate.
The SQL optimizer can only evaluate an equality condition if it meets the following conditions:
Only the relational operator ( =) was used as operator.
It has one of the following forms:
<column_spec> = <extended_value_spec> <column_spec> = <subquery>
If an equality condition has a different form, the SQL optimizer attempts to transform it. The SQL optimizer transforms the following expressions into conditions without NOT with the negated operator =:
... NOT (<column_spec> <> <extended_value_spec>) ... NOT (<column_spec> <> <subquery>)
The SQL optimizer can only transform queries if the QueryRewrite function is enabled (special database parameter EnableQueryRewrite).
For more information, see Database Administration, Special Database Parameters.
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).
Equality Condition
EXPLAIN SELECT *
FROM hotel.customer
WHERE name = 'Smith'
Qualification: name = 'Smith'
Primary key of CUSTOMER table: cno
Indexes via qualified columns: FULL_NAME_INDEX (name,firstname)
Search strategy: RANGE CONDITION FOR INDEX
To find all data records with the name Smith, the database system can use the strategy FULL_NAME_INDEX (name,firstname).
TABLENAME |
COLUMN_OR_INDEX |
STRATEGY |
PAGECOUNT |
---|---|---|---|
CUSTOMER |
FULL_NAME_INDEX |
RANGE CONDITION FOR INDEX |
34 |
NAME |
(USED INDEX COLUMN) |
||
RESULT IS NOT COPIED, COSTVALUE IS |
6 |
Displaying the Search Strategies Used by SQL Statements (EXPLAIN)
SQL Reference Manual, Comparison Predicate (comparison_predicate)
You can find additional simple example SQL statements in the SQL Tutorial.