Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Indexable Predicates in ORACLE -- Help!
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' ) andobject_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 CorpReceived on Tue Sep 18 2001 - 17:59:44 CDT