RE: Doubt related to partition by reference

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Sun, 25 Feb 2018 14:14:06 -0500
Message-ID: <00cd01d3ae6c$e71ced50$b556c7f0$_at_rsiz.com>

Well, in my arrogant opinion your application is officially broken. However, it seems unlikely you can change your application to do things completely correctly.  

IF a small change is possible, I would change the initial destination table so that it is nullable and not partitioned. Then put a trigger on the fk_id that when it is set from null to anything the row is inserted into the table with the reference partitions as you desire (complete). This should work reasonable if all updates to the initial insertion table place before the id is created.  

You may either immediately delete the row from the interim location table OR, at a periodic maintenance interval like a day or month, either truncate or rotate table names to create the new insertion point. You may find using a synonym and altering it amongst a rotating set of initial insertion tables to be useful, since then there is no point in time when the insertion target is not available and there is no deletion activity on the table receiving the initial inserts.  

Of course the real solution is to have the id in hand at the insertion time so that you do not have to ever to the functional double insert and eventual truncation. The Zitelli reference is the best one I am aware of, by the way.  

Good luck.  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Eriovaldo Andrietta Sent: Saturday, February 24, 2018 7:32 PM To: William Robertson
Cc: ORACLE-L
Subject: Re: Doubt related to partition by reference  

Hi Willian,  

You area right and I cannot create a dummy invoice.  

I got this script, but it depends of a FK not null.

Look at this document : http://www.nocoug.org/download/2010-05/Zitelli-Reference_Partitioning_NoCOUG.pdf

In the slide 14 it says about restrictions for using PARTITION REFERENCE.    

This is what I was looking for.

Sample:

• YEAR - MONTH DROP TABLE ERI_PART;

CREATE TABLE eri_part (

   id            number,
   year          NUMBER, 
   month         NUMBER,
   day           NUMBER,

   amount_sold NUMBER)
PARTITION BY RANGE (year,month)

  (PARTITION before2012 VALUES LESS THAN (2012,1),
   PARTITION before2013 VALUES LESS THAN (2013,1),
   PARTITION partfuture VALUES LESS THAN (MAXVALUE,0));

alter table eri_part
  add constraint PK_ERI_PART primary key (ID)   using index
;

drop table eri_part_filha;

CREATE TABLE ERI_PART_FILHA (

ID         NUMBER, 
FK_ID      NUMBER NOT NULL,  <<<<<<<<<<<<<<<<< this column must be not null and cannot be DEFERRABLE
TEXTO      VARCHAR2(30),

CONSTRAINT pk_eri_part_filha PRIMARY KEY (id), CONSTRAINT fk_eri_part_filha FOREIGN KEY (fk_id) REFERENCES eri_part (id) ON DELETE CASCADE )
PARTITION BY REFERENCE (fk_eri_part_filha); );   

create index idx_eri_part_01 on eri_part (MONTH, year) ;    

insert into eri_part (ID, year, month, day, amount_sold) values (1,2011, 12, 15, 1000);   
insert into eri_part (ID, year, month, day, amount_sold) values (3,2012, 01, 15, 1000);   
insert into eri_part (ID, year, month, day, amount_sold) values (4,2012, 02, 15, 1000);   
insert into eri_part (ID, year, month, day, amount_sold) values (5,2012, 03, 15, 1000);   
insert into eri_part (ID, year, month, day, amount_sold) values (6,2012, 04, 15, 1000);   
insert into eri_part (ID, year, month, day, amount_sold) values (7,2012, 05, 15, 1000);   
insert into eri_part (ID, year, month, day, amount_sold) values (8,2012, 06, 15, 1000);   
insert into eri_part (ID, year, month, day, amount_sold) values (9,2012, 07, 15, 1000);   
insert into eri_part (ID, year, month, day, amount_sold) values (10,2012, 08, 15, 1000);   
insert into eri_part (ID, year, month, day, amount_sold) values (11,2012, 09, 15, 1000);   
insert into eri_part (ID, year, month, day, amount_sold) values (12,2012, 10, 15, 1000);   
insert into eri_part (ID, year, month, day, amount_sold) values (13,2012, 11, 15, 1000);   
insert into eri_part (ID, year, month, day, amount_sold) values (14,2012, 12, 15, 1000);   
insert into eri_part (ID, year, month, day, amount_sold) values (15,2012, 12, 15, 1000);   
insert into eri_part (ID, year, month, day, amount_sold) values (16,2013, 12, 15, 1000);   
insert into eri_part (ID, year, month, day, amount_sold) values (17,2014, 12, 15, 1000);   
insert into eri_part (ID, year, month, day, amount_sold) values (18,NULL, NULL, 15, 1000);   

insert into eri_part_filha VALUES (1,1,'LINHA A');
insert into eri_part_filha VALUES (2,1,'LINHA B');

insert into eri_part_filha VALUES (3,2,'LINHA C'); insert into eri_part_filha VALUES (4,2,'LINHA D');

commit;

select * from eri_part where year = 2010; select * from eri_part where year = 2012; select /*+ NO_INDEX (eri_part) */ * from eri_part where year = 2013;

call dbms_stats.gather_table_stats(user,'ERI_PART',ESTIMATE_PERCENT => 100);

select * from  eri_part partition ( partfuture ) ;
select * from  eri_part partition ( before2012 ) ;
select * from  eri_part partition ( before2013 ) ;

select * from  eri_part_filha partition ( partfuture ) ;

select * from eri_part_filha partition ( before2012 ) ; select * from eri_part_filha partition ( before2013 ) ;

It works fine.  

Now I am looking for a solution different of PARTITION REFERENCE to apply when I have the NULL column (as FK_ID in the sample)  

Thanks and regards

Eriovaldo    

2018-02-24 6:21 GMT-03:00 William Robertson <william_at_williamrobertson.net>:

How do you want to partition those invoice details that aren’t associated with any invoice? The whole idea of reference partitioning is that partitioning cascades from the parent to the child, so there can’t be orphans.  

Can you create a dummy invoice 0 and make that the default when the parent invoice is unknown? These would all go in one partition though, so if there are a huge number of them it might defeat the partitioning strategy.  

William  

On 23 Feb 2018, at 01:50, Eriovaldo Andrietta <ecandrietta_at_gmail.com> wrote:

​

Hello,  

I am using oracle 12.2 version.

I have a partitioned table INVOICE by range that has a column ID as primary key.

I have other table INVOICE_DETAIL that has a column FK_INVCE_ID NUMBER(20,0) that is a reference to INVOICE.ID  

I tried to create a partition by reference and got this error.  

ORA-14651: reference partitioning constraint is not supported  

I cannot change the INVOICE_DETAIL. FK_INVCE_ID to NOT NULL, it must allow NULL.  

Is there another way to create the partition by reference for INVOICE_DETAIL when the column FK_INVOICE_ID is filled ?  

Regards

Eriovaldo    

--
http://www.freelists.org/webpage/oracle-l

Received on Sun Feb 25 2018 - 20:14:06 CET