Re: Bizarre Cost Based Optimizer?

Thomas,
  Are hint and rule base optimizer related - i.e. would optimzer use rule if hint is used when optimizer_mode=choose or cbo ? If this is not true, then what type of hints will use cbo or rule respectively ?

Thanks in advance Ted Chyn

In article <37ab3934.22212970_at_newshost.us.oracle.com>,   tkyte_at_us.oracle.com wrote:
> A copy of this was sent to Robert Miller
<robert_miller_at_systemsunion.com>
> (if that email address didn't require changing)
> On Wed, 28 Jul 1999 15:44:13 +0100, you 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.
> >
>
> No, the RBO will *not* use the index if col1, col2, col3, col4, col5
allow NULLS
> -- I bet there is some difference in the table you are testing with.
Try this
> small example that shows the CBO will use the index if the columns
are not
> NULLABLE (nulls are not indexed -- hence neither can use the index if
the
> columns are all nullable as there could be rows in the table not
pointed to by
> the index -- when you use the predicate below, you preclude NULLS
from the
> result set and hence it can use the index)
>
> SQL> create table taba ( col1 int, col2 int, col3 int, col4 int, col5
int );
> Table created.
>
> SQL> CREATE UNIQUE INDEX IND1 ON
> 2 TABA (COL1,COL2,COL3,COL4,COL5)
> 3 /
> Index created.
>
> SQL> set autotrace on explain
>
> REM -- rule based -- NO INDEX USED:
>
> SQL> SELECT * FROM TABA
> 2 ORDER BY COL1,COL2,COL3,COL4,COL5
> 3 /
>
> no rows selected
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE
> 1 0 SORT (ORDER BY)
> 2 1 TABLE ACCESS (FULL) OF 'TABA'
>
> REM -- cost based -- NO INDEX CAN BE USED EITHER:
>
> SQL> select /*+ FIRST_ROWS */ * from taba
> 2 ORDER BY COL1,COL2,COL3,COL4,COL5
> 3 /
>
> no rows selected
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=4 Card=82
> Bytes=8200)
>
> 1 0 SORT (ORDER BY) (Cost=4 Card=82 Bytes=8200)
> 2 1 TABLE ACCESS (FULL) OF 'TABA' (Cost=1 Card=82 Bytes=8200
> )
>
> REM -- make one of the columns non null and retry:
>
> SQL> alter table taba modify col1 NOT NULL;
>
> Table altered.
>
> SQL> SELECT * FROM TABA
> 2 ORDER BY COL1,COL2,COL3,COL4,COL5
> 3 /
>
> no rows selected
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE
> 1 0 INDEX (FULL SCAN) OF 'IND1' (UNIQUE)
>
> SQL>
> SQL> select /*+ FIRST_ROWS */ * from taba
> 2 ORDER BY COL1,COL2,COL3,COL4,COL5
> 3 /
>
> no rows selected
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=26 Card=82
> Bytes=8200)
>
> 1 0 INDEX (FULL SCAN) OF 'IND1' (UNIQUE) (Cost=26 Card=82 Byte
> s=8200)
>
> so, that shows the CBO and RBO will use the index *only when they can*
>
> SQL>
> SQL> set autotrace off
>
> >For the CBO it does a full table scan then a SORT to the TEMP
tablespace
> >completely ignoring the Index.
> >
>
> it might also do that if it feels it will access the entire table
(more then 20%
> of the indexed values) AND there are columns in the table NOT in the
index that
> you are getting. Consider this:
>
> SQL> create table taba
> 2 ( col1 int NOT NULL, col2 int, col3 int, col4 int, col5 int,
col6 int);
>
> Table created.
>
> SQL>
> SQL> CREATE UNIQUE INDEX IND1 ON
> 2 TABA (COL1,COL2,COL3,COL4,COL5)
> 3 /
>
> Index created.
>
> SQL>
> SQL> set autotrace on explain
> SQL>
> SQL> select /*+ FIRST_ROWS */ * from taba
> 2 ORDER BY COL1,COL2,COL3,COL4,COL5
> 3 /
>
> no rows selected
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=4 Card=82
> Bytes=8200)
>
> 1 0 SORT (ORDER BY) (Cost=4 Card=82 Bytes=8200)
> 2 1 TABLE ACCESS (FULL) OF 'TABA' (Cost=1 Card=82 Bytes=8200
> )
>
> SQL>
> SQL> select /*+ FIRST_ROWS */ col1, col2, col3, col4, col5 from taba
> 2 ORDER BY COL1,COL2,COL3,COL4,COL5
> 3 /
>
> no rows selected
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=26 Card=82
> Bytes=5330)
>
> 1 0 INDEX (FULL SCAN) OF 'IND1' (UNIQUE) (Cost=26 Card=82 Byte
> s=5330)
>
> Here i added another column to the table for the first query with the
select *.
> The optimize said "hey, i'll have to read an index block, do a
scattered read to
> a table, read a block, scattered read to table, read a block and so
on.". Since
> every value in the index will cause me to do a random IO to the table
-- this
> will be bad. Let me full scan and sort the table and then full scan
the sorted
> table, this will be faster.
>
> The predicate again, allows it to use the index as it believe <20% of
the table
> will be accessed via 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
> >
> >
>
> --
> See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to
Oracle8i'...
> Current article is "Part I of V, Autonomous Transactions" updated
June 21'st
>
> Thomas Kyte tkyte_at_us.oracle.com
> Oracle Service Industries Reston, VA USA
>
> Opinions are mine and do not necessarily reflect those of Oracle
Corporation
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Fri Jul 30 1999 - 10:10:55 CDT