Oracle Scratchpad

April 2, 2014

Tweaking

Filed under: Function based indexes,Indexing,Oracle — Jonathan Lewis @ 6:24 pm BST Apr 2,2014

The following question came up on OTN recently:

Which one gives better performance? Could please explain.

1) nvl( my_column, ‘N’) <> ‘Y’

2) nvl( my_column, ‘N’) = ‘N’

It’s a question that can lead to a good 20 minute discussion – if you were in some sort of development environment and had a fairly free hand to do whatever you wanted.

The most direct answer is that you could expect the performance to be the same whichever option you chose – but the results might be different, of course, unless you had a constraint on my_column that ensured that it would hold only null, ‘Y’, or ‘N’.  (Reminder:  the constraint check (my_column in (‘Y’,’N’) will allow nulls in the column).

On the other hand, if you create a function-based index on nvl(my_column,’N’) then the second option would give the optimizer the opportunity to use the index – which could make it the more efficient option if a sufficiently small quantity of the data from a large table matched the predicate. Of course in this case you would need a histogram on the hidden column definition supporting the index so that the optimizer could detect the data skew.

But if a function-based index is a step in the right direction it’s worth thinking about how to pick the best function-based index.


create index t1_i1 on t1(case my_column when 'Y' then null else 'N' end);

execute dbms_stats.gather_table_stats(user,'t1',method_opt=>'for all columns size 1');

select * from t1 where case my_column when 'Y' then null else 'N' end = 'N';

Provided you can change the queries that use the original predicate, you can create a function-based index that is the smallest possible index to assist your query, and you won’t need a histogram to allow the optimizer to get the correct cardinality since there will be just one distinct value in the index (and the underlying column).

It’s possible to go a little further – what if the typical query that started this discussion was really something like:


select  *
from    t1
where   nvl(my_column, 'N') = 'N'
and     other_column = {some value}

If the nvl() predicate always returned a small fraction of the data we might engineer an index to handle the requirement of the entire where clause:


create index t1_i2 on t1(case my_column when 'Y' then null else other_column end);
execute dbms_stats.gather_table_stats(user,'t1',method_opt=>'for all hidden columns size 254');

In this case you might need a histogram on the hidden column definition supporting the index, which is why I’ve changed my method_opt. We’ve constructed an index that is the smallest possible index that will satisfy the query as precisely as possible while giving the optimizer the best chance of producing accurate cardinality estimates. [Update: until the appearance of Kirill's comment (#1) I forgot to point out that once again you have to change the original SQL from using the nvl() expression to using a case expression that matches the index.]

Footnote

It would be easy to fall into the trap of playing around with this type of tweaking every time you hit a performance problem. Bear in mind, though, that even indexes which are as small and accurately targeted as possible can introduce various types of overhead (contention, cost of gathering stats, instability of histogram collection); so always try to think through the potential costs as well as the benefits of such an approach – is it really worth the effort.

It’s also worth noting that things don’t always work the way you expect.

Finally, of course, you might choose to create “proper” virtual columns in 11g so that you can refer to them by name, rather than handling them indirectly as you have to when they are “undocumented” hidden columns generated by function-based indexes.

 

2 Comments »

  1. Hi jonathan
    Thanks for a very interesting case. However I can not force index t1_i2 to be used. Can you explain why. Example is below:

    create table t1 (my_column varchar2(1), other_column number(10));
    insert into t1 values(NULL,10);
    insert into t1 values(NULL,10);
    insert into t1 values(NULL,10);
    insert into t1 values(NULL,10);
    insert into t1 values(NULL,10);
    insert into t1 values(NULL,10);
    insert into t1 values(NULL,10);
    insert into t1 values(NULL,10);
    insert into t1 values(NULL,10);
    insert into t1 values(NULL,10);
    insert into t1 values('Y',5);
    insert into t1 values('Y',5);
    insert into t1 values('Y',5);
    insert into t1 values('Y',5);
    insert into t1 values('Y',5);
    insert into t1 values('Y',5);
    insert into t1 values('N',3);
    insert into t1 values('N',3);
    insert into t1 values('N',3);
    commit;
    create index t1_i2 on t1(case my_column when 'Y' then null else other_column end);
    execute dbms_stats.gather_table_stats(user,'t1',method_opt=>'for all hidden columns size 254');
    select * from t1 where nvl(my_column, 'N') = 'N' and other_column = 3;
    select /*+ INDEX (t1 t1_i2)*/ * from t1 where nvl(my_column, 'N') = 'N' and other_column = 3;
    

    –> same result – FTS

    Thanks

    — Kirill Loifman

    Comment by Kirill — April 15, 2014 @ 10:00 am BST Apr 15,2014 | Reply

    • Kirill,

      I forgot to point out that you would also have to change the nvl() expression in the original SQL to an equivalent case expression matching the alternative index.

      Comment by Jonathan Lewis — April 15, 2014 @ 3:21 pm BST Apr 15,2014 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

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

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,266 other followers