| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
Home -> Community -> Usenet -> c.d.o.server -> Function Based Indexes with in clause?
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 BoyerReceived on Thu Aug 08 2002 - 21:16:09 CDT
![]() |
![]() |