XANDOR (XML index anding) node

This node allows ANDed predicates to be applied to multiple indexes to reduce underlying table accesses to a minimum.

Node name: XANDOR

Represents: The index over XML data ANDing of the results of multiple index scans, used for the evaluation of complex predicates from a single query.

In order for this operation to be used, the following conditions must be met:
  • Only equality predicates are used.
  • There are no wildcards in the index lookup path.
  • All predicates are used on the same XML column.

If any of these conditions are not met the Index intersection operation will be used instead.

An access plan with multiple XANDORed index over XML data scans as shown by the db2exfmt tool might look like this:
                                          Rows 
                                         RETURN 
                                         (   1) 
                                          Cost 
                                           I/O 
                                           |
                                       0.00915933 
                                         NLJOIN 
                                         (   2) 
                                         985.789 
                                         98.9779 
                                         /--+--\
                                    2.96215  0.00309213 
                                    FETCH      XSCAN  
                                    (   3)     (  11) 
                                    340.113    217.976 
                                      19         27 
                                   /---+---\
                              2.96215      210000 
                              RIDSCN   TABLE: DB2XML   
                              (   4)        TPCHX 
                              332.008 
                                18 
                                |
                              2.96215 
                              SORT   
                              (   5) 
                              331.957 
                                18 
                                |
                              2.96215 
                              XANDOR 
                              (   6) 
                              331.784 
                                18 
       +----------------+--------+-------+----------------+
     355.62           6996.81          105000           105000 
     XISCAN           XISCAN           XISCAN           XISCAN 
     (   7)           (   8)           (   9)           (  10) 
     165.892          3017.54        1.6473e+06         851554 
        9               81              27768            14898 
       |                |                |                |
     210000           210000           210000           210000 
 XMLIN: DB2XML    XMLIN: DB2XML    XMLIN: DB2XML    XMLIN: DB2XML   
    TPCHX_IDX        TPCHX_IDX        TPCHX_IDX        TPCHX_IDX 

Each XISCAN node will perform an index scan and feed the XANDOR node with the XML node IDs that qualify. The XANDOR node will apply the AND and OR predicates and return the XML nodes that satisfy the XML pattern for the query.

Performance suggestions:
  • Over time, database updates can cause an index to become fragmented, resulting in more index pages than necessary. This can be corrected by dropping and re-creating the index, or by reorganizing it.
  • If statistics are not current, update them using the RUNSTATS command.
  • In general, index scans are most effective when only a few rows qualify. To estimate the number of qualifying rows, the optimizer uses the statistics that are available for the columns referenced in predicates. If some values occur more frequently than others, it is important to request distribution statistics by using the WITH DISTRIBUTION clause with the RUNSTATS command. By using the non-uniform distribution statistics, the optimizer can distinguish among frequently and infrequently occurring values.

Feedback