Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: SQL
Expertise: Intermediate
Jul 11, 2005



Application Security Testing: An Integral Part of DevOps

Improve Performance by Disabling and Enabling Constraints

For most of us, working with large databases is a day-to-day activity. All too often, backup and restoration are considerably slow—usually because of the constraints imposed on the tables. Take a look at the following code:

CREATE TABLE Contractors 
 contractor_id numeric(5) not null, 
 contractor_name varchar2(50) not null, 
 CONSTRAINT contractor_pk PRIMARY KEY (contractor_id) 
In this code, PRIMARY KEY is the constraint. On creation, the constraints remain enabled. Now, assuming that it has a few million records, backing up this table is going to take a long time.

In cases like this, you can boost performance by disabling the constraint, running the backup, and then re-enabling the constraint. Here's the syntax for disabling the constraint:

ALTER TABLE table_name DISABLE constraint constraint_name
For enabling:

ALTER TABLE table_name ENABLE constraint constraint_name.
Using this code with the example, the table_name is Contractors and the constraint_name is contractor_pk.

MS Sridhar
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date