Re: Bizarre Cost Based Optimizer?

Hi

Tried what you advised and it does look like the CBO make the right choice based on its costs, I have not timed this though.

Forcing the CBO to use the index gave costs :-

        SELECT STATEMENT HINT: FIRST_ROWS
4218

        TABLE ACCESS BY INDEX ROWID ANALYZED 4218

	INDEX                        		FULL SCAN
ANALYZED			 439

And letting it do a sort :-

        SELECT STATEMENT HINT: ALL_ROWS
4151

        SORT ORDER BY
4151

	TABLE ACCESS			FULL
ANALYZED			 359

Thanks for your help.

> -----Original Message-----
> From: Jonathan Lewis [SMTP:jonathan_at_jlcomp.demon.co.uk]
> Posted At: Wednesday, July 28, 1999 5:11 PM
> Posted To: server
> Conversation: Bizarre Cost Based Optimizer?
> Subject: 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 Thu Jul 29 1999 - 09:35:58 CDT