Path: news.easynews.com!easynews!crtntx1-snh1.gtei.net!nycmny1-snh1.gtei.net!news.gtei.net!newsfeed.mathworks.com!wn3feed!worldnet.att.net!204.127.198.204!attbi_feed4!attbi.com!sccrnsc01.POSTED!not-for-mail
From: "Jim Kennedy" <kennedy-family@attbi.com>
Newsgroups: comp.databases.oracle.server
References: <ubs8cvkug.fsf@grossprofit.com>
Subject: Re: Function Based Indexes with in clause?
Lines: 70
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <BjH49.39514$7n5.7474@sccrnsc01>
NNTP-Posting-Host: 12.241.212.232
X-Complaints-To: abuse@attbi.com
X-Trace: sccrnsc01 1028866401 12.241.212.232 (Fri, 09 Aug 2002 04:13:21 GMT)
NNTP-Posting-Date: Fri, 09 Aug 2002 04:13:21 GMT
Organization: AT&T Broadband
Date: Fri, 09 Aug 2002 04:13:21 GMT
Xref: easynews comp.databases.oracle.server:157327
X-Received-Date: Thu, 08 Aug 2002 21:10:26 MST (news.easynews.com)

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@hotpop.com> wrote in message
news: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