Bizarre Cost Based Optimizer?

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 Received on Wed Jul 28 1999 - 09:44:13 CDT