Partitioning -- 10 : Virtual Column Based Partitioning

Oracle 11g supports specifying a Virtual Column as the Partition Key.

A Virtual Column is a column where the value is derived on the basis of an expression on other columns or sql/plsql functions.  The actual value is not stored in the block holding the row but is computed when the row is retrieved.

For example :

create table my_sales_table
(invoice_id  number primary key,
 invoice_date date,
 sale_year number(4) generated always as ( to_number(to_char(invoice_date,'YYYY')) ) virtual,
 customer_id number,
 sale_value number
 )
/

insert into my_sales_table
 (invoice_id, invoice_date, customer_id, sale_value)
 values
 (1,sysdate,100,10200)
/

select invoice_id, invoice_date, sale_year
 from my_sales_table
/

INVOICE_ID  INVOICE_DATE  SALE_YEAR
         1  28-NOV-18          2018

The MY_SALES_TABLE can be List, Range or Hash Partitioned on SALE_YEAR.

drop table my_sales_table;

create table my_sales_table
(invoice_id  number primary key,
 invoice_date date,
 sale_year number(4) generated always as ( to_number(to_char(invoice_date,'YYYY')) ) virtual,
 customer_id number,
 sale_value number
 )
 partition by list(sale_year)
 (partition p_2018 values (2018),
  partition p_2019 values (2019),
  partition p_2020 values (2020)
  )
/

insert into my_sales_table
 (invoice_id, invoice_date, customer_id, sale_value)
 values
 (1,sysdate,100,10200)
/

select invoice_date, sale_year from my_sales_table partition (p_2018)
/

INVOICE_DATE  SALE_YEAR
28-NOV-18          2018

Thus, the SALE_YEAR value is not actually stored on disk, yet each partition has rows based on the SALE_YEAR (derived) value.