I have a table which has a DATE_ID column (Integer data type). This column basically stores dates in ID format. Input data:

I need to partition this table (YEARly) based on date_id column. Please note that this is an existing process and we are migrating our tables from database to another. These column datatypes cannot be changed as they will be referred by downstream process in that fashion only.

I tried below interval partitioning but somehow didn't work. Can someone pls help?

CREATE TABLE test (date_id INT NOT NULL, text VARCHAR2(500))
PARTITION BY RANGE (DATE_ID) INTERVAL (365) 
 (
  PARTITION P0  VALUES LESS THAN (19961231), 
  PARTITION P1  VALUES LESS THAN (19991231),
  PARTITION P2  VALUES LESS THAN (20091231)
 );

CodePudding user response：

I got the solution:

CREATE TABLE test (
    DATE_ID INTEGER
)
partition BY RANGE (DATE_ID )
interval(10000)
(partition p0 values less than(19961231),
 partition p1 values less than(19991231),
 partition p1 values less than(20091231)
);

CodePudding user response：

You can add a virtual column in order to add an interval partition along with a decent date type column, and able to cross-check the eligibility of the existing integer values as been considered as date values such as

CREATE TABLE test( 
                   date_id INT NOT NULL, 
                   text    VARCHAR2(500)
                  );

ALTER TABLE test ADD (dt AS ( TO_DATE(date_id,'yyyymmdd') ));

ALTER TABLE test MODIFY
PARTITION BY RANGE(dt) INTERVAL (INTERVAL '1' YEAR) 
(
  PARTITION P1996  VALUES LESS THAN (date'1997-01-01'), 
  PARTITION P1999  VALUES LESS THAN (date'2000-01-01'),
  PARTITION P2009  VALUES LESS THAN (date'2010-01-01')
 );

if your aim is to add yearly partition as the title implies for the year range from 1996 to 2006, then rather you can prefer using a dynamic method in order to generate the desired code block such as

DECLARE
  v_ddl  CLOB;
BEGIN 
  FOR year in 1996..2006
   LOOP  
    v_ddl := v_ddl||' PARTITION p'||year||' VALUES LESS THAN (date'''||TO_CHAR(year 1)||'-01-01''),'||CHR(13);
   END LOOP;
    v_ddl := 'ALTER TABLE test MODIFY PARTITION BY RANGE(dt) INTERVAL (INTERVAL ''1'' YEAR)'||CHR(13)||'('||CHR(13)||RTRIM(v_ddl,','||CHR(13));
    v_ddl := v_ddl||CHR(13)||')';
    DBMS_OUTPUT.PUT_LINE(v_ddl);
    EXECUTE IMMEDIATE v_ddl;
END;
/