Which index Oracle is using?

We did the follow test in Oracle 8.0.5:

create table t3 (
col1 varchar2(10) primary key,
col2 varchar2(10));

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

create index ind_col2_t3 on t3(col2);

truncate table plan_table;
explain plan for select * from t3 where col2='22222';
select operation,options,object_name from plan_table;

OPERATION            OPTIONS              OBJECT_NAME
-------------------- -------------------- ------------------------------
SELECT STATEMENT
TABLE ACCESS         BY INDEX ROWID       T3
INDEX                RANGE SCAN           IND_COL2_T3

Ok!!!

drop index ind_col2_t3;
create index ind_col2_t3 on t3(col2) reverse;

truncate table plan_table;
explain plan for select * from t3 where col2='22222';
select operation,options,object_name from plan_table;
OPERATION OPTIONS OBJECT_NAME
-------------------- -------------------- ------------------------------
SELECT STATEMENT
TABLE ACCESS FULL T3

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

Thanks in advance,

Alexandre Leite
[email protected]

Ans1:
I'm only guessing here, but if this is the COMPLETE
list of test commands is it possible that the first query
chose to run rule-based and the second chose to run
cost based ? There are some features which force
cost based optimisation to take place (even if the
RULE hint is given).

Jonathan Lewis

Ans2:
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

Hosted by www.Geocities.ws