Re: Using partition with interval and insert NULL

From: Eriovaldo Andrietta <ecandrietta_at_gmail.com>
Date: Tue, 19 May 2015 08:17:48 -0300
Message-ID: <CAJdDhaPJq3jR799wr2jU-1co2izSgEUaX2upmONZM9NcspwQ=A_at_mail.gmail.com>

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 - 13:17:48 CEST

This message: [ Message body ]
Next message: Mark W. Farnham: "RE: Using partition with interval and insert NULL"
Previous message: Ls Cheng: "Re: toad's query to find index sql"
Maybe in reply to: Eriovaldo Andrietta: "Using partition with interval and insert NULL"
Next in thread: Mark W. Farnham: "RE: Using partition with interval and insert NULL"
Reply: Mark W. Farnham: "RE: Using partition with interval and insert NULL"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message