Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Function based indexes and index fast full scan
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), mname varchar2(60)
, fname 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 26Why does oracle use index fast full scan insted of range scan?
TIA. Received on Sun Jun 30 2002 - 12:13:36 CDT