How to create Oracle Function Based Index

How to create Oracle Function Based Index
=============================================

(It minimize the cost of query but need to carefully about its appropriate place)

Normaly creating a function on an indexed column in the where clause of a query guaranteed
an index would not be used. In Oracle 8i release introduced Function Based Indexes to solve
this problem.

In this method, you can index functions on a column level not on a where clause query.


This capability allows you to have case insenstive searches or sorts, search on complex equations,
and extend the SQL language efficiently by implementing your own functions and operators
and then searching on them.

you can also create a function as your requierment and then you can use it on your table columns index.


Without creating a function based index, you must implement a function in where clause of a query .

Practical example :




SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jan 5 11:20:15 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn scott@test107
Enter password:
Connected.
SQL>
SQL>
SQL> set autotrace traceonly
SQL>
SQL> create table fun_based_index as select * from all_objects ;

Table created.

SQL>
SQL>
SQL>
SQL>
SQL>
SQL> set pagesize 100
SQL> set pagesize 1000
SQL> set linesize 1000
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> drop index scott.ind_timstamp_desc
2 /

Index dropped.

SQL>
SQL> SELECT *
2 FROM scott.fun_based_index
3 ORDER BY TIMESTAMP DESC ;

53953 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3083779380

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 53953 | 4531K| | 1255 (2)| 00:00:16 |
| 1 | SORT ORDER BY | | 53953 | 4531K| 12M| 1255 (2)| 00:00:16 |
| 2 | TABLE ACCESS FULL| FUN_BASED_INDEX | 53953 | 4531K| | 173 (3)| 00:00:03 |
----------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
223 recursive calls
0 db block gets
788 consistent gets
0 physical reads
0 redo size
2645702 bytes sent via SQL*Net to client
39936 bytes received via SQL*Net from client
3598 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
53953 rows processed

SQL>
SQL>
SQL>
SQL> Create index scott.ind_timstamp_desc on scott.fun_based_index(TIMESTAMP DESC) ;

Index created.

SQL>
SQL>
SQL>
SQL> SELECT *
2 FROM scott.fun_based_index
3 ORDER BY TIMESTAMP DESC ;

53953 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3083779380

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 53953 | 4531K| | 1255 (2)| 00:00:16 |
| 1 | SORT ORDER BY | | 53953 | 4531K| 12M| 1255 (2)| 00:00:16 |
| 2 | TABLE ACCESS FULL| FUN_BASED_INDEX | 53953 | 4531K| | 173 (3)| 00:00:03 |
----------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
745 consistent gets
0 physical reads
0 redo size
2645702 bytes sent via SQL*Net to client
39936 bytes received via SQL*Net from client
3598 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
53953 rows processed

SQL> ANALYZE TABLE scott.fun_based_index COMPUTE STATISTICS;

Table analyzed.

SQL>
SQL>
SQL> /

Table analyzed.

SQL> SELECT *
2 FROM scott.fun_based_index
3 ORDER BY TIMESTAMP DESC ;

53953 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3083779380

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 53953 | 4531K| | 1255 (2)| 00:00:16 |
| 1 | SORT ORDER BY | | 53953 | 4531K| 12M| 1255 (2)| 00:00:16 |
| 2 | TABLE ACCESS FULL| FUN_BASED_INDEX | 53953 | 4531K| | 173 (3)| 00:00:03 |
----------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
745 consistent gets
0 physical reads
0 redo size
2645702 bytes sent via SQL*Net to client
39936 bytes received via SQL*Net from client
3598 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
53953 rows processed

SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> drop index scott.ind_timstamp_desc ;

Index dropped.

SQL>
SQL>

================================================================================
================================================================================
========================another example ========================================
================================================================================
================================================================================

SQL> SELECT *
2 FROM scott.fun_based_index
3 where lower(object_name)=lower('SNAP_SITE$') ;


Execution Plan
----------------------------------------------------------
Plan hash value: 483125779

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 540 | 46440 | 172 (3)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| FUN_BASED_INDEX | 540 | 46440 | 172 (3)| 00:00:03 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(LOWER("OBJECT_NAME")='snap_site$')


Statistics
----------------------------------------------------------
223 recursive calls
0 db block gets
789 consistent gets
0 physical reads
0 redo size
1204 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>
SQL>
SQL>
SQL>
SQL>
================================================================================
=================Creating non function based index==============================
================================================================================

SQL>
SQL> Create index scott.ind_timstamp_desc on scott.fun_based_index(object_name) ;

Index created.

SQL>
SQL>
SQL> SELECT *
2 FROM scott.fun_based_index
3 where lower(object_name)=lower('SNAP_SITE$') ;

OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DAT
------------------------------ ------------------------------ ------------------------------ ---------- ---
SYS SNAP_SITE$ 233


Execution Plan
----------------------------------------------------------
Plan hash value: 483125779

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 540 | 46440 | 172 (3)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| FUN_BASED_INDEX | 540 | 46440 | 172 (3)| 00:00:03 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(LOWER("OBJECT_NAME")='snap_site$')


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
746 consistent gets
0 physical reads
0 redo size
1204 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>
SQL> drop index scott.ind_timstamp_desc ;

Index dropped.

SQL>
SQL>


================================================================================
=================Creating a function based index==============================
================================================================================

SQL>
SQL> Create index scott.ind_timstamp_desc on scott.fun_based_index(lower(object_name)) ;

Index created.

SQL>
SQL>
SQL>
SQL>
SQL> SELECT *
2 FROM scott.fun_based_index
3 where lower(object_name)=lower('SNAP_SITE$') ;

OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DAT
------------------------------ ------------------------------ ------------------------------ ---------- ---
SYS SNAP_SITE$ 233


Execution Plan
----------------------------------------------------------
Plan hash value: 25314321

-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 540 | 46440 | 114 (0)| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID| FUN_BASED_INDEX | 540 | 46440 | 114 (0)| 00:00:02 |
|* 2 | INDEX RANGE SCAN | IND_TIMSTAMP_DESC | 216 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access(LOWER("OBJECT_NAME")='snap_site$')


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
1 physical reads
0 redo size
1204 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>