Function-based indexes and the easy life of a database developer

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

Yesterday i was working at a big customer site on a 9.2.0.7 database and i was checking the results of a procedure before and after the extensive change and in particular i was trying to understand why i was getting more records after the last modification.
In order to do so, i had to perform a piece-wise comparison of certain sub-strings taken from a column in a table where the only available index was the on the numeric primary key.

You can see my original query below:

select
substr(message, 33, 5) as pfx
,substr(message, 87, 9) as tt_a
,substr(message, 107, 9) as fv_a
,substr(message, 127, 7) as id_a
from test_fidx_tab a
where a.master_id = 110539
and not exists (
select 1
from test_fidx_tab b
where b.master_id = 110538
and substr(b.message, 33, 5) = substr(a.message, 33, 5)
and substr(b.message, 87, 9) = substr(a.message, 87, 9)
and substr(b.message, 107, 6) = substr(a.message, 107, 6)
and substr(b.message, 127, 7) = substr(a.message, 127, 7)
);

The table contained roughly 360,000 rows and a first attempt to execute this query did not return any results within a reasonable time (several minutes), so i started thinking of creating a function-based index on the table:

create index fx_1 on test_fidx_tab(
master_id
,substr(message, 33, 5)
,substr(message, 87, 9)
,substr(message, 107, 6)
,substr(message, 127, 7)
);

But after creating the index, the execution plan of the query did not change.
After some attempts of changing the index structure, in case the optimizer didn't like it for some reason, i finally manually added an optimizer hint.

select
substr(message, 33, 5) as pfx
,substr(message, 87, 9) as tt_a
,substr(message, 107, 9) as fv_a
,substr(message, 127, 7) as id_a
from test_fidx_tab a
where a.master_id = 110539
and not exists (
select /*+ INDEX(B FX_1) */ 1
from test_fidx_tab b
where b.master_id = 110538
and substr(b.message, 33, 5) = substr(a.message, 33, 5)
and substr(b.message, 87, 9) = substr(a.message, 87, 9)
and substr(b.message, 107, 6) = substr(a.message, 107, 6)
and substr(b.message, 127, 7) = substr(a.message, 127, 7)
);

The execution plan was now taking into account the special function-based index and the execution of the query lasted less than a second.
But wait a minute, why didn't the optimizer pick up the index automatically?

After working for years on 10g, i had almost forgotten that on 9iR2 and earlier, the cost based optimizer would not work properly until the statistics were collected on the index and on the underlying table.

exec dbms_stats.gather_table_stats(
ownname => 'TEST',
tabname => 'TEST_FIDX_TAB',
cascade => TRUE);

After gathering the statistics, the function based index was picked up by the optimizer *without* having to specify any hint.

Then i decided to repeat this exercise on a 10gR2 instance where i could verify that the optimizer was instantly picking up the function-based index because oracle 10g automatically gathers the statistics for objects having stale or missing statistics. Again, no need of adding optimizer hints.

Note that the automatic collection of statistics feature appeared in 10gR1.

May be it's not enough to justify an upgrade for a company, but certainly it's one of those things that makes happy a developer, especially when you have to suddenly downgrade the brain after years of easy life with Oracle 10g.