Function Based Index
Why do you need a Function Based Index?
Considering the example of traditional “EMP” table, if you need to query the table based on emp_name then the query could be
SELECT * FROM EMP WHERE EMP_NAME =’JOE’;
Instead of making the full table scan on the above query you can create a non-unique index on the field “EMP_NAME”. By creating the index the above query makes use of index while execution.
if your requirement is to change the above query to
SELECT * FROM EMP WHERE UPPER(EMP_NAME)=’JOE’;
Since an function has been applied on EMP_NAME column, oracle will not utilize the index in executing the above query which will result in hampering the performance of your query. So Oracle has introduced Function Based Index to encounter the above problem.
1. You can create index based on function or Expression.
2. The session that you are trying to make use of FUNCTION INDEX should have the parameter QUERY_REWRITE_ENABLE is set to TRUE and QUERY_REWRITE_INEGRITY is set to TRUSTED. Oracle uses the Concept of Query rewriting i.e internal transformation of query, which is equivalent to source query but has different code to used function index.
3. Computed value of the function gets stored along with the rowid. So the function should be deterministic function. Deterministic means should always return the same value for the same parameter.
4.The function can also be a stored function and user should have execute privilege for that function.
5. Oracle maintains a time based dependencies between a function based index and the stored function that is used in the index creation.When function index is created, Oracle puts timestamp of creation. Naturally, this timestamp is more than timestamp of the function.
Later on if the Stored function is changed which is a having a timestamp greater than index, then oracle marks the index as invalid.
You can query the validity status of an index by the following query
Select STATUS from user_indexes where index_name=’index_name’;
The user can validate the Index structure using
ANALYZE INDEX INDEX_DUMMY VALIDATE STRUCTURE;
6. The decision whether to used index or not can be based on number of distinct values in that column. For function based inded this decision may not be take from distinct values of columns becuase different set of column values may give same value of expression.
7. To get the real benefit of Function based indexes , use histograms on the function based index columns.
Let us consider an example of a function based index.
I have a table called Symb_history which stores the historical data of stocks . A non unique index is available on the column ‘SYMBOL’.
The below query which doesn’t use a function
SQL> SELECT * FROM SYMB_HISTORY WHERE SYMBOL='EDUCOMP';
Execution Plan
---------------------------------------------------------- Plan hash value: 2881556207
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1208 | 48320 | 38 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| SYMB_HISTORY | 1208 | 48320 | 38 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | SYMB_HISTORY_IDX | 1208 | | 7 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SYMBOL"='EDUCOMP')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
75 consistent gets
0 physical reads
0 redo size
23733 bytes sent via SQL*Net to client
743 bytes received via SQL*Net from client
35 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
501 rows processed
If I change the above query to use UPPER function, then the index is not utilized, and also observe the CPU-COST.
SQL> select * from symb_history where upper(symbol)='EDUCOMP';
Execution Plan
---------------------------------------------------------- Plan hash value: 3343617986
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 37779 | 1475K| 6386 (5)| 00:01:17 |
|* 1 | TABLE ACCESS FULL| SYMB_HISTORY | 37779 | 1475K| 6386 (5)| 00:01:17 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(UPPER("SYMBOL")='EDUCOMP')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
22272 consistent gets
22221 physical reads
0 redo size
23733 bytes sent via SQL*Net to client
743 bytes received via SQL*Net from client
35 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
501 rows processed
So Let us now create a function based index on the UPPER function and the see the difference
Creating the Function based index
SQL> CREATE INDEX SYMB_FUN_IDX ON SYMB_HISTORY(UPPER(SYMBOL));
Analyzing the table after creating the function based index
SQL> ANALYZE TABLE SYMB_HISTORY COMPUTE STATISTICS;
Making rewriting possible for the cost based Optimizer
SQL> ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED;
SQL> ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;
And the result … is ‘SYMB_FUND_IDX’ FUNCTION BASED INDEX is being used and CPU-cost has gone down
SQL> SELECT * FROM SYMB_HISTORY WHERE UPPER(SYMBOL)='EDUCOMP';Execution Plan
----------------------------------------------------------
Plan hash value: 3467047201
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1208 | 48320 | 14 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| SYMB_HISTORY | 1208 | 48320 | 14 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | SYMB_FUN_IDX | 1208 | | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(UPPER("SYMBOL")='EDUCOMP')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
75 consistent gets
3 physical reads
0 redo size
23733 bytes sent via SQL*Net to client
743 bytes received via SQL*Net from client
35 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
501 rows processed