How do I create a 5 partition system in Oracle DB that alternates by year?

Question

I want to create a table with a year column that partitions by the year value. Rather than set a specific range however, I want each consecutive year to go to another partition on a 5 year cycle for 5 partitions. For example, rows with the year 2020 will be in partition1, 2021 = partition 2, 2022 = partition 3, 2023 = partition 4, 2024 = partition 5 and 2025 will restart the process for partition1. I am pretty confident that the MOD function will be needed but all of the intuitive ways I have tried, such as doing the following, have not worked.

   PARTITION BY RANGE (MOD(year, 5))
(
   PARTITION year_remainder_0 VALUES LESS THAN (1),
   PARTITION year_remainder_1 VALUES LESS THAN (2),
   PARTITION year_remainder_2 VALUES LESS THAN (3),
   PARTITION year_remainder_3 VALUES LESS THAN (4),
   PARTITION year_remainder_other VALUES LESS THAN (5)
)

Any insights would be appreciated.

The error I get when I run the above is a missing right parenthesis btw, not really sure why but I have confirmed that all parenthesis are have a matching sibling.

Answer 1

Add a virtual column to your table defined as MOD(year, 5) and then list or range partition by that column:

CREATE TABLE mytable
 ([othercols],
  modyear integer AS (MOD(year,5)) VIRTUAL
)
  PARTITION BY RANGE (modyear)
(
   PARTITION year_remainder_0 VALUES LESS THAN (1),
   PARTITION year_remainder_1 VALUES LESS THAN (2),
   PARTITION year_remainder_2 VALUES LESS THAN (3),
   PARTITION year_remainder_3 VALUES LESS THAN (4),
   PARTITION year_remainder_other VALUES LESS THAN (5)
)

Just be sure to add a scheduled process that truncates the coming year modulus before you start to re-populate it on the next wrap.