Re: Oracle8 Reverse Indexes Problem

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
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Alexandre Leite wrote in message <7djs86$8eb$1_at_srv4-poa.nutecnet.com.br>...
>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.
Received on Sun Mar 28 1999 - 02:35:41 CST