Interval partitioning on varchar2 column

Question

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.

try that: `PARTITION BY RANGE (to_date(snapshot_month || '/01', 'yyyy/mm/dd')) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')) ( PARTITION p_until_01_1900 VALUES LESS THAN (TO_DATE('1900/01/01', 'yyyy/mm/dd')) ); ` — g00dy, Jul 12 '17 at 11:13
You can't insert to_date to the range column.. that's raise an error. — user2671057, Jul 12 '17 at 11:23
Check this link http://www.dba-oracle.com/t_partitioning_tables.htm -< Ctrl + F (paste "Here is an example"). — g00dy, Jul 12 '17 at 11:32
I don't think there would be a problem, if you use a `to_date()` function to provide the `partition by` with "date", as per the example. — g00dy, Jul 12 '17 at 12:13

score 1 · Answer 1 · answered Jul 12 '17 at 11:17

1

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')
);

answered Jul 12 '17 at 11:17

Wernfried Domscheit

54,457
9
76
110

I can do that but the script that insert the data is working dynamiclly, so it perform execute immediate 'insert into history_01 select * from data_01'.. that's why I want the structure to be the same. I can change the script to insert the execute immediate only the columns which are not virtual... but I want something else if exists.. – user2671057 Jul 12 '17 at 11:27
Create a view for it: `CREATE TABLE T_HISTORY_01 (...; CREATE VIEW HISTORY_01 AS SELECT APPLICATION, PROCESS, SNAPSHOT_MONTH FROM T_HISTORY_01;` – Wernfried Domscheit Jul 12 '17 at 11:52

Interval partitioning on varchar2 column

1 Answers1