Intended use case for virtual columns with function-based index?

Question

What is the intended use case for virtual columns with a function-based index?

---

The reason I ask (novice):

I've been exploring options for pre-computing columns in a table. For example, in a roads table, populate a field using the spatially-intersecting zone number from a zone table. More information here: Options for computing fields.

My experience is that spatial queries are often slow — whether using Oracle's SDO_GEOMETRY datatype/functions or using a user-defined object type like Esri's ST_GEOMETRY datatype/functions. So I definitely do want to pre-compute that calculation — to avoid constantly making costly calculations every time the query is used.

At first, I was tempted to create a virtual column with a function-based index. But now (with input from others), I'm starting to wonder if precomputing a column using a function-based index might not be the right use for FBIs. Instead, I'm wondering if simply calculating a field in the table using a trigger might be a better option. (I'm aware that materialized views are also an option. But I don't have CREATE MATERIALIZED VIEW privileges.)

---

So, long story short, I suspect a virtual column and an FBI isn't appropriate for my use case. With that said, what is the right use case for a virtual column and an FBI?

Answer 1

Virtual columns don't precompute anything on their own, you're just storing the expression in the metadata. If you are planning on having filters against these expressions then you might want a virtual column for a few reasons - you don't need to write the entire expression each time, Oracle will gather statistics against the column for you so you get better plans (should your queries be complicated enough for this cardinality to matter).

A (function based) index on the virtual column makes filtering on the expression even faster as the expression is now indexed so you can do quick look ups (it's an index). You don't have to create the virtual column too - you can just create the function based index against the expression, but this means you have to type the whole expression out again each time (Oracle is quite forgiving with how you type it out though). You still get the benefits of statistics as the index will have stats and in the background there will be an invisible column which can have statistics against it (although I believe the default is for this not to happen).

If you are doing this, be careful that your expression is deterministic, the result should only change based on the row. This also includes things like NLS parameters which might give you different results. There was a bug fix a few versions ago where Oracle disabled indexing on regex expressions because they can be dependent on language settings, if you force this then you can end up with corrupt data.

Triggers can work but I've lost count on the number of times I've seen an assumption being made in a trigger which ended up causing corruption. Materialized views can allow you to have even more complex expressions being indexed but you have to store the whole result set and the additional indexes all over again. You'll also need to make sure you can refresh the materialized view appropriately, fast refreshes get more difficult as you add complexity.