Oracle: Performance feature 1

A lot of people have known that since Oracle 8 you have had the ability to create functional indexes. Basically functional indexes allow you to create an index of calculated values. The most common example provided is the infamous to_char (date-to-char) conversion function.

Consider this SQL, which returns the product and amount for today from the sales table.

select product,amount
from sales
where to_char(purchase_date,'YYYYMMDD') = '20060308'

If there is an normal index on purchase_date, it can't be used in this case as the explicit conversion of purchase_date to a character string.

Here Oracle's functional index can come to the rescue. Creating an index of to_char(...) calculated values:

Create index ix_func_sales_pdate
on sales(to_char(purchase_date,'YYYYMMDD'))

Henceforth the above SQL can use the functional index to retrieve calculated values of to_char, and sped access to rows which have those calculated values.

However, hat-tip to Tom Kyte of asktom.oracle.com fame, you can use the ability to create an index of calculated values to speed access for any user-defined function!
Oracle normal indexes (B-Tree) don't index NULL values. So you create a function which will return NULL for rows you don't want (which won't be indexed) and non-NULL values for rows which you require.
The best use of this ability is for columns of low cardinality, which are highly skewed to one value. For example, a table contains a column opt_out which tracks permission to see marketing information. The opt_out column has two values 0 or 1, where 0 = No and 1 = Yes. Naturally it is skewed, which most values in the column being 0.

You have a nightly process called send_newsletter which requires a list of people who have opted in, your choice of index for this column should be a functional index not a BITMAP index. BITMAP indexes work better when there are multiple low cardinality columns.
You need to create a function which turns opt_out = 0 into NULL. A simple solution is to use CASE.
eg.
CASE
when opt_out = 0 then NULL -- the most common first
when opt_out = 1 then 1
else NULL end
Creating a functional index on that function will mean Oracle will only index rows which have opted IN to receive the newsletter. Retrieving rows using SQL like below will sped access via the functional index.

select email_addr
from customers
where opt_in(opt_out) = 1

This is a powerful feature of Oracle which can be used in much more complex functional examples. The main caveat is that inserts,updates will be slower as the row values have to be calculated on insert or update. This penalty is less than the penalty of BITMAP indexes.

Have Fun