Migrating from 9i to 11g – Virtual column & partitioning

1 Virtual Columns

Virtual column is a new feature in Oracle 11g – this allows derivation from a function / expression. The values are not stored within the table – they are just displayed only when calculated. It mainly gives advantage in disk space utilization – as it does not get stored in the database and it can be indexed. We can gather optimizer statistics and histograms on virtual columns.

Prior to Oracle 11g we need to either use a trigger / view to archive a functionality like this. While creating virtual columns we can either include a datatype or let the database determine the data type based on the expression. The keywords “generated always” and “virtual” are optional. A column expression can refer to a user-defined DETERMINISTIC functions. While writing insert scripts on these tables its mandatory to specify column names.

They can be indexed, partitioned, constraints and foreign keys can be created. However, virtual columns cannot be used for IOT, external tables, objects, clusters or temporary tables. These columns only work within the specified table, you cannot reference the columns within other tables.

virtual Column partitioning

A new level of partitioning is introduced with Oracle 11g – virtual column based partitioning. Virtual column will be the partition key for virtual column based partitioning. Prior to 11g it was possible to create only on database columns (physical). We need to create a normal column and make the trigger to update it then partition the same.

How could we have possibly achieved this in Oracle 9i

Oracle 9i

Oracle 9i
create table emp3
(
empno number,
depno number,
exp_years number);
/

CREATE OR REPLACE VIEW emp3_view as
select empno, depno, exp_years,
case
when exp_years = 10 then ‘Super Senior’
else ‘Ultra Senior’
end expi_level from emp3

insert into emp3 (empno, depno, exp_years) values
(1, 101, 2)
/

insert into emp3 (empno, depno, exp_years) values
(2, 102, 6)
/

insert into emp3 (empno, depno, exp_years) values
(2, 102, 15)
/

SQL> select * from emp3_view;

EMPNO DEPNO EXP_YEARS EXPI_LEVEL
———- ———- ———- ————
1 101 2 Junior
2 102 6 Senior
2 102 15 Super Senior

Elapsed: 00:00:00.04

Statistics
———————————————————-
75 recursive calls
0 db block gets
16 consistent gets
0 physical reads
0 redo size
677 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed

**********************************************************************************

Oracle 11g
create table emp2
(
empno number,
depno number,
exp_years number,
expi_level varchar2(20) generated always as
(
case
when exp_years = 10 then ‘Super Senior’
else ‘Ultra Senior’
end
) virtual
);

insert into emp2 (empno, depno, exp_years) values
(1, 101, 2)
/

insert into emp2 (empno, depno, exp_years) values
(2, 102, 6)
/

insert into emp2 (empno, depno, exp_years) values
(2, 102, 15)
/

SQL> select * from emp2;

EMPNO DEPNO EXP_YEARS EXPI_LEVEL
———- ———- ———- ————
1 101 2 Junior
2 102 6 Senior
2 102 15 Super Senior

Elapsed: 00:00:00.04

Statistics
———————————————————-
102 recursive calls
0 db block gets
20 consistent gets
0 physical reads
0 redo size
677 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed

**********************************************************************************

Virtual Indexes

Creating index on virtual column

CREATE INDEX emp2_idx on emp2 (expi_level )

Execution Plan
———————————————————-
Plan hash value: 927509100

—————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 51 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP2 | 1 | 51 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP2_IDX | 1 | | 1 (0)| 00:00:01 |
—————————————————————————————-

Predicate Information (identified by operation id):
—————————————————

2 – access(“EXPI_LEVEL”=’Junior’)

Note
—–
– dynamic sampling used for this statement

Statistics
———————————————————-
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
603 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

**********************************************************************************

Virtual column partitioning

Virtual columns as we know are expression based – they can be partitioned even though they are stored as meta data only. Prior to 11g it is restricted only to actual (physical) columns – but from 11g even virtual columns could be partitioned.

Creating partition on virtual column

create table emp2
(
empno number,
depno number,
exp_years number,
expi_level varchar2(20) generated always as
(
case
when exp_years = 10 then ‘Super Senior’
else ‘Ultra Senior’
end
) virtual
) PARTITION BY LIST (EXPI_LEVEL)
(partition l1 values (‘Junior’, ‘Senior’) tablespace SYSTEM,
partition l2 values (‘Super Senior’, ‘Ultra Senior’) tablespace USERS,
partition l3 values (default) tablespace users)

Table Created

drop table emp2