Oracle Partition - Partitioning key(s)

About

The partitioning key(s) is an ordered list of columns.

Articles Related

Restrictions

The list columns must contain only columns (no expression). If you want to use a function in the partition key, you need to create a virtual_column.

Example

Virtual Column

Oracle Database - Virtual Column

• The partition key chosen is the D_TIME_CODE column with the char datatype holding a calendar prefix with a week code. Example of data FW201602.
• The scheme chosen is a Range Interval Partitioning.
• The data type of the table column must not change

In this configuration, it's not possible to create directly a Range Interval Partitioning because the key column must be a number or a date. Therefore we create a virtual column transforming the character to a number.

create table EXAMPLE (
       D_TIME_CODE        VARCHAR2(10 CHAR),
       D_TIME_CODE_V    NUMBER GENERATED ALWAYS AS (  substr(D_TIME_CODE,3) ) VIRTUAL
   ) 
  PARTITION BY RANGE (D_TIME_CODE_V) 
  INTERVAL(1)
 (
    PARTITION p1 VALUES LESS THAN ( 201014 )
  )