Oracle Partition - Partitioning key(s)

1 - About

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

3 - 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.

4 - Example

4.1 - 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 )
  ) 

Data Science
Data Analysis
Statistics
Data Science
Linear Algebra Mathematics
Trigonometry

Powered by ComboStrap