Re: Which plan is better - what COST really means ?

kyte writes taht comparing the cost of two different queries doesnt mean anything. if you really want to you can run a 10053 trace and get a calculator to figure out how the cost is calculated. its useful occasionally to see how oracle calculates with more buckets in hostograms or with different optimizer settings, but most of the time its not useful(unless you are really bored). i believe jonathan lewis talked about some tests on here where he actually believes that comparing two costs 'may' be useful.

personally if you can run the query the most effective and easiest way to see if one query is better than another is by setting autotrace on and looking at 'consistent gets'. This seems to work about 99% of the time. Occasionally I get radical reductions in logical io(consistent gets) with little to know performance improvements. one time i actually got a radical reduction in logical io and greater response time. that only happened once.

if you have two long running queries that you are comparing this wont work. I dont like to rely on the cost since i dont know enough about it. One good indicator that alot of people over look is the 'bytes' column. if oracle thinks its going to need to go through less bytes in a bulk query it typically performs better... this is not 100% accurate either unfortunately.

--
http://www.freelists.org/webpage/oracle-l