Tag: function based

What is a Function-Based Index?

A function-based index is an index created on the result of a function or expression.

This type of index is more used for situations with atypical searches on the specified columns (e.g. search on an user email address ignoring sensitivity can be optimised using a function based index; rounded up amounts.)

Frequent use cases?

This type of index is not behaving like our documented bitmap index, therefore the use cases will actually differ a lot. It can easily be used in cases of high cardinality distinct values, where the searches follow a particular generic pattern, but the data input can vary to a high degree.

Example

Consider the following table as a subscription table for email notifications on your particular site.

create table test_tb
( row_id number
, email_address varchar2(100)
, status char(6)
, gender varchar2(20)
, age number
, education varchar2(100)
, user_preferences varchar2(500)
);

create index test_bidx on test_tb(email_address);
create index test_fbidx on test_tb(upper(email_address));
create index test_fbidx_g on test_tb(upper(gender));

Now, if you are a marketing company wanting to analyse the data from muliple sites to asses users preferences for particular companies, you will want to be able to match the data for same user, so you can see the correlation between various data gathered. This is wherethe index will come in handy.

select count(*) as no_entries, upper(gender)
from test_tb
group by upper(gender);

Also, if you want to do an analysis based on email this can be very useful as a query like the one bellow will not use the test_bidx index, but the test_fbidx one:

select * from test_tb where upper(email_address) = 'no-reply@domain.com';