CODEWITHSUNDEEP
oracle-databaseoracle12cdatabase-partitioning
Prasanna Narayanan
Prasanna Narayanan

Reputation: 439

Can I create a growing Interval partitioned table with a default/maxvalue partition?

Summary of the question: To Create table with partitions which are range partitioned. However records which do not know the range value should reside in a different (default) partition and be moved to the correct partition when the value is filled. The default partition would never be dropped while the other partitions would be dropped after a defined retention period via an script.

The whole story:

I have a table where the records have to be placed in a partition based on a date field. This is a growing table and after some time the data from these partitions can be purged. I used to create table with something like the snippet below.

This works fine because we knew the value of the date column based on which we partition (RDATE). However in our new project we do not know this when a record is inserted. The value would eventually be filled in during the course of the application processing.

My initial thought was to create MAXPARTITION (MAXVALUE) which would be a catch-all partition for records which do not have the date filled and enable ROW MOVEMENTS so that when the date is filled it moves into an appropriate partition. However I think it is not possible to have both MAXVALUE partition and interval partitioning together. Is that right?

Also Is there a better way to do this?

PARTITION BY RANGE ("RDATE") INTERVAL (NUMTODSINTERVAL (1,'DAY')) 
SUBPARTITION BY HASH ("RKEY") 
SUBPARTITION TEMPLATE ( 
   SUBPARTITION "SP01", 
   SUBPARTITION "SP02", 
   SUBPARTITION "SP03", 
   SUBPARTITION "SP04", 
   SUBPARTITION "SP05", 
   SUBPARTITION "SP06", 
   SUBPARTITION "SP07", 
   SUBPARTITION "SP08", 
   SUBPARTITION "SP09", 
   SUBPARTITION "SP10", 
   SUBPARTITION "SP11", 
   SUBPARTITION "SP12", 
   SUBPARTITION "SP13", 
   SUBPARTITION "SP14", 
   SUBPARTITION "SP15", 
   SUBPARTITION "SP16" ) 
(PARTITION "INITIALPARTITION"  VALUES LESS THAN (TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

I expect a table with default and range partitions and records to move to the range partitions from the default when a column is filled.

Upvotes: 0

Views: 1262

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59436

The column you use as partition key cannot be NULL but you can use a workaround like this:

CREATE TABLE ... (
    ...
    RDATE DATE,
    PARTITION_KEY DATE GENERATED ALWAYS AS (COALESCE(RDATE, DATE '1969-12-31')) 
)
PARTITION BY RANGE (PARTITION_KEY) INTERVAL (NUMTODSINTERVAL (1,'DAY'))
...
(PARTITION INITIAL_PARTITION VALUES LESS THAN (DATE '1970-01-01'))
ENABLE ROW MOVEMENT;

If you insert a record with RDATE = NULL then it will be inserted into partition INITIAL_PARTITION. For the initial data (e.g. 1970-01-01) you must select a values whicc will never fall into the "real" date values. You could also use a date in far future, e.g. 

CREATE TABLE ... (
    ...
    RDATE DATE,
    PARTITION_KEY DATE GENERATED ALWAYS AS (COALESCE(RDATE, DATE '2999-12-31')) 
)
PARTITION BY RANGE (PARTITION_KEY) INTERVAL (NUMTODSINTERVAL (1,'DAY'))
...
(PARTITION INITIAL_PARTITION VALUES LESS THAN (DATE '2019-04-01'))
ENABLE ROW MOVEMENT;

-- Create DEFAULT_PARTITION
INSERT INTO ... (RDATE) VALUES (NULL);
ROLLBACK;
ALTER TABLE ... RENAME PARTITION FOR (TIMESTAMP '2999-12-31 00:00:00') TO DEFAULT_PARTITION;

Upvotes: 1

Related Questions