Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


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

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.

 

 

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