In list causing Multiple index scan.

Prashant Shettar

In list causing Multiple index scan.

Hello Everyone 

   I have DB2 luw 10.5 fp5 running on AIX platform. When I tried to explain the below query, Optimizer is scanning matching index twice.

Do we know the reason?

 

SQL query :

select pm.id_value,pm.token
from FIN_DA_PAYMENT_MEANS pm
where pm.ENTITY_STATUS_CREATING_DATE between '01.03.2017 00:00:00.000000' and '01.03.2017 23:59:59.999999'
and pm.type in ('virtualCreditCardToken', 'creditCardToken')
for read only with ur

                                  2.40324e+07                                                                          4.80647e+07
                              RIDSCN                                                                                 TABLE: HRSDB
                                     ( 3)                                                                                  FIN_DA_PAYMENT_MEANS
                               1.15779e+06 Q4
                                     87895.5
/--------------------------------+----------------\
1.20162e+07                                  1.20162e+07
SORT                                                   SORT
( 4)                                                          ( 6)
578894                                                 578894
43947.8                                                43947.8
|                                                                 |
1.20162e+07                                     1.20162e+07
IXSCAN                                                 IXSCAN
( 5)                                                           ( 7)
569664                                                 569664
43947.8                                               43947.8
|                                                               |
4.80647e+07                                  4.80647e+07
INDEX: HRSDB                                INDEX: HRSDB
FN_DA_PT_MNS_TYP_I                FN_DA_PT_MNS_TYP_I
Q4 Q4

 

Table has matching index 

 

HRSDB  FN_DA_PT_MNS_TYP_I D 1 RELATIONAL DATA - Y 2 - - - - NO +TYPE

 

Regards

Prashant Shettar 

 

Edited By:
Prashant Shettar[Organization Members] @ Apr 13, 2017 - 08:50 PM (Europe/Berlin)

Joe Geller

RE: In list causing Multiple index scan.
(in response to Prashant Shettar)

Prashant,

What do you mean by matching index?  What are the columns in the index which is being used?  It looks like the TYPE column is part of the index, but not the leading column, which would make it an index screening predicate.  DB2 would have to scan the whole index checking each entry for the value of this column.  I'm not sure, but it is possible that DB2 cannot check for both values (from the IN list) in one pass of the index.  If so, that would be why it is making two passes.

Joe



In Reply to Prashant Shettar:

Hello Everyone 

   I have DB2 luw 10.5 fp5 running on AIX platform. When I tried to explain the below query, Optimizer is scanning matching index twice.

Do we know the reason?

 

SQL query :

select pm.id_value,pm.token
from FIN_DA_PAYMENT_MEANS pm
where pm.ENTITY_STATUS_CREATING_DATE between '01.03.2017 00:00:00.000000' and '01.03.2017 23:59:59.999999'
and pm.type in ('virtualCreditCardToken', 'creditCardToken')
for read only with ur

                                  2.40324e+07                                                                          4.80647e+07
                              RIDSCN                                                                                 TABLE: HRSDB
                                     ( 3)                                                                                  FIN_DA_PAYMENT_MEANS
                               1.15779e+06 Q4
                                     87895.5
/--------------------------------+----------------\
1.20162e+07                                  1.20162e+07
SORT                                                   SORT
( 4)                                                          ( 6)
578894                                                 578894
43947.8                                                43947.8
|                                                                 |
1.20162e+07                                     1.20162e+07
IXSCAN                                                 IXSCAN
( 5)                                                           ( 7)
569664                                                 569664
43947.8                                               43947.8
|                                                               |
4.80647e+07                                  4.80647e+07
INDEX: HRSDB                                INDEX: HRSDB
FN_DA_PT_MNS_TYP_I                FN_DA_PT_MNS_TYP_I
Q4 Q4

 

Table has matching index 

 

HRSDB  FN_DA_PT_MNS_TYP_I D 1 RELATIONAL DATA - Y 2 - - - - NO +TYPE

 

Regards

Prashant Shettar 

 

Philip Gunning

In list causing Multiple index scan.
(in response to Prashant Shettar)
Type is not the leading column in the index so you won’t get matching….hence index scan…



From: Prashant Shettar [mailto:[login to unmask email]
Sent: Thursday, April 13, 2017 2:47 PM
To: [login to unmask email]
Subject: [DB2-L] - In list causing Multiple index scan.



Hello Everyone

I have DB2 luw 10.5 fp5 running on AIX platform. When I tried to explain the below query, Optimizer is scanning matching index twice.

Do we know the reason?



SQL query :

select pm.id_value,pm.token
from FIN_DA_PAYMENT_MEANS pm
where pm.ENTITY_STATUS_CREATING_DATE between '01.03.2017 00:00:00.000000' and '01.03.2017 23:59:59.999999'
and pm.type in ('virtualCreditCardToken', 'creditCardToken')
for read only with ur

2.40324e+07 4.80647e+07
RIDSCN TABLE: HRSDB
( 3) FIN_DA_PAYMENT_MEANS
1.15779e+06 Q4
87895.5
/-------+-------\
1.20162e+07 1.20162e+07
SORT SORT
( 4) ( 6)
578894 578894
43947.8 43947.8
| |
1.20162e+07 1.20162e+07
IXSCAN IXSCAN
( 5) ( 7)
569664 569664
43947.8 43947.8
| |
4.80647e+07 4.80647e+07
INDEX: HRSDB INDEX: HRSDB
FN_DA_PT_MNS_TYP_I FN_DA_PT_MNS_TYP_I
Q4 Q4



Table has matching index



HRSDB FN_DA_PT_MNS_TYP_I D 1 RELATIONAL DATA - Y 2 - - - - NO +TYPE



Regards

Prashant Shettar





-----End Original Message-----

Prashant Shettar

RE: In list causing Multiple index scan.
(in response to Philip Gunning)

Hello 

Thanks for your answer,

 Type column is the only column used in the index. 

Here is the index definition :

CREATE INDEX "HRSDB "."FN_DA_PT_MNS_TYP_I" ON "HRSDB "."FIN_DA_PAYMENT_MEANS"
("TYPE" ASC)

COMPRESS NO
INCLUDE NULL KEYS ALLOW REVERSE SCANS;