RE: Using partition with interval and insert NULL

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Tue, 19 May 2015 08:26:53 -0400
Message-ID: <00c101d0922f$17c44000$474cc000$_at_rsiz.com>



It appears that your plan is to have every new insertion in one partition (forever) and then to move each row to a year-month partition when the row gets an actual "delete_date."  

Is that correct?  

(Whether or not this is a useful strategy compared to things like occasional copy-keep and partition exchange, I want to be sure I understand your plan before I offer further advice.)  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Eriovaldo Andrietta
Sent: Tuesday, May 19, 2015 7:18 AM
To: Cherif Ben Henda
Cc: pavan_843_at_yahoo.co.in; ORACLE-L
Subject: Re: Using partition with interval and insert NULL  

Thanks Cherif and Pavan for ansewring.  

Now , I have another doubt.  

I would like to create a partition by year and and for each year by month.

In the last example I used INTERVAL.

I have the code below.

Is there a way to create the partition structure without hard code the year, as :  

 PARTITION before_2007 VALUES LESS THAN (2007) TABLESPACE tbs    

drop table DATA_LOAD;  

CREATE TABLE DATA_LOAD ( data_id NUMBER(6),

  source_id NUMBER,

  insert_date DATE,

  delete_date date,

  data_size NUMBER,

  state VARCHAR2(20),

  sys_current_date DATE DEFAULT SYSDATE,

  year_delete_date NUMBER GENERATED ALWAYS AS (TO_NUMBER(TO_CHAR(delete_date,'YYYY'))) VIRTUAL,

  mm_delete_date NUMBER GENERATED ALWAYS AS (TO_NUMBER(TO_CHAR(delete_date,'MM'))) VIRTUAL)

PARTITION BY RANGE (year_delete_date)

SUBPARTITION BY RANGE (mm_delete_date)

 ( PARTITION before_2007 VALUES LESS THAN (2007) TABLESPACE tbs

(SUBPARTITION before_2007_01 VALUES LESS THAN (2),

    SUBPARTITION before_2007_02 VALUES LESS THAN (3),

    SUBPARTITION before_2007_03 VALUES LESS THAN (4),

    SUBPARTITION before_2007_04 VALUES LESS THAN (5),

    SUBPARTITION before_2007_05 VALUES LESS THAN (6),

    SUBPARTITION before_2007_06 VALUES LESS THAN (7),

    SUBPARTITION before_2007_07 VALUES LESS THAN (8),

    SUBPARTITION before_2007_08 VALUES LESS THAN (9),

    SUBPARTITION before_2007_09 VALUES LESS THAN (10),

    SUBPARTITION before_2007_10 VALUES LESS THAN (11),

    SUBPARTITION before_2007_11 VALUES LESS THAN (12),

    SUBPARTITION before_2007_12 VALUES LESS THAN (MAXVALUE)),

   PARTITION before_2008 VALUES LESS THAN (2009) TABLESPACE tbs

(SUBPARTITION before_2008_01 VALUES LESS THAN (2),

    SUBPARTITION before_2008_02 VALUES LESS THAN (3),

    SUBPARTITION before_2008_03 VALUES LESS THAN (4),

    SUBPARTITION before_2008_04 VALUES LESS THAN (5),

    SUBPARTITION before_2008_05 VALUES LESS THAN (6),

    SUBPARTITION before_2008_06 VALUES LESS THAN (7),

    SUBPARTITION before_2008_07 VALUES LESS THAN (8),

    SUBPARTITION before_2008_08 VALUES LESS THAN (9),

    SUBPARTITION before_2008_09 VALUES LESS THAN (10),

    SUBPARTITION before_2008_10 VALUES LESS THAN (11),

    SUBPARTITION before_2008_11 VALUES LESS THAN (12),

    SUBPARTITION before_2008_12 VALUES LESS THAN (MAXVALUE)),

   PARTITION before_2009 VALUES LESS THAN (2010) TABLESPACE tbs

(SUBPARTITION before_2009_01 VALUES LESS THAN (2),

    SUBPARTITION before_2009_02 VALUES LESS THAN (3),

    SUBPARTITION before_2009_03 VALUES LESS THAN (4),

    SUBPARTITION before_2009_04 VALUES LESS THAN (5),

    SUBPARTITION before_2009_05 VALUES LESS THAN (6),

    SUBPARTITION before_2009_06 VALUES LESS THAN (7),

    SUBPARTITION before_2009_07 VALUES LESS THAN (8),

    SUBPARTITION before_2009_08 VALUES LESS THAN (9),

    SUBPARTITION before_2009_09 VALUES LESS THAN (10),

    SUBPARTITION before_2009_10 VALUES LESS THAN (11),

    SUBPARTITION before_2009_11 VALUES LESS THAN (12),

    SUBPARTITION before_2009_12 VALUES LESS THAN (MAXVALUE)),

   PARTITION before_2010 VALUES LESS THAN (2011) TABLESPACE tbs

(SUBPARTITION before_2010_01 VALUES LESS THAN (2),

    SUBPARTITION before_2010_02 VALUES LESS THAN (3),

    SUBPARTITION before_2010_03 VALUES LESS THAN (4),

    SUBPARTITION before_2010_04 VALUES LESS THAN (5),

    SUBPARTITION before_2010_05 VALUES LESS THAN (6),

    SUBPARTITION before_2010_06 VALUES LESS THAN (7),

    SUBPARTITION before_2010_07 VALUES LESS THAN (8),

    SUBPARTITION before_2010_08 VALUES LESS THAN (9),

    SUBPARTITION before_2010_09 VALUES LESS THAN (10),

    SUBPARTITION before_2010_10 VALUES LESS THAN (11),

    SUBPARTITION before_2010_11 VALUES LESS THAN (12),

    SUBPARTITION before_2010_12 VALUES LESS THAN (MAXVALUE)),

   PARTITION before_2011 VALUES LESS THAN (2012) TABLESPACE tbs

(SUBPARTITION before_2011_01 VALUES LESS THAN (2),

    SUBPARTITION before_2011_02 VALUES LESS THAN (3),

    SUBPARTITION before_2011_03 VALUES LESS THAN (4),

    SUBPARTITION before_2011_04 VALUES LESS THAN (5),

    SUBPARTITION before_2011_05 VALUES LESS THAN (6),

    SUBPARTITION before_2011_06 VALUES LESS THAN (7),

    SUBPARTITION before_2011_07 VALUES LESS THAN (8),

    SUBPARTITION before_2011_08 VALUES LESS THAN (9),

    SUBPARTITION before_2011_09 VALUES LESS THAN (10),

    SUBPARTITION before_2011_10 VALUES LESS THAN (11),

    SUBPARTITION before_2011_11 VALUES LESS THAN (12),

    SUBPARTITION before_2011_12 VALUES LESS THAN (MAXVALUE)),

   PARTITION before_2012 VALUES LESS THAN (2013) TABLESPACE tbs

(SUBPARTITION before_2012_01 VALUES LESS THAN (2),

    SUBPARTITION before_2012_02 VALUES LESS THAN (3),

    SUBPARTITION before_2012_03 VALUES LESS THAN (4),

    SUBPARTITION before_2012_04 VALUES LESS THAN (5),

    SUBPARTITION before_2012_05 VALUES LESS THAN (6),

    SUBPARTITION before_2012_06 VALUES LESS THAN (7),

    SUBPARTITION before_2012_07 VALUES LESS THAN (8),

    SUBPARTITION before_2012_08 VALUES LESS THAN (9),

    SUBPARTITION before_2012_09 VALUES LESS THAN (10),

    SUBPARTITION before_2012_10 VALUES LESS THAN (11),

    SUBPARTITION before_2012_11 VALUES LESS THAN (12),

    SUBPARTITION before_2012_12 VALUES LESS THAN (MAXVALUE)),

   PARTITION before_2013 VALUES LESS THAN (2014) TABLESPACE tbs

(SUBPARTITION before_2013_01 VALUES LESS THAN (2),

    SUBPARTITION before_2013_02 VALUES LESS THAN (3),

    SUBPARTITION before_2013_03 VALUES LESS THAN (4),

    SUBPARTITION before_2013_04 VALUES LESS THAN (5),

    SUBPARTITION before_2013_05 VALUES LESS THAN (6),

    SUBPARTITION before_2013_06 VALUES LESS THAN (7),

    SUBPARTITION before_2013_07 VALUES LESS THAN (8),

    SUBPARTITION before_2013_08 VALUES LESS THAN (9),

    SUBPARTITION before_2013_09 VALUES LESS THAN (10),

    SUBPARTITION before_2013_10 VALUES LESS THAN (11),

    SUBPARTITION before_2013_11 VALUES LESS THAN (12),

    SUBPARTITION before_2013_12 VALUES LESS THAN (MAXVALUE)),

   PARTITION before_2014 VALUES LESS THAN (2015) TABLESPACE tbs

(SUBPARTITION before_2014_01 VALUES LESS THAN (2),

    SUBPARTITION before_2014_02 VALUES LESS THAN (3),

    SUBPARTITION before_2014_03 VALUES LESS THAN (4),

    SUBPARTITION before_2014_04 VALUES LESS THAN (5),

    SUBPARTITION before_2014_05 VALUES LESS THAN (6),

    SUBPARTITION before_2014_06 VALUES LESS THAN (7),

    SUBPARTITION before_2014_07 VALUES LESS THAN (8),

    SUBPARTITION before_2014_08 VALUES LESS THAN (9),

    SUBPARTITION before_2014_09 VALUES LESS THAN (10),

    SUBPARTITION before_2014_10 VALUES LESS THAN (11),

    SUBPARTITION before_2014_11 VALUES LESS THAN (12),

    SUBPARTITION before_2014_12 VALUES LESS THAN (MAXVALUE)),

   PARTITION before_2999 VALUES LESS THAN (MAXVALUE) TABLESPACE tbs

(SUBPARTITION before_2999_01 VALUES LESS THAN (2),

    SUBPARTITION before_2999_02 VALUES LESS THAN (3),

    SUBPARTITION before_2999_03 VALUES LESS THAN (4),

    SUBPARTITION before_2999_04 VALUES LESS THAN (5),

    SUBPARTITION before_2999_05 VALUES LESS THAN (6),

    SUBPARTITION before_2999_06 VALUES LESS THAN (7),

    SUBPARTITION before_2999_07 VALUES LESS THAN (8),

    SUBPARTITION before_2999_08 VALUES LESS THAN (9),

    SUBPARTITION before_2999_09 VALUES LESS THAN (10),

    SUBPARTITION before_2999_10 VALUES LESS THAN (11),

    SUBPARTITION before_2999_11 VALUES LESS THAN (12),

    SUBPARTITION before_2999_12 VALUES LESS THAN (MAXVALUE))

)

 ENABLE ROW MOVEMENT ;  

  • Insert massivo

declare

seq1 NUMBER;

seq2 NUMBER;

BEGIN DELETE FROM DATA_LOAD; seq1 := 0;

seq2 := 0;

while (seq1 < 11)

  loop

    seq2 := 30;

     while (seq2 < 3001)

      loop

       INSERT INTO DATA_LOAD (DATA_ID, SOURCE_ID, INSERT_DATE, DATA_SIZE, STATE, SYS_CURRENT_DATE)

            VALUES           (       1,        1, SYSDATE-seq2,         1,
'M:'||seq2,          DEFAULT);

       INSERT INTO DATA_LOAD (DATA_ID, SOURCE_ID, INSERT_DATE,  DATA_SIZE,
STATE, SYS_CURRENT_DATE)
            VALUES           (       1,        2, SYSDATE-seq2,         1,
'M:'||seq2,          DEFAULT);

       INSERT INTO DATA_LOAD (DATA_ID, SOURCE_ID, INSERT_DATE,  DATA_SIZE,
STATE, SYS_CURRENT_DATE)
            VALUES           (       1,        3, SYSDATE-seq2,         1,
'M:'||seq2,          DEFAULT);

       INSERT INTO DATA_LOAD (DATA_ID, SOURCE_ID, INSERT_DATE,  DATA_SIZE,
STATE, SYS_CURRENT_DATE)
            VALUES           (       1,        4, SYSDATE-seq2,         1,
'M:'||seq2,          DEFAULT);

       INSERT INTO DATA_LOAD (DATA_ID, SOURCE_ID, INSERT_DATE,  DATA_SIZE,
STATE, SYS_CURRENT_DATE)
            VALUES           (       1,        5, SYSDATE-seq2,         1,
'M:'||seq2,          DEFAULT);

       INSERT INTO DATA_LOAD (DATA_ID, SOURCE_ID, INSERT_DATE,  DATA_SIZE,
STATE, SYS_CURRENT_DATE)
            VALUES           (       1,        6, SYSDATE-seq2,         1,
'M:'||seq2,          DEFAULT);

       seq2 := seq2 + 1;

      end loop;

   seq1 := seq1 + 1;

  end loop;

end;

/    

UPDATE DATA_LOAD SET delete_date = insert_date+1

 WHERE to_char(insert_date, 'YYYY') = 2007;

UPDATE DATA_LOAD SET delete_date = insert_date+1

 WHERE to_char(insert_date, 'yyyy') = 2008;

UPDATE DATA_LOAD SET delete_date = insert_date+1

 WHERE to_char(insert_date, 'yyyy') = 2009;

UPDATE DATA_LOAD SET delete_date = insert_date+1

 WHERE to_char(insert_date, 'yyyy') = 2010;

UPDATE DATA_LOAD SET delete_date = insert_date+1

 WHERE to_char(insert_date, 'yyyy') = 2011;

UPDATE DATA_LOAD SET delete_date = insert_date+1

 WHERE to_char(insert_date, 'yyyy') = 2012;

UPDATE DATA_LOAD SET delete_date = insert_date+1

 WHERE to_char(insert_date, 'yyyy') = 2013;

UPDATE DATA_LOAD SET delete_date = insert_date+1

 WHERE to_char(insert_date, 'yyyy') = 2014;

UPDATE DATA_LOAD SET delete_date = insert_date+1

 WHERE to_char(insert_date, 'yyyy') = 2015;  

commit;  

BEGIN  dbms_stats.gather_table_stats(user,'DATA_LOAD',ESTIMATE_PERCENT => 100, GRANULARITY=>'ALL' , CASCADE => true);

END; /  

select TABLE_NAME,COMPOSITE,PARTITION_NAME,NUM_ROWS,subpartition_count from user_tab_partitions where TABLE_NAME='DATA_LOAD';

select TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,NUM_ROWS from USER_TAB_SUBPARTITIONS where TABLE_NAME='DATA_LOAD';

select table_name, partitioning_type, partition_count, interval from user_part_tables;  

SELECT * FROM DATA_LOAD; select count(*) from (

SELECT * FROM DATA_LOAD WHERE DELETE_DATE IS NULL );  

select count(*) from (

SELECT * FROM DATA_LOAD WHERE DELETE_DATE IS not NULL

);    

Regards

Eriovaldo    

2015-05-19 5:55 GMT-03:00 Cherif Ben Henda <cherif.benhenda_at_gmail.com>:  

Hi Eriovaldo,  

You can try to use virtual column as below.  

CREATE TABLE DATA_LOAD_INT ( data_id NUMBER(6),

  source_id NUMBER,

  insert_date DATE,

  delete_date DATE,

  data_size NUMBER,

  state VARCHAR2(20),

  sys_current_date DATE DEFAULT SYSDATE,

  year_delete_date NUMBER GENERATED ALWAYS AS (TO_NUMBER(TO_CHAR(delete_date,'YYYY'))) VIRTUAL,

  mm_delete_date NUMBER GENERATED ALWAYS AS (TO_NUMBER(TO_CHAR(delete_date,'MM'))) VIRTUAL,

  delete_date_part date GENERATED always as ( NVL(delete_date,TO_DATE('01-01-1900', 'DD-MM-YYYY'))) VIRTUAL   

  )

  PARTITION BY RANGE (delete_date_part)

  INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))     ( PARTITION p0 VALUES LESS THAN (TO_DATE('01-01-1900', 'DD-MM-YYYY'))

      )

enable row movement

;

Thanks,

Cherif.  

2015-05-19 5:21 GMT+01:00 pavan kumar <pavan_843_at_yahoo.co.in>:

Hi Eriovaldo,  

If you refer to oracle documentation, it clearly says.conveys the restrictions that "NULL" value are not allowed

For INTERVAL Clause
"You cannot specify NULL values for the partitioning key column."  

Try to use some default value for that column, it may resolve issue.  

  • Thanks

Pavan Kumar N  


From: Eriovaldo Andrietta <ecandrietta_at_gmail.com> To: ORACLE-L <oracle-l_at_freelists.org> Sent: Tuesday, 19 May 2015 6:13 AM
Subject: Using partition with interval and insert NULL  

Hello,  

I have the code below and it is not working right, when I insert data.

I got the error message: ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions

the insert has a null in the column delete_date.  

Is there a way to define the partition using interval with NULL ?    

DROP TABLE DATA_LOAD_INT;   CREATE TABLE DATA_LOAD_INT ( data_id NUMBER(6),

  source_id NUMBER,

  insert_date DATE,

  delete_date DATE,

  data_size NUMBER,

  state VARCHAR2(20),

  sys_current_date DATE DEFAULT SYSDATE,

  year_delete_date NUMBER GENERATED ALWAYS AS (TO_NUMBER(TO_CHAR(delete_date,'YYYY'))) VIRTUAL,

  mm_delete_date NUMBER GENERATED ALWAYS AS (TO_NUMBER(TO_CHAR(delete_date,'MM'))) VIRTUAL)

  PARTITION BY RANGE (delete_date)

  INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))     ( PARTITION p0 VALUES LESS THAN (TO_DATE('01-01-1900', 'DD-MM-YYYY'))

      )

enable row movement

;  

  • Insert massivo

declare

seq1 NUMBER;

seq2 NUMBER;

BEGIN DELETE FROM DATA_LOAD_INT; seq1 := 0;

seq2 := 0;

while (seq1 < 11)

  loop

    seq2 := 30;

     while (seq2 < 3001)

      loop

       INSERT INTO DATA_LOAD_INT (DATA_ID, SOURCE_ID, INSERT_DATE,
DELETE_DATE, DATA_SIZE,       STATE, SYS_CURRENT_DATE) 

            VALUES           (       1,        1, SYSDATE-seq2,       null,
1,  'M:'||seq2,          DEFAULT);

       seq2 := seq2 + 1;

      end loop;

   seq1 := seq1 + 1;

  end loop;

end;

/  

Regards

Eriovaldo          

--
http://www.freelists.org/webpage/oracle-l
Received on Tue May 19 2015 - 14:26:53 CEST

Original text of this message