Wednesday, February 01, 2006

Using EXPLAIN in DB2

Using EXPLAIN to improve SQL performance
The information under this heading, up to the end of this chapter, is
Product-sensitive Programming Interface and Associated Guidance Information, as
defined in Appendix I, “Notices” on page 893.
Definitions and purpose: EXPLAIN is a monitoring tool that produces information
about the following:
. A plan, package, or SQL statement when it is bound. The output appears in a
table you create called PLAN_TABLE, which is called a plan table. For
experienced users, you can use PLAN_TABLE to give optimization hints to
DB2.
| . An estimated cost of executing an SQL SELECT, INSERT, UPDATE, or
| DELETE statement. The output appears in a table you create called
| DSN_STATEMNT_TABLE, which is called a statement table. For more
| information about statement tables, see “Estimating a statement's cost” on
| page 679.
| . User-defined functions referred to in the statement, including the specific name
| and schema. The output appears in a table you create called
| DSN_FUNCTION_TABLE, which is called a function table. For more
| information about function tables, see “Ensuring that DB2 executes the
| intended user-defined function” on page 294.
Other tools: Other tools that can help you tune SQL queries include:
. DB2 Visual Explain
Visual Explain is a graphical workstation feature of DB2 that provides:
– An easy-to-understand display of a selected access path
– Suggestions for changing an SQL statement
– An ability to invoke EXPLAIN for dynamic SQL statements
– An ability to provide DB2 catalog statistics for referenced objects of an
access path
– A subsystem parameter browser with keyword 'Find' capabilities
For information on using DB2 Visual Explain, which is a separately packaged
CD-ROM provided with your DB2 Version 6 license, see DB2 Visual Explain
online help.
. DB2 Performance Monitor (PM)
DB2 PM is a performance monitoring tool that formats performance data. DB2
PM combines information from EXPLAIN and from the DB2 catalog. It displays
access paths, indexes, tables, table spaces, plans, packages, DBRMs, host
variable definitions, ordering, table access and join sequences, and lock types.
Output is presented in a dialog rather than as a table, making the information
easy to read and understand.
. DB2 Estimator
| DB2 Estimator for Windows is an easy-to-use, stand-alone tool for estimating
| the performance of DB2 for OS/390 applications. You can use it to predict the
| performance and cost of running the applications, transactions, SQL
Ò Copyright IBM Corp. 1983, 1999 637
| statements, and utilities. For instance, you can use DB2 Estimator for
| estimating the impact of adding or dropping an index from a table, estimating
| the change in response time from adding processor resources, and estimating
| the amount of time a utility job will take to run.
Chapter overview: This chapter includes the following topics:
. “Obtaining PLAN_TABLE information from EXPLAIN”
| . “Estimating a statement's cost” on page 679
. “First questions about data access” on page 645
. “Interpreting access to a single table” on page 655
. “Interpreting access to two or more tables” on page 661
. “Interpreting data prefetch” on page 669
. “Determining sort activity” on page 674
. “Processing for views and nested table expressions” on page 675
. “ Chapter 7-5. Parallel operations and query performance” on page 685
Obtaining PLAN_TABLE information from EXPLAIN
The information in PLAN_TABLE can help you to:
. Design databases, indexes, and application programs
. Determine when to rebind an application
. Determine the access path chosen for a query
For each access to a single table, EXPLAIN tells you if an index access or table
space scan is used. If indexes are used, EXPLAIN tells you how many indexes and
index columns are used and what I/O methods are used to read the pages. For
joins of tables, EXPLAIN tells you the join method and type, the order in which DB2
joins the tables, and when and why it sorts any rows.
The primary use of EXPLAIN is to observe the access paths for the SELECT parts
of your statements. For UPDATE and DELETE WHERE CURRENT OF, and for
INSERT, you receive somewhat less information in your plan table. And some
| accesses EXPLAIN does not describe: for example, the access to LOB values,
| which are stored separately from the base table, and access to parent or
dependent tables needed to enforce referential constraints.
The access paths shown for the example queries in this chapter are intended only
to illustrate those examples. If you execute the queries in this chapter on your
system, the access paths chosen can be different.
Steps to obtain PLAN_TABLE information: The summary of steps to obtain
information from EXPLAIN is as follows:
1. Have appropriate access to a plan table. To create the table, see “Creating
PLAN_TABLE” on page 639.
2. Populate the table with the information you want. For instructions, see
“Populating and maintaining a plan table” on page 644.
3. Select the information you want from the table. For instructions, see
“Reordering rows from a plan table” on page 645.
638 Application Programming and SQL Guide
Creating PLAN_TABLE
Before you can use EXPLAIN, you must create a table called PLAN_TABLE to hold
the results of EXPLAIN. A copy of the statements needed to create the table are in
| the DB2 sample library, under the member name DSNTESC. (Unless you need the
| information they provide, it is not necessary to create a function table or statement
| table to use EXPLAIN.)
Figure 168 shows the format of a plan table. Table 68 on page 640 shows the
content of each column.
Your plan table can use many formats, but use the 49-column format because it
gives you the most information. If you alter an existing plan table to add new
columns, specify the columns as NOT NULL WITH DEFAULT, so that default
values are included for the rows already in the table. However, as you can see in
Figure 168, certain columns do allow nulls. Do not specify those columns as NOT
NULL WITH DEFAULT.
QUERYNO INTEGER NOT NULL PREFETCH CHAR(1) NOT NULL WITH DEFAULT
QBLOCKNO SMALLINT NOT NULL COLUMN_FN_EVAL CHAR(1) NOT NULL WITH DEFAULT
APPLNAME CHAR(8) NOT NULL MIXOPSEQ SMALLINT NOT NULL WITH DEFAULT
PROGNAME CHAR(8) NOT NULL -------28 column format --------
PLANNO SMALLINT NOT NULL VERSION VARCHAR(64) NOT NULL WITH DEFAULT
METHOD SMALLINT NOT NULL COLLID CHAR(18) NOT NULL WITH DEFAULT
CREATOR CHAR(8) NOT NULL -------3ð column format --------
TNAME CHAR(18) NOT NULL ACCESS_DEGREE SMALLINT
TABNO SMALLINT NOT NULL ACCESS_PGROUP_ID SMALLINT
ACCESSTYPE CHAR(2) NOT NULL JOIN_DEGREE SMALLINT
MATCHCOLS SMALLINT NOT NULL JOIN_PGROUP_ID SMALLINT
ACCESSCREATOR CHAR(8) NOT NULL -------34 column format --------
ACCESSNAME CHAR(18) NOT NULL SORTC_PGROUP_ID SMALLINT
INDEXONLY CHAR(1) NOT NULL SORTN_PGROUP_ID SMALLINT
SORTN_UNIQ CHAR(1) NOT NULL PARALLELISM_MODE CHAR(1)
SORTN_JOIN CHAR(1) NOT NULL MERGE_JOIN_COLS SMALLINT
SORTN_ORDERBY CHAR(1) NOT NULL CORRELATION_NAME CHAR(18)
SORTN_GROUPBY CHAR(1) NOT NULL PAGE_RANGE CHAR(1) NOT NULL WITH DEFAULT
SORTC_UNIQ CHAR(1) NOT NULL JOIN_TYPE CHAR(1) NOT NULL WITH DEFAULT
SORTC_JOIN CHAR(1) NOT NULL GROUP_MEMBER CHAR(8) NOT NULL WITH DEFAULT
SORTC_ORDERBY CHAR(1) NOT NULL IBM_SERVICE_DATA VARCHAR(254) NOT NULL WITH DEFAULT
SORTC_GROUPBY CHAR(1) NOT NULL ------43 column format --------
TSLOCKMODE CHAR(3) NOT NULL WHEN_OPTIMIZE CHAR(1) NOT NULL WITH DEFAULT
TIMESTAMP CHAR(16) NOT NULL QBLOCK_TYPE CHAR(6) NOT NULL WITH DEFAULT
REMARKS VARCHAR(254) NOT NULL BIND_TIME TIMESTAMP NOT NULL WITH DEFAULT
-------25 column format -------- ------46 column format -----------
OPTHINT CHAR(8) NOT NULL WITH DEFAULT
HINT_USED CHAR(8) NOT NULL WITH DEFAULT
PRIMARY_ACCESSTYPE CHAR(1) NOT NULL WITH DEFAULT
-------49 column format-----------
| Figure 168. Format of PLAN_TABLE
Chapter 7-4. Using EXPLAIN to improve SQL performance 639
Table 68 (Page 1 of 4). Descriptions of columns in PLAN_TABLE
Column Name Description
QUERYNO | A number intended to identify the statement being explained. For a row produced by
| an EXPLAIN statement, you can specify the number in the SET QUERYNO clause.
| For a row produced by non-EXPLAIN statements, you can specify the number using
| the SET QUERYNO clause, which is an optional part of the SELECT, INSERT,
| UPDATE and DELETE statement syntax. Otherwise, DB2 assigns a number based
| on the line number of the SQL statement in the source program.
| FETCH statements do not each have an individual QUERYNO assigned to them.
| Instead, DB2 uses the QUERYNO of the DECLARE CURSOR statement for all
| corresponding FETCH statements for that cursor.
| When the values of QUERYNO are based on the statement number in the source
| program, values greater than 32767 are reported as 0. Hence, in a very long
| program, the value is not guaranteed to be unique. If QUERYNO is not unique, the
| value of TIMESTAMP is unique.
QBLOCKNO The position of the query in the statement being explained (1 for the outermost query,
2 for the next query, and so forth). For better performance, DB2 might merge a query
block into another query block. When that happens, the position number of the
merged query block will not be in QBLOCKNO.
APPLNAME The name of the application plan for the row. Applies only to embedded EXPLAIN
statements executed from a plan or to statements explained when binding a plan.
Blank if not applicable.
PROGNAME The name of the program or package containing the statement being explained.
Applies only to embedded EXPLAIN statements and to statements explained as the
result of binding a plan or package. Blank if not applicable.
PLANNO The number of the step in which the query indicated in QBLOCKNO was processed.
This column indicates the order in which the steps were executed.
METHOD A number (0, 1, 2, 3, or 4) that indicates the join method used for the step:
0 First table accessed, continuation of previous table accessed, or not used.
1 Nested loop join. For each row of the present composite table, matching
rows of a new table are found and joined.
2 Merge scan join. The present composite table and the new table are
scanned in the order of the join columns, and matching rows are joined.
3 Sorts needed by ORDER BY, GROUP BY, SELECT DISTINCT, UNION, a
quantified predicate, or an IN predicate. This step does not access a new
table.
4 Hybrid join. The current composite table is scanned in the order of the
join-column rows of the new table. The new table is accessed using list
prefetch.
CREATOR The creator of the new table accessed in this step; blank if METHOD is 3.
TNAME The name of a table, temporary table, materialized view, table expression, or an
intermediate result table for an outer join that is accessed in this step; blank if
METHOD is 3.
For an outer join, this column contains the temporary table name of the work file in
the form DSNWFQB( qblockno). Merged views show the base table names and
correlation names. A materialized view is another query block with its own
materialized views, tables, and so forth.
TABNO Values are for IBM use only.
640 Application Programming and SQL Guide
Table 68 (Page 2 of 4). Descriptions of columns in PLAN_TABLE
Column Name Description
ACCESSTYPE The method of accessing the new table:
I By an index (identified in ACCESSCREATOR and ACCESSNAME)
I1 By a one-fetch index scan
N By an index scan when the matching predicate contains the IN keyword
R By a table space scan
M By a multiple index scan; followed by MX, MI, or MU
MX By an index scan on the index named in ACCESSNAME
MI By an intersection of multiple indexes
MU By a union of multiple indexes
blank Not applicable to the current row.
MATCHCOLS For ACCESSTYPE I, I1, N, or MX, the number of index keys used in an index scan;
otherwise, 0.
ACCESSCREATOR For ACCESSTYPE I, I1, N, or MX, the creator of the index; otherwise, blank.
ACCESSNAME For ACCESSTYPE I, I1, N, or MX, the name of the index; otherwise, blank.
INDEXONLY Whether access to an index alone is enough to carry out the step, or whether data
too must be accessed. Y=Yes; N=No.
SORTN_UNIQ Whether the new table is sorted to remove duplicate rows. Y=Yes; N=No.
SORTN_JOIN Whether the new table is sorted for join method 2 or 4. Y=Yes; N=No.
SORTN_ORDERBY Whether the new table is sorted for ORDER BY. Y=Yes; N=No.
SORTN_GROUPBY Whether the new table is sorted for GROUP BY. Y=Yes; N=No.
SORTC_UNIQ Whether the composite table is sorted to remove duplicate rows. Y=Yes; N=No.
SORTC_JOIN Whether the composite table is sorted for join method 1, 2 or 4. Y=Yes; N=No.
SORTC_ORDERBY Whether the composite table is sorted for an ORDER BY clause or a quantified
predicate. Y=Yes; N=No.
SORTC_GROUPBY Whether the composite table is sorted for a GROUP BY clause. Y=Yes; N=No.
TSLOCKMODE An indication of the mode of lock to be acquired on either the new table, or its table
space or table space partitions. If the isolation can be determined at bind time, the
values are:
IS Intent share lock
IX Intent exclusive lock
S Share lock
U Update lock
X Exclusive lock
SIX Share with intent exclusive lock
N UR isolation; no lock
If the isolation cannot be determined at bind time, then the lock mode determined by
the isolation at run time is shown by the following values.
NS For UR isolation, no lock; for CS, RS, or RR, an S lock.
NIS For UR isolation, no lock; for CS, RS, or RR, an IS lock.
NSS For UR isolation, no lock; for CS or RS, an IS lock; for RR, an S lock.
SS For UR, CS, or RS isolation, an IS lock; for RR, an S lock.
The data in this column is right justified. For example, IX appears as a blank followed
by I followed by X. If the column contains a blank, then no lock is acquired.
Chapter 7-4. Using EXPLAIN to improve SQL performance 641
Table 68 (Page 3 of 4). Descriptions of columns in PLAN_TABLE
Column Name Description
TIMESTAMP Usually, the time at which the row is processed, to the last .01 second. If necessary,
DB2 adds .01 second to the value to ensure that rows for two successive queries
have different values.
REMARKS A field into which you can insert any character string of 254 or fewer characters.
PREFETCH Whether data pages are to be read in advance by prefetch. S = pure sequential
prefetch; L = prefetch through a page list; blank = unknown or no prefetch.
COLUMN_FN_EVAL When an SQL column function is evaluated. R = while the data is being read from
the table or index; S = while performing a sort to satisfy a GROUP BY clause; blank
= after data retrieval and after any sorts.
MIXOPSEQ The sequence number of a step in a multiple index operation.
1, 2, ... n For the steps of the multiple index procedure (ACCESSTYPE is MX,
MI, or MU.)
0 For any other rows (ACCESSTYPE is I, I1, M, N, R, or blank.)
VERSION The version identifier for the package. Applies only to an embedded EXPLAIN
statement executed from a package or to a statement that is explained when binding
a package. Blank if not applicable.
COLLID The collection ID for the package. Applies only to an embedded EXPLAIN statement
executed from a package or to a statement that is explained when binding a
package. Blank if not applicable.
Note: The following nine columns, from ACCESS_DEGREE through CORRELATION_NAME, contain the null value if
the plan or package was bound using a plan table with fewer than 43 columns. Otherwise, each of them can
contain null if the method it refers to does not apply.
ACCESS_DEGREE The number of parallel tasks or operations activated by a query. This value is
determined at bind time; the actual number of parallel operations used at execution
time could be different. This column contains 0 if there is a host variable.
ACCESS_PGROUP_ID The identifier of the parallel group for accessing the new table. A parallel group is a
set of consecutive operations, executed in parallel, that have the same number of
parallel tasks. This value is determined at bind time; it could change at execution
time.
JOIN_DEGREE The number of parallel operations or tasks used in joining the composite table with
the new table. This value is determined at bind time, and can be 0 if there is a host
variable. The actual number of parallel operations or tasks used at execution time
could be different.
JOIN_PGROUP_ID The identifier of the parallel group for joining the composite table with the new table.
This value is determined at bind time; it could change at execution time.
SORTC_PGROUP_ID The parallel group identifier for the parallel sort of the composite table.
SORTN_PGROUP_ID The parallel group identifier for the parallel sort of the new table.
PARALLELISM_MODE The kind of parallelism, if any, that is used at bind time:
I Query I/O parallelism
C Query CP parallelism
X Sysplex query parallelism
MERGE_JOIN_COLS The number of columns that are joined during a merge scan join (Method=2).
CORRELATION_NAME The correlation name of a table or view that is specified in the statement. If there is
no correlation name then the column is blank.
PAGE_RANGE Whether the table qualifies for page range screening, so that plans scan only the
partitions that are needed. Y = Yes; blank = No.
642 Application Programming and SQL Guide
Table 68 (Page 4 of 4). Descriptions of columns in PLAN_TABLE
Column Name Description
JOIN_TYPE The type of an outer join.
F FULL OUTER JOIN
L LEFT OUTER JOIN
blank INNER JOIN or no join
RIGHT OUTER JOIN converts to a LEFT OUTER JOIN when you use it, so that
JOIN_TYPE contains L.
GROUP_MEMBER The member name of the DB2 that executed EXPLAIN. The column is blank if the
DB2 subsystem was not in a data sharing environment when EXPLAIN was
executed.
IBM_SERVICE_DATA Values are for IBM use only.
WHEN_OPTIMIZE When the access path was determined:
blank At bind time, using a default filter factor for any host variables, parameter
markers, or special registers.
B At bind time, using a default filter factor for any host variables, parameter
markers, or special registers; however the statement will be reoptimized at
run time using input variable values for input host variables, parameter
markers, or special registers. The bind option REOPT(VARS) must be
specified for reoptimization to occur.
R At run time, using input variables for any host variables, parameter markers,
or special registers. The bind option REOPT(VARS) must be specified for
this to occur.
QBLOCK_TYPE For each query block, the type of SQL operation performed. For the outermost query,
it identifies the statement type. Possible values:
SELECT SELECT
INSERT INSERT
UPDATE UPDATE
DELETE DELETE
SELUPD SELECT with FOR UPDATE OF
DELCUR DELETE WHERE CURRENT OF CURSOR
UPDCUR UPDATE WHERE CURRENT OF CURSOR
CORSUB Correlated subquery
NCOSUB Noncorrelated subquery
BIND_TIME The time at which the plan or package for this statement or query block was bound.
For static SQL statements, this is a full-precision timestamp value. For dynamic SQL
statements, this is the value contained in the TIMESTAMP column of PLAN_TABLE
appended by 4 zeroes.
| OPTHINT A string that you use to identify this row as an optimization hint for DB2. DB2 uses
| this row as input when choosing an access path.
| HINT_USED If DB2 used one of your optimization hints, it puts the identifier for that hint (the value
| in OPTHINT) in this column.
| PRIMARY_ACCESSTYPE Indicates whether direct row access will be attempted first:
| D DB2 will try to use direct row access. If DB2 cannot use direct row access at
| runtime, it uses the access path described in the ACCESSTYPE column of
| PLAN_TABLE.
| blank DB2 will not try to use direct row access.
Chapter 7-4. Using EXPLAIN to improve SQL performance 643
Populating and maintaining a plan table
For the two distinct ways to populate a plan table, see:
. “Execute the SQL statement EXPLAIN”
. “Bind with the option EXPLAIN(YES)”
| When you populate the plan table through DB2's EXPLAIN, any INSERT triggers
| on the table are not activated. If you insert rows yourself, then those triggers are
| activated.
For tips on maintaining a growing plan table, see “Maintaining a plan table.”
Execute the SQL statement EXPLAIN
You can populate PLAN_TABLE by executing the SQL statement EXPLAIN. In the
statement, specify a single explainable SQL statement in the FOR clause.
You can execute EXPLAIN either statically from an application program, or
dynamically, using QMF or SPUFI. For instructions and for details of the
authorization you need on PLAN_TABLE, see DB2 SQL Reference.
Bind with the option EXPLAIN(YES)
You can populate a plan table when you bind or rebind a plan or package. Specify
the option EXPLAIN(YES). EXPLAIN obtains information about the access paths for
all explainable SQL statements in a package or the DBRMs of a plan. The
information appears in table package_owner.PLAN_TABLE or
plan_owner.PLAN_TABLE. For dynamically prepared SQL, the qualifier of
PLAN_TABLE is the current SQLID.
Performance considerations: EXPLAIN as a bind option should not be a
performance concern. The same processing for access path selection is performed,
regardless of whether you use EXPLAIN(YES) or EXPLAIN (NO). With
EXPLAIN(YES), there is only a small amount of overhead processing to put the
results in a plan table.
If a plan or package that was previously bound with EXPLAIN(YES) is automatically
rebound, the value of field EXPLAIN PROCESSING on installation panel DSNTIPO
determines whether EXPLAIN is run again during the automatic rebind. Again, there
is a small amount of overhead for inserting the results into a plan table.
EXPLAIN for remote binds: A remote requester that accesses DB2 can specify
EXPLAIN(YES) when binding a package at the DB2 server. The information
appears in a plan table at the server, not at the requester. If the requester does not
support the propagation of the option EXPLAIN(YES), rebind the package at the
requester with that option to obtain access path information. You cannot get
information about access paths for SQL statements that use private protocol.
Maintaining a plan table
DB2 adds rows to PLAN_TABLE as you choose; it does not automatically delete
rows. To clear the table of obsolete rows, use DELETE, just as you would for
deleting rows from any table. You can also use DROP TABLE to drop a plan table
completely.
644 Application Programming and SQL Guide
Reordering rows from a plan table
Several processes can insert rows into the same plan table. To understand access
paths, you must retrieve the rows for a particular query in an appropriate order.
Retrieving rows for a plan
The rows for a particular plan are identified by the value of APPLNAME. The
following query to a plan table returns the rows for all the explainable statements in
a plan in their logical order:
SELECT \ FROM JOE.PLAN_TABLE
WHERE APPLNAME = 'APPL1'
ORDER BY TIMESTAMP, QUERYNO, QBLOCKNO, PLANNO, MIXOPSEQ;
The result of the ORDER BY clause shows whether there are:
. Multiple QBLOCKNOs within a QUERYNO
. Multiple PLANNOs within a QBLOCKNO
. Multiple MIXOPSEQs within a PLANNO
All rows with the same non-zero value for QBLOCKNO and the same value for
QUERYNO relate to a step within the query. QBLOCKNOs are not necessarily
executed in the order shown in PLAN_TABLE. But within a QBLOCKNO, the
PLANNO column gives the substeps in the order they execute.
For each substep, the TNAME column identifies the table accessed. Sorts can be
shown as part of a table access or as a separate step.
What if QUERYNO=0? In a program with more than 32767 lines, all values of
QUERYNO greater than 32767 are reported as 0. For entries containing
QUERYNO=0, use the timestamp, which is guaranteed to be unique, to distinguish
individual statements.
Retrieving rows for a package
The rows for a particular package are identified by the values of PROGNAME,
COLLID, and VERSION. Those columns correspond to the following four-part
naming convention for packages:
LOCATION.COLLECTION.PACKAGE_ID.VERSION
COLLID gives the COLLECTION name, and PROGNAME gives the PACKAGE_ID.
The following query to a plan table return the rows for all the explainable
statements in a package in their logical order:
SELECT \ FROM JOE.PLAN_TABLE
WHERE PROGNAME = 'PACK1' AND COLLID = 'COLL1' AND VERSION = 'PROD1'
ORDER BY QUERYNO, QBLOCKNO, PLANNO, MIXOPSEQ;
First questions about data access
When you examine your EXPLAIN results, try to answer the following questions:
. “Is access through an index? (ACCESSTYPE is I, I1, N or MX)” on page 646
. “Is access through more than one index? (ACCESSTYPE=M)” on page 646
. “How many columns of the index are used in matching? (MATCHCOLS=n)” on
page 647
. “Is the query satisfied using only the index? (INDEXONLY=Y)” on page 648
Chapter 7-4. Using EXPLAIN to improve SQL performance 645
| . “Is direct row access possible? (PRIMARY_ACCESSTYPE = D)” on page 648
. “Is a view or nested table expression materialized?” on page 652
. “Was a scan limited to certain partitions? (PAGE_RANGE=Y)” on page 652
. “What kind of prefetching is done? (PREFETCH = L, S, or blank)” on page 653
. “Is data accessed or processed in parallel? (PARALLELISM_MODE is I, C, or
X)” on page 653
. “Are sorts performed?” on page 654
. “Is a subquery transformed into a join?” on page 654
. “When are column functions evaluated? (COLUMN_FN_EVAL)” on page 654
As explained in this section, they can be answered in terms of values in columns of
a plan table.
Is access through an index? (ACCESSTYPE is I, I1, N or MX)
If the column ACCESSTYPE in the plan table has one of those values, DB2 uses
an index to access the table named in column TNAME. The columns
ACCESSCREATOR and ACCESSNAME identify the index. For a description of
methods of using indexes, see “Index access paths” on page 656.
| Is access through more than one index? (ACCESSTYPE=M)
Those values indicate that DB2 uses a set of indexes to access a single table. A
set of rows in the plan table contain information about the multiple index access.
The rows are numbered in column MIXOPSEQ in the order of execution of steps in
the multiple index access. (If you retrieve the rows in order by MIXOPSEQ, the
result is similar to postfix arithmetic notation.)
The examples in Figure 169 and Figure 170 on page 647 have these indexes: IX1
on T(C1) and IX2 on T(C2). DB2 processes the query in these steps:
| 1. Retrieve all the qualifying record identifiers (RIDs) where C1=1, using index
| IX1.
2. Retrieve all the qualifying RIDs where C2=1, using index IX2. The intersection
of those lists is the final set of RIDs.
3. Access the data pages needed to retrieve the qualified rows using the final RID
list.
SELECT \ FROM T
WHERE C1 = 1 AND C2 = 1;
Figure 169. PLAN_TABLE output for example with intersection (AND) operator
TNAME
ACCESSTYPE
MATCHCOLS
ACCESSNAME
INDEXONLY
PREFETCH
MIXOPSEQ
T M 0 N L 0
T MX 1 IX1 Y 1
T MX 1 IX2 Y 2
T MI 0 N 3
646 Application Programming and SQL Guide
The same index can be used more than once in a multiple index access, because
more than one predicate could be matching, as in Figure 170 on page 647.
SELECT \ FROM T
WHERE C1 BETWEEN 1ðð AND 199 OR
C1 BETWEEN 5ðð AND 599;
Figure 170. PLAN_TABLE output for example with Union (OR) Operator
TNAME
ACCESSTYPE
MATCHCOLS
ACCESSNAME
INDEXONLY
PREFETCH
MIXOPSEQ
T M 0 N L 0
T MX 1 IX1 Y 1
T MX 1 IX1 Y 2
T MU 0 N 3
The steps are:
1. Retrieve all RIDs where C1 is between 100 and 199, using index IX1.
2. Retrieve all RIDs where C1 is between 500 and 599, again using IX1. The
union of those lists is the final set of RIDs.
3. Retrieve the qualified rows using the final RID list.
| How many columns of the index are used in matching?
| (MATCHCOLS=n)
If MATCHCOLS is 0, the access method is called a nonmatching index scan. All
the index keys and their RIDs are read.
If MATCHCOLS is greater than 0, the access method is called a matching index
scan: the query uses predicates that match the index columns.
In general, the matching predicates on the leading index columns are equal or IN
predicates. The predicate that matches the final index column can be an equal, IN,
or range predicate (<, <=, >, >=, LIKE, or BETWEEN).
The following example illustrates matching predicates:
SELECT \ FROM EMP
WHERE JOBCODE = '5' AND SALARY > 6ðððð AND LOCATION = 'CA';
INDEX XEMP5 on (JOBCODE, LOCATION, SALARY, AGE);
The index XEMP5 is the chosen access path for this query, with MATCHCOLS = 3.
There are two equal predicates on the first two columns and a range predicate on
the third column. Though there are four columns in the index, only three of them
can be considered matching columns.
Chapter 7-4. Using EXPLAIN to improve SQL performance 647
Is the query satisfied using only the index? (INDEXONLY=Y)
In this case, the method is called index-only access. For a SELECT operation, all
the columns needed for the query can be found in the index and DB2 does not
access the table. For an UPDATE or DELETE operation, only the index is required
to read the selected row.
| Index-only access to data is not possible for any step that uses list prefetch
| (described under “What kind of prefetching is done? (PREFETCH = L, S, or blank)”
| on page 653. Index-only access is not possible when returning varying-length data
| in the result set or a VARCHAR column has a LIKE predicate, unless the
| VARCHAR FROM INDEX field of installation panel DSNTIP4 is set to YES and
| plan or packages have been rebound to pick up the change. See Section 2 of DB2
| Installation Guide for more information.
If access is by more than one index, INDEXONLY is Y for a step with access type
MX, because the data pages are not actually accessed until all the steps for
intersection (MI) or union (MU) take place.
| When an SQL application uses index-only access for a ROWID column, the
| application claims the table space or table space partition. As a result, contention
| may occur between the SQL application and a utility that drains the table space or
| partition. Index-only access to a table for a ROWID column is not possible if the
| associated table space or partition is in an incompatible restrictive state. For
| example, an SQL application can make a read claim on the table space only if the
| restrictive state allows readers.

2 Comments:

Blogger Jk said...

Good explanation ... Nice job.

10:56 AM  
Blogger Sagi said...

Execellent information..Thanks a lot.

10:04 PM  

Post a Comment

<< Home