In this article I will explain you what is the behaviour of Indexes on tables with Virtual Columns. Virtual Column is a very good feature in Oracle Database, especially because they can have values "dynamically" based on the value of  others columns. Talking about Indexes and Virtual Columns, those indexes are as if they were Function-Based Indexes, have you read my article about it? Read here. Since the value of Virtual Columns are generated based on the values of others columns you can not manipulate that value as free as  you want, I mean you can not perform DMLs against a Virtual Column, you can use it in WHERE clause and in your SELECTs as I show you below:

I will use the following table in this article:

SQL> create table dgomez(
value1 varchar(10),
value2 varchar2(10),
result as (value1||value2));

Table created.

I will try to perform an INSERT in the table:

SQL> insert into dgomez values ('dei','by','go');
insert into dgomez values ('dei','by','go')
*
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns

As you can see, Oracle is saying INSERT is not allowed.

So, let's talk about Indexes. I will follow my last style of articles: From the Concept to Internals. So Let me give you the first concept:

Concept:

"The value of Virtual Columns are not stored physically in the table, they are stored in the database metadata".

SQL> insert into dgomez (value1,value2) values ('dei','by');

1 row created.

SQL> commit;

Commit complete.

I have inserted 1 row, and the column "RESULT" should have the value "DEIBY" since that column is the concatenation of columns VALUE1 and VALUE2. If I select the data I will confirm the result:

SQL> select * from dgomez;

VALUE1     VALUE2     RESULT
---------- ---------- --------------------
dei        by         deiby

SQL>

And now, let's go to Internals:

Internals:

I will show you the block of the table where my row is stored:

data_block_dump,data header at 0x7fabd4286064
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x7fabd4286064
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f8e
avsp=0x1f7a
tosp=0x1f7a
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f8e
block_row_dump:
tab 0, row 0, @0x1f8e
tl: 10 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 3] 64 65 69<<--dei
col 1: [ 2] 62 79<<--by
end_of_block_dump
End dump data blocks tsn: 16 file#: 5 minblk 135 maxblk 135

As you can see the value "deiby" is not stored physically. the value is generated based on the value of the others columns, kind of "dynamically". But, where is that information? I mean, how Oracle knows what is the "expresion" that it has to use in order to give me he value "deiby"?

Well, that information is in the dictionary as I show you below:

SQL> select table_name, column_name, data_default from dba_tab_columns where column_name='RESULT' and table_name='DGOMEZ';

TABLE_NAME COLUMN_NAME                    DATA_DEFAULT
---------- ------------------------------ --------------------
DGOMEZ     RESULT                         "VALUE1"||"VALUE2"

Yes, we have resolved the mystery. Oracle is using the dictionary, the same behaviour that a Column with "default value". By the way, have you read my article about internals of columns with default value? You can read here.

The Concept:

"The Indexes on Virtual Columns have the same behaviour that Function-Based Indexes".

Internals:

I will create the following B-Tree Index on the virtual column:

create index vcolumn on dgomez(result);

SQL> create index vcolumn on dgomez(result);

Index created.

Now let me show you how the block looks:

Leaf block dump
===============
header address 140375975551076=0x7fabd4286064
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 1
kdxcofbo 38=0x26
kdxcofeo 8017=0x1f51
kdxcoavs 7979
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
row#0[8017] flag: ------, lock: 0, len=15
col 0; len 5; (5): 64 65 69 62 79 <<--deiby
col 1; len 6; (6): 01 40 00 87 00 00 <<---ROWID
----- end of leaf block dump -----
End dump data blocks tsn: 16 file#: 5 minblk 139 maxblk 139

As you can see the Index has the "real value" stored as the Key, in this case since we are indexing the column "RESULT" and the value of the first row is "deiby" the index is using that value as the key even if the column is "virtual Column", it is exactly the same behaviour that Function-Based Index where you specify a "function" on the column like "UPPER".

is there any difference using Bitmap Indexes? Let's see.

I will create a Bitmap Index on the virtual Column:

SQL> create bitmap index bitvcolumn on dgomez(result) ;

Index created.

Now let's take a look into internals:

Leaf block dump
===============
header address 140375975551076=0x7fabd4286064
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 4
kdxcosdc 0
kdxconro 1
kdxcofbo 38=0x26
kdxcofeo 8008=0x1f48
kdxcoavs 7970
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
row#0[8008] flag: ------, lock: 0, len=24
col 0; len 5; (5): 64 65 69 62 79 <<--Deiby
col 1; len 6; (6): 01 40 00 87 00 00<<--Beginning of ROWIDs
col 2; len 6; (6): 01 40 00 87 00 07<<--Ending of ROWIDs
col 3; len 1; (1): 00<<-Bitmap String
----- end of leaf block dump -----
End dump data blocks tsn: 16 file#: 5 minblk 139 maxblk 139


Nop, we can happily drink our coffee because we know now that there is not any especial thing behind Indexes on Virtual Columns....
oh wait!!! I spoke too soon:

Virtual Column are not supported in IO tables as you can see below:

Method 1:

SQL> create table dgomez(
value1 varchar(10) primary key,
value2 varchar2(10),
result as (value1||value2)) organization index ; 2 3 4
result as (value1||value2)) organization index
*
ERROR at line 4:
ORA-54008: expression column is not supported for an index organized table

Method 2:

SQL> create table dgomez(
value1 varchar(10) primary key,
value2 varchar2(10)) organization index ; 2 3

Table created.

alter table dgomez add result as (value1||value2);

SQL> alter table dgomez add result as (value1||value2);
alter table dgomez add result as (value1||value2)
*
ERROR at line 1:
ORA-54008: expression column is not supported for an index organized table

Hope you enjoyed this article Wink