CODEWITHSUNDEEP
oracle-databasepartitioningvarchar
user2671057
user2671057

Reputation: 1533

Interval partitioning on varchar2 column

I have a huge table that holds a lot of data - history, and current.

I Have an automatic job that move old data from some tables to historic tables

(and then delete from the source).

So I want to build an

interval-partition

table which get the old data from that table.

The problem is that the base-date-column in the source is varchar2 in this pattern:

2017/07

And the new table columns datatypes need to be identical to the source.

So when I'm trying to create the interval-partitioning table, I can't do a range on it.

You get an error if you try to use

to_date

.

How can I do that???

This is the source table:

    CREATE TABLE 
DATA_01( 
APPLICATION   VARCHAR2(10 BYTE),
PROCESS       VARCHAR2(100 BYTE),
SNAPSHOT_MONTH VARCHAR2(7 BYTE)
);

That what I wanted to do but getting error:

CREATE TABLE 
HISTORY_01 ( 
    APPLICATION   VARCHAR2(10 BYTE) NOT NULL ENABLE,
    PROCESS       VARCHAR2(100 BYTE),
    SNAPSHOT_MONTH VARCHAR2(7 BYTE)
) 
PARTITION BY RANGE (to_date(snapshot_month, 'yyyy/mm')) 
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')) 
(  
   PARTITION p_until_01_1900 VALUES LESS THAN (TO_DATE('01-01-1900', 'DD-MM-YYYY'))
);

Thanks.

Upvotes: 1

Views: 2949

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59436

Define a virtual column and use this for partitioning:

CREATE TABLE 
HISTORY_01 ( 
    APPLICATION   VARCHAR2(10 BYTE) NOT NULL ENABLE,
    PROCESS       VARCHAR2(100 BYTE),
    SNAPSHOT_MONTH VARCHAR2(7 BYTE),
    PARTITION_KEY TIMESTAMP GENERATED ALWAYS AS (TO_TIMESTAMP(snapshot_month, 'yyyy/mm')) VIRTUAL
)
PARTITION BY RANGE (PARTITION_KEY) 
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')) 
(  
   PARTITION p_until_01_1900 VALUES LESS THAN (TIMESTAMP '1900-01-01 00:00:00')
);

Upvotes: 1

Related Questions