When a Query Access Plan is displayed using either the db2exfmt
utility, or the IBM Optim(TM) family of Database tools, such as
visual explain or
the access plan graph or access plan explorer features under
Optim Query Workload Tuner, the
RowID column (shown as $RID$ in the column list) is not included
in the list of columns that are retrieved from an index and used
either in Index ANDing, Index ORing, Starjoin IXA, or Fetch
operations. In some cases, if the $RID$ is the only column
returned
by these operators, it appears in the current version of the
explains that no
columns are output from these operators. The need is to get the
$RID$ column
passed through these operators so they can indicate that output
columns are provided.
In the following extract of db2exfmt output, prior to this fix,
the Q4.$RID$ column was missing:
Input Streams:
-------------
8) From Object FINLAY.FX4
Estimated number of rows: 1e+06
Number of columns: 2
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q4."D4"(A)+Q4.$RID$
Output Streams:
--------------
9) To Operator #19
Estimated number of rows: 10
Number of columns: 2
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q4."D4"(A)+Q4.$RID$
This missing column can lead to a number of issues with the
various tools:
1) db2exfmt cannot indicate the quantifier number "Qxxx" in the
query graph because no columns may show as flowing from an Index
Scan (IXSCAN) operator.
2) Optim Query Workload Tuner may not be able to perform column
analysis
functions because either extra, unnecessary columns are shown
being retrieved from the index, or columns may be missing from
Explain.
This fix only affects the output from the Explain Facility for
DB2 for Linux, Unix and Windows output, it does not in any way
affect or indicate a problem or issue with the query access
plan. For IXSCAN operators, the list of columns shown in explain
is dervied for Explain purposes only and does not impact the
plan execution. |
