Re: Function based indexes and index fast full scan

Hello Aviv

You are probably missing:

alter session set query_rewrite_enabled=true;

greg_at_GREG> select count(*) from users where upper(fname) = 'GREG';

  COUNT(*)

         0

Ausführungsplan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=30 Card=1 Bytes=26)    1 0 SORT (AGGREGATE)
   2 1 TABLE ACCESS (FULL) OF 'USERS' (Cost=30 Card=242 Bytes=6292)

greg_at_GREG> r
  1* alter session set query_rewrite_enabled=true

greg_at_GREG> select count(*) from users where upper(fname) = 'GREG';

  COUNT(*)

         0

Ausführungsplan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=26)    1 0 SORT (AGGREGATE)
   2 1 INDEX (RANGE SCAN) OF 'IN_USERS_UP_FNAME' (NON-UNIQUE) (Cost=1 Card=242 Byte

          s=6292)

I did generate statistics on the table.

Have a nice day

Gregor

--
-----------------------------------------------------------------------
Dipl.-Ing. Gregor J. Theis (3x ORACLE OCP DBA 8, 8i, 9i)
Ingenieurbüro Theis Scharnhorststraße 29 D-40477 Düsseldorf
Tel: +49-211-9448490 Mobil: +49-171-6701288 Fax: +49-211-488994
mailto:gregor.theis@ib-theis.de http://www.ib-theis.de

"Aviv" <ronensh_at_hotmail.com> schrieb im Newsbeitrag
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.