Page 1 of 1

Converting Non Matching index scan.

PostPosted: Fri Feb 24, 2012 11:22 am
by GOPI84
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.

Re: Converting Non Matching index scan.

PostPosted: Fri Feb 24, 2012 9:22 pm
by GOPI84
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,

Re: Converting Non Matching index scan.

PostPosted: Mon Feb 27, 2012 7:42 pm
by GuyC
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.