Browse Prior Art Database

Optimization of Like Predicate in an SQL Query

IP.com Disclosure Number: IPCOM000109607D
Original Publication Date: 1992-Sep-01
Included in the Prior Art Database: 2005-Mar-24
Document File: 4 page(s) / 154K

Publishing Venue

IBM

Related People

Boykin, JR: AUTHOR [+3]

Abstract

The following is a description of an optimization technique used by the SQL Compiler on the SQL 'LIKE' predicate. The predicate may contain an 'ESCAPE' clause. This invention creates two constants that are used as high and low values to narrow the possible candidates that qualify for the like predicate. The result of this is an improved execution of the LIKE predicate within an SQL statement. BACKGROUND

This text was extracted from an ASCII text file.
This is the abbreviated version, containing approximately 52% of the total text.

Optimization of Like Predicate in an SQL Query

       The following is a description of an optimization
technique used by the SQL Compiler on the SQL 'LIKE' predicate.  The
predicate may contain an 'ESCAPE' clause.  This invention creates two
constants that are used as high and low values to narrow the possible
candidates that qualify for the like predicate.  The result of this
is an improved execution of the LIKE predicate within an SQL
statement.
BACKGROUND

      The following is the syntax for the LIKE predicate:
where the string constant and host variable can contain the following
special characters:
  1. % (percent sign) - which is substituted with any sequence of
zero or more characters.
  2. _ (underscore)   - which is substituted with exactly one
character.

      If an escape character is specified, it may be used to indicate
that the character following the escape character in the pattern is
NOT to be treated as a special character.
EXAMPLE

      Given the following SQL table (with index) that stores all of
the codepage information that is defined in SQL.H:
     CREATE TABLE codepages (country      VARCHAR(30),
                             define_name  VARCHAR(20),
                             define_value SMALLINT,
                             )
     CREATE INDEX define ON codepages (define_name)
The following query will select the information stored for all the
extended codepages.  By convention, the extended codepages that are
defined today follow this format: SQL_CP_10xx, where each 'x'
represents a digit.
  SELECT country, define_name, define_value FROM codepages
  WHERE define_name LIKE 'SQL+_CP+_10__' ESCAPE '+'
Whether the table is scanned through the base table or its index,
every row within the table must presently be accessed to determine if
it should be reported to the application.
THE OPTIMIZATION

      Using this invention, a LIKE predicate which meets criteria is
transformed into start and stop key access predicates in order to
achieve a key access scan through a table's index.  Criteria is as
follows:
1) The left-hand side of the Like predicate is a column contained in
an index with all previous keys of the index qualified with an '=' or
'IS NULL' predicate.
2) The escape character (if specified) must be a constant.
3) The search pattern on the right-hand side of the LIKE predicate is
one of the following:
   a) The keyword USER:
       <column> LIKE USER
       In this case, both the start and stop keys for the <column>
become the search string USER.
   b) A string-constant without special characters:
       <column> LIKE 'Belgium'
       In this case, both the start and stop keys for the <column>
become the search...