Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

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

User_1871May 24 2022 — edited May 25 2022

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?

This post has been answered by User_1871 on May 25 2022
Jump to Answer
Comments
Post Details
Added on May 24 2022
1 comment
150 views