Partition table in Oracle 11g using virtual columns

From 11g, Oracle has added few more partitioning methods to the list of partitioning methods. One of the method is partition using virtual column. A virtual column is a column which does not store any data but is calculated on the fly.

Actions that can be performed on virtual columns:

1. A virtual column can be used in WHERE clause of a SELECT, UPDATE or DELETE statement.
2. Statistics can be collected on them.
3. They can be used as a partition key to partition a table.
4. Indexes can be created on these columns.
5. Constraints can be created on them.

Actions that cannot be performed on virtual columns:

1. Virtual columns cannot be updated.
2. A virtual column cannot be of user defined datatype.
3. No DML's are allowed on virtual columns.
4. Virtual columns can only be created on normal tables and not on index organized or temporary tables.

Virtual column based partitioning:

SYNTAX:

CREATE TABLE <>
(
COL1 datatype1,
COL2 datatype2,
COL3 [datatype] GENERATED ALWAYS AS <expression>
)[TABLESPACE tablespace_name]
PARTITION BY <partition_type>(COL3)
(
PARTITION <partition_name> VALUES LESS THAN (value1),
PARTITION <partition_name> VALUES LESS THAN (value2),
PARTITION <partition_name> VALUES LESS THAN (value3),
PARTITION <partition_name> VALUES LESS THAN (MAXVALUE)
);

Here we have used range partitioning method to partition a table.