Re: Indexable Predicates in ORACLE -- Help!

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 18 Sep 2001 15:59:44 -0700
Message-ID: <9o8jl00ltf@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(*)

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(*)

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.

--
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 Tue Sep 18 2001 - 17:59:44 CDT