Forum Stats

  • 3,873,041 Users
  • 2,266,499 Discussions


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

User_1871 Member Posts: 247 Red Ribbon
edited May 25, 2022 7:42PM in SQL & PL/SQL

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?


Best Answer