Re: Bizarre Cost Based Optimizer?

Hi Rob,

    Try dropping the ORDER BY from the query with the WHERE clause and see what the CBO does.

    I suspect that as soon as the CBO sees the query doesn't have a WHERE clause that it says to itself "Self, since there isn't a where clause I have to scan the full table" -- and it does, but if there is a WHERE clause which references the LEADING column in an index the CBO will use the index.

regards
Jerry Gitomer

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?
>
>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 - 11:01:53 CDT