Last week I published an article about filtered indexes in SQL Server. This feature allows to save disk space by giving up on indexing all rows in a table when only a relatively small subset is going to be used. I also showed how it can improve query performance. Result I achieved was quite satisfying. This time I want to focus on features available in Oracle database that could help me do the same. There is no point to hide it - Oracle does not have filtered indexes feature. But do not worry! There is a workaround ...

If you are not familiar with filtered indexes in SQL Server, you can read about them in my previous article. Just for reminding purposes - a filtered index contains references to only a subset of rows from a table. Rows that match conditions defined in the index. Other rows are not covered by that index and it cannot be used for querying them. But if the database knows that other rows do not fulfill conditions from the query, the filtered index can greatly help. Key advantages of the feature are:

1. If only a subset of rows is indexed, the index is smaller and consumes less space on a disk.
2. Lighter index is more efficient for querying.
3. Index maintenance is easier - index rebuilding takes less time.

If you have a table and a very frequently running query against it which uses the same subset of rows, filtered indexes can be very helpful. Unfortunately, it is a SQL Server feature not Oracle. A good news is that PL/SQL developers are not left with nothing - even there is no equivalent, a workaround exists.

The workaround exploits one feature and one truth about Oracle databases:

1. A function-based index.
2. Not indexing NULL values.

I will quickly describe both.

Function-based index

Not only values from one or more columns can be indexed. Oracle database can also index values that are returned by a function. For example, there might a table with three columns:

I might be interested in a margin for each product - a difference between the selling price and the buying price. I can easily write a query to return a margin for each product.

SELECT prod_id, prod_sell_price - prod_buy_price
FROM Products

That was easy, but I have millions of products in that table and I want to filter out only those where the margin is higher than 10. A proper query would be as follows:

SELECT prod_id, prod_sell_price - prod_buy_price
FROM Products
WHERE prod_sell_price - prod_buy_price > 10;

That was not difficult either, but the above query is very inefficient if it is not supported by a proper index. Indexing any or even all the columns would not help much because the database must filter by a virtual column not a physical one. Values of (prod_sell_price - prod_buy_price) does not exist in the table but they can be easily calculated from the values that ARE in the table.

To help in such cases, Oracle implemented function-based indexes. They can index values that are results of a function or a calculation - like (prod_sell_price - prod_buy_price). Such index can be easily created by the following statement.

CREATE INDEX IX_prod_margin ON Products (prod_sell_price - prod_buy_price);

If such index exists, the database has a chance to use it for optimizing the query that filters by the margin.

Not indexing NULL values

NULL values in the relational theory means unknown. NULL is not a value like any other. It means that the value is not known, not set, not provided. Based on this concept, architects from Oracle Corporation thought hmm, NULL is not a value but a lack of value so why to index missing values? As they thought, they implemented it. NULLs are not included in an index. The consequences are important.

First of all, Oracle cannot efficiently query for NULLs. For example a query with WHERE prod_sell_price IS NULL cannot be easily optimized by creating an index on the prod_sell_price column. Rows matching this condition are not reflected in the index so it is pretty useless for such query.

Secondly, as only real values are included in the index, it is smaller.

Filtered index equivalent in Oracle

Function-based index feature and the fact of not indexing NULL values enables an ability to achieve a similar result in Oracle as filtered indexes do in SQL Server. I will refer to the same query sample that I used to present filtered indexes in the previous article.

SELECT *
FROM user_messages
WHERE mes_receiver_usr_id = 234
    AND mes_unread = 0;

Most of the rows in the table have mes_unread = 1. Only a relatively small subset has mes_unread = 0.

I know that NULLs are not included in the index by design and I do not want to include the rows with mes_unread = 1, so my first step will be to come up with a calculation or a function that would convert mes_unread unwanted values (1) to NULL. It can be simply done by CASE WHEN mes_unread = 1 THEN NULL ELSE mes_unread END expression.

In the second step I create a function-based index on a result of this expression.

CREATE INDEX IX_mes_unread ON user_messages (CASE WHEN mes_unread = 1 THEN NULL ELSE mes_unread END);

After doing this, there is an index that consists of references to rows with mes_unread other than 1 because NULLs are not indexed.

I am almost done but not completely. My query still does not use the index. The reason is pretty simple - the database does not understand how this index created on the expression could help the query that does not use the expression. I hope the solution is becoming clear - I need to make the query to use the expression. Here is how the query could look like:

SELECT *
FROM user_messages
WHERE mes_receiver_usr_id = 234
    AND (CASE WHEN mes_unread = 1 THEN NULL ELSE mes_unread END) = 0;

VoilĂ ! That is all. The index is small as it does not contain references to rows with mes_unread = 1 and It is more efficient because it is not so big and heavy. The result achieved is quite similar to what filtered indexes offer in SQL Server. The biggest disadvantage is a need of modifying the query to use the indexed expression. If you can afford it, it can make a big improvement for some of your queries.