Query performance oddity

[BKQc]

I have a query on a junction table that looks like this:

SELECT 1 
FROM MyTable
WHERE (k1 = :A1 OR k2 = :A2 OR k3 = :A3 OR k4 = :A4 OR k5 = :A5 OR k6 = :A6 OR k7 = :A7 OR k8 = :A8 OR k9 = :A9 OR k10 = :A10 OR k11 = :A11) AND ROWNUM <= 1

The role is to know if a given node is present anywhere in the junctions. This is a generic system that MAY use the eleven columns but would, most of the time, use between 2 and 4 columns. Actually, my index is made of the 11 keys so my explain plan is

SELECT STATEMENT, GOAL = ALL_ROWS			Cost=1	Cardinality=1	Bytes=50
 COUNT STOPKEY					
  INDEX FULL SCAN	Owner object=MYOWNER	OBJECT NAME=MYT_PRIMARY	Cost=1	Cardinality=1	Bytes=50

From what I understand, COUNT STOPKEY should make the query stop on the first encounter but according to the detail provided in Enterprise Manager, 95,83% of runs extract all lines! This gives me a request that takes, in average, near 40 seconds to run on a 1million lines table! Half and half between buffer gets and disk reads.

Is there something I don't understand about COUNT STOPKEY? Is there anything I could do to optimize this?

Any help will be gladly appreciated!

 

[LKBrwnDBA]

Perhaps this has something to do with it?:

SELECT STATEMENT, GOAL = ALL_ROWS . . .

Maybe if you add the "FIRST n ROWS" hint it would work.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[karluk]

I would experiment by reversing the sort order in the index (i.e. if a column is currently in ascending order, change it to descending). Perhaps the majority of your queries don't find a row that matches the selection criteria until almost the end of the index scan. If so, reversing the sort order should provide evidence of what's happening.

 

[fredericofonseca]

This is a generic system that MAY use the eleven columns but would, most of the time, use between 2 and 4 columns.

does this mean that K1 and K2 will always be populated, and that K3 and K4 may be populated and that the others will almost never be used?
or does it mean that of the 11 columns ANY 2-4 will normally be populated?

if the last option then one index with all the columns will not really help you much - on this case you would be better off having an individual index for each column.
If the first one then the columns which are always populated should be the first ones on the index list, and of those the ones with higher cardinality should be first.

And... are you using rule or cost based optimizer? (cost probably as that is default for 11g).
if the first option above is true I would also try and add "ordered predicates" as a hint as that may make a difference.
And if you are using cost based optimizer on this particular query I would also try rule based.

and finally do you have full statistics on the table? not just sample ones, and obviously updated frequently

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886