Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> 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...Received on Tue Jul 02 2002 - 08:06:45 CDT
> 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.