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')
