Re: Indexable Predicates in ORACLE -- Help!

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 19 Sep 2001 05:32:59 -0700
Message-ID: <9oa39r0n2o@drn.newsguy.com>

In article <77fd0218.0109190328.5069363e_at_posting.google.com>, bzamborski_at_uss.com says...
>
>Thomas Kyte <tkyte_at_us.oracle.com> wrote in message
>news:<9o8jl00ltf_at_drn.newsguy.com>...
>> In article <9o7a16$gm4$1_at_wanadoo.fr>, "Eric says...
>> >
>> >When you use the NOT clause Oracle can't indexes on the associate(s)
>> >column(s)
>> >
>> >"Brian Z" <bzamborski_at_uss.com> a écrit dans le message de news:
>> >77fd0218.0109180259.5b8826f2_at_posting.google.com...
>> >> I have some experience with DB2, but minimal knowledge of Oracle. Is
>> >> there a listing of what predicates are indexable in Oracle?? I.E. If I
>> >> have a 'Where acol not in ('a','b') --- is this indexable? Is anything
>> >> indexable when a 'NOT' is used? In DB2, this negates the use of an
>> >> index...I wanted to know if this is the same in Oracle. Thanks in
>> >> advance!
>> >
>> >
>>
>> well, not entirely true. It can use an index in many cases -- for example:
>>
>> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create table t ( x int, y char(2000) );
>> Table created.
>>
>> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create bitmap index t_idx on t(x);
>> Index created.
>>
>>ops$tkyte_at_ORA817DEV.US.ORACLE.COM> exec dbms_stats.set_table_stats( user, 'T',
>> numrows=>10000000, numblks => 100000 );
>>
>> PL/SQL procedure successfully completed.
>>
>> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> set autotrace on explain
>>ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select count(*) from t where x NOT IN (1,2)
>> 2 /
>>
>> COUNT(*)
>> ----------
>> 0
>>
>>
>> Execution Plan
>> ----------------------------------------------------------
>> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=29 Card=1 Bytes=13)
>> 1 0 SORT (AGGREGATE)
>> 2 1 BITMAP CONVERSION (COUNT)
>> 3 2 BITMAP INDEX (FULL SCAN) OF 'T_IDX'
>>
>>
>>
>> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> set autotrace off
>>
>> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> drop index t_idx;
>> Index dropped.
>>
>> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create index t_idx on t(x);
>> Index created.
>>
>> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> set autotrace on explain
>>ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select count(*) from t where x NOT IN (1,2)
>> 2 /
>>
>> COUNT(*)
>> ----------
>> 0
>>
>>
>> Execution Plan
>> ----------------------------------------------------------
>> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=13)
>> 1 0 SORT (AGGREGATE)
>> 2 1 INDEX (FAST FULL SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=1 Card=25001
>> Bytes=325013)
>>
>>
>> and in 9i there is a skip scan that will take advantage of this as well:
>>
>> ops$tkyte_at_ORA9I.WORLD> create table t
>> 2 as
>>3 select decode(mod(rownum,3), 0, 'M', 1, 'F', 2, 'U' ) gender, all_objects.*
>> 4 from all_objects
>> 5 /
>>
>> Table created.
>>
>> ops$tkyte_at_ORA9I.WORLD> create index t_idx on t(gender,object_id)
>> 2 /
>>
>> Index created.
>>
>> ops$tkyte_at_ORA9I.WORLD>
>> ops$tkyte_at_ORA9I.WORLD> analyze table t compute statistics
>> 2 for table
>> 3 for all indexes
>> 4 for all indexed columns
>> 5 /
>>
>> Table analyzed.
>>
>> ops$tkyte_at_ORA9I.WORLD>
>> ops$tkyte_at_ORA9I.WORLD> set autotrace traceonly explain
>>ops$tkyte_at_ORA9I.WORLD> select * from t t1 where gender not in ( 'M', 'F' ) and
>> object_id = 55;
>>
>> Execution Plan
>> ----------------------------------------------------------
>> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=1 Bytes=98)
>> 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=5 Card=1 Bytes=98)
>> 2 1 INDEX (SKIP SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=4 Card=1)
>>
>>
>>
>> ops$tkyte_at_ORA9I.WORLD> set autotrace off
>>
>> see
>>http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1156161921465
>> for more info on skip scans.
>
>Is this true if the access path selected were not index only? In other
>words, would the index still be used if the select were : Select y
>from t where x not in (1,2) ?? (With the y column not in the index???)

example right above the question for 9i -- it used the index to do a table access by rowid to get the rest of the columns....

even in 8i -- it can use an INDEX_FFS to do this depending on the optimizer:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create table t   2 as
  3 select decode(mod(rownum,3), 0, 'M', 1, 'F', 2, 'U' ) gender, all_objects.*   4 from all_objects
  5 /

Table created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create index t_idx on t(gender,object_id) 2 /

Index created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> analyze table t compute statistics   2 for table
  3 for all indexes
  4 for all indexed columns
  5 /

Table analyzed.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> 
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> set autotrace traceonly explain
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select /*+ FIRST_ROWS INDEX_FFS( t1 t_idx )

*/ * from t t1 where gender not in ( 'M', 'F' ) and object_id = 55;

Execution Plan

0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=43 Card=1 Bytes=99) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=43 Card=1 Bytes=99) 2 1 INDEX (FULL SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=42 Card=1)

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> set autotrace off

here it would use the index to perform less IO then it would if it full scanned the table itself.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp

Received on Wed Sep 19 2001 - 07:32:59 CDT