Performance Tip: Use Function-based indexes

January 19, 2007 at 8:29 pm | Posted in Documentum and Oracle, Performance | Leave a comment

Why does a query like

select * from dm_sysobject where upper(object_name) like 'A%'

seem to take longer and longer as the number of objects increase whereas

select * from dm_sysobject where object_name like 'A%'

continues to perform well?

The answer is probably that the database execution plan for the 1st query uses a tablescan to access the table dm_sysobject_s. The 2nd query will probably use an index on dm_sysobject_s.object_name. If a function (in this case UPPER) is applied to the column in the predicate (where clause) it is not possible to use the index on the column.

If you are using Oracle there is a very useful feature called ‘Function-based indexes’ that allows Oracle to use an index even when a function has been applied to the column. You have to create the index at the database level rather than using the Documentum MAKE_INDEX API. So in this case you would do the following:

  1. Open SQLPlus and login as the docbase owner
  2. create index fn_idx_upper_object_name on dm_sysobject_s(upper(object_name));

This command creates a function-based index called fn_idx_upper_object_name.

Once you have done this it is necessary to ensure the database statistics include the new index. Do this by running the Documentum admin job dm_UpdateStats; alternatively to update the stats for just this index use the following command in SQLPlus:

execute dbms_stats.gather_index_stats(ownname=>USER,indname=>'FN_IDX_UPPER_OBJECT_NAME');

You should note that when this feature was first introduced sometime in Oracle 8 it was necessary to set some init.ora parameters QUERY_REWRITE_ENABLED and QUERY_REWRITE_INTEGRITY. At some point this requirement was dropped and certainly it is not necessary when using function-based indexes in Oracle 9.2 or 10. This is despite the fact that the Oracle Performance Tuning Guide for 9.2 still says it is necessary!

Advertisements

Leave a Comment »

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Blog at WordPress.com.
Entries and comments feeds.

%d bloggers like this: