Java tips, observations, bugs and problems from the world of Spring, Weblogic, Oracle, MySQL and many other technologies...

Monday, 28 March 2011

Oracle Function Based Indexes

Oracle’s function based indexes are a powerful feature that, from experience, is rarely seen or used by Java developers. I guess that this is because if a Java developer has anything to do with SQL at all, then they’ll usually write their SQL, test it and deliver it to the source control system. At that point, the project’s DBA (if there is one and if he has the time) takes a look at the new SQL and does a bit of tuning if necessary. This makes me think that function based indexes are really a ‘DBA thing’. So, for Java programmers, in this blog I’m going to demonstrate function based indexes using a simple EMPLOYEEtable, which we’ll first need to create and populate, so bare with me as this is the boring bit.

In this example, the big idea is to be able to find employees by surname and, without any indexes on these columns, our search would initiate a slow full table scan and therefore the obvious thing to do would be to index the surname column:

create index emp_idx on employee(surname);

Enabling the explain plan functionality, we can now do search on the employee’s surname:

At this point, all is well, but as usual the requirements guy comes into your office and says that the customer now wants case insensitive employee surname searches, and you spend a little time redeveloping your SQL and come up with:

One thing to note is that a function based index is highly tuned to a specific where clause(s); for example, if the requirement changed again and you had to search on full names, you’d have to both write a new piece of SQL and, in order to maintain performance, add a new specific index:

There you have it, this is a simple outline of function based indexes, there is a lot more to this subject; such as using your own PL/SQL functions as indexes and getting hold of the correct user privileges, so more may follow...