How to create future partitions for already partitioned table

Question

What is the most suitable way to create partitions for the already partitioned table TBLDATA with two indexes (TBLDATA~1 and TBLDATA~2) in the Oracle DB?

My table consists of TBLDATA_MIN_PART, TBLDATA_MAX_PART and all the monthly partitions created since the start of partitioning on this table. (the last partition - tbldata_202312_part for DEC 2023)

Until now we SPLIT the tbldata_max_part into sub-partitions retrospectively:

ALTER TABLE schema.tbldata SPLIT PARTITION tbldata_max_part AT ('20240101') INTO (PARTITION tbldata_202312_part TABLESPACE TBLSPC3 NOCOMPRESS, PARTITION tbldata_max_part);

Afterwards:

ALTER INDEX schema."TBLDATA~1" REBUILD PARTITION tbldata_202312_part TABLESPACE TBLSPC3 NOCOMPRESS;
ALTER INDEX schema."TBLDATA~2" REBUILD PARTITION tbldata_202312_part TABLESPACE TBLSPC3 NOCOMPRESS;

I would like to create now the partitions to the future for the whole year 2024, which would be the most suitable way to do so?

I can to split to January 2024 partition - tbldata_max_part will be empty afterwards, but will the DB automatically assign JAN2024 data to the January partition or again to the maxpart?

Answer 1

You want to get away from splits altogether. They are expensive and invasive to perform. Follow these steps:

1. If there is any data in your MAXVALUE partition, split it out to a new Jan partition so you don't lose that data.
2. Drop your MAXVALUE partition and never create it again.
3. Set an interval on the table:

If your partition key datatype is a date: alter table xyz set interval(numtoyminterval(1,'MONTH'))

If your datatype is a number representing a date: alter table xyz set interval(100) . This works because a new month changes the 3rd least significant digit. (But it will not work if you have any dates super far in the future, like in 9999. The # of months [multiplied by the # of subpartitions, if any] between your oldest and latest date cannot exceed 1,048,575 or it will error due to Oracle's maximum partition limit even if you don't actually have that many partitions. This is one side-effect of setting an interval on numbers that represent dates. But with real dates this isn't an issue.)

If your datatype is a varchar2 string representing a date, however, or if that column ever uses a dummy value in the range 12/01/9999-12/31/9999 (which will cause an internal datatype overflow and error), you won't be able to use interval partitioning. In that case, simply pre-create the partitions manually (1 years' worth). You could take that further and create a script that does it automatically on a schedule. Another option is to create a virtual, invisible column that turns your string into a real date, substitutes 12/01/9999-12/31/9999 with something less, and then repartition the table on this virtual column. Then you can use an interval.

Once interval partitioning is set, the moment an insert statement adds a new value that doesn't fall into one of your existing partitions, it will automatically create the new partition it needs on the fly.