Re: Doubt related to partition by reference
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-lReceived on Sun Feb 25 2018 - 01:32:12 CET