A virtual column applies a function to a column in the table. The database only computes this at runtime. The value is not stored in the table.
So instead of creating a function-based index on standard_hash or substr, you can use one of these in a virtual column. Then create the index on that:
alter table acco_table
add text_hash varchar2(40 char) as (
standard_hash ( text_column )
);
create index vc_text_hash_index
on acco_table ( text_hash );
There are a couple of advantages to doing this over a function-based index:
The optimizer can gather statistics on the virtual column
You can see what the indexed values are, making the application easier to understand.
This can also be useful if you want to extract the middle of the string for some reason. For example, removing a common prefix or getting certain fields from delimited or fixed-width data.
But this highlights a general problem with indexing free-text fields. Often you want to search for specific values within a string. So you need wildcards at the start and end of the search string:
select * from acco_table
where text_column like ‘%middle text%’;
Standard indexes are practically useless for these types of query. Bringing the question of why you’re bothering to index these columns at all.
Luckily there is a way to index large text strings in Oracle Database, allowing you to do ad-hoc searches of them.
