|Efficient SQL Statements - A brief non-version specific guide to writing efficient SQL statements.|
relish said...Only doc seen which refers to driving and driven tables.. and comparisions bet'n exists and in and more...
Vijay said...This is a very good site and it really help beginners like me to understand the concepts clearly. Thanks for your help.
Amit said...Great site, took a query that took 17 minutes to run down to 42 seconds after the great explanation of Exists vs In
Srikanth said...Refering to your example regarding IN vs. EXISTS.
You said your IN example takes 1000 * 1000 rows. But why is it so. I think it will run only for once and takes all ITEM NOs and pass them to main query which will read all 1000 rows from PRODCUTS table.
wowo said...Hi Tim,
your hint with /*+ RULE */ for or statement is priceless!
Thanks a lot!
Jomon said...Great document... Clear explanation...
Kurt said...Do you realize that in your IN vs. EXISTS query, ITEMS is the parent table, and PRODUCTS is the child? The query is trying to list all the children that have a parent. (I would guess that is all of them.)
Also, modern versions of Oracle almost always generate the same (or extremely similar) execution plans for equivalent EXISTS and IN statements. If you follow your own advice and "try both variants to see which works best", you will discover they work more-or-less the same.
Kurt said......and another thing:
The statement "Remember, a full table scan of a small table is often more efficient than access by rowid." is now, and has always been, completely wrong.
ROWID is the exact location of a row. There is no faster way to reach a row.
Kurt said...Under "Improving Parse Speed", the notion that there is measurable benefit from using a single character alias name is laughable. You would probably get more benefit from uppercasing your identifiers so Oracle doesn't have to do it for you to match objects. (That is a joke, of course. There is no measurable benefit from that either.)
1) The article was written over 9 years ago. At that point, there was a significant difference between the functionality of IN and EXISTS.
2) I do not mention parent/child. The SQL is an example of the IN and EXISTS only. The tables could be called A and B for all I care.
3) Agreed, should have said access by index.
I will alter the table names and rowid/index reference to reduce confusion.
eugene said...I figured much of this out on my own over the years. It is nice to see that someone has explanations of why it works the way it does.
For instance, I knew the explain plan parsed from bottom to top and I would change the order of my from and where clause but, I did not know that the optimizer wants to create a driving table from the smallest result set.
I just saw performance increase by moving them around. Now that I know what to move and where well… We’ll see more performance in my queries without experimenting with all the tables and where conditions.
Thank you, Tim!
Darryl said...Regarding the "When Things Look Bad!" section.
"Write sensible queries in the first place!" - Is that really necessary? No.