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 the XANDOR node 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 IXAND node 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 recreating 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.