Re: Function based indexes and index fast full scan

As I remember when doing SQL-tuning :
Functions on indexed columns is bad for the indexes.

On Sun, 30 Jun 2002 19:13:36 +0200, 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.
Received on Tue Jul 02 2002 - 00:35:02 CDT