Examples of using indexes with queries that specify fn:starts-with

Starting in version 10.1, for queries with predicates that contain the fn:starts-with function, the optimizer can choose to use a VARCHAR-type index to speed up the query.

No changes are necessary to existing VARCHAR indexes, and you do not need to use any special syntax in the CREATE INDEX statement when you create new VARCHAR indexes.

The fn:starts-with function determines whether a string begins with a specific substring.

Creating a VARCHAR type index

This example demonstrates how to create a table with an XML column, insert data into it, and create an index of VARCHAR type.

First, create the table called FAVORITE_CDS, which has a column called CDINFO that is of type XML:

CREATE TABLE favorite_cds (
  NAME          CHAR(20) NOT NULL,
  CDID          BIGINT,
  CDINFO        XML
);

Insert a record into the FAVORITE_CDS table:

INSERT INTO favorite_cds VALUES('John Peterson', 01,
'<FAVORITECDS>
    <CD>
         <TITLE>Top hits</TITLE>
         <ARTIST>Good Singer</ARTIST>
         <COMPANY>Top Records</COMPANY>
         <YEAR>1999</YEAR>
    </CD>
    <CD>
         <TITLE>More top hits</TITLE>
         <ARTIST>Better Singer</ARTIST>
         <COMPANY>Better Music </COMPANY>
         <YEAR>2005</YEAR>
    </CD>
    <CD>
         <TITLE>Even more top hits</TITLE>
         <ARTIST>Best Singer</ARTIST>
         <COMPANY>Best Music</COMPANY>
         <YEAR>2010</YEAR>
    </CD>
</FAVORITECDS>');

You can create a VARCHAR index, for example called year_idx, on the /FAVORITECDS/CD/YEAR path, for example:

CREATE INDEX year_idx ON favorite_cds (cdinfo) 
   GENERATE KEYS USING XMLPATTERN '/FAVORITECDS/CD/YEAR' 
   AS SQL VARCHAR(20);

You can also create a VARCHAR index, for example called company_idx, on the /FAVORITECDS/CD/COMPANY path. The following example creates a case insensitive index on company name by using the fn:upper-case function:

CREATE INDEX company_idx ON favorite_cds (cdinfo) 
   GENERATE KEYS USING XMLPATTERN '/FAVORITECDS/CD/COMPANY/fn:upper-case(.)' 
   AS SQL VARCHAR(20);

Running queries that have predicates containing fn:starts-with

For the following query, the optimizer might choose to use the VARCHAR index, year_idx, instead of performing a table scan to find CDs from the 1990s (in this example, the fn:starts-with function finds CDs having a year that starts with 199). The optimizer can choose to use the year_idx index, if it is found to be cheaper.

XQUERY for $y in db2-fn:xmlcolumn
   ('FAVORITE_CDS.CDINFO')/FAVORITECDS/CD/YEAR
    [fn:starts-with(., "199")]/.. return $y

--------------------------------
<CD>
   <TITLE>Top hits</TITLE>
   <ARTIST>Good Singer</ARTIST>
   <COMPANY>Top Records</COMPANY>
   <YEAR>1999</YEAR>
</CD>
1 record(s) selected.

Note that queries written in a format where you specify a context item expression (.) for the first parameter of the fn:starts-with function enable the optimizer to choose only one index scan, whereas other formats might require two index scans. For this reason, a similar query written in the following format will likely run more slowly:

XQUERY for $y in db2-fn:xmlcolumn
   ('FAVORITE_CDS.CDINFO')/FAVORITECDS/CD
    [fn:starts-with(YEAR, "199")] return $y

For the next query, the optimizer might choose to use the VARCHAR index, company_idx, instead of performing a table scan to find records where the company name starts with BES. The optimizer can choose to use the company_idx index, if it is found to be cheaper.

XQUERY for $y in db2-fn:xmlcolumn
   ('FAVORITE_CDS.CDINFO')/FAVORITECDS/CD/COMPANY
    [fn:starts-with(fn:upper-case(.), "BES")]/.. return $y

--------------------------------
    <CD>
         <TITLE>Even more top hits</TITLE>
         <ARTIST>Best Singer</ARTIST>
         <COMPANY>Best Music</COMPANY>
         <YEAR>2010</YEAR>
    </CD>
1 record(s) selected.