devxlogo

Improve Performance by Disabling and Enabling Constraints

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&#151usually 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.

devx-admin

Share the Post: