techblog 

Functional indexes are a very useful feature of Oracle that in effect pre-calculate the result of applying a function to a table column and index that. This is often used to allow case insensitive queries. So if you have a table CUSTOMERS with a NAME column you can create an index on LOWER(NAME) to index the name converted to lower case. So long as your code also uses LOWER(NAME) (and not UPPER(NAME)!) in its queries, then your case insensitive lookups will be nice and fast.

I recently ran across a bit of a gotcha when using functional indexes with outer joins. I will describe this with the following simple tables. They describe some data held by a large supermarket:

CREATE TABLE CUSTOMERS(ID NUMBER, NAME VARCHAR2(100));
CREATE TABLE LOYALTY_CARDS(ID NUMBER, CUSTOMER_ID NUMBER, EMAIL VARCHAR2(1000));

So there is a table of customers and a table of loyalty cards (and hopefully a foreign key constraint between the two, which I’ve left out). Suppose a customer can either have a loyalty card or not. They can’t have more than one. The EMAIL column holds their email address - they must provide one to have a loyalty card. We also have a PL/SQL function Clean_Up() that removes extraneous whitespace from the email, puts it in lower case etc.

Now, since we often want details about a customer complete with their loyalty card (if any) we define a view that encapsulates this join. We want customers with and without loyalty cards to be returned, so we use an outer join:

CREATE OR REPLACE VIEW CUSTOMER_AND_CARD AS
SELECT C.ID, C.NAME, L.ID LOYALTY_CARD_ID, Clean_Up(L.EMAIL) EMAIL
FROM CUSTOMERS C, LOYALTY_CARDS L
WHERE C.ID = L.CUSTOMER_ID (+);

(I use the old fashioned Oracle syntax for outer joins because I’m an old time Oracle user - sorry). Now suppose we want to run queries against this view, based on the cleaned up email address. Fair enough, so we create a functional index on Clean_Up(EMAIL). So when we run a query such as:

SELECT *
FROM CUSTOMER_AND_CARD
WHERE EMAIL = 'chris@nominett.orc.uk';

we would expect it to use the functional index to quickly find if there was a matching loyalty card. If not, it would quickly return no rows, if so, it would quickly return the data.

Unfortunately this is not the case. I think this is because the function is applied to the result of the outer join. So in theory it could be applied to a ‘null row’ (is that the correct terminology?) returned when there is no matching row in the outer joined table. When I hit this particular problem it ended up doing a full table scan on the equivalent of the CUSTOMER table which, as in this example, would likely be catastrophic for performance.

The solution is to reassure the optimizer that you are not interested in the possibility of a ‘null row’ coming from the LOYALTY_CARD table:

SELECT *
FROM CUSTOMER_AND_CARD
WHERE EMAIL = 'chris@nominett.orc.uk'
AND LOYALTY_CARD_ID IS NOT NULL

Now the optimizer will start using the functional index again and all is well with the world.

I can’t help but think that this is in fact a bug, or at the very least a limitation of functional indexes. If you are using an ordinary (i.e. non-functional) index this problem does not occur.