Re: Function based indexes and index fast full scan

Aviv wrote:

> 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.
>

Any valid statistics on IN_USERS_UP_FNAME and USERS ? With choose and no statistics => rule based and choose and 'some' statistics => all_rows. Try with stats in place and first_rows.

/Svend Received on Mon Jul 01 2002 - 13:40:59 CDT