Re: Bizarre Cost Based Optimizer?

Robert

A full table scan may be more efficient than an index scan. Given you are asking for all the data in your first select statement, and that full table scans can do mulitblock I/O, you may be seeing the CBO making a correct decision.

HTH. Pete

Robert Miller wrote:

> 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?
>
> Surely the CBO would avoid a Full Table Scan and a Sort at all Cost even
> given this simple SQL Statement, or am I missing something?
>
> Thanks
>
> Rob

--
Regards

Pete

Received on Wed Jul 28 1999 - 11:05:18 CDT