Range Interval Partition On Number date Column (Doc ID 2860059.1)

Last updated on JULY 20, 2024

Applies to:

Goal

We are attempting to partition one of our Oracle Business Intelligence Application (OBIA) tables. The OBIA set of tables stores a lot dates as NUMBER datatype in the following format, 20220321

For example, for the table t1 (d1 number , ch1 varchar2), we would like to RANGE INTERVAL partition on the column d1 by YEAR MONTH. This is a NUMBER datatype.

We were trying to use MOS document Doc ID 1514047.1 as a starting point. The example in the document is using a NUMBER field with just YEAR and MONTH. Ours has YEAR, MONTH, and DAY. When I performed a test, I found that it's auto creating new partition for each day instead of by year month.






SQL> insert into t1 values (20220602, 'Invoice15');
insert into xx_interval_part_test values (20220603, 'Invoice16');
insert into xx_interval_part_test values (20220604, 'Invoice17');
insert into xx_interval_part_test values (20220605, 'Invoice18');
insert into xx_interval_part_test values (20220606, 'Invoice19');
insert into xx_interval_part_test values (20220607, 'Invoice19');
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>

1 row created.

SQL>
SQL> commit;

Commit complete.
 

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.