Friday, October 31, 2008

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.

4 comments:

Anonymous said...

Nice anecdote.

Tony said...

This type of thing is why I got in the habit of always issues a

exec dbms_stats.gather_table_stats

after I create a new index. Even though I am now working on 11gR1 I still do this. I guess I don't want to leave it up to chance.

Mark Freeman said...

Does 10g automatically gather stats on the hidden columns it creates for function-based indexes or is it still necessary to run gather_table_stats on every table with a function_based index with method_opt=>'FOR ALL HIDDEN COLUMNS SIZE 1'?

Byte64 said...

Good question Mark,
actually i don't know.
I'd need to perform a test to confirm or deny, this kind of scenarios needs to be tested whatever the documentation says or doesn't.

Thank you
Flavio

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles