Alternative to VALUES LESS THAN in interval partitioing creation query

Question

As we know that Interval partitioning is an extension of Range partitioning and it takes much of its syntax from range partitioning.

From various sources on the net, I assume that interval partitioning creation query do have mandatory following clause: VALUES LESS THAN (XXX)

But when we go for interval partitioning, is there any simpler way where we do not provide any VALUE LESS THAN CLAUSE. I was searching for something similar like EQUAL TO (012019) where 012019 corresponds to the January month Interval of 2019 year.

I have gone through following links for the help/understanding but couldn't find useful for my concern.

http://www.dba-oracle.com/t_interval_partitioning.htm

https://docs.oracle.com/database/121/VLDBG/GUID-C121EA1B-2725-4464-B2C9-EEDE0C3C95AB.htm

The code used by me is like as follows:

create table 
pos_data ( 
   start_date        DATE,
   store_id          NUMBER,
   inventory_id      NUMBER(6),
   qty_sold          NUMBER(3)
) 
PARTITION BY RANGE (start_date) 
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')) 
(  
   PARTITION pos_data_p2 VALUES LESS THAN (TO_DATE('1-7-2007', 'DD-MM-YYYY')),
   PARTITION pos_data_p3 VALUES LESS THAN (TO_DATE('1-8-2007', 'DD-MM-YYYY')) 
);

From my search It looks like there is no other way apart from the one VALUE LESS THAN.

Please share if anyone have some understanding about someother approach for creating interval based partitioning.

Remainder: my concern is in BOLD above

Answer 1

I think what you are looking for is the partition extended name

PARTITION FOR(DATE'2019-01-01')

Actually the LESS THAN definition plays in interval partitioning close to zero role.

You use it only once while creating the table to define some lower bound of the data.

Here is an example to define a table containing data starting from the year 2019

create table pos_data ( 
   start_date        DATE,
   store_id          NUMBER
)
SEGMENT CREATION DEFERRED
PARTITION BY RANGE (start_date) 
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')) 
(  
   PARTITION pos_data_init VALUES LESS THAN (TO_DATE('1-1-2019', 'DD-MM-YYYY')) 
);

Note that the first partition is not an interval partition (INTERVAL = NO) and doesn't physically exists due to SEGMENT CREATION DEFERRED. It will also never contain any data, as you start with 2019 content.

select PARTITION_POSITION,PARTITION_NAME,INTERVAL,MIN_EXTENT, HIGH_VALUE
from user_tab_partitions where table_name = 'POS_DATA'
order by PARTITION_POSITION;

PARTITION_POSITION PARTITION_NAME INTERVAL MIN_EXTENT HIGH_VALUE                     
------------------ -------------- -------- ---------- ------------------------------- 
                 1 POS_DATA_INIT  NO                  TO_DATE(' 2019-01-01 00:00:00',  

New partitions are created on the fly e.g. while inserting new data, you don't need to specify LESS THAN

insert into pos_data(start_date,store_id) values(DATE'2019-01-01',1);

PARTITION_POSITION PARTITION_NAME INTERVAL MIN_EXTENT HIGH_VALUE                    
------------------ -------------- -------- ---------- -------------------------------
                 1 POS_DATA_INIT  NO                  TO_DATE(' 2019-01-01 00:00:00',  
                 2 SYS_P16713     YES               1 TO_DATE(' 2019-02-01 00:00:00', 

While accessing the table you use the partition_extended_name, you may choose any date within the month to reference the partition.

select * from  pos_data
partition for (date'2019-01-15');

Same syntax may be used for partition maintainance

alter table pos_data move partition for (date'2019-01-30') compress;