Function-based index when not using index access

Question

I'm using a function-based index with a user-defined function for the first time, and have stumbled across a performance problem when the index can't be used.

Internally, a function-based index seems to generate a hidden table column (of type varchar2(4000), since my function returns a varchar2), and indexes that. That works fine when the index is used, but sometimes we have to do a full table scan using the function as a filter, and in that case I see a performance degradation by a factor of 6. Seems in that case, Oracle does not use the hidden column, but recomputes the function for each row, make the query CPU-bound instead of IO-bound.

Is there a way to make Oracle use that hidden column also for filtering? I wonder if I'm missing some rewrite options or something along those lines.

If not, I'll have to define the column myself and using a trigger to keep it up to date. I'd prefer using the function-based index for transparency and easier maintanance.

Seems tricky, because that "hidden column" is not stored in the table itself, it is what makes up the index. So it is not available during a table scan. You'd have to make Oracle go through the index (or at least join it somehow) to get to that precalculated data. — Thilo
– Thilo, Commented Jul 24, 2012 at 9:39
If the column is not actually there, it's clear that I can't have the best of both worlds. I just looked at dba_tab_cols and thought that there was a real, just hidden column. If it's only a virtual construct for building the index, that answers my question - a need a real column for the scan. — Chris
– Chris, Commented Jul 24, 2012 at 9:54
Well, it is real in the sense that it exists in a materialized form inside the index. As opposed to a virtual column that is only a definition with data computed on-the-fly, same as derived columns in a view. — Thilo
– Thilo, Commented Jul 24, 2012 at 9:57
Yes, I was aware of that, which is why I was so astonished at seeing that SYS_NC....$ column there - my error to assume it was in the table. I'll go the column+trigger way then. Thanks! — Chris
– Chris, Commented Jul 24, 2012 at 10:07

APC · Accepted Answer · 2012-07-24 12:13:59Z

3

Which version of Oracle are you using? If it's 11g you should try using a virtual column. This is a column whose value is derived from an expression or a literal. They're defined as part of the table, so they have a visibility in a table DESC (unlike a function-based index). We can build indexes on virtual columns. And they are maintained automatically, without the need for a trigger.

So you can add a virtual column to your table using the same expression as your function based index. Perhaps like this:

create table t23
   (id number
    , col_a varchar2(10)
    , vcol_a as (upper(substr(col_a, 1, 1)))
  )
/

Note that we cannot insert or update a virtual column. So you need to specify the projection of the insert statement:

insert into t23 (id, col_a) values (1, 'this is a test');

Then you can build a regular index on the virtual column:

create index t23_vc_i on t23(vcol_a)
/

Don't forget to drop your function based index!

edited Jul 24, 2012 at 12:13

answered Jul 24, 2012 at 10:55

APC

147k20 gold badges180 silver badges289 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Chris Over a year ago

Unfortunately, most of our customers still use 10g. But still thanks for bringing this feature to my attention. I like that I could specify a type for this column, because I find the varchar2(4000) implicitly used by the function-based index a bit problematic (and I think the substr-workaround is too flaky).

Thilo Over a year ago

A virtual column, however, is not materialized. So it is also computed on the fly, and the CPU bottleneck will not be mitigated. Building an index on it is equivalent to the function index that is in place now.

Community · Accepted Answer · 2017-05-23 11:48:13Z

There is no general way a function-based index can be used in a table scan.

The assumption I made in my question, namely "Internally, a function-based index seems to generate a hidden table column ...", is simply wrong: the results of the function are not stored in a table column, but only in the index.

So, unless there is a way to access the index when performing the scan (the only way I can think of is if it's a combined index starting with the key column(s)), the precomputed function result can't be used.

The 11g "virtual column" feature does not help either, as the column is not stored in the table, but computed on-the-fly, similar to using the function in a view.

In summary: if you can't rule out table scans, and your function call is expensive (slow), use a real column in combination with a "before insert or update" trigger. A function-based index won't do.

(Note: Added this answer because I did not want to let this question stand as unanswered. The credit for the answer belongs to thilo, who pointed out that the column is never materialized).

Collectives™ on Stack Overflow

Function-based index when not using index access

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related