Partitioning a table refenceing other

Question

I have a target table T1 which doesn't have a Date field. The current size is increasing rapidly. Hence I need to add a Date field and also perform table partitioning on this target table.

T1 has PRIMARY KEY (DOCID, LABID)
 and has CONSTRAINT FOREIGN KEY (DOCID) REFERENCES T2

Table T2 is also complex table and has many rules in it.

T2 has PRIMARY KEY (DOCID)

My question is, as I need to partition T1. Is it possible NOT TO perform any step for T2 before T1 is partition? DBA told me that I need to partition T2 first before I touch T1??

The question is if a Partitioned table can have a FK constraint referencing a non partitioned table? — Ricardo Arnold, Mar 07 '16 at 07:32
Just to be clear, the referenced table T2 doesn't have the date that you plan to partition T1 on - so you aren't talking about [reference partitioning](https://docs.oracle.com/cd/E11882_01/server.112/e25523/partition.htm#CACIHDII)? So what would you partition T1 on? Or, what did your DBA recommend, and did they explain why? — Alex Poole, Mar 07 '16 at 08:55
@AlexPoole You are correct too. The referenced table T2 has a date on which I guess T1 will be partitioned on. — Some Java Guy, Mar 07 '16 at 10:01
So you're adding another FK on T2 that references the date on T1? Did you look at the doc I linked to before? Partitioning T1 would reduce duplicated data and maintenance. You don't have to do that but you might as well. Do you have a reason to go against your DBA's advice? — Alex Poole, Mar 07 '16 at 11:40

score 1 · Accepted Answer · answered Mar 07 '16 at 12:04

1

There is no need to partition T2 before partitioning T1. Foreign key constraints do not care in the slightest bit about partitioning.

Best of luck.

answered Mar 07 '16 at 12:04

Bob Jarvis - Слава Україні

48,992
9
77
110

Can you please share any related link which can put some light on my scenario.. thanks – Some Java Guy Mar 07 '16 at 15:28

Marmite Bomber · Answer 2 · 2016-03-07T18:30:03.247

You have – as proposed by others - two options. The first one is to add a redundant column DATE to the table T1 (child) and introduce the range partitioning on this column.

The second option is to use reference partitioning. Below is the simplified DDL for those options.

Range partitioning on child

create table T2_P2 /* parent */
(docid number not null,
 trans_date date not null,
 pad varchar2(100),
 CONSTRAINT t2_p2_pk PRIMARY KEY(docid)
 );

create table T1_P2 /* child */
(docid number not null,
 labid number not null,
 trans_date date not null,  /** redundant column **/
 pad varchar2(100),
 CONSTRAINT t1_p2_pk PRIMARY KEY(docid, labid),
 CONSTRAINT t1_p2_fk
 FOREIGN KEY(docid) REFERENCES T2_P2(docid) 
 )
   PARTITION BY RANGE(trans_date)
    ( PARTITION Q1_2016 VALUES LESS THAN (TO_DATE('01-APR-2016','DD-MON-YYYY')) 
    );

Reference partition

create table T2_RP /* parent */
(docid number not null,
 trans_date date not null,
 pad varchar2(100),
 CONSTRAINT t2_rp_pk PRIMARY KEY(docid)
 )
   PARTITION BY RANGE(trans_date)
    ( PARTITION Q1_2016 VALUES LESS THAN (TO_DATE('01-APR-2016','DD-MON-YYYY')) 
    );

create table T1_RP /* child */
(docid number not null,
 labid number not null,
 pad varchar2(100),
 CONSTRAINT t1_rp_pk PRIMARY KEY(docid, labid),
 CONSTRAINT t1_rp_fk
 FOREIGN KEY(docid) REFERENCES T2_RP(docid) 
 )
 PARTITION BY REFERENCE(t1_rp_fk);

Your question is basically if the first option is possible, so the answer is YES.

To decide if the first option is preferable I’d suggest checking three criteria:

Migration

The first option requires a new DATE column in the child table that must be initialized during the migration (and of course correct maintained by the application).

Lifecycle

It could be that the lifecycle of both tables is the same (e.g. both parent and child records are kept for 7 year). In this case is preferable if both tables are partitioned (on the same key).

Access

For queries such as below you will profit from the reference partitioning (both tables are pruned - i.e. only the partitions with the accessed date are queried).

select * from T2_RP T2 join T1_RP T1 on t2.docid = t1.docid
where t2.trans_date = to_date('01012016','ddmmyyyy');

In the first option you will (probalbly) end with FTS on T2 and to get pruning on T1 you need to add predicate T2.trans_date = t1.trans_date

Having said that, I do not claim that the reference partition is superior. But I think it's worth to examine both options in your context and see which one is better.

Partitioning a table refenceing other

2 Answers2