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???)
Received on Wed Sep 19 2001 - 06:28:44 CDT
