JL Computer Consultancy

There's no such thing as a Function-Based Index

(Recreated from an original written for the Dizwell Wiki).

Jan 2007

Well, okay, that’s what the manuals call them, but it would be so much better if they were called “indexes with virtual columns” - because that’s what they are, and that’s a name that would eliminate confusion.

To demonstrate what I mean, ask yourself this question: “Can the rule based optimizer use a function-based index ?”. The answer is ‘Yes’, as the following code fragment demonstrates: 

create table t1 as
select
         rownum                         id,
         dbms_random.value(0,500)       n1,
         rpad('x',10)                   small_vc,
         rpad('x',100)                  padding
from
         all_objects
where
         rownum <= 3000
;
 
create index t1_i1 on t1(id, trunc(n1));
 
set autotrace traceonly explain
 
select
         /*+ rule */
         small_vc
from
         t1
where    id = 55
and      trunc(n1) between 1 and 10
;
 
set autotrace off
 
Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=HINT: RULE
1   0    TABLE ACCESS (BY INDEX ROWID) OF 'T1'
2   1      INDEX (RANGE SCAN) OF 'T1_I1' (NON-UNIQUE)

Last time I asked an audience if the rule-based optimizer (RBO) could use a function-based index, most of them thought the answer was no. Even the Oracle manuals make the same mistake - for example in the 10g Release 2 Application Developers Guide p5-8, one of the restrictions on function-based indexes is “Only cost based optimization can use function-based indexes”.

If I had asked the audience “Can the rule-based optimizer use an index which includes a virtual column ?” I wonder how many of them would have paused for thought, and asked themselves what would happen if the index started with “ordinary” columns and the “function-based” bit was later on in the index.

The manuals should, of course, state: “The rule-based optimizer cannot take advantage of any virtual columns in an index, or of any columns that follow the first virtual column”. Given a correct name, and a correct description of functionality you can then conclude that if the first column is a virtual column the rule-based optimizer won’t use the index.

I’m not suggesting, by the way, that you should be using the rule-based optimiser, or even that this specific example of functionality is going to be particularly beneficial to many people (RBO still uses the “trunc(n1)” as a filter predicate after reaching the table rather than as an access predicate – or even filter predicate - on the index); but it does it demonstrate how easy it is for the wrong name, or terminology, to distract people from the truth.

And here’s another thought for Oracle Corporation. Since it seems to be easy to implement virtual columns (there is a hidden entry for column in the data dictionary, and the text of the function defining the column appears as the default value), why should they exist only in indexes? Why can’t we have virtual columns which aren’t indexed, so that we can collect statistics on a virtual column and give the optimizer some information about the data distribution of some commonly used expression that we don’t actually want to build an index on.  (Update Jan 2007 – this is likely to happen in 11g according to ‘sneak preview’ presentations made by Oracle at OW2006.

P.S. There really are function-based indexes in Oracle. But Oracle Corp. calls them domain indexes (or co-operative indexes) and tells you that the things you build them with are operators, not functions ... which actually makes them operator-based indexes!

Back to Index of Topics