Oppolzer - Informatik / Blog

To add and make it more concise:

you will see an index access only if:

- you add an index, that contains only DELTA_FILE_BYTE or
starts with DELTA_FILE_BYTE (matching index scan),  or

- if the percentage of qualifying rows is very very low (non matching
index scan).

If an index is chosen or not, depends on - at least - three factors:

a) is matching index scan possible or not (your index from the last post: no;
because DELTA_FILE_BYTE is not at the beginning of the index)

b) clusterratio of the index

c) selectivity of the predicate (filter factor)

If the filter factor is too high, given the clusterratio of the index etc.,
DB2 does not gain anything from using the index over TS scan.

You normally cannot control the clusterratio, because in this szenario,
you already have another index which is the CLUSTERing index.

And: if the percentage of qualifying rows in this case really is 100 %,
TS scan is the right choice, anyway.

Kind regards

Bernd



Am 02.01.2015 um 19:27 schrieb Bernd Oppolzer:
> 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
>>
>> -----End Original Message-----
>