Oracle Scratchpad

June 29, 2018

Truncate upgrade

Filed under: 12c,Infrastructure,Oracle,Upgrades — Jonathan Lewis @ 8:22 am BST Jun 29,2018

Connor McDonald produced a tweet yesterday linking to a short video he’d created about an enhancement to the truncate command in 12c. If you have referential integrity declared between a parent and child table then in 12c you can truncate the parent table and Oracle will truncate the child table for you – rather than raising an error. The feature requires the foreign key constraint to be declared “on delete cascade” – which is an option that I don’t see used very often. Unfortunately if you try to change an existing foreign key constraint to meet this requirement you’ll find that you can’t (yet) use the “alter table modify constraint” to make the necessary change. As Connor pointed out, you’ll have to drop and recreate the constraint – which leaves you open to bad data getting into the system or an outage while you do the drop and recreate.

If you don’t want to stop the system but don’t want to leave even a tiny window for bad data to arrive here’s a way to do it. In summary:

  1. Add a virtual column to the child table “cloning” the original foreign key column
  2. Create an index on the  virtual column (if you have concerns about “foreign key locking”)
  3. Add a foreign key constraint based on the virtual column
  4. Drop the old foreign key constraint
  5. Recreate the old foreign key constraint “on delete cascade”
  6. Drop the virtual column

Here’s some sample SQL:

rem	Script:		122_truncate_workaround.sql
rem	Author:		Jonathan Lewis
rem	Dated:		Jun 2018
rem	Purpose:	
rem	Last tested 
rem	via LiveSQL

drop table child;
drop table parent;

create table parent (
	p number,
	constraint p_pk primary key(p)

create table child (
	c	number,
	p	number,
	constraint c_pk primary key(c),
	constraint c_fk_p foreign key (p) references parent

create index c_fk_p on child(p);

insert into parent values(1);
insert into child values(1,1);


prompt	==========================================================================
prompt	Truncate  should fail with
prompt	ORA-02266: unique/primary keys in table referenced by enabled foreign keys
prompt	==========================================================================

truncate table parent;

alter table child add (
	pv generated always as (p+0) virtual

create index c_ipv on child(pv) online;

alter table child add constraint c_fk_pv
	foreign key (pv)
	references parent(p)
	on delete cascade
	enable novalidate
alter table child modify constraint c_fk_pv validate;

alter table child drop constraint c_fk_p;

prompt	===================================================================================
prompt	Illegal insert (first 1) should fail with
prompt	ORA-02291: integrity constraint (TEST_USER.C_FK_PV) violated - parent key not found
prompt	===================================================================================

insert into child (c,p) values(2,2);
insert into child (c,p) values(2,1);

alter table child add constraint c_fk_p
	foreign key (p)
	references parent(p)
	on delete cascade
	enable novalidate

alter table child modify constraint c_fk_p validate;

prompt	===================================================
prompt	Dropping the virtual column results in Oracle
prompt	dropping the index and constraint at the same time
prompt	===================================================

alter table child drop column pv;

The overhead of this strategy is significant – I’ve created an index (which you may not need, or want, to do) in anticipation of a possible “foreign key locking” issue – and I’ve used the online option to avoid locking the table while the index is created which means Oracle has to use a tablescan to acquire the data. I’ve enabled a new constraint without validation (which takes a brief lock on the table) then validated it (which doesn’t lock the table but could do a lot of work). Then I’ve dropped the old constraint and recreated it using the same novalidate/validate method to minimise locking time. If I were prepared simply to drop and recreate the original foreign key I wouldn’t need to create that index and I’d only do one validation pass rather than two.



  1. Very nice for sharing. -Balvant.

    Comment by Balvant Chhasiya — June 29, 2018 @ 10:31 am BST Jun 29,2018 | Reply

  2. Regarding validating of FK constraints, does the engine use index fast full scans when an index is present?

    I’m curious because in eBusiness Suite we’re required to set “_fast_full_scan_enabled” = FALSE, so it might be necessary to set it to TRUE at the session level before going through this process. (I wonder if it would resort to index full scan in that case, even if an FTS would be better.)

    I also wonder whether it’s possible to parallelize the index FFS, or the FTS if such is performed.

    Comment by Jason B. — July 9, 2018 @ 3:21 pm BST Jul 9,2018 | Reply

    • Jason B,

      I’ve got a library note that validating a foreign key constraint will just run a join query shaped roughly like:

      select /*+ all_rows ordered dynamic_sampling(2) */ A.rowid, :1, :2, :3
       "TEST_USER"."CHILD" A , "TEST_USER"."PARENT" B where( "A"."OBJECT_ID" is not
        null) and( "B"."OBJECT_ID" (+)= "A"."OBJECT_ID") and( "B"."OBJECT_ID" is

      It’s just an SQL statement, so the optimizer will optimize it like any other statement – which means it could probably take any legal path – include full scans, or parallel tablescans, depending on the column and system definitions. I may have a blog note about this somewhere on the site already – though maybe it’s just a draft.

      Comment by Jonathan Lewis — July 9, 2018 @ 9:19 pm BST Jul 9,2018 | Reply

  3. […] comment arrived yesterday on an earlier posting about an enhancement to the truncate command in 12c that raised the topic of what Oracle might do […]

    Pingback by Validate FK | Oracle Scratchpad — July 10, 2018 @ 10:42 am BST Jul 10,2018 | Reply

  4. Great article indeed. What are the consequences of not dropping the virtual column and/or associated index ? Ravin Maharaj

    Comment by Ravin Maharaj — August 8, 2018 @ 6:57 pm BST Aug 8,2018 | Reply

    • Ravin,

      If you don’t drop the index and column afterwards it means you’ll have an extra index to maintain on every insert and delete (and some updates, maybe) on the parent that probably doesn’t help you improve the performance on any queries; and if you haven’t dropped the spare constraint on the child table you’ll be doing two foreign key validations for every row inserted into the child.

      Comment by Jonathan Lewis — August 9, 2018 @ 5:30 pm BST Aug 9,2018 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: