An in is just a bunch of ors. The CBO (cost based optimizer) might be
thinking that it is cheaper to do a full table scan than an index look up on
the number of things in the in clause.
Jim
"Galen Boyer" <galenboyer_at_hotpop.com> wrote in message
news:ubs8cvkug.fsf_at_grossprofit.com...
> I'm having trouble getting Oracle to use a function based index
> with an IN clause. I bastardized a TOM KYTE example to display
> my issue. The example I bastardized and turned into an IN query
> is found http://osi.oracle.com/~tkyte/article1/index.html
>
> ORA>create table emp as select * from all_tables;
>
> Table created.
>
> ORA>update emp set table_name = initcap(table_name);
>
> 276 rows updated.
>
> ORA>commit;
>
> Commit complete.
>
> ORA>create index emp_upper_idx on emp(upper(table_name));
>
> Index created.
>
> ORA>set autotrace on explain
>
> ORA>select table_name, owner from emp where upper(table_name) = 'T1';
>
> TABLE_NAME OWNER
> ------------------------------ ------------------------------
> T1 ELM
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=1 Card=7 Bytes=238)
> 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=1 Card=7
Bytes=238)
> 2 1 INDEX (RANGE SCAN) OF 'EMP_UPPER_IDX' (NON-UNIQUE) (Cost=1
Card=7)
>
>
> ORA>select table_name, owner from emp where upper(table_name)
> in ('T1','HD_STORE_COLORS_TBL2');
>
> TABLE_NAME OWNER
> ------------------------------ ------------------------------
> T1 ELM
> Hd_Store_Colors_Tbl2 ELM
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=1 Card=14
Bytes=476)
> 1 0 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=14 Bytes=476)
>
>
> So, for some reason, Oracle correctly uses the index for a single
> lookup, but chooses to full scan with an IN clause.
>
> Any ideas?
>
> Oracle version is 8.1.7.0.0
>
> --
> Galen Boyer
Received on Thu Aug 08 2002 - 23:13:21 CDT
