Virtual Column Solution-Indexes

The idea here is to first create a virtual column applying a SQL function on the extended column that returns a value less than 6398 bytes. Then that virtual column can be indexed, and this provides a mechanism for better performance when issuing queries against extended columns. An example will demonstrate this. First, create a table with an extended column:
$ sqlplus eoda/foo@PDB1
SQL> create table t(x varchar2(32767)); Table created.

Now insert some test data into the table:
SQL> insert into t select to_char(level)|| rpad(‘abc’,10000,’xyz’) from dual connect by level < 1001
unionselect to_char(level)from dual connect by level < 1001;2000 rows created.

Now suppose that you know the first ten characters of the extended column are sufficiently selective enough to return small portions of the rows in the table. Therefore, you create a virtual column based on a substring of the extended column:
SQL> alter table t add (xv as (substr(x,1,10))); Table altered.

Now create an index on the virtual column and gather statistics:
SQL> create index te on t(xv);Index created.
SQL> exec dbms_stats.gather_table_stats(user,’T’); PL/SQL procedure successfully completed.

Now when querying the virtual column, the optimizer can take advantage of the index in equality and range predicates in the WHERE clause, for example:
SQL> set autotrace traceonly explain
SQL> select count(*) from t where x = ‘800’;

Notice that even though the index is on the virtual column, the optimizer can still use it when querying directly against the extended column X (and not the virtual column XV). The optimizer can also use this type of index in a range-type search:
SQL> select count(*) from t where x >’800′ and x<‘900’;

Like the SUBSTR function, you can also base a virtual column on the STANDARD_HASH function. The STANDARD_HASH function can be applied to a long character string and return a fairly unique RAW value much less than 6398 bytes. Let’s look at a couple of examples using a virtual column based on STANDARD_HASH.

Assuming the same table and seed data as used with the prior SUBSTR examples, here we add a virtual column to the table using STANDARD_HASH, create an index, and generate statistics:
SQL> alter table t add (xv as (standard_hash(x))); Table altered.
SQL> create index te on t(xv);Index created.
SQL> exec dbms_stats.gather_table_stats(user,’T’); PL/SQL procedure successfully completed.

The STANDARD_HASH works well when using equality predicates in the WHERE clause.
For example:
SQL> set autotrace traceonly explain
SQL> select count(*) from t where x=’300′;

The index on a STANDARD_HASH–based virtual column allows for efficient equality-­ based searches, but does not work for range-based searches, as the data is stored in an index based on the randomized hash value, for example:
SQL> select count(*) from t where x >’800′ and x<‘900’;