Re: Using partition with interval and insert NULL

This is my scenary 1
At first time, the data is loaded without DELETE_DATE column filled. Then, this column will be populated according the time passing. The idea is keep partition by YEAR and subpartition by MONTH of the DELETE_DATE column.

I have another scenary , where I would like to consider a mix of 2 columns : INSERT_DATE and DELETE_DATE.
The column INSERT_DATE is always filled. When I have the DELETE_DATE equal NULL I need to considerar all lines of the table. In this situation, I would like a partition by YEAR and subpartition by MONTH of the INSERT_DATE. When the column DELETE_DATE != NULL I need to consider only lines that are in the interval. The interval can be 6,12, 18 , 24 ... months before a choosen date (MM-YYYY).

In both cases the query restriction clause must retrieve :

--> all lines that compose the list of data in the period (MM-YYYY - number of months).

Regards
Eriovaldo

2015-05-19 9:26 GMT-03:00 Mark W. Farnham <mwf_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