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.