Minimize the time it takes to enable referential constraints by doing it in parallel

Alter session to "FORCE PARALLEL DDL"

The table in this example is created with the NOPARALLEL attribute. Using ALTER SESSION FORCE PARALLEL DDL, the validation of the constraint is done in parallel. This cuts the time consumed to under one minute in my environment.

 -- Disable the constraint (only takes a second)
ALTER TABLE usr.Table1 DISABLE CONSTRAINT TABLE1_FK_COL1;

-- Enable constraint in parallel takes about 40 seconds (NOPARALLEL takes approx. 5 minutes)
ALTER TABLE usr.Table1 ENABLE NOVALIDATE CONSTRAINT TABLE1_FK_COL1 ;
ALTER SESSION FORCE PARALLEL DDL PARALLEL 8;
ALTER TABLE usr.Table1 ENABLE VALIDATE CONSTRAINT TABLE1_FK_COL1 ;
ALTER SESSION ENABLE PARALLEL DDL;

What the documentation says about ALTER SESSION ... PARALLEL

The PARALLEL parameter determines whether all subsequent DML, DDL, or query statements in the session will be considered for parallel execution. This clause enables you to override the degree of parallelism of tables during the current session without changing the tables themselves.

FORCE forces parallel execution of subsequent statements in the session. If no parallel clause or hint is specified, then a default degree of parallelism is used. This clause overrides any parallel_clause specified in subsequent statements in the session but is overridden by a parallel hint.

Specify ENABLE to execute subsequent statements in the session in parallel. This is the default for DDL and query statements.
  • DML: DML statements are executed in parallel mode if a parallel hint or a parallel clause is specified.
  • DDL: DDL statements are executed in parallel mode if a parallel clause is specified.
  • QUERY: Queries are executed in parallel mode if a parallel hint or a parallel clause is specified.

Read more about ALTER SESSION [ENABLE | DISABLE | FORCE] PARALLEL [DML | DDL | QUERY] in the Oracle documentation on the links below.

Comments

Post a Comment

Popular posts from this blog

Set up CrashPlan on QNAP NAS using QPKG, the easy way

Image
Introduction to CrashPlan Description Here's how Wikipedia describes CrashPlan backup software: CrashPlan is backup software that allows Windows, Mac, Linux and Solaris users to back up their data to an offsite data center, computers belonging to friends and family, as well as to attached drives / shared folders. There is a free version (for non-datacenter backups) and CrashPlan+ which is for backing up to CrashPlan's servers. Both versions allow users to back up data automatically. How it can be used on your NAS If you are the owner of a NAS from QNAP, you can install CrashPlan and use it to backup the contents of your NAS to the CrashPlan data center, to other computers and/or external harddisks. You can also allow incoming backups, so that you and your friends and family can back up valuable documents and files to your NAS. Installation instructions I will not go into any more detail about what CrashPlan is. It's time to get it installed and set up on a
Read more

Samsung Account "processing failed" after firmware upgrade

How to resolve the "Processing failed" error logging in to Samsung Account after upgrade A Samsung Account can be used to remotely control your phone. From your web browser you can locate your phone on a map, trigger an alarm so that you can find it in your home, wipe the contents of the phone if it's been stolen, and more. Simply put; it may prove to be very useful when something bad has happened. After upgrading my Samsung Galaxy S2 phone to Ice Cream Sandwich (ICS), I got the error message "Processing failed" when trying to log in to my Samsung Account. Searching the web I found that I'm definitely not the only one. The solution is very easy, once you know about it.
Read more

How to remove Vlingo/Voice Command from your Android phone without rooting

Here's an easy way to remove/uninstall "Voice Command" from Vlingo, the annoying app that starts every time the home button is double-tapped on Samsung Galaxy S2. And sometimes when it's just pressed once... Since "Voice Command" is a system application, it's not easily uninstalled and used to require the phone to be rooted and thus warranty voided. Unlike the other ways of removing the app, this method works on Android phones that have been upgraded to "Ice-Cream Sandwich" (ICS) and does not require the phone to be "rooted" . Please note that there may not be a way to install it again, should you change your mind later. Maybe a factory reset brings it back, maybe it doesn't. I don't know, and have not tried to get it back. Follow these steps to remove "Voice Command" from your Galaxy S2 with ICS without root
Read more