Virtual Indexes for Query performance optimization, in DB2 v10 for z/OS
The table DSN_VIRTUAL_INDEXES, enables Optimization Tools to
test the effect of creation and/or dropping of Indices on the performance of SQL
queries.
Optimizer tools like Data Studio Query Optimizer or Dell SQL
optimizer will make an entries into DSN_VIRTUAL_INDEXES Table when trying to
optimize the SQL query performance. It’s not advised to make entries into the Table manually.
We don’t have any SQL query optimizer tool installed, and I
wanted to test whether an Index creation on the Table will improve the SQL
query performance. For that, I did the following.
Step 1: I created DSN_VIRTUAL_INDEXES Table.
DDL for DSN_VIRTUAL_INDEXES Table can be found in SDSNSAMP
library. Below is the DDL I used for the creation of Table.
CREATE TABLE
RFO7936.DSN_VIRTUAL_INDEXES (
TBCREATOR VARCHAR(128)
FOR MIXED DATA NOT NULL,
TBNAME VARCHAR(128)
FOR MIXED DATA NOT NULL,
IXCREATOR VARCHAR(128)
FOR MIXED DATA NOT NULL,
IXNAME VARCHAR(128)
FOR MIXED DATA NOT NULL,
ENABLE CHARACTER(1)
FOR MIXED DATA NOT NULL,
MODE CHARACTER(1)
FOR MIXED DATA NOT NULL,
UNIQUERULE CHARACTER(1)
FOR MIXED DATA NOT NULL,
COLCOUNT SMALLINT NOT NULL,
CLUSTERING CHARACTER(1)
FOR MIXED DATA NOT NULL,
NLEAF INTEGER NOT NULL,
NLEVELS SMALLINT NOT NULL,
INDEXTYPE CHARACTER(1)
FOR MIXED DATA NOT NULL WITH DEFAULT,
PGSIZE SMALLINT NOT NULL,
FIRSTKEYCARDF DOUBLE NOT NULL DEFAULT -1,
FULLKEYCARDF DOUBLE NOT NULL DEFAULT -1,
CLUSTERRATIOF DOUBLE NOT NULL DEFAULT -1,
PADDED CHARACTER(1)
FOR MIXED DATA NOT NULL WITH DEFAULT,
COLNO1 SMALLINT WITH DEFAULT NULL,
ORDERING1 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO2 SMALLINT WITH DEFAULT NULL,
ORDERING2 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO3 SMALLINT WITH DEFAULT NULL,
ORDERING3 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO4 SMALLINT WITH DEFAULT NULL,
ORDERING4 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO5 SMALLINT WITH DEFAULT NULL,
ORDERING5 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO6 SMALLINT WITH DEFAULT NULL,
ORDERING6 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO7 SMALLINT WITH DEFAULT NULL,
ORDERING7 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO8 SMALLINT WITH DEFAULT NULL,
ORDERING8 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO9 SMALLINT WITH DEFAULT NULL,
ORDERING9 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO10 SMALLINT WITH DEFAULT NULL,
ORDERING10 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO11 SMALLINT WITH DEFAULT NULL,
ORDERING11 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO12 SMALLINT WITH DEFAULT NULL,
ORDERING12 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO13 SMALLINT WITH DEFAULT NULL,
ORDERING13 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO14 SMALLINT WITH DEFAULT NULL,
ORDERING14 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO15 SMALLINT WITH DEFAULT NULL,
ORDERING15 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO16 SMALLINT WITH DEFAULT NULL,
ORDERING16 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO17 SMALLINT WITH DEFAULT NULL,
ORDERING17 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO18 SMALLINT WITH DEFAULT NULL,
ORDERING18 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO19 SMALLINT WITH DEFAULT NULL,
ORDERING19 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO20 SMALLINT WITH DEFAULT NULL,
ORDERING20 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO21 SMALLINT WITH DEFAULT NULL,
ORDERING21 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO22 SMALLINT WITH DEFAULT NULL,
ORDERING22 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO23 SMALLINT WITH DEFAULT NULL,
ORDERING23 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO24 SMALLINT WITH DEFAULT NULL,
ORDERING24 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO25 SMALLINT WITH DEFAULT NULL,
ORDERING25 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO26 SMALLINT WITH DEFAULT NULL,
ORDERING26 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO27 SMALLINT WITH DEFAULT NULL,
ORDERING27 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO28 SMALLINT WITH DEFAULT NULL,
ORDERING28 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO29 SMALLINT WITH DEFAULT NULL,
ORDERING29 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO30 SMALLINT WITH DEFAULT NULL,
ORDERING30 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO31 SMALLINT WITH DEFAULT NULL,
ORDERING31 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO32 SMALLINT WITH DEFAULT NULL,
ORDERING32 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO33 SMALLINT WITH DEFAULT NULL,
ORDERING33 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO34 SMALLINT WITH DEFAULT NULL,
ORDERING34 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO35 SMALLINT WITH DEFAULT NULL,
ORDERING35 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO36 SMALLINT WITH DEFAULT NULL,
ORDERING36 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO37 SMALLINT WITH DEFAULT NULL,
ORDERING37 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO38 SMALLINT WITH DEFAULT NULL,
ORDERING38 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO39 SMALLINT WITH DEFAULT NULL,
ORDERING39 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO40 SMALLINT WITH DEFAULT NULL,
ORDERING40 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO41 SMALLINT WITH DEFAULT NULL,
ORDERING41 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO42 SMALLINT WITH DEFAULT NULL,
ORDERING42 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO43 SMALLINT WITH DEFAULT NULL,
ORDERING43 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO44 SMALLINT WITH DEFAULT NULL,
ORDERING44 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO45 SMALLINT WITH DEFAULT NULL,
ORDERING45 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO46 SMALLINT WITH DEFAULT NULL,
ORDERING46 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO47 SMALLINT WITH DEFAULT NULL,
ORDERING47 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO48 SMALLINT WITH DEFAULT NULL,
ORDERING48 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO49 SMALLINT WITH DEFAULT NULL,
ORDERING49 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO50 SMALLINT WITH DEFAULT NULL,
ORDERING50 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO51 SMALLINT WITH DEFAULT NULL,
ORDERING51 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO52 SMALLINT WITH DEFAULT NULL,
ORDERING52 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO53 SMALLINT WITH DEFAULT NULL,
ORDERING53 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO54 SMALLINT WITH DEFAULT NULL,
ORDERING54 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO55 SMALLINT WITH DEFAULT NULL,
ORDERING55 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO56 SMALLINT WITH DEFAULT NULL,
ORDERING56 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO57 SMALLINT WITH DEFAULT NULL,
ORDERING57 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO58 SMALLINT WITH DEFAULT NULL,
ORDERING58 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO59 SMALLINT WITH DEFAULT NULL,
ORDERING59 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO60 SMALLINT WITH DEFAULT NULL,
ORDERING60 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO61 SMALLINT WITH DEFAULT NULL,
ORDERING61 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO62 SMALLINT WITH DEFAULT NULL,
ORDERING62 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO63 SMALLINT WITH DEFAULT NULL,
ORDERING63 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL,
COLNO64 SMALLINT WITH DEFAULT NULL,
ORDERING64 CHARACTER(1)
FOR MIXED DATA WITH DEFAULT NULL
)
IN LDARFO.VIRIDX
AUDIT NONE
DATA CAPTURE NONE
NOT VOLATILE
APPEND NO;
Step 2: I made the following entry into the DSN_VIRTUAL_INDEXES
Table.
|
TBCREATOR
|
LDARFO
|
|
TBNAME
|
PAEMPLOYEE
|
|
IXCREATOR
|
RFO7936
|
|
IXNAME
|
VIR_IDX
|
|
ENABLE
|
Y
|
|
MODE
|
C
|
|
UNIQUERULE
|
D
|
|
COLCOUNT
|
1
|
|
CLUSTERING
|
N
|
|
NLEAF
|
384715
|
|
NLEVELS
|
-1
|
|
INDEXTYPE
|
2
|
|
PGSIZE
|
4
|
|
FIRSTKEYCARDF
|
384715
|
|
FULLKEYCARDF
|
384715
|
|
CLUSTERRATIOF
|
-1
|
|
PADDED
|
Y
|
|
COLNO1
|
36
|
|
ORDERING1
|
A
|
Above entry says that, I want to test whether creation of a
non-unique Index on 36th column of PAEMPLOYEE Table will improve
query performance or not.
Step 3: I executed the following EXPLAIN
statement in SPUFI.
EXPLAIN
PLAN SET QUERYNO = 2
FOR
SELECT *
FROM LDARFO.PAEMPLOYEE WHERE SECURITY_NBR='300668'
Step 4: Once the statement mentioned in Step
3 is executed, we can query PLAN_TABLE to find out whether the Optimizer has
considered the Virtual Index for SQL query optimization.
Below entry is found in PLAN_TABLE, which shows that the Virtual Index is considered by the Optimizer.
We have to make sure that the statistics
values for the virtual index are valid. Having something like -1 for the number
of leaf pages will not help Optimizer in considering the Virtual Index for the
optimization.
As long as you have 'Y' in the ENABLE column for the Index in
DSN_VIRTUAL_INDEXES, and the Index definition is valid, it should be considered
by the Optimizer when access path selection is performed for a query targeting
the underlying table.
Note: The
above explained is a simple scenario, but can follow the same process for
different complex scenarios.
No comments:
Post a Comment