Oracle Scratchpad

May 7, 2018

FBIs don’t exist

Filed under: Function based indexes,Indexing,Oracle — Jonathan Lewis @ 9:24 am BST May 7,2018

This is a reprint (of a reprint) of a note I wrote more than 11 years ago on my old website. I’ve decided to republish it on the blog simply because one day I’ll probably decide to stop paying for the website given how old all the material is and this article makes an important point about the need (at least some of the time) for accuracy in the words you use to describe things.

—————————————————————————-

There’s no such thing as a function-based index.

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:


rem
rem     Script:         fbi_rule.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Jan 2005
rem

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, then 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 optimizer, 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 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 each such 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!

—————————————————————————-

 Footnote (May 2018)

I’ve updated the reference to the 10g manuals (chapter 5 page 8) to include a URL, but the URL is for 11gR2 since the only 10g manual I could find online was the full pdf download.  It’s  interesting to note what restrictions on the use of “function-based” indexes are reported in this manual, and I’m not sure that all of them were true at the time, and I’m fairly sure that some of them must be false by now, which is why it’s always good to have test scripts that you can run as you upgrade.

There is an interesting variation over time for this example:

  • In 9.2.0.8 and 10.2.0.5 the predicate on trunc(n1) is a filter predicate on the table
  • In 11.1.0.7 the predicate trunc(n1) became an access predicate in the index
  • In 11.2.0.4 the optimizer (finally) declined to use the index under the rule hint (but introduced a strange side effect … more about that later)

Execution plan from 11.1.0.7


Execution Plan
----------------------------------------------------------
Plan hash value: 1429545322

---------------------------------------------
| Id  | Operation                   | Name  |
---------------------------------------------
|   0 | SELECT STATEMENT            |       |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |
|*  2 |   INDEX RANGE SCAN          | T1_I1 |
---------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"=55 AND TRUNC("N1")>=1 AND TRUNC("N1")<=10)

Note
-----
   - rule based optimizer used (consider using cbo)

In passing – the change in the execution plan from 10g to 11.1 to 11.2 does mean that anyone still using the rule-based optimizer could find that an upgrade makes a difference to rule-based execution plans.

As well as ignoring the index, 11.2.0.4 did something else that was new. I happened to have a second index on the table defined as (n1, trunc(id)); this had no impact on the execution plan for all the previous versions of Oracle, apart from switching to a full tablescan 11.2.0.4 also introduced an extra predicate:


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3617692013

----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|*  1 |  TABLE ACCESS FULL| T1   |
----------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TRUNC("N1")<=10 AND TRUNC("N1")>=1 AND
              TRUNC("ID")=TRUNC(55) AND "ID"=55)

Note
-----
   - rule based optimizer used (consider using cbo)

Some piece of code somewhere must have been looking at the second “function-based index” – or, at least, it’s virtual column definition – to be able to generate that trunc(id) = trunc(55) predicate. This was a detail introduced in 11.2.0.2, affected by fix control 9263333: “generate transitive predicates for virtual column expressions”. It’s possible that a change like this could result in changes in execution plan due to the extra predicates – even under rule-based optimisation.

Leave a Comment »

No comments yet.

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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

w

Connecting to %s

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

Powered by WordPress.com.