Re: Bizarre Cost Based Optimizer?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 28 Jul 1999 17:16:27 GMT
Message-ID: <37ab3934.22212970@newshost.us.oracle.com>

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 Received on Wed Jul 28 1999 - 12:16:27 CDT