DB2 10.5 for Linux, UNIX, and Windows

Examples of using indexes that specify fn:exists

You can use the fn:exists function to create an index that stores a Boolean value (a single character T or F) to indicate whether it is true or false that an element or attribute holds a data value (rather than the empty sequence).

Creating an index with fn:exists

This example demonstrates how to create a table with an XML column, insert data into it, and create an index using the fn:exists function.

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

CREATE TABLE income (
  ID           INT PRIMARY KEY NOT NULL,
  INCOMEINFO   XML
);

Insert three records into the INCOME table:

INSERT INTO income VALUES('1', 
'<Employee>
    <salary type="regular">
         <base>5500.00</base>
         <bonus>1000.00</bonus>
    </salary>
</Employee>');

INSERT INTO income VALUES('2', 
'<Employee>
    <salary type="contractor">
         <base>7600.00</base>
    </salary>
</Employee>');

INSERT INTO income VALUES('3', 
'<Employee>
    <salary>
         <base>2600.00</base>
         <bonus>500.00</bonus>
    </salary>
</Employee>');

You can create an index, for example called exists_bonus_idx, to check which employees have bonuses, by using the path /Employee/salary/fn:exists(bonus):

CREATE INDEX exists_bonus_idx ON 
   income(incomeinfo) GENERATE KEYS USING XMLPATTERN 
   '/Employee/salary/fn:exists(bonus)' AS SQL VARCHAR(1);

You can also create an index, for example called exists_any_attrib_idx, that checks for the existence of any attributes for the salary element, by using the path /Employee/salary/fn:exists(@*):

CREATE INDEX exists_any_attrib_idx ON 
   income(incomeinfo) GENERATE KEYS USING XMLPATTERN 
   '/Employee/salary/fn:exists(@*)' AS SQL VARCHAR(1);

Running queries that use these indexes

An index that was created using the fn:exists function is considered by the optimizer only when both of the following statements are true:

The path of the index pattern matches the XML path in the query predicate.
The query predicate performs a search for the element or attribute that is specified as the parameter of fn:exists in the CREATE INDEX statement

For the following query, the optimizer might choose to use the index, exists_bonus_idx, instead of performing a table scan, if it involves less work:

XQUERY db2-fn:xmlcolumn('INCOME.INCOMEINFO')
    /Employee/salary[fn:exists(bonus)];

--------------------------------
<salary type="regular"><base>5500.00</base><bonus>1000.00</bonus></salary>
<salary><base>2600.00</base><bonus>500.00</bonus></salary>

2 record(s) selected.

When this query is rewritten in the following form, the exists_bonus_idx index is also considered:

XQUERY db2-fn:xmlcolumn('INCOME.INCOMEINFO')/Employee/salary[bonus];

--------------------------------
<salary type="regular"><base>5500.00</base><bonus>1000.00</bonus></salary>
<salary><base>2600.00</base><bonus>500.00</bonus></salary>

2 record(s) selected.

The exists_bonus_idx index is considered for the following two queries, which find all employees who do not have a bonus:

XQUERY db2-fn:xmlcolumn('INCOME.INCOMEINFO')
   /Employee/salary[not(fn:exists(bonus))];

--------------------------------
<salary type="contractor"><base>7600.00</base></salary>

1 record(s) selected.

XQUERY db2-fn:xmlcolumn('INCOME.INCOMEINFO')
   /Employee/salary[fn:not(fn:exists(bonus))];
--------------------------------
<salary type="contractor"><base>7600.00</base></salary>

1 record(s) selected.

For the following query, the optimizer might choose to use the index, exists_any_attrib_idx. This index checks for any attributes for the salary element. In our example data, only the type attribute exists. Therefore, in this case, the query predicate is true only if the attribute @type exists in the XML path /Employee/salary:

XQUERY db2-fn:xmlcolumn('INCOME.INCOMEINFO')
   /Employee/salary[fn:exists(@type)];
--------------------------------
<salary type="regular"><base>5500.00</base><bonus>1000.00</bonus></salary>
<salary type= "contractor"><base>7600.00</base></salary>

2 record(s) selected.

The exists_any_attrib_idx index is also considered by the optimizer for queries written in the following forms:

XQUERY db2-fn:xmlcolumn('INCOME.INCOMEINFO')/Employee/salary[bonus and @type];

 --------------------------------
<salary type="regular"><base>5500.00</base><bonus>1000.00</bonus></salary>

1 record(s) selected.


XQUERY db2-fn:xmlcolumn('INCOME.INCOMEINFO')
  /Employee/salary[bonus and base > 3000];

 --------------------------------
<salary type="regular"><base>5500.00</base><bonus>1000.00</bonus></salary>

1 record(s) selected.


XQUERY db2-fn:xmlcolumn('INCOME.INCOMEINFO')
   /Employee/salary[bonus and bonus > 600];

 --------------------------------
<salary type="regular"><base>5500.00</base><bonus>1000.00</bonus></salary>

1 record(s) selected.


XQUERY db2-fn:xmlcolumn('INCOME.INCOMEINFO')
   /Employee/salary[bonus][bonus > 600];

 --------------------------------
<salary type="regular"><base>5500.00</base><bonus>1000.00</bonus></salary>

1 record(s) selected.


XQUERY for $e in db2-fn:xmlcolumn('INCOME.INCOMEINFO')
   /Employee/salary where $e/bonus return $e;

 --------------------------------
<salary type="regular"><base>5500.00</base><bonus>1000.00</bonus></salary>
<salary><base>2600.00</base><bonus>500.00</bonus></salary>

1 record(s) selected.

Indexes created with the UNIQUE keyword

For an index created with the UNIQUE keyword that also specifies the fn:exists function in the XMLPATTERN clause, the unique semantics constrain the appearance of xml-wildcard (and other syntax, such as "//" ) over only the context step of the index pattern, but not over the input argument of fn:exists. For example, the following statement is valid:

CREATE UNIQUE INDEX i2 ON tbx1(x1) GENERATE KEYS USING XMLPATTERN 
'/node/node1/fn:exists(*)' AS SQL VARCHAR(1)

Whereas, this second statement returns an error, because the context step of the index pattern is not unique:

CREATE UNIQUE INDEX i2 ON tbx1(x1) GENERATE KEYS USING XMLPATTERN 
'/node/*/fn:exists(a)' AS SQL VARCHAR(1)