Re: Function based indexes and index fast full scan

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Tue, 02 Jul 2002 18:13:39 +0100
Message-ID: <3D21DF43.43DA@yahoo.com>

Vladimir M. Zakharychev wrote:
>
> Make sure you fully analyzed the table:
>
> analyze table users compute statistics
> for table
> for all indexes
> for all indexed columns;
>
> to provide CBO with as much information about your data
> as you can. CBO surely should've selected range scan here,
> the fact that it does index ffs probably means it does not
> have all statistics it could use here. No guarantee I am
> correct though.
>
> --
> Vladimir Zakharychev (bob@dpsp-yes.com) http://www.dpsp-yes.com
> Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
> All opinions are mine and do not necessarily go in line with those of my employer.
>
> "Aviv" <ronensh_at_hotmail.com> wrote in message
> news:f44c6b66.0206300913.79f51a45_at_posting.google.com...
> > Hello,
> >
> > I'm tring to use FBI (function based indexes) with the upper function.
> >
> > I define next table and FBI:
> >
> > create table users(
> > USER_ID NUMBER(15)
> > , fname varchar2(60) not null
> > , mname varchar2(60)
> > , lname varchar2(60) not null
> > ....
> > );
> >
> > create index IN_USERS_UP_FNAME on users(upper(fname));
> >
> > The table have about 40k records.
> >
> > When I'm executing next query:
> > select upper(fname) from users where upper(fname)='USER113';
> > The explan plan is as follow:
> > Operation Object Name Rows Bytes Cost Object Node
> > --------------------------------------------------------------------
> > SELECT STATEMENT Hint=CHOOSE 458 26
> > INDEX FAST FULL SCAN IN_UP_FNAME 458 6 K 26
> >
> > OR
> > select upper(fname) from users where upper(fname) like 'USER113%'
> > Operation Object Name Rows Bytes Cost Object Node
> > ----------------------------------------------------------
> > SELECT STATEMENT Hint=CHOOSE 2 K 26
> > INDEX FAST FULL SCAN IN_UP_FNAME 2 K 33 K 26
> > Why does oracle use index fast full scan insted of range scan?
> >
> > TIA.
This done on 9.2

SQL> create table users(

  2      USER_ID     NUMBER(15)
  3      , fname   varchar2(60) not null
  4      , mname   varchar2(60)
  5      , lname   varchar2(60) not null
  6     );

Table created.

SQL>
SQL> insert /*+ APPEND */ into users
  2 select rownum, 'USER'||rownum,rownum,rownum   3 from sys.source$
  4 where rownum < 40000;

39999 rows created.

SQL>
SQL> create index IN_USERS_UP_FNAME on users(upper(fname));

Index created.

SQL>
SQL> analyze table users compute statistics;

Table analyzed.

SQL> 
SQL> set autotrace on
SQL> 
SQL> select upper(fname) from users where upper(fname)='USER113';

UPPER(FNAME)

---

USER113 Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=9)    1 0 INDEX (RANGE SCAN) OF 'IN_USERS_UP_FNAME' (NON-UNIQUE) (Co

          st=1 Card=1 Bytes=9)

So it does work - check your stats and ensure that your init.ora query_rewrite.. parms are correct.

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."

Received on Tue Jul 02 2002 - 12:13:39 CDT