Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Function Based Indexes with in clause?

Function Based Indexes with in clause?

From: Galen Boyer <galenboyer_at_hotpop.com>
Date: 8 Aug 2002 21:16:09 -0500
Message-ID: <ubs8cvkug.fsf@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 - 21:16:09 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US