Donald K. Burleson Advanced Oracle Tips and Tricks for the Oracle DBA

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));

Table altered.

SQL> desc emp

 Name                                      Null?    Type
 ----------------------------------------- -------- --------------------------- 
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)
 YEARLY_SAL                                         NUMBER

SQL> select ename, sal, comm, yearly_sal
  2  from emp;

ENAME             SAL       COMM YEARLY_SAL
---------- ---------- ---------- ----------
SMITH             800
ALLEN            1600        300     480000
WARD             1250        500     625000
JONES            2975
MARTIN           1250       1400    1750000

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 virtual_emp (
  2  empno      NUMBER  PRIMARY KEY,
  3  ename      VARCHAR2(20) NOT NULL,
  4  social_id  NUMBER(9),
  5  sal        NUMBER(7,2),
  6  hrly_pay   NUMBER(7,2) GENERATED ALWAYS AS (sal/2000));

Table created.
 

Note that the key syntax to use when creating tables that make use of virtual columns is the GENERATED ALWAYS phrase. An optional syntax parameter, VIRTUAL, can be used in addition when creating virtual columns as shown in the below example.

SQL> CREATE TABLE v_emp
  2  (name  VARCHAR2(20),
  3   hr_rate NUMBER(7,2),
  4  income   NUMBER(7,2) generated always as (hr_rate*2080)
  5  virtual
  6  );

Table created.

Virtual columns have the following limitations.

• They cannot be created on user defined types, LOB or RAW.

• Delete or insert operations cannot be performed on virtual columns. If you attempt to do so, Oracle will generate an ORA-54013 error.

• They cannot be created on index-organized tables (IOT), external table, temporary tables, objects, or clusters.

• All of the virtual columns used in expressions must belong to the same table.

• They cannot be updated by the SET clause in an UPDATE statement.

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
  2  (
  3  emp_id     number  not null,
  4  job_no     number,
  5  fname      varchar2(20),
  6  mname      varchar2(20),
  7  lname      varchar2(20),
  8  salary     number,
  9  bonus      number,
 10  position   varchar2(50),
 11  tcomp      as (salary+bonus) virtual
 12  )
 13  partition by range(tcomp)
 14  (partition p_10g values less than (20000),
 15  partition p_20g values less than (30000),
 16  partition p_50g values less than (60000),
 17  partition p_100g values less than (200000),
 18  partition p_500g values less than (600000),
 19  partition p_more values less than (maxvalue));

Table created.

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
  2  (emp_id,job_no,fname,mname,lname,salary,bonus,position)
  3  values
  4  (1,1,'Scott','Smith','Henry',30000,1200,'DBA');

1 row created.

SQL> insert into workers
  2  (emp_id,job_no,fname,mname,lname,salary,bonus,position)
  3  values
  4  (2,2,'Howard','M','Ostro',250000,25000,'Director');

1 row created.

SQL> commit;

Commit complete.

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
  2  from workers partition (p_50g);

FNAME                LNAME                    SALARY      BONUS      TCOMP
-------------------- -------------------- ---------- ---------- ----------
Scott                Henry                     30000       1200      31200

SQL> select fname, lname, salary, bonus, tcomp
  2  from workers partition (p_500g);

FNAME                LNAME                    SALARY      BONUS      TCOMP
-------------------- -------------------- ---------- ---------- ----------
Howard               Ostro                    250000      25000     275000

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
  2  select fname, lname, salary, bonus, tcomp
  3  from workers partition (p_500g);

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------Plan hash
value: 475732617

--------------------------------------------------------------------------------------------
-----

| Id  | Operation              | Name    | Rows  | Bytes | Cost (%CPU)| Time
 | Pstart| Pstop |

--------------------------------------------------------------------------------------------
-----

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------|   0 |
SELECT STATEMENT       |         |     1 |    63 |     3   (0)| 00:00:01 |       |       |

|   1 |  PARTITION RANGE SINGLE|         |     1 |    63 |     3   (0)| 00:00:01 |     5 |     5 |

|   2 |   TABLE ACCESS FULL    | WORKERS |     1 |    63 |     3   (0)| 00:00:01 |     5
|     5 |

--------------------------------------------------------------------------------------------
-----

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

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 -  2011 by Burleson Enterprises. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.