Restrictiveness of index definitions

Whether or not the evaluation of a query can make use of an index often depends on how restrictive the index definition is compared to your query. The following examples show a number of queries and indexes that can be used together.

Indexes for queries with a range predicate

The following query retrieves company information for employees with a salary greater than 35000 from the table companyinfo with the XML column companydocs:

SELECT companydocs FROM companyinfo
   WHERE XMLEXISTS('$x/company/emp[@salary > 35000]' 
   PASSING companydocs AS "x")

To be compatible, an index over XML data needs to include employee salary attribute nodes among the indexed nodes, and to store values as DOUBLE or DECIMAL type.

The query could use either one of the following indexes over XML data, for example:

CREATE INDEX empindex on companyinfo(companydocs)
   GENERATE KEY USING XMLPATTERN '//@salary' AS SQL DECIMAL(10,2)
   
CREATE INDEX empindex on companyinfo(companydocs)
   GENERATE KEY USING XMLPATTERN '/company/emp/@salary' 
   AS SQL DECIMAL(10,2)

Indexes that can be used by multiple queries

The following query retrieves company information for employees with the employee ID 31664.

SELECT companydocs FROM companyinfo
   WHERE XMLEXISTS('$x/company/emp[@id="31664"]' 
   PASSING companydocs AS "x")

A second query retrieves company information for departments with the ID K55.

SELECT companydocs FROM companyinfo
   WHERE XMLEXISTS('$x/company/emp/dept[@id="K55"] 
   PASSING companydocs AS "x")

To be compatible with both queries, the index over XML data needs to include employee ID attribute nodes and department ID attribute nodes among the indexed nodes, and to store values in the index as a VARCHAR type.

The queries can use this index over XML data:

CREATE INDEX empdeptindex on companyinfo(companydocs)
   GENERATE KEY USING XMLPATTERN '//@id' AS SQL VARCHAR(25)

Inclusion of namespaces when restricting XQuery predicates

Consider the following table with an XML column that contains customer information, and an index that is created on the XML column:

CREATE TABLE customer(xmlcol XML) %

CREATE UNIQUE INDEX customer_id_index ON customer(xmlcol)
   GENERATE KEY USING XMLPATTERN 
   'DECLARE DEFAULT ELEMENT NAMESPACE 
   "http://mynamespace.org/cust";/Customer/@id' 
   AS SQL DOUBLE %

Note: The statement terminator used in this section is the percentage sign (%), since the semicolon (;) already serves as the namespace delimiter.

The following query fails to match the index:

SELECT xmlcol FROM customer 
   WHERE XMLEXISTS('$xmlcol/*:Customer[@id=1042]' 
   PASSING xmlcol AS "xmlcol") %

In order for the query to be able to use the index, the query must be as restrictive as or more restrictive than the index. The index customer_id_index covers only customer elements in one particular namespace (http://mynamespace.org/cust). Since *: is used in the query to denote any namespace, the index is not used. This can be counter-intuitive if one expects *: to match the namespace in the index definition.

For the query to make use of the index, either the index needs to become less restrictive, or the query needs to become more restrictive.

The following less restrictive index customer_id_index2 could be used successfully with the same query:

CREATE UNIQUE INDEX customer_id_index2 ON customer(xmlcol)
      GENERATE KEY USING XMLPATTERN '/*:Customer/@id' AS SQL DOUBLE %

The following more restrictive query can make use of the initial index customer_id_index:

SELECT xmlcol FROM customer 
   WHERE XMLEXISTS('
   DECLARE NAMESPACE ns = "http://mynamespace.org/cust"; 
   $xmlcol/ns:Customer[@id=1042]' 
   PASSING xmlcol AS "xmlcol") %

When the appropriate namespace is specified explicitly in the query, the index customer_id_index can be used, since the query is now just as restrictive as the index. The index customer_id_index2 could also be used, since it is less restrictive than the query in this example.