Re: Function based indexes and index fast full scan

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.