OracleBrains.Com header image 2

Understanding Function Based Indexes

December 10th, 2006 by Rajender Singh · 3 Comments

First have a look at following query:

SELECT e_name, age, address
FROM employees
WHERE UPPER(e_name) = ‘RAJENDER SINGH’;

Now, One of the important instruction that I normally give to my team that when ever they are writing the SQL query, never ever use function in a WHERE CLAUSE of a SQL statement ( as above). Because doing so will guarantee that a normal index would not be used. However, if really necessary to overcome this setback use special type of Index called Function Based Indexes.

When using the Function Based Indexes, we index column on the product of a function (internal or user written) or an expressions rather then on its column value.

SQL for creating function index is as follows:
CREATE INDEX emp_upper_idx
ON employees(UPPER(e_name));

Where emp_upper_idx is name of the index, employees is a table name and UPPER(e_name)) is the function.

However there are few prerequisites we must make sure before we can use it in our schema because internally when ever this type of SQL query is passed and if there is Function Based Index to support it, the query is rewritten to allow the index to be used.

First, that our schema must be granted system privilege query rewrite.

Example as follows:

GRANT QUERY REWRITE TO user;

Secondly, following session or system variable must be set:

QUERY_REWRITE_ENABLED=TRUE
QUERY_REWRITE_INTEGRITY=TRUSTED


Tags: Interesting Coding Showcase · Oracle Administration · Oracle Database

3 responses so far ↓

  • 1 Radhika // Oct 30, 2007 at 11:27 pm

    How can I make this function based index work with IN in the WHERE condition? I have a query that needs to be as follows -
    SELECT DISTINCT a,b,c FROM table WHERE UPPER(a) IN (’XYZ,’LMN’).
    The function based index works when I use = instead of IN. But I can’t get it to work with the IN clause (I’ve tried using hints). Any suggestions?

  • 2 Rajender Singh // Nov 2, 2007 at 8:52 am

    Hi,

    I tried to create same situation as yours as follows:

    1. Created a function base index
    CREATE INDEX job_index1 ON emp(UPPER(job));

    2. Ran then folowing SQL
    SELECT DISTINCT job FROM emp WHERE UPPER(job) IN ( ‘CLERK’,'SALESMAN’);

    3. I got explain plan as follows:

    PLAN_TABLE_OUTPUT
    —————————————————————
    Plan hash value: 982647806

    —————————————————————
    | Id | Operation | Name | Rows | By
    —————————————————————
    | 0 | SELECT STATEMENT | | 1 |
    | 1 | HASH UNIQUE | | 1 |
    | 2 | INLIST ITERATOR | | |
    | 3 | TABLE ACCESS BY INDEX ROWID| EMP | 1 |
    |* 4 | INDEX RANGE SCAN | JOB_INDEX1 | 1 |
    —————————————————————

    PLAN_TABLE_OUTPUT
    —————————————————————

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

    4 - access(UPPER(”JOB”)=’CLERK’ OR UPPER(”JOB”)=’SALESMAN’)

    Its shows that Funtion based index do work in “IN” clause too!

    Let me know if I am missing any information!

    Regards

  • 3 madhuri // Jul 11, 2008 at 1:20 pm

    u r explanation is impressive.i want such example for use of B-tree and Bitmap index .where exactly we will use these indexes and how will be benefited

Leave a Comment