Path: news.easynews.com!easynews!novia!newscene.com!newscene.com!newscene!novia!novia!sequencer.newscene.com!not-for-mail From: Galen Boyer Newsgroups: comp.databases.oracle.server Subject: Function Based Indexes with in clause? Date: 8 Aug 2002 21:16:09 -0500 Lines: 59 Sender: gboyer@KLINK Message-ID: Original-Sender: galenboyer@hotpop.com User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.1 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Xref: easynews comp.databases.oracle.server:157320 X-Received-Date: Thu, 08 Aug 2002 19:13:18 MST (news.easynews.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