Function based indexes and index fast full scan

From: Aviv <ronensh_at_hotmail.com>
Date: 30 Jun 2002 10:13:36 -0700
Message-ID: <f44c6b66.0206300913.79f51a45@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. Received on Sun Jun 30 2002 - 12:13:36 CDT