Converting Non Matching index scan.



IBM's flagship relational database management system

Converting Non Matching index scan.

Postby GOPI84 » Fri Feb 24, 2012 11:22 am

Hello,

Am trying to convert the access path for one of the tables in my query to matching index scan but unable to do it.All of the indexable and stage one predicates are being utilised in my program, and optimiser is choosing the indexed access paths for all of them, with match cols 1 or greater that 1.Its just this for this one table that am getting
a non matching index scan.Is there any way to convert the nonmatching index scan to a matching index scan.Also, no sorting is being done.
GOPI84
 
Posts: 4
Joined: Mon Mar 22, 2010 5:19 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Converting Non Matching index scan.

Postby GOPI84 » Fri Feb 24, 2012 9:22 pm

Also, from the dsn plan table and dsn predicate table am able to identify the column that is not getting picked up inspite of being indexable.
Below is one of the condition in which the predicate is compared with a correlated subquery to obtain matching values.

AND C.EFFDT =
(SELECT MAX(C1.EFFDT)
FROM PS_NAMES C1
WHERE C1.EMPLID = C.EMPLID
AND C1.NAME_TYPE = C.NAME_TYPE
AND C1.EFFDT <= CURRENT DATE)

Here, EFFDT is the column for which C.EFFDT is showing non matching indexed access for C correlation that is PS_NAMES itself.

Please , help in understanding the cause with ACCCESSTYPE=I AND MATCHCOLS = 0.
Thanks,
GOPI84
 
Posts: 4
Joined: Mon Mar 22, 2010 5:19 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Converting Non Matching index scan.

Postby GuyC » Mon Feb 27, 2012 7:42 pm

you want to access ps_names C using c.EFFDT, but c.EFFDT can only be determined using a correlated subselect in which you use C.EMPLID, C.NAME_TYPE.
So c.effdt can only be determined after you have read c.emplid,c.name-type which is quite impossible.
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post