RE: Set index disabled/enabled
We are disabling/enabling indexes and constrains ton a regular basis when
loading data into our data warehouse.
We disable contraints and indexes which are referenced by referental
integrity
constraints. After the data is reloaded, these objects are enabled.
For example:
The following script runs before reloading "mag" data--
SET LOCK MODE TO WAIT 7200; BEGIN WORK;
LOCK TABLE mag IN EXCLUSIVE MODE;
! lock_test.sh sap mag # Wait for exclusive access
LOCK TABLE customers IN EXCLUSIVE MODE;
! lock_test.sh sap customers # Wait for exclusive access
SET CONSTRAINTS customers_c5_mag DISABLED;
SET CONSTRAINTS mag_c0_pk DISABLED;
SET INDEXES mag_x0_pk DISABLED;
COMMIT WORK;
The following script runs after reloading "mag" data--
SET PDQPRIORITY 100;
SET LOCK MODE TO WAIT 7200; BEGIN WORK;
LOCK TABLE mag IN EXCLUSIVE MODE;
! lock_test.sh sap mag # Wait for exclusive access
LOCK TABLE customers IN EXCLUSIVE MODE;
! lock_test.sh sap customers # Wait for exclusive access
-- The following error is expected and can be ignored:
-- 316: Index (mag_x0_pk) already exists in database.
CREATE UNIQUE INDEX sap.mag_x0_pk
ON mag (mag_nbr) FILLFACTOR 100
IN sapix_ds1 DISABLED; SET INDEXES mag_x0_pk ENABLED;
-- The following error is expected and can be ignored:
-- 704: Primary key already exists on the table.
ALTER TABLE mag
ADD CONSTRAINT PRIMARY KEY (mag_nbr)
CONSTRAINT sap.mag_c0_pk DISABLED; SET CONSTRAINTS mag_c0_pk ENABLED;
SET CONSTRAINTS customers_c5_mag ENABLED;
COMMIT WORK;
UPDATE STATISTICS MEDIUM FOR TABLE mag DISTRIBUTIONS ONLY;
UPDATE STATISTICS HIGH FOR TABLE mag (mag_nbr );
I hope this information addresses your questions.
Rick Bernstein
Alaris Medical Systems
-----Original Message-----
From: Gabriel Weisz [mailto:gweisz@SMTPLINK.PUROLATOR.COM]
Sent: Wednesday, September 16, 1998 09:11
To: informix-list@iiug.org
Subject: Set index disabled/enabled
Can anyone tell me if you are running a
"SET INDEXES FOR TABLE X DISABLE"
and then
"SET INDEXES FOR TABLE X ENABLE"
if there is an index reorg. involved in the process or not.
It seems to me that it is but I am not so sure. The manual
don't explain the process and I will be very grateful if someone
can explain me the process that actually is going on if there is a
reorg or only some flag that disable/enable the usage of the indexes.
Gabriel Weisz
Informix DBA Purolator Courier Inc.