Virtual Column Based Partitioning in Oracle 11g
Oracle 11g has another new type of partitioning called Virtual Column Based partitioning. Virtual Column based partitioning allows data partitioning to be based on virtual column as the partitioning key. Virtual columns are new feature in 11g that allow derivation from a function or expression evaluation results. As such, values for virtual columns are not stored within the table, rather the values for the virtual columns are evaluated on demand when calculated. Indexes and tables can be partitioned as such on new virtual columns.
For instance, to add a new virtual column to an existing table, SCOTT.EMP, we would execute the following statement.
SQL> ALTER TABLE EMP ADD (yearly_sal as (sal*comm));
SQL> select ename, sal, comm, yearly_sal
ENAME SAL COMM YEARLY_SAL
Now when we issue the above query against the SCOTT.EMP table, we have the new virtual column, yearl_sal that can be used in calculations.
If we wanted to create a new table that uses virtual columns we could issue a CREATE TABLE like that found in the following figure example.
SQL> CREATE TABLE v_emp
Virtual columns have the following limitations.
Now letís walk through an example of how to create a virtual column based partition table in the following exercise. First, we create a new table called workers.
SQL> create table workers
Now that we have created our new virtual column based partition table, letís insert some sample test data to see how row placement in the partitions is evaluated.
SQL> insert into workers
1 row created.
SQL> insert into workers
Virtual column based partitions have the same functionality as that in range or list based partitions. Now letís take a peek at how the optimizer views virtual based partitions and row position.
SQL> select fname, lname, salary, bonus, tcomp
SALARY BONUS TCOMP
Here we see that the partitions return values based on the virtual column based partitions. By viewing the execution plan from dbms_xplan we can view the use of the virtual column based partitions as shown below.
SQL> explain plan for
SQL> select * from table(dbms_xplan.display);
9 rows selected.
The nice thing about virtual column based partitions is that they allow development staff to create partitions based on business requirements when other types of partitioning are limited in terms of flexibility when business rules call for partitioning based on specific needs for the application.
Copyright © 1996 - 2014 by Burleson. All rights reserved.
Oracleģ is the registered trademark
of Oracle Corporation.