Partitioning -- 2 : Simple Range Partitioning -- by DATE

Range Partitioning allows you to separate a logical table into a number of distinct physical segments, each segment holding data that maps to a range of values.
(I encourage you to read the Introduction in the first post in this series)

The simplest and most common implementation is Range Partitioning by a DATE column.

SQL> l
  1  create table sales_data
  2  (sale_id number primary key,
  3   sale_date date,
  4   invoice_number varchar2(21),
  5   customer_id number,
  6   product_id number,
  7   sale_value number)
  8  partition by range (sale_date)
  9  (partition P_2015 values less than (to_date('01-JAN-2016','DD-MON-YYYY'))
 10   tablespace TBS_YEAR_2015,
 11  partition P_2016 values less than (to_date('01-JAN-2017','DD-MON-YYYY'))
 12   tablespace TBS_YEAR_2016,
 13  partition P_2017 values less than (to_date('01-JAN-2018','DD-MON-YYYY'))
 14   tablespace TBS_YEAR_2017,
 15  partition P_2018 values less than (to_date('01-JAN-2019','DD-MON-YYYY'))
 16   tablespace TBS_YEAR_2018,
 17  partition P_2019 values less than (to_date('01-JAN-2020','DD-MON-YYYY'))
 18   tablespace TBS_YEAR_2019,
 19  partition P_MAXVALUE values less than (MAXVALUE)
 20   tablespace USERS
 21* )
SQL> /

Table created.

SQL> 

Here, I have created each Partition in a separate tablespace.  Note that the Partition Key (SALE_DATE) does not have to be the same as the Primary Key (SALE_ID)

I have also created a MAXVALUE Partition  (Some DBAs/Developers may mistakenly assume this to be a *default* partition.  Range Partitioning, unlike List Partitioning, does not have the concept of a "default" partition.  This simply is the Partition for incoming rows that have Partition Key value that is higher than the last (highest) defined Partition Key Upper Bound (31-Dec-2019 23:59:59 in this case)).

I can look up the data dictionary for these partitions in this manner :

SQL> select partition_name, tablespace_name   
  2  from user_tab_partitions
  3  where table_name = 'SALES_DATA'
  4  order by partition_position
  5  /

PARTITION_NAME         TABLESPACE_NAME
------------------------------ ------------------------------
P_2015          TBS_YEAR_2015
P_2016          TBS_YEAR_2016
P_2017          TBS_YEAR_2017
P_2018          TBS_YEAR_2018
P_2019          TBS_YEAR_2019
P_MAXVALUE         USERS

6 rows selected.

SQL> 

Partitions are ordered by Partition *Position*  not Name.

How do I add a new partition for data for the year 2020 ?  By "splitting" the MAXVALUE partition.

SQL> alter table sales_data                 
  2  split partition P_MAXVALUE
  3  at (to_date('01-JAN-2021','DD-MON-YYYY'))
  4  into
  5  (partition P_2020 tablespace TBS_YEAR_2020, partition P_MAXVALUE)
  6  /

Table altered.

SQL> 
SQL> select partition_name, high_value
  2  from user_tab_partitions
  3  where table_name = 'SALES_DATA'
  4  order by partition_position
  5  /

PARTITION_NAME         HIGH_VALUE
------------------------------ ---------------------------------------------
P_2015          TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD
P_2016          TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD
P_2017          TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD
P_2018          TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD
P_2019          TO_DATE(' 2020-01-01 00:00:00', 'SYYYY-MM-DD
P_2020          TO_DATE(' 2021-01-01 00:00:00', 'SYYYY-MM-DD
P_MAXVALUE         MAXVALUE

7 rows selected.

SQL> 
SQL> l
  1  select partition_name, tablespace_name
  2  from user_tab_partitions
  3  where table_name = 'SALES_DATA'
  4* order by partition_position
SQL> /

PARTITION_NAME         TABLESPACE_NAME
------------------------------ ------------------------------
P_2015          TBS_YEAR_2015
P_2016          TBS_YEAR_2016
P_2017          TBS_YEAR_2017
P_2018          TBS_YEAR_2018
P_2019          TBS_YEAR_2019
P_2020          TBS_YEAR_2020
P_MAXVALUE         USERS

7 rows selected.

SQL> 

Note that, irrespective of the data format I specify in the CREATE or SPLIT commands, Oracle presents the Upper Bound Date (HIGH_VALUE) in it's own format, using a Gregorian Calendar.

How do I remove an older partition ?

SQL> alter table sales_data
  2  drop partition P_2015
  3  /

Table altered.

SQL> 

A DROP command is very simple.

In my next post, I will add Indexes to this table.