Oracle Scratchpad

June 5, 2012

Truncate Partition

Filed under: Infrastructure,Oracle,Partitioning — Jonathan Lewis @ 5:11 pm BST Jun 5,2012

A recent posting on the OTN database forum (which I didn’t answer correctly on my first attempt) raised the problem of truncating partitions when there are referential integrity constraints in place. Let’s start with a demonstration of the problem:

create table parent (
	id 	number,
	v1	varchar2(10),
	padding	varchar2(100),
	constraint	p_pk primary key(id) using index local
)
partition by range(id) (
	partition p1000 values less than (1000),
	partition p2000 values less than (2000),
	partition p3000 values less than (3000),
	partition p4000 values less than (4000),
	partition p5000 values less than (5000)
)
;

create table child (
	id_p		number,
	seq_c		number,
	v1		varchar2(10),
	padding		varchar2(100),
	constraint	c_fk_p foreign key (id_p) references parent,
	constraint	c_pk primary key (id_p, seq_c) using index local
)
partition by range(id_p) (
	partition p1000 values less than (1000),
	partition p2000 values less than (2000),
	partition p3000 values less than (3000),
	partition p4000 values less than (4000),
	partition p5000 values less than (5000)
)
;

insert into parent
select
	rownum - 1,
	rpad(rownum - 1,10),
	rpad('x',100)
from
	all_objects
where
	rownum <= 4000
;

insert into child
select
	id,
	val,
	v1,
	padding
from
	parent,
	(
		select 1 val from dual
		union all
		select 2 val from dual
		union all
		select 3 val from dual
		union all
		select 4 val from dual
	)
;

-- gather some stats on the two tables.

alter table child  truncate partition p4000;
alter table parent truncate partition p4000;

The problem is that you can’t truncate the parent partition because Oracle thinks there may be related data in the child partition – despite the fact that the internal code could be written to see that the partition’s data segment was “new”. The output from the two truncate commands is as follows:

-- the call for the child works

Table truncated.

alter table parent truncate partition p4000
            *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

You can’t truncate the parent partition unless you disable the foreign key constraint – and it might take quite a long time to re-enable it since we usually only worry about partitioning for large objects.

There is a slightly surprising workaround that might be good enough. If you set the foreign key constraint to novalidate, rather than disable, you can tell Oracle to exchange partitions without checking the validity of the foreign key constraint. So:

create table parent_ex (
	id 	number,
	v1	varchar2(10),
	padding	varchar2(100),
	constraint	px_pk primary key(id)
)
;

create table child_ex (
	id_p		number,
	seq_c		number,
	v1		varchar2(10),
	padding		varchar2(100),
	constraint	cx_fk_px foreign key (id_p) references parent_ex,
	constraint	cx_pk primary key (id_p, seq_c)
)
;

alter table child modify constraint c_fk_p
		rely
		enable
		novalidate
;

alter table child_ex modify constraint cx_fk_px
		rely
		enable
		novalidate
;

prompt	============================================================
prompt	We can now exchange the child and parent partitions we want.
prompt	============================================================

alter table child exchange partition p4000 with table child_ex
	including indexes
	without validation
;

alter table parent exchange partition p4000 with table parent_ex
	including indexes
	without validation
;

Having done the two exchanges, we have two empty partitions in the partitioned table and we can truncate the resulting parent_ex and child_ex tables. If we wanted we could also modify the referential integrity constraint back to validate mode – although this would take some time, of course, and we might just decide that it wasn’t necessary because (we believe) all the old data would be correct, and we may only be worried about making sure that new data will be checked.

The downside to this method is that we need to keep (or recreate) a pair of tables in the right tablespace so that we can do the exchange when we need to; but that doesn’t seem to be a terrible price to pay for the ability to “truncate” a single partition without disabling referential integrity constraints.

16 Comments »

  1. In this example the child table could be PARTITION BY REFERENCE(c_fk_p) and then TRUNCATE PARTITION in the parent will be allowed when the corresponding partition in the child is empty.

    In general, PARTITION BY REFERENCE is useful if you have tables connected by a referential constraint and you want to equi-partition the child and parent tables. Partition maintenance operations such as ADD, DROP, SPLIT and MERGE PARTITION will cascade implicitly to the child when performed on the parent. Since the rdbms is enforcing equi-partitioning, the enabled ref constraint does not block operations such as TRUNCATE and EXCHANGE PARTITION when the relevant partition of the other table is empty.

    Comment by George Eadon — June 5, 2012 @ 5:48 pm BST Jun 5,2012 | Reply

    • George,

      Thanks for highlighting that possibility. The OP was talking about 10g so the option made available through 11g completely slipped my mind.

      Comment by Jonathan Lewis — June 7, 2012 @ 5:47 pm BST Jun 7,2012 | Reply

  2. Reblogged this on lava kafle kathmandu nepal.

    Comment by lkafle — June 6, 2012 @ 2:47 am BST Jun 6,2012 | Reply

  3. Jonathan

    You’ve blogged about such a smilar case several years ago

    https://jonathanlewis.wordpress.com/2006/12/10/drop-parent-partition/

    Best regards

    Comment by Houri Mohamed — June 6, 2012 @ 1:37 pm BST Jun 6,2012 | Reply

    • Mohamed,

      Thanks for that – I really ought to read my own work from time to time ;)

      Interestingly one of the notes I wrote on that blog item claimed that the mechanism described by George Eadon doesn’t work with one particular pattern. However I can’t find the test case to justify the claim, and the quick test I ran based on the description didn’t repeat the problem in 11.1.0.7 or 11.2.0.3, so either there was a limitation in the earliest version that has been addressed, or I was wrong.

      Comment by Jonathan Lewis — June 7, 2012 @ 6:04 pm BST Jun 7,2012 | Reply

  4. Nice Article,
    I had also written an Article about Disabling and Enabling Constraints, What precautions to be taken while performing these Operations etc.. in http://www.way2db.in/oracle-constraints.html

    Comment by kshashikanth seo (@kshashikanthseo) — June 19, 2012 @ 3:47 am BST Jun 19,2012 | Reply

  5. Jon,

    I’m bemused, from above, what I understand is that, our target is to truncate p4000 partition. If this is so, then there would not need of child_ex table to be created. Only thing we need to do is create the parent_ex table and exchange the partition of p4000 parent table to parent_ex table and then go for truncate of p4000 partition of “parent” table. Not getting why we are dealing with p3000 i.e “alter table child exchange partition p3000 with table child_ex
    including indexes
    without validation”;
    Or do you think my understanding is going on wrong direction?

    Comment by ramnadhgRamnadh — December 6, 2013 @ 12:12 pm BST Dec 6,2013 | Reply

    • ramnadhgRamnadh,

      Thanks for the note – I hadn’t noticed that I’d switched from partition p4000 to p3000 in the working example, so I’ve now corrected that to make the two parts consistent.

      The exchange with the child is unnecessary, we could just truncate the child partition in place. I think I left it the way I did simply to have a consistent pattern all the way down the referential integrity tree.

      Comment by Jonathan Lewis — December 10, 2013 @ 10:35 am BST Dec 10,2013 | Reply

  6. Thanks for the post!

    We are in a similar situation in our production database. The parent table is partition based on the month and the table grows by about 500GB a month. We want to truncate partitions older than 3 months and rebuilding the constraints after the truncate takes about 45minutes now. If we use this approach, would it be possible to estimate how much time it would take to do the exchange partitions?

    Comment by R Rao — February 14, 2014 @ 5:27 am BST Feb 14,2014 | Reply

    • If you have any global indexes on either table then an exchange will have to delete the existing index entries (presumably about 1/4 of them, based on your comment about “keeping 3 months of single month partitions”) – this will probably take some time. The exchange should then be very quick. HOWEVER – you should test the mechanism before you use it in case there are any features of your system that introduce side effects that I haven’t allowed for.

      Do you have a test system at a suitable scale ? If not then recreate the tables (perhaps with an export / import of structure only) and ensure you have a few million rows per table – flush the buffer cache and test.

      Enable 10046 trace level 8 with the test to see what happens and what waits occur as they may give some hint about the time to exchange – for example without validation” might be a good idea, but “with validation” may work better, Oracle uses different SQL statements to check the data depending on which option you choose (and which version of Oracle you’ve got.)

      Comment by Jonathan Lewis — February 14, 2014 @ 8:21 am BST Feb 14,2014 | Reply

  7. Jonathan,

    Here is an alternate solution:

    ALTER TABLE child MODIFY CONSTRAINT c_fk_p DISABLE VALIDATE;
    ALTER TABLE parent TRUNCATE PARTITION p4000; — You could also DROP parent partition here.
    ALTER TABLE child MODIFY CONSTRAINT c_fk_p ENABLE VALIDATE;

    You can set the FK of the child table in DISABLE VALIDATE state. This means no DML allowed on child table (which means downtime). I get it! But now you can truncate/drop parent partition w/o any problem. And then enable the FK on child. This step will not check the data and will be very fast.

    All the above 3 statements ran under 1 second. So although there is a step that will disable the DML for a very short amount of time, you are able to drop parent/child partitions w/o compromising referential integrity.

    Comment by guest — April 2, 2014 @ 7:21 pm BST Apr 2,2014 | Reply

    • guest,

      Thanks for that information.
      I’ve just tried it (with and without the RELY) option on 11.2.0.4 and it works.

      However it has left me with a referential integrity constraint which is enabled, validated, and wrong because I have a load of child rows without parents !
      I’m not sure that that’s the way I want Oracle to behave.

      Comment by Jonathan Lewis — April 2, 2014 @ 8:15 pm BST Apr 2,2014 | Reply

  8. Of course. But you would drop the child partition first and then drop the parent partition. As you said, WE know what we are doing, but Oracle doesn’t. We just have to be very careful to use this.

    Comment by guest — April 2, 2014 @ 8:19 pm BST Apr 2,2014 | Reply

    • Agreed, we do know (or should know) what we’re doing; but I can’t help feeling that there’s a difference between setting a constraint to NOVALIDATE to do something and being able to get into an invalid state when the constraint is VALIDATEd. At least we can look at the state and KNOW that we shouldn’t expect the data to be correct if the constraint is novalidate.

      Comment by Jonathan Lewis — April 2, 2014 @ 8:25 pm BST Apr 2,2014 | Reply

      • Would you consider this a bug?

        Comment by guest — April 2, 2014 @ 8:27 pm BST Apr 2,2014 | Reply

        • I’m not sure – I think it probably ought to be.

          We can’t delete from the child while the constraint is in this state (and that doesn’t seem appropriate, though inserts and updates should be blocked):

          SQL> delete from child partition (p1000) where rownum <= 100;
          delete from child partition (p1000) where rownum <= 100
          *
          ERROR at line 1:
          ORA-25128: No insert/update/delete on table with constraint (TEST_USER.C_FK_P) disabled and validated
          

          On the other hand you can delete from the parent – even if the RI constraint is specified as “on delete cascade” – but then it’s disabled so that cascade doesn’t happen.
          Being able to truncate the parent is consistent with being able to delete the parent – but it doesn’t strike me as being consistent with being unable to delete the child.

          I haven’t checked the manuals yet to see if they say anything about restrictions, or give warnings about potentially counter-intuitive behaviour

          Comment by Jonathan Lewis — April 2, 2014 @ 8:38 pm BST Apr 2,2014


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,529 other followers