INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Query performance oddity

Query performance oddity

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

CODE --> SQL

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

CODE -->

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!

RE: Query performance oddity

Perhaps this has something to do with it?:

CODE

SELECT STATEMENT, GOAL = ALL_ROWS . . . 
Maybe if you add the "FIRST n ROWS" hint it would work.
3eyes

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

RE: Query performance oddity

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.

RE: Query performance oddity

Quote:


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
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close