Re: Bizarre Cost Based Optimizer?

I think I came across this in 7.3.3, and there was significant evidence of an error in the optimizer - I think it may even have been bugged.

It is NOT unreasonable for Oracle to decide that a tablescan (up to 32 blocks per read) and a sort (with a large sort memory) would be cheaper than an unsorted one-block-at-a-time index scan, but it is a bit unlikely.

To check if this is an error, try using EXPLAIN PLAN to hint the use of the index without the WHERE clause.

If the cost drops when the hint is in place, then the optimiser is displaying an error. If the cost increases then the optimiser is doing the right thing (according to its calculations).

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Robert Miller wrote in message
<1ED9BB6BF02AD211AE2400104B59E64E16113D_at_RDEX1>...
>Hi
>
>It you create an index on a table using five of the columns
>
>CREATE UNIQUE INDEX IND1 ON
> TABA (COL1,COL2,COL3,COL4,COL5)
>
>Then do a select on TABA with an ORDER BY using the above columns
>
>SELECT * FROM TABA
>ORDER BY COL1,COL2,COL3,COL4,COL5
>
>You would expect either Optimizer to use Index IND1,but that's not what
>I am experiencing.
>
>For the RBO it always uses the index and does a full Index scan and
>hence no SORT or TEMP tablespace activity.
>
>For the CBO it does a full table scan then a SORT to the TEMP tablespace
>completely ignoring the Index.
>
>Only if you add a WHERE clause to the SELECT statement to limit your
>selection does the CBO use the Index IND1.
>
>SELECT * FROM TABA
>WHERE COL1 BETWEEN 'VALUE_A' AND 'VALUE_B'
>ORDER BY COL1,COL2,COL3,COL4,COL5
>
>Does this sound like what should be happening, assuming that your
>STATISTICS are up to date?
>
Received on Wed Jul 28 1999 - 11:10:51 CDT