Understanding Function Based Index In Oracle Database

fncbased_indx

Traditionally, performing a function on an indexed column in the where clause of a query guaranteed an index would not be used. Oracle 8i introduced Function-Based Indexes to counter this problem. Rather than indexing a column, you index the function on that column to store the result of the function, not the original column data.

SQL> CREATE TABLE user_data (
  2   id          NUMBER(10)    NOT NULL,
  3   first_name  VARCHAR2(40)  NOT NULL,
  4   last_name   VARCHAR2(40)  NOT NULL,
  5   gender      VARCHAR2(1),
  6   dob         DATE
  7  );

Table created.

SQL> BEGIN
  2    FOR cur_rec IN 1 .. 2000 LOOP
  3      IF MOD(cur_rec, 2) = 0 THEN
  4        INSERT INTO user_data
  5        VALUES (cur_rec, 'John' || cur_rec, 'Doe', 'M', SYSDATE);
  6      ELSE
  7        INSERT INTO user_data
  8        VALUES (cur_rec, 'Jayne' || cur_rec, 'Doe', 'F', SYSDATE);
  9      END IF;
 10      COMMIT;
 11    END LOOP;
 12  END;
 13  /

PL/SQL procedure successfully completed.

SQL> COMMIT;

Commit complete.

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'user_data', cascade => TRUE);

PL/SQL procedure successfully completed.

--No Index On first_name
SQL> SELECT * FROM user_data WHERE UPPER(first_name) = 'JOHN2';

        ID FIRST_NAME                               LAST_NAME                                G DOB
---------- ---------------------------------------- ---------------------------------------- - ---------
         2 John2                                    Doe                                      M 28-APR-20

Execution Plan
----------------------------------------------------------
Plan hash value: 2489064024

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |    20 |   540 |     5   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| USER_DATA |    20 |   540 |     5   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   1 - filter(UPPER("FIRST_NAME")='JOHN2')
   
SQL> CREATE INDEX first_name_idx ON user_data (first_name);

Index created.

SQL> ANALYZE INDEX first_name_idx VALIDATE STRUCTURE;

Index analyzed.

SQL> SELECT * FROM user_data WHERE UPPER(first_name) = 'JOHN2';

        ID FIRST_NAME                               LAST_NAME                                G DOB
---------- ---------------------------------------- ---------------------------------------- - ---------
         2 John2                                    Doe                                      M 28-APR-20

Execution Plan
----------------------------------------------------------
Plan hash value: 2489064024

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |    20 |   540 |     5   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| USER_DATA |    20 |   540 |     5   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   1 - filter(UPPER("FIRST_NAME")='JOHN2')


SQL> DROP INDEX first_name_idx;

Index dropped.

SQL> CREATE INDEX first_name_idx ON user_data (UPPER(first_name));

Index created.

SQL> ANALYZE INDEX first_name_idx VALIDATE STRUCTURE;

Index analyzed.

SQL> SELECT * FROM user_data WHERE UPPER(first_name) = 'JOHN2';

        ID FIRST_NAME                               LAST_NAME                                G DOB
---------- ---------------------------------------- ---------------------------------------- - ---------
         2 John2                                    Doe                                      M 28-APR-20

Execution Plan
----------------------------------------------------------
Plan hash value: 1309354431

----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |    20 |   540 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| USER_DATA      |    20 |   540 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | FIRST_NAME_IDX |     8 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

   2 - access(UPPER("FIRST_NAME")='JOHN2')   
   
SQL> DROP INDEX first_name_idx;

Index dropped.

SQL> CREATE INDEX first_name_idx ON user_data (gender, UPPER(first_name), dob);

Index created.

SQL> ANALYZE INDEX first_name_idx VALIDATE STRUCTURE;

Index analyzed.

SQL> SELECT * FROM user_data WHERE gender = 'M' AND UPPER(first_name) = 'JOHN2';

        ID FIRST_NAME                               LAST_NAME                                G DOB
---------- ---------------------------------------- ---------------------------------------- - ---------
         2 John2                                    Doe                                      M 28-APR-20

Execution Plan
----------------------------------------------------------
Plan hash value: 1309354431

----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |    10 |   270 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| USER_DATA      |    10 |   270 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | FIRST_NAME_IDX |     4 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

2	- access("GENDER"='M' AND UPPER("FIRST_NAME")='JOHN2')

SQL> SELECT * FROM user_data WHERE UPPER(first_name) = 'JOHN2';

        ID FIRST_NAME                               LAST_NAME                                G DOB
---------- ---------------------------------------- ---------------------------------------- - ---------
         2 John2                                    Doe                                      M 07-MAY-22
Execution Plan
----------------------------------------------------------
Plan hash value: 2489064024

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |    20 |   540 |     5   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| USER_DATA |    20 |   540 |     5   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   1 - filter(UPPER("FIRST_NAME")='JOHN2')

Shoumadip Das

Hi Folks, I am Shoumadip Das hailing from Kolkata, India welcoming you to explore my blog www.oraclemasterpiece.com. I am a self motivated and successful professional working in a leading IT Giant for more than 10 years.My area of expertise includes –

  • Oracle Database Programming (SQL & PL/SQL)
  • Perl Programming
  • Unix Programming

Read More

Leave a Reply

Your email address will not be published. Required fields are marked *