Systems Engineering and RDBMS

  • Click Here for Decipher's Homepage

  • Categories

  • Questions?

    Please send your wish list of things that you would like us to write about or if you have suggestions to help improve this blog site. You can send all questions/suggestions to: Blog Support
  • Archives

  • Blog Stats


Functions on Indexed columns

Posted by decipherinfosys on January 18, 2007

A common mistake that we have seen a lot is that folks apply functions on indexed columns in their queries in the filter criteria (where clause). In most of the scenarios, as soon as you apply functions on an indexed column, the index seek operation (SQL Server lingo) is rendered useless and the optimizer is forced to do an index scan operation instead. The reason why we say “most of the times” is because say you apply an ISNULL() function on a not null column and the query is such that the column won’t have a null value (no outer join on that table), then the optimizer is smart enough to recognize that and omits the function usage.

The reason why application of a function renders the index seek operation useless is because the index pages do not store the data after the application of that function and thus the optimizer cannot perform a seek operation. Here are some test examples:

–Create a test table
–and populate it with 10000 records
–create a clustered index on the nullable column

create table test (col1 int null)
create clustered index test_ind_1 on test (col1)
declare @i int
set @i = 1
while (@i < 10000)
insert into test values (@i)
set @i = @i + 1

Now, let’s see what plans do we get for these two queries:

set showplan_text on

select * from test where col1 = 9000go

–Clustered Index Seek(OBJECT:([testDB].[dbo].[test].[test_ind_1]), SEEK:([testDB].[dbo].[test].[col1]=CONVERT_IMPLICIT(int,[@1],0)) ORDERED FORWARD)

select * from test where isnull(col1, 1) = 9000

–Clustered Index Scan(OBJECT:([testDB].[dbo].[test].[test_ind_1]), WHERE:(isnull([testDB].[dbo].[test].[col1],(1))=(9000)))

You can see that it does a scan instead of the seek operation when we apply the function. Had this been a NOT NULL column, the optimizer would have still done the seek operation despite the application of the function.

So, does that mean that when we have to apply a function on an indexed column, we are always doomed in terms of performance? Because in certain cases, application of functions on indexed columns does have a valid requirement like using the UPPER() or LOWER() functions on text columns can at times be required. In those situations, is there a work-around?

Yes, there definitely is and it is available in all three major RDBMS (Oracle, SQL Server and DB2 LUW). However, there implementations are very different. Oracle, by far has the best implementation. It has what is called as a FBI or a Function Based Index. One can create a FBI using the function applied to the column and thus the index entries are data enteries after the application of the index. In the case of SQL Server, one can make use of the computed columns i.e. create another column which will get populated automatically when a value gets inserted/updated in the driver column and the value stored in this computed column will be the one after the application of the function. One can then index this computed column and use that in their filter criteria.

One Response to “Functions on Indexed columns”

  1. […] use functions on indexed columns in the filter criteria or in the join conditions, then that will negate the usage of the index. There are ways to get that to work (using FBI in Oracle and computed […]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

<span>%d</span> bloggers like this: