Re: Cost vs efficiency?

In article <rbJy7.6$mL2.511_at_nsw.nnrp.telstra.net>, sweidanz_at_yahoo.com says...
>
>Tom,
>Thanks for taking the time to answer my question. Can i just clarify few
>things:
>1. The name of the book you are referring to, is that Practical
>Oracle8i-Building Efficient Databases or there is a new one?
>

yes, thats the one (dec 2000)

>2. I haven't got what you mean totally different queries? My understanding
>is that the two queries are the same, in terms of tables, logic, criteria
>and everything else except that they are written in two different ways.

thats right -- they are TWO different queries. The specific ones he was refering to was a comparision of:

select * from t where x in ( select y from t2 )

and

select * from t where exists ( select null from t2 where t2.y = t.x )

semantically -- they mean the same thing and give the same answer (not so with "not in" and "not exists"!!!). However, they are NIGHT and DAY different processing wise.

>The result is going always be the same, right. So i am assuming that i
>have one query and i want to figure out which of the two ways is more
>efficient. I will rely on the cost to find out the best. Otherwise, how by
>looking at the query, execution plan, i would tell which one is more
>efficient?

you have to know more about the data, how the query would be processed and so on. Best to run it and see. The particular discussion centered on how "where in" and "where exists" -- while very similar -- are processed very much differently.

>
>Like for example, sometimes i got asked by developers, which way of
>writing this query is more efficeint, this way or that way? My answer
>would be run the execution plan for both and see which one has the least
>cost and use it. After reading your comments i am confused and i might be
>wrong with my understanding. Is there other ways to find out?
>

run it against the data and see. Looking at the cost -- no way. Sometimes you can look at the plan and if you understand how to read it and understand the data, you can figure out which is better.

For example:

select * from 10_row_table where x in ( select y from 1_BILLION_row_table );

and

select * from 10_row_table where exists ( select null from 1_BILLION_row_table where x = y )

I would probably *probably* go with #2 and use the where exists.

>I am planning to buy the book you and Sybrand recommended, but if you can
>give me some lights now, i would really appreciate it.
>
>Thanks,ZS

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp