Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

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

Debugging Oracle 11g Applications
by Ben
Prusinski

 

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

 

Figure 1.9: Adding virtual column to existing table for Oracle 11g

 

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.
 

Figure 1.10: Creating table with virtual column in Oracle 11g


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.


Figure 1.11: Using the virtual keyword to create virtual columns Oracle 11g

 

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.

 

 Figure 1.12: Creating virtual column based partitions with Oracle 11g

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.

 

Figure 1.13: Inserting data into virtual column partitioned table

 

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

 

Figure 1.14: Query virtual column based partition results for Oracle 11g

 

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.


Figure 1.15: Explain plan for Virtual Column Based Partition Oracle 11g

 

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.

 

 

Oracle Consulting

††
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 
   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracleģ is the registered trademark of Oracle Corporation.