Oppolzer - Informatik / Blog

There seems to be some sort of misunderstanding on your part
what an index on the original query could/should accomplish;
because there is only a WHERE condition on this one column
(DELTA_FILE_BYTE), this is the only one column where an index
IMO makes sense (apart from rare situations like more than one table
in one table space etc.).

In your case, you (and DB2) will probably get no benefit from an index
access over table space scan, and so DB2 chooses TS scan, which
is in some (or many) situations the best choice. So it is chosen, and
all indexes are avoided. So it is here, IMO, and you will never get
index access.

Reasons for that can be:

- a big part of all rows is read

- no index matches the search criteria

- indexes match, but have bad clusterratio values

- the pros of seq. prefetch etc. are too high; index access would
leed to synchronous waits (which is in other words the same as
bad clusterratio)

- etc.

I would try to add an index on DELTA_FILE_BYTE, but I believe,
you earlier told that all columns have the value C, so all rows qualify,
anyway.

And: from your statistics I read that the index has only 125 pages,
that is pretty small. So your table is a small test table, if this index
contains all columns. With such small test tables, you cannot do
any performance analysis, which does make sense. DB2 will always
choose TS scan on small tables.

Another word to the PLAN_TABLE excerpt:

the subselects in the SELECT list are implemented as correlated
subqueries, which sounds pretty good to me, because they will make
use of the so called "correlated subquery cache", that is: for every combination
of search arguments, the query will be executed only once; for the following
executions, the value will be fetched from the cache, which should be
helpful in this case. No reason to rewrite the query, IMO. All is well.

HTH

kind regards

Bernd



Am 02.01.2015 um 17:21 schrieb T.W.:
>
> I tried creating an index on payment_tran with all of the columns in the select
> and DB2 still won't use it with the original SQL. I guess we'll go to option 2
> and write a program to do this.
>
>
>
> QUALIFIER: INDEX=XXXX.MGPTRA2X.
> Column Name                    Column
> ----v----1----v----2----v----3----v--
> NLEAF  . . . . . . . . . . . . 125
> NLEVELS  . . . . . . . . . . . 3
> BPOOL  . . . . . . . . . . . . BP32K
> PGSIZE . . . . . . . . . . . . 32
> ERASERULE  . . . . . . . . . . N
> DSETPASS . . . . . . . . . . .
> CLOSERULE  . . . . . . . . . . Y
> SPACE  . . . . . . . . . . . . 0
> IBMREQD  . . . . . . . . . . . N
> CLUSTERRATIO . . . . . . . . . 100
>
> QUALIFIER: INDEX=XXXX.MGPTRA2X.
> CmIndex Name                      Column
> ----v----1----v----2----v----3----v----4----v----5----
>   XXXX.MGPTRA2X                   CLIENT_NO
>   XXXX.MGPTRA2X                   DELTA_FILE_BYTE
>   XXXX.MGPTRA2X                   LN_NO
>   XXXX.MGPTRA2X                   PMT_TRANS_DT
>   XXXX.MGPTRA2X                   PMT_SEQ_NO
>
> STMTNO     COST*RATE SQL-STATEMENT
>      0     64.577194 SELECT LN_NO AS "LOAN_NUMBER" , PMT_TRANS_DT AS "PMT
> COST*RATE QB PL MIX QTYPE  METH ACC MTCH IX TBNAME             IXNAME
> 64.577194  1  1   0 SELECT    0 R      0 N  PAYMENT_TRAN
>  0.096646  2  1   0 CORSUB    0 I      4 N  VALID_VALUES       MGVVAL1X
>  0.096646  3  1   0 CORSUB    0 I      4 N  VALID_VALUES       MGVVAL1X
>  0.096646  4  1   0 CORSUB    0 I      4 N  VALID_VALUES       MGVVAL1X
>  0.096646  5  1   0 CORSUB    0 I      4 N  VALID_VALUES       MGVVAL1X