Look Ma, no FBI (Function Based Index)

At the customer site where I work, we are using an Oracle 9i. More specific a 9.2.0.5 database.
While demonstrating to one of my colleagues when an index can and cannot be used, I noticed something strange when I ran
the same script on my laptop. My laptop has an Oracle 10r2 on it.

I told my colleague that you would need a FBI, Function Based Index, when you apply a function like UPPER to an indexed column. That is
if you would want to use the index in the first place.

FBI in action

Starting up SQL*Plus to demonstrate, I ran this script:

SQL&gt; create table t<br />  2  as<br />  3  select *<br />  4    from all_objects<br />  5  /<br /><br />Table created.<br /><br />SQL&gt; create index tidx on t (owner, object_name)<br />  2  /<br /><br />Index created.<br /><br />SQL&gt; set autot trace expl<br />SQL&gt; select owner, object_name<br />  2    from t<br />  3   where owner = 'ALEX'<br />  4     and object_name = 'T'<br />  5  /<br /><br />Execution Plan<br />----------------------------------------------------------<br />   0      SELECT STATEMENT Optimizer=CHOOSE<br />   1    0   INDEX (RANGE SCAN) OF 'TIDX' (NON-UNIQUE)<br />

As you can see in the execution plan, it uses the index on OWNER and OBJECT_NAME as expected. When you apply the UPPER function to
OWNER and OBJECT_NAME it will go from an INDEX RANGE SCAN to a FULL TABLE SCAN:

SQL&gt; select owner, object_name<br />  2    from t<br />  3   where upper (owner) = 'ALEX'<br />  4     and upper (object_name) = 'T'<br />  5  /<br /><br />Execution Plan<br />----------------------------------------------------------<br />   0      SELECT STATEMENT Optimizer=CHOOSE<br />   1    0   TABLE ACCESS (FULL) OF 'T'<br />

When an index is created using the UPPER function in the definition, the index can be used. Or can it?

SQL&gt; create index fbi_idx on t (upper (owner), upper (object_name))<br />  2  /<br /><br />Index created.<br /><br />SQL&gt; select owner, object_name<br />  2    from t<br />  3   where upper (owner) = 'ALEX'<br />  4     and upper (object_name) = 'T'<br />  5  /<br /><br />Execution Plan<br />----------------------------------------------------------<br />   0      SELECT STATEMENT Optimizer=CHOOSE<br />   1    0   TABLE ACCESS (FULL) OF 'T'<br /><br />

Even though I used the same functions in the Index creation and the Query, the execution plan does not show the use
of the index.
After gathering statistics on the table, the index is being used:

SQL> exec dbms_stats.gather_table_stats (user, 'T')

PL/SQL procedure successfully completed.

SQL> select owner, object_name
  2    from t
  3   where upper (owner) = 'ALEX'
  4     and upper (object_name) = 'T'
  5  /

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=32)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=1 Bytes=
          32)

   2    1     INDEX (RANGE SCAN) OF 'FBI_IDX' (NON-UNIQUE) (Cost=1 Car
          d=1)

So, there you have it. When you apply a function on an indexed column, you can use a Function Based Index in order to use the index aswell.
And just for completeness, the version of the database:

SQL&gt; set autot off<br />SQL&gt; select *<br />  2    from v$version<br />  3  /<br /><br />BANNER<br />----------------------------------------------------------------<br />Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production<br />PL/SQL Release 9.2.0.5.0 - Production<br />CORE    9.2.0.6.0       Production<br />TNS for Compaq Tru64 UNIX: Version 9.2.0.5.0 - Production<br />NLSRTL Version 9.2.0.5.0 - Production<br /><br />SQL&gt;<br /><br />

Things change: Oracle 10r2 on my Laptop

Like I said before I ran the same script on my laptop, why I don’t know, but I did.

SQL&gt; create table t<br />  2  as<br />  3  select *<br />  4    from all_objects<br />  5  /<br /><br />Table created.<br /><br />SQL&gt; create index tidx on t (owner, object_name)<br />  2  /<br /><br />Index created.<br /><br />SQL&gt; select owner, object_name<br />  2    from t<br />  3   where owner = 'ALEX'<br />  4     and object_name = 'T'<br />  5  /<br /><br />Execution Plan<br />----------------------------------------------------------<br />Plan hash value: 3897160578<br /><br />-------------------------------------------------------------------------<br />| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |<br />-------------------------------------------------------------------------<br />|   0 | SELECT STATEMENT |      |     1 |    34 |     1   (0)| 00:00:01 |<br />|*  1 |  INDEX RANGE SCAN| TIDX |     1 |    34 |     1   (0)| 00:00:01 |<br />-------------------------------------------------------------------------<br /><br />Predicate Information (identified by operation id):<br />---------------------------------------------------<br /><br />   1 - access(&quot;OWNER&quot;='ALEX' AND &quot;OBJECT_NAME&quot;='T')<br /><br />Note<br />-----<br />   - dynamic sampling used for this statement<br />

Nothing unusual here, like expected the index is being used in the execution plan.
Running the next part of the script did show something that I didn’t expect:

SQL&gt; select owner, object_name<br />  2    from t<br />  3   where upper (owner) = 'ALEX'<br />  4     and upper (object_name) = 'T'<br />  5  /<br /><br />Execution Plan<br />----------------------------------------------------------<br />Plan hash value: 2008626562<br /><br />-----------------------------------------------------------------------------<br />| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |<br />-----------------------------------------------------------------------------<br />|   0 | SELECT STATEMENT     |      |     5 |   170 |    71   (5)| 00:00:01 |<br />|*  1 |  INDEX FAST FULL SCAN| TIDX |     5 |   170 |    71   (5)| 00:00:01 |<br />-----------------------------------------------------------------------------<br /><br />Predicate Information (identified by operation id):<br />---------------------------------------------------<br /><br />   1 - filter(UPPER(&quot;OWNER&quot;)='ALEX' AND UPPER(&quot;OBJECT_NAME&quot;)='T')<br /><br />Note<br />-----<br />   - dynamic sampling used for this statement<br /><br />

The index is being used as well. Even though it changed from an INDEX RANGE SCAN to an INDEX FAST FULL SCAN. The index
is still being used. Wow, this CBO is really smart. Don’t you just love it?

Again for completeness, the version information

SQL&gt; set autot off<br />SQL&gt; select *<br />  2    from v$version<br />  3  /<br /><br />BANNER<br />----------------------------------------------------------------<br />Personal Oracle Database 10g Release 10.2.0.1.0 - Production<br />PL/SQL Release 10.2.0.1.0 - Production<br />CORE    10.2.0.1.0      Production<br />TNS for 32-bit Windows: Version 10.2.0.1.0 - Production<br />NLSRTL Version 10.2.0.1.0 - Production<br /><br />SQL&gt;<br />

Jumping to conclusions

While typing all this, it dawned to me… Dynamic Sampling is used default on an Oracle 10. It even says so in the note of the execution plan. What if I would gather statistics on the
Oracle 9i? Would the CBO know that it can use the index instead of going after the full table? Let’s give it a whirl:

SQL&gt; exec dbms_stats.gather_table_stats (user, 'T')<br /><br />PL/SQL procedure successfully completed.<br /><br />SQL&gt; select owner, object_name<br />  2    from t<br />  3   where upper (owner) = 'ALEX'<br />  4     and upper (object_name) = 'T';<br /><br /><br />Execution Plan<br />----------------------------------------------------------<br />   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=2 Bytes=64)<br />   1    0   INDEX (FAST FULL SCAN) OF 'TIDX' (NON-UNIQUE) (Cost=4 Card<br />          =2 Bytes=64)<br />

That’s what you get when you jump to conclusions. I was too hasty…
But still…. It is still amazing that the CBO will use the index, even though it is not a Function Based Index.

…one more thing

Using SQL Developer to look at the table definition, it showed on the INDEXES-tab the column names as
SYS_NC00014$ and SYS_NC00015$. When I looked in PL/SQL Developer, the index definition showed the same
as the one I entered.
Strange, but what really struck me is that you can use these system generated columns in your query as well. No need to
do so, but it is my database and I thought I give it a try:

SQL&gt; select owner, object_name<br />  2    from t<br />  3   where SYS_NC00014$ = 'ALEX'<br />  4     and SYS_NC00015$ = 'T'<br />  5  /<br /><br /><br />Execution Plan<br />----------------------------------------------------------<br />Plan hash value: 1971231365<br /><br />---------------------------------------------------------------------------------------<br />| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |<br />---------------------------------------------------------------------------------------<br />|   0 | SELECT STATEMENT            |         |     5 |   170 |     2   (0)| 00:00:01 |<br />|   1 |  TABLE ACCESS BY INDEX ROWID| T       |     5 |   170 |     2   (0)| 00:00:01 |<br />|*  2 |   INDEX RANGE SCAN          | FBI_IDX |     1 |       |     1   (0)| 00:00:01 |<br />---------------------------------------------------------------------------------------<br /><br />Predicate Information (identified by operation id):<br />---------------------------------------------------<br /><br />   2 - access(UPPER(&quot;OWNER&quot;)='ALEX' AND UPPER(&quot;OBJECT_NAME&quot;)='T')<br /><br />Note<br />-----<br />   - dynamic sampling used for this statement<br />