Operators Used in the WHERE Clause
A predicate can use the operators as described in Operators. For detailed information about using the LIKE operator, see LIKE Operator.
The following sections contain additional information about using the LIKE operator in the predicate of a WHERE clause.
Indexed Columns with LIKE Operator
If the left operand of the LIKE operator is the name of an indexed column of type string, ActiveSpaces converts the LIKE operator into a range query using >= and <. This enables the ActiveSpaces index selection algorithm to select the index and use it for scanning rows when processing the query. For example, take a look at the following statement:
SELECT * FROM mytable WHERE lastname LIKE ‘Long’Internally this statement is converted to the following statement and the index on lastname can be used to perform a range scan.
SELECT * FROM mytable WHERE lastname >= ‘Long’ AND lastname < ‘long’However, the index for lastname cannot be used if <character sequence pattern> is a long value or starts with a wildcard (%, _) or digit.
Pattern Matching with LIKE Operator
When using the LIKE operator in a WHERE clause, using a character sequence pattern that begins with a wild card results in a full table scan.
It is also important to be aware that you might end up scanning more rows than you would expect because of the way pattern matching works when matching is not case sensitive. For example, if you have an index defined for
lastname
and you run the following query:
SELECT * FROM mytable WHERE lastname LIKE 'm%'
The index is used, but all the rows are scanned where the value of lastname start with "M" through "Z" and "a" through "m".
Copyright © Cloud Software Group, Inc. All rights reserved.