Function based indexes and index fast full scan 
Author Message
 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)
    , 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.



Fri, 17 Dec 2004 01:13:36 GMT
 Function based indexes and index fast full scan

Hello Aviv

You are probably missing:

alter session set query_rewrite_enabled=true;
==============================================


  COUNT(*)
----------
         0

Ausfhrungsplan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=30 Card=1 Bytes=26)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'USERS' (Cost=30 Card=242 Bytes=6292)


  1* alter session set query_rewrite_enabled=true


  COUNT(*)
----------
         0

Ausfhrungsplan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=26)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (RANGE SCAN) OF 'IN_USERS_UP_FNAME' (NON-UNIQUE) (Cost=1
Card=242 Byte
          s=6292)

I did generate statistics on the table.

Have a nice day

Gregor
--
-----------------------------------------------------------------------
Dipl.-Ing. Gregor J. Theis (3x ORACLE OCP DBA 8, 8i, 9i)
Ingenieurbro Theis Scharnhorststra?e 29 D-40477 Dsseldorf
Tel: +49-211-9448490 Mobil: +49-171-6701288 Fax: +49-211-488994



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



Fri, 17 Dec 2004 03:52:50 GMT
 Function based indexes and index fast full scan
Hi,

As I remember it, the "Index FFS" plan becomes attractive if you have
a db_file_multiblock_read_count of greater than 4.

I was explained to me that the Index FFS is like a FTS, and sequential
prefetch is invoked.

In your example, with 40k rows at, using, say, 100 bytes per row on an
8k blocksize, there are 80 rows per block, and the whole index will
reside in 500 data blocks.

You are right, I would expect a range scan to have less I/O. . . .



Fri, 17 Dec 2004 07:02:48 GMT
 Function based indexes and index fast full scan
As I remember when doing SQL-tuning :
Functions on indexed columns is bad for the indexes.
Quote:

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



Sat, 18 Dec 2004 13:35:02 GMT
 Function based indexes and index fast full scan

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

...unless indexes are built on these functions (function-based indexes),
and the question is about such indexes.

--

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.



Sat, 18 Dec 2004 20:36:42 GMT
 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.

--

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.


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



Sat, 18 Dec 2004 21:06:45 GMT
 Function based indexes and index fast full scan

Quote:

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

> --

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



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

This done on 9.2

SQL> create table users(
  2      USER_ID     NUMBER(15)
  3      , fname   varchar2(60) not null
  4      , mname   varchar2(60)
  5      , lname   varchar2(60) not null
  6     );

Table created.

SQL>
SQL> insert /*+ APPEND */ into users
  2  select rownum, 'USER'||rownum,rownum,rownum
  3  from sys.source$
  4  where rownum < 40000;

39999 rows created.

SQL>
SQL> create index IN_USERS_UP_FNAME on users(upper(fname));

Index created.

SQL>
SQL> analyze table users compute statistics;

Table analyzed.

SQL>
SQL> set autotrace on
SQL>
SQL> select upper(fname) from users where upper(fname)='USER113';

UPPER(FNAME)
------------------------------------------------------------
USER113

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=9)
   1    0   INDEX (RANGE SCAN) OF 'IN_USERS_UP_FNAME' (NON-UNIQUE) (Co
          st=1 Card=1 Bytes=9)

So it does work - check your stats and ensure that your init.ora
query_rewrite.. parms are correct.

hth
connor
--
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."



Sun, 19 Dec 2004 01:13:39 GMT
 Function based indexes and index fast full scan

Quote:

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

> --

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



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

This done on 9.2

SQL> create table users(
  2      USER_ID     NUMBER(15)
  3      , fname   varchar2(60) not null
  4      , mname   varchar2(60)
  5      , lname   varchar2(60) not null
  6     );

Table created.

SQL>
SQL> insert /*+ APPEND */ into users
  2  select rownum, 'USER'||rownum,rownum,rownum
  3  from sys.source$
  4  where rownum < 40000;

39999 rows created.

SQL>
SQL> create index IN_USERS_UP_FNAME on users(upper(fname));

Index created.

SQL>
SQL> analyze table users compute statistics;

Table analyzed.

SQL>
SQL> set autotrace on
SQL>
SQL> select upper(fname) from users where upper(fname)='USER113';

UPPER(FNAME)
------------------------------------------------------------
USER113

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=9)
   1    0   INDEX (RANGE SCAN) OF 'IN_USERS_UP_FNAME' (NON-UNIQUE) (Co
          st=1 Card=1 Bytes=9)

So it does work - check your stats and ensure that your init.ora
query_rewrite.. parms are correct.

hth
connor
--
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."



Sun, 19 Dec 2004 01:13:39 GMT
 
 [ 8 post ] 

 Relevant Pages 

1. Index Fast Full Scan vs Index Full Scan?

2. Difference between a full scan, and a fast full scan for an INDEX

3. Index Full Scan Vs. Index Range Scan

4. Index scan vs Full table scan

5. ORA-01031 with Create Index for a Function Based Index

6. Query engine choosing full table scan over index seek

7. Delete on FULL INDEX SCAN - is it inefficient ??

8. full table scan vs index path for 1block table

9. Full table scans/indexed read

10. Optimizer puzzle: unnecessary full index scan

11. full table scan cheaper than index??

12. query did a full table scan even with an index


 
Powered by phpBB® Forum Software