The right pane allows you to find out the average query time for the XML Query with index used in the previous page. You can also see the execution time of the same query with and without index in a graph.
If you are interested in looking at the explain plan and see how the index is being used, follow the steps below.
Explain Snapshot
Run the following steps on the DB2 CLP window to view the explain snapshot. If you have already created the explain tables, skip to step #5.
PerformanceXQuery.sql
and replace '~~~SCHEMA~~~' with the Default Schema.PerformanceXQuery.sql
DBNAME
-1 -o explain_xmlquery_ind.out
. Replace DBNAME
with the database you are working on. The Explain Plan can be viewed in the output file explain_xmlquery_ind.out
.Click here to view the sample output of the Explain Access Plan : Sample Explain Access Plan
Total cost captured in access plan
Review the explain output and locate the Total Cost
right above the access graph. The sample plan above shows a total cost of 37.86.
Operation in access plan
The access plan shows the XISCAN operator scanning the XML document with reference to the INDEX
and a TBSCAN scanning the relational data and then joining the result
set of two operations.
XISCAN operator
This operation is performed for a single query predicate. Its evaluation narrows down the qualifying return set of rows IDs and XML node IDs by range scanning any associated index over XML data before accessing the base table. The use of an index can improve the performance of a query because the compiler determines whether and how to use index information to complete the query. This typically results in: