Lesson 8	Function-based Indexes
Objective	Create function-based Index

Function-based indexes in Oracle

Function-based indexes were introduced in Oracle8. Oracle provides a wealth of functions such as 1) to_char, 2) decode, and 3) substr that can transform the existing values of a column. In Oracle8, one could specify these built-in functions directly into the index creation syntax, resulting in an index that contains a transformation of the data values in the table column. In addition, you may create your own functions and use them in the index definition.

Using a function-based Index

Function-based indexes are particularly useful for indexing on sub-values of a DATE column.
For example, assume that we have a table with a DATE column:

This shows sql code to create a table with a DATE column. — This shows sql code to create a table with a DATE column
create table stats ( stat_date DATE, db_name char(12), other_data char(200) );

While we may create an index on the STAT_DATE column, what would we do if we required an index only on the year portion of stats_date ? Prior to Oracle8, we would have needed to define a redundant YEAR column in our table to support an index. We could issue the following query to get the 1999 rows, but it requires an alter session statement to set the date format to YYYY:

This shows sql code to alter date format to YYYY.

Alter session set nls_date_format = 'YYYY'; select * from stats where stats_date = to_date(2019, 'YYYY');

The best alternative is to create an index based upon the character representation of the date column.

create index char_date_idx
on stats
( 
   to_char(stat_date, 'YYYY-MM-DD'),
   db_name
)
tablespace stat_ts
storage (initial 10m next 10);

We have created an index on the character representation of the DATE data type column. If we enter a query where

to_char(stats_date,'YYYY') = '1999',

the SQL optimizer will use the index to retrieve the 1999 rows.