CODEWITHSUNDEEP
databaseoracle-databasepartitioningdatabase-partitioning
literg
literg

Reputation: 512

Why Oracle database requires to provide at least single partition when creating partition by interval

I wonder why Oracle Databases require that at least single partition is defined when creating PARTITION BY RANGE INTERVAL

This is correct:

CREATE TABLE FOO ( bar VARCHAR2(10), creation_date timestamp(6) not null ) PARTITION BY RANGE (creation_date) INTERVAL (NUMTODSINTERVAL(1,'DAY')) ( PARTITION part_01 values LESS THAN (TO_DATE('01-03-2018','DD-MM-YYYY')) )

This however not:

CREATE TABLE FOO ( bar VARCHAR2(10), creation_date timestamp(6) not null ) PARTITION BY RANGE (creation_date) INTERVAL (NUMTODSINTERVAL(1,'DAY'))

I would expect that the first partition would be required in some migration case but not when creating a new table.

Oracle documentation about that:

The INTERVAL clause of the CREATE TABLE statement establishes interval partitioning for the table. You must specify at least one range partition using the PARTITION clause. https://docs.oracle.com/cd/E11882_01/server.112/e25523/part_admin001.htm#BAJHFFBE

Upvotes: 2

Views: 346

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59436

Without default interval Oracle does not know where to start the interval. For daily partition it is not so obvious but imagine you have one partition per week, i.e. 7 days.

Shall it be Monday-Monday or Sunday-Sunday or something else?

What does an interval of "1 DAY" mean? From 00:00:00 - 23:59:59 (as implicitly given in your example) or something else, for example 12:00:00 - 11:59:59 (which would be PARTITION part_01 values LESS THAN (TO_DATE('01-03-2018 12:00','DD-MM-YYYY HH24:MI')))

Upvotes: 7

Related Questions