26 | 06 | 2017
Latest Articles
Popular Articles

Database Design

Virtual Column Partitioning

User Rating:  / 0
PoorBest 

Virtual Column Partitioning

 

See also Partitioned Tables Overview

New partition possibility from 11G R1 onwards  : Virtual Column based partition. 

 

The partition key is is a virtual column and is not physically stored in the table in stead the partition key is function of some other column(s) of the table row.

Partition pruning and partition wise joins can take place.

 

Here is an example

 

SQL> create table TABLE_TV1 (
          TV1_ID NUMBER(18) not null,
          TV1_name  varchar2(100) not null,
          TV1_FIXED_SALARY number(10,2) not null,
          TV1_VARIABLE_SALARY number(10,2) not null,
          TV1_TOTAL_SALARY number(10,2) GENERATED ALWAYS AS
         ( TV1_FIXED_SALARY + TV1_VARIABLE_SALARY ) VIRTUAL
     )
     PARTITION BY RANGE (TV1_TOTAL_SALARY)
       ( 
         PARTITION TV1_p1 VALUES LESS THAN (50000),
         PARTITION TV1_p2 VALUES LESS THAN (100000),
         PARTITION TV1_p3 VALUES LESS THAN (150000),
         PARTITION TV1_p4 VALUES LESS THAN (MAXVALUE)
       );
 

SQL> alter table TABLE_TV1 add constraint PK_TV1 primary key (TV1_ID);

SQL> create sequence SEQ_TABLE_TV1 cache 1000;

 

Virtual columns can be indexed

 

SQL> create index TV1_IDX1 on TABLE_TV1 (TV1_FIXED_SALARY + TV1_VARIABLE_SALARY) ;

 

When we populate the table we notice the virtual column is effectively the partitition key.

 

SQL> insert into table_tv1 (tv1_id,tv1_name,tv1_fixed_salary,tv1_variable_salary) values (seq_table_tv1.nextval, 'Coconut Brasil',40000,15000);

1 row created.

SQL> insert into table_tv1 (tv1_id,tv1_name,tv1_fixed_salary,tv1_variable_salary) values (seq_table_tv1.nextval, 'Kid Creol',60000,25000);

1 row created.

SQL> insert into table_tv1 (tv1_id,tv1_name,tv1_fixed_salary,tv1_variable_salary) values (seq_table_tv1.nextval, 'Harry Belafonte',100000,250000);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from table_tv1 partition (tv1_p1);

SQL> select * from table_tv1 partition (tv1_p2);

SQL> select * from table_tv1 partition (tv1_p3);

SQL> select * from table_tv1 partition (tv1_p4);