Re: Oracle8 Reverse Indexes Problem

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sun, 28 Mar 1999 14:53:43 GMT
Message-ID: <370b4129.20700655@192.86.155.100>

A copy of this was sent to "Alexandre Leite" <aleite_at_sysdesign.com.br> (if that email address didn't require changing) On Sat, 27 Mar 1999 21:14:17 -0300, you wrote:

>Hi all,
>
>We did the follow test in Oracle 8.0.5:
>

[snip]

>
>Why the Oracle RDBMS didn't use "ind_col2_t3" reverse index?
>My select statment uses an equality expression in where clause.
>

the reverse key index is visible to the CBO (cost based) only. RBO doesn't see it. Even so, if you just analyze the table -- you probably won't get the index to be used either (table is too small). Here is a sample:

SQL> create table t3 (
2 col1 varchar2(10) primary key,
3 col2 varchar2(10));
Table created.

SQL> insert into t3 values ('1','12134');
SQL> insert into t3 values ('2','14123');
SQL> insert into t3 values ('3','19769');

SQL> create index ind_col2_t3 on t3(col2); Index created.

SQL> set autotrace on explain;

SQL> select * from t3 where col2='22222'; no rows selected

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T3' 2 1 INDEX (RANGE SCAN) OF 'IND_COL2_T3' (NON-UNIQUE) SQL> drop index ind_col2_t3;
Index dropped.

SQL> create index ind_col2_t3 on t3(col2) reverse; Index created.

SQL> select * from t3 where col2='22222'; no rows selected

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'T3'

IT FULL SCANS SINCE RBO cannot see the index

SQL> select col2 from t3 where col2='22222'; no rows selected

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'T3'

IT FULL SCANS AGAIN SINCE RBO cannot see the index

SQL> analyze table t3 compute statistics; Table analyzed.

SQL> select * from t3 where col2='22222'; no rows selected

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=11) 1 0 TABLE ACCESS (FULL) OF 'T3' (Cost=1 Card=1 Bytes=11)

IT STILL FULL SCANS since the table is so small and it sees it must read the
index and the table to get the *

SQL> select col2 from t3 where col2='22222'; no rows selected

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=11) 1 0 INDEX (RANGE SCAN) OF 'IND_COL2_T3' (NON-UNIQUE) (Cost=1 C

Now it uses the index since the column can be gotten from the index alone
no table access by rowid.

SQL> insert into t3 select rownum+100, rownum from all_objects; 18724 rows created.

SO now the table has lots more rows

SQL> analyze table t3 compute statistics; Table analyzed.

inform the optimizer of this fact....

SQL> select * from t3 where col2='22222'; no rows selected

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=2 Bytes=28) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T3' (Cost=3 Card=2 Bytes 2 1 INDEX (RANGE SCAN) OF 'IND_COL2_T3' (NON-UNIQUE) (Cost=1

Now the index is used even tho we have the table access by rowid
since there are lots of rows...

SQL> select col2 from t3 where col2='22222'; no rows selected

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=2 Bytes=28) 1 0 INDEX (RANGE SCAN) OF 'IND_COL2_T3' (NON-UNIQUE) (Cost=1 C

>Thanks in advance,
>
>Alexandre Leite
>leite_alexandre_at_hotmail.com
>
>

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sun Mar 28 1999 - 08:53:43 CST