LIKE Condition 
A LIKE condition is a search condition with a LIKE predicate.
Prerequisites
The SQL optimizer can only evaluate a LIKE condition if it meets the following conditions:
Only the LIKE operator was used as operator.
The LIKE condition has the following form:
<extended_value_spec> must not start with the match_string or match_set SQL syntax elements of a LIKE predicate.
If possible, the database system converts conditions of the form
Syntax... NOT (<column_spec> NOT LIKE <extended_value_spec>)
End of the code.into an expression of the form
Syntax... (<column_spec> LIKE <extended_value_spec>
End of the code.
NoteThe SQL optimizer can only transfor queries if the QueryRewrite function is enabled (special database parameter EnableQueryRewrite).
More information: Database Administration, Special Database Parameters
End of the note.
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.
LIKE Condition
EXPLAIN SELECT *
FROM hotel.customer
WHERE name LIKE 'Wa%'
Qualification: name LIKE 'Wa%'
Primary key of CUSTOMER table: cno
Indexes via qualified columns: FULL_NAME_INDEX (name,firstname)
Search strategy: RANGE CONDITION FOR INDEX
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 |
4 |
The database system can use the index FULL_NAME_INDEX (name, firstname). It sets the start key in the first record that meets the LIKE condition with the first two character values (Wade, Francisco), and the stop key in the first record that does not meet the condition any more (Weaver, Alfredo).