Who can read this: A basic understanding of indexes, updates and their effects on indexes would help better understand the topic of this document. This article is intended to be an introduction to the topic of Function based index.
Who should not read this: This document is meant to be a basic manual for those who would like to learn what are function based indexes and what magic can they do. So the persons who are already aware of this concepts, please do not waste your time. Also this concept is confined to Oracle database. There are no general topics discussed in this article.
Now let's start with the topic straightaway:
What and why a function based index is used?
Function based indexes were introduced with Oracle 8i. This was the most coveted features that Oracle came up with its 8i version of database. Traditionally there was no such feature that will use an index if your select statement has a function in it. But with this feature it was possible to use functions such as average, sum and still Oracle will use an index. This is a huge performance gain considering that the data handled by such databases were enormous.
So now you might have got a small idea of what a function based index can do. In a nutshell it is a mechanism whereby frequently accessed functions to a table can be created as a function index and oracle will use the index while such a query is fired. Isn't this a huge performance bonus?
A function based index is created just the same way as the normal index is created. Yes it is with the CREATE INDEX statement that the function based index is created. The following example is borrowed from Tom Kyte (see references for the URL):
SQL> create table emp as select * from scott.emp;
Table created.
SQL> update emp set ename = initcap(ename);
14 rows updated.
SQL> commit;
Commit complete.
SQL> create index emp_upper_idx on emp(upper(ename));
Index created.
In the above section of code, first we are creating a table with the same copy as that of scott.emp, and updating the values with InitCaps. Once the data is committed, the index for UPPER function is created. There is no difference in the clause rather than we can use functions here. Now if any query from an application or from the user with case insensitive queries will use this index.
SQL> set autotrace on explain
SQL> select ename, empno, sal from emp where upper(ename) = 'KING';
ENAME EMPNO SAL
---------- ---------- ----------
King 7839 5000
This is the execution plan of the select query:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=40)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=1 Card=1 Bytes=40)
2 1 INDEX (RANGE SCAN) OF 'EMP_UPPER_IDX' (NON-UNIQUE) (Cost=1 Card=1)
It is visible that the INDEX (RANGE SCAN) is used in this case rather than a FULL TABLE SCAN.
This feature becomes more powerful when you consider the fact that user-defined functions could be used as indexes to tables. This will make the application work faster. An example of such a feature is available in the URL provided for Kyte's article.
Privileges required for creating a function based index
1. You must have the query rewrite privilege for your schema
2. You must have the global query rewrite privilege for creating in other schemas
3. If the optimizer has to use a function based index, the following parameters must be set:
a. QUERY_REWRITE_ENABLED=TRUE
b. QUERY_REWRITE_INTEGRITY=TRUSTED (These are modifyable through ALTER SYSTEM, ALTER SESSION statements and as well through init.ora file)
Where not to use function based index
As it is with index, the same criteria holds good for function based index.
References:
Ask Tom: http://asktom.oracle.com/tkyte/article1/
Oracle-Base: http://www.oracle-base.com/articles/8i/FunctionBasedIndexes.php
No comments :
Post a Comment