Oracle Partitioning Types - VII (Virtual Column Based Partitioning)

Virtual Column Based Partitioning
As the name suggest oracle allows us to define partitions based on a virtual column of a table. All types of partitioning methods are allowed for virtual columns. The only thing that is not allowed is if the virtual column uses a PL\SQL function in its definition.

SQL> drop table DATA_LOAD;
Table dropped.

SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
target_date DATE,
data_size NUMBER,
state VARCHAR2(20),
diff_date NUMBER GENERATED ALWAYS AS (target_date-load_date) VIRTUAL
)
PARTITION BY RANGE (load_date)
SUBPARTITION BY RANGE (diff_date)
( PARTITION data_01_2009 VALUES LESS THAN (TO_DATE('01-FEB-2009','DD-MON-YYYY')) TABLESPACE DATA_01
(SUBPARTITION data_01_2009_1 VALUES LESS THAN (10),
SUBPARTITION data_01_2009_2 VALUES LESS THAN (20),
SUBPARTITION data_01_2009_3 VALUES LESS THAN (MAXVALUE)
) ,
PARTITION data_02_2009 VALUES LESS THAN (TO_DATE('01-MAR-2009','DD-MON-YYYY')) TABLESPACE DATA_02
(SUBPARTITION data_02_2009_1 VALUES LESS THAN (10),
SUBPARTITION data_02_2009_2 VALUES LESS THAN (20),
SUBPARTITION data_02_2009_3 VALUES LESS THAN (MAXVALUE)
) ,
PARTITION data_03_2009 VALUES LESS THAN (TO_DATE('01-APR-2009','DD-MON-YYYY')) TABLESPACE DATA_03
(SUBPARTITION data_03_2009_1 VALUES LESS THAN (10),
SUBPARTITION data_03_2009_2 VALUES LESS THAN (20),
SUBPARTITION data_03_2009_3 VALUES LESS THAN (MAXVALUE)
) ,
PARTITION data_04_2009 VALUES LESS THAN (TO_DATE('01-MAY-2009','DD-MON-YYYY')) TABLESPACE DATA_04
(SUBPARTITION data_04_2009_1 VALUES LESS THAN (10),
SUBPARTITION data_04_2009_2 VALUES LESS THAN (20),
SUBPARTITION data_04_2009_3 VALUES LESS THAN (MAXVALUE)
)
);
Table created.

SQL> insert into DATA_LOAD values (1,2,sysdate-60,sysdate-50,1,'DELHI',DEFAULT);
1 row created.

SQL> insert into DATA_LOAD values (2,3,sysdate-50,sysdate-35,2,'RAJASTHAN',DEFAULT);
1 row created.

SQL> select * from DATA_LOAD;
DATA_ID SOURCE_ID LOAD_DATE TARGET_DA DATA_SIZE STATE DIFF_DATE
---------- ---------- --------- --------- ---------- -------------------- ----------
1 2 09-MAR-09 19-MAR-09 1 DELHI 10
2 3 19-MAR-09 03-APR-09 2 RAJASTHAN 15
3 4 08-APR-09 13-APR-09 3 RAJASTHAN 5
SQL> select TABLE_NAME,COMPOSITE,PARTITION_NAME,NUM_ROWS,subpartition_count from user_tab_partitions where TABLE_NAME='DATA_LOAD';

TABLE_NAME COM PARTITION_NAME NUM_ROWS SUBPARTITION_COUNT
--------------- --- --------------- ---------- ------------------
DATA_LOAD YES DATA_01_2009 0 3
DATA_LOAD YES DATA_02_2009 0 3
DATA_LOAD YES DATA_03_2009 2 3
DATA_LOAD YES DATA_04_2009 1 3

SQL> select TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,NUM_ROWS from USER_TAB_SUBPARTITIONS where TABLE_NAME='DATA_LOAD';
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME NUM_ROWS
--------------- --------------- ------------------------------ ----------
DATA_LOAD DATA_01_2009 DATA_01_2009_3 0
DATA_LOAD DATA_01_2009 DATA_01_2009_2 0
DATA_LOAD DATA_01_2009 DATA_01_2009_1 0
DATA_LOAD DATA_02_2009 DATA_02_2009_3 0
DATA_LOAD DATA_02_2009 DATA_02_2009_2 0
DATA_LOAD DATA_02_2009 DATA_02_2009_1 0
DATA_LOAD DATA_03_2009 DATA_03_2009_3 0
DATA_LOAD DATA_03_2009 DATA_03_2009_2 2
DATA_LOAD DATA_03_2009 DATA_03_2009_1 0
DATA_LOAD DATA_04_2009 DATA_04_2009_3 0
DATA_LOAD DATA_04_2009 DATA_04_2009_2 0
DATA_LOAD DATA_04_2009 DATA_04_2009_1 1

So we can see that the data has been sub partitioned based on virtual column.

Another example of normal range partition using the virtual column:
SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
target_date DATE,
data_size NUMBER,
state VARCHAR2(20),
diff_date NUMBER GENERATED ALWAYS AS (target_date-load_date) VIRTUAL)
PARTITION BY RANGE (diff_date)
( PARTITION data_01_2009 VALUES LESS THAN (10) TABLESPACE DATA_01,
PARTITION data_02_2009 VALUES LESS THAN (20) TABLESPACE DATA_02,
PARTITION data_03_2009 VALUES LESS THAN (30) TABLESPACE DATA_03,
PARTITION data_04_2009 VALUES LESS THAN (MAXVALUE) TABLESPACE DATA_04
);
Table created.

To see what error we get while using PL\SQL function while creating virtual column and using it as paritioning key:

The Funtion:
SQL> create or replace function add_test (a in number,b in number)
return number
is
c number;
begin
c:=a+b;
RETURN c;
end;
/
Function created.

SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
target_date DATE,
data_size NUMBER,
state VARCHAR2(20),
sum_up NUMBER GENERATED ALWAYS AS (add_test(source_id,data_size)) VIRTUAL)
PARTITION BY RANGE (sum_up)
( PARTITION data_01_2009 VALUES LESS THAN (10) TABLESPACE DATA_01,
PARTITION data_02_2009 VALUES LESS THAN (20) TABLESPACE DATA_02,
PARTITION data_03_2009 VALUES LESS THAN (30) TABLESPACE DATA_03,
PARTITION data_04_2009 VALUES LESS THAN (MAXVALUE) TABLESPACE DATA_04
);
sum_up NUMBER GENERATED ALWAYS AS (add_test(source_id,data_size)) VIRTUAL)
*
ERROR at line 8:
ORA-54021: Cannot use PL/SQL expressions in partitioning or subpartitioning columns

SQL> !oerr ora 54021
54021, 0000, "Cannot use PL/SQL expressions in partitioning or subpartitioning columns"
// *Cause: Attempted to partition a table on a virtual column that contained
// PL/SQL expressions.
// *Action: This is not supported.

Part I
Part II
Part III
Part IV
Part V
Part VI