Re: Cost vs efficiency?

In article <Yryx7.9$cH1.347_at_nsw.nnrp.telstra.net>, sweidanz_at_yahoo.com says...
>
>I was reading some of the messages in asktom.oracle.com discussions and i
>was wondering about the following:
>One of the example was demonstrating the differences between using IN and
>EXISTS with big and small tables. The only thing that made me confused is
>that the same query when you run it using EXISTS has a larger cost than
>when you run it using IN. And apparently by getting the statistics in each
>run you can see the one with larger cost is more efficient than the one
>with smaller cost, in terms of number of consistent gets, db block gets
>and number of rows returned by each step in the execution plan.
>

you cannot really compare the costs across two queries. You can only compare the costs of various execution plans within a given query.

You were comparing apples and oranges. The costs of two totally different queries cannot realistically be compared.

I can show you queries with small costs that take hours to run. I can show you queries with big costs that run instantly.

The cost has nothing really to do with how fast the query will execute. It is just the plan with the smallest overall cost in the set of plans generated for a particular query.

As Sybrand mentioned -- get Jonathan Lewis's book. Its really good and goes into some details on this here and there.

>My question is what does the cost mean? My understanding is that the
>optimiser, when using the cost based, will evaluate each execution plan
>and based on the least cost will determine which one to use. The least
>cost means that number of block reads/data and indexes will be minimum and
>in addition to other factors as well. right? So how come larger cost is
>more efficient than smaller cost query?
>
>I wanted to post this question to Tom but apparently there is no posting
>messages to the site at present.
>
>thanks for any help,
>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