Oracle Scratchpad

October 2, 2013

FBI decode

Filed under: Bugs,CBO,Function based indexes,Indexing,Oracle — Jonathan Lewis @ 8:26 am BST Oct 2,2013

It probably won’t surprise many people to hear me say that the decode() function can be a bit of a nuisance; and I’ll bet that quite a lot of people have had trouble occasionally trying to get function-based indexes that use this function to behave properly. So (to put it all together and support the general directives that case is probably a better choice than decode() and that the cast() operator is an important thing to learn) here’s an example of how function-based indexes don’t always allow you to work around bad design/code. (Note: this is a model of a problem I picked up at a client site, stripped to a minimum – you have to pretend that I’m not allowed to fix the problem by changing code).

First we create some data and indexes, and gather all relevant stats:

rem     Script:         fbi_bug_10204.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Nov 2010

create table t1 (
        state   varchar2(10),
        n1      number, 
        v1      varchar2(10), 
        padding varchar2(100)

insert into t1
        rownum <= 5000  -- > comment to avoid wordpress format issue

create index t1_f1 on t1(decode(state,'CLOSED', to_number(null), n1 ));
create index t1_f2 on t1(to_number(decode(state,'CLOSED', null, n1 )));

                ownname          => user,
                tabname          =>'T1',
                estimate_percent => 100,
                method_opt       => 'for all columns size 1'

Before doing anything else, take a look at the index definitions and compare them with the definitions that Oracle has stored in the data dictionary – notice what’s gone missing in the first one and what’s appeared in the second one:

column index_name format a10		heading "Index"
column column_position format 999	heading "Posn"
column column_expression format a72	heading "Expression"

	index_name, column_position, column_expression
	table_name = 'T1'

Index      Posn Expression
---------- ---- ------------------------------------------------------------------------
T1_F1         1 DECODE("STATE",'CLOSED',NULL,"N1")

Now we run a couple of queries that should be able to use the indexes we’ve defined. We’ll even include hints so that we don’t have to rely on the statistics to allow the optimizer to pick the right index:

	/*+ index(t1 t1_f1) */
	decode(state,'CLOSED', to_number(null), n1 ) = 100

	/*+ index(t1 t1_f2) */
	to_number(decode(state,'CLOSED', null, n1 )) = 100

The effects vary with version of Oracle:

  • doesn’t use the index for the first query, but uses the index for the second query.

so let’s go up a version

  • doesn’t use the index for the first query, and doesn’t use the index for the second query which also returns the wrong result – oops;

never mind, maybe it will work in 11gR2

  • doesn’t use the index for the first query, and doesn’t use the index for the second query – but at least it doesn’t return the wrong result … because it crashes with Oracle error ORA-01722: invalid number.

Still, there’s always 12c

  • does exactly the same as

An interesting oddity – the generated predicate that causes the extra problems from 11g onwards doesn’t match the index definition (viz: the expression is transformed one way to create the index, and another way when used in a predicate). Here’s the execution plan pulled from the memory (with a little bit of cosmetic tidying):

| Id  | Operation         | Name | Rows  | Bytes | Cost  |
|   0 | SELECT STATEMENT  |      |       |       |    15 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    13 |    15 |

Predicate Information (identified by operation id):

Notice how an extra to_char() has slipped into the predicate.

I suspect the problems are appearing because of the way that decode() uses the data type of its first output parameter to coerce the type of all subsequent output parameters (which is why I used to_number(null) in the first index) – but somewhere along the line the transformation engine seems to be getting a little lost.

If you run into this type of problem and you can change the SQL then two things may help:

  • cast(null as number) didn’t cause a problem, and allowed an FBI to work.
  • case when state = ‘CLOSED’ then to_number(null) else n1 end allowed an FBI to work

Update Feb 2017

The 2nd query still fails with ORA-01722: invalid number on


1 Comment »

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

    Pingback by Tweaking | Oracle Scratchpad — April 2, 2014 @ 6:25 pm BST Apr 2,2014 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by