On Function Based Indexes In Oracle

Indexes are used to access data more efficiently to gain performance in tems of execution time. Oracle has many types of indexes. I mentioned indexes here that are based on functions. Below example shows this usage:


SQL> conn hr/hr
Connected.
SQL> create table t(a number primary key using index(create index idx_a on t(a)), z number, m varchar(32));
Table created.
SQL> insert into t values(1,2,'MENNAN');
1 row created.
SQL> insert into t values(2,22,'ALI');
1 row created.
SQL> insert into t values(3,12,'sss');
1 row created.
SQL> create index idx_z on t(z);
Index created.
SQL> create index idx_upper_m on t( upper(m));
Index created.
SQL> set autotrace on;
SQL> select * from t where upper(m) = 'ALI';
         A          Z M
---------- ---------- ---------------
         2         22 ALI
Execution Plan
----------------------------------------------------------
Plan hash value: 745746091
-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    44 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T           |     1 |    44 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_UPPER_M |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(UPPER("M")='ALI')
Note
-----
   - dynamic sampling used for this statement
SQL> select * from t where m = 'ALI';
         A          Z M
---------- ---------- --------------------------------
         2         22 ALI

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    44 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    44 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("M"='ALI')
Note
-----
   - dynamic sampling used for this statement