Re: Doubt related to partition by reference

From: Eriovaldo Andrietta <ecandrietta_at_gmail.com>
Date: Sat, 24 Feb 2018 21:32:12 -0300
Message-ID: <CAJdDhaNTxwqANdLfQF+Vb2TZStf_Z8aJzkAzePHbcWTTQ__eSA_at_mail.gmail.com>



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 - 01:32:12 CET

Original text of this message