 | |
#1
 
| |||
| |||
|
speed up set integrity? -
08-29-2010
, 08:11 PM
#2
| |||
| |||
|
|
* * Hi there, * * I'm loading about 100 GB of data into a database that contains about 1 GB of data prior * * to the load. There is a foreign constraint between two of the tables and I'm trying to * * run 'set integrity' to validate the contents of the tables. However, it's taking more than * * 12 hours to run and I'm wondering if there's anything I can do to speed it up. The two * * tables are using about 25% of the tablespaces and each table contains approximately * * 100 millions rows. * * The disk subsystem is about 50% busy and the CPU is about 10% busy while the * * 'set integrity' command is running. The foreign constraint references indexed columns * * in the two tables. Below are the definitions of the tables and the constraint. * * Thanks for any help, * * Bill CREATE TABLE "HPSS * *"."STORAGESEGTAPE" *( * * * * * * * * * "SSID" CHAR(32) FOR BIT DATA NOT NULL, * * * * * * * * * "VVID" CHAR(32) FOR BIT DATA , * * * * * * * * * "SCLASS_ID" INTEGER , * * * * * * * * * "NUM_READS" INTEGER , * * * * * * * * * "NUM_WRITES" INTEGER , * * * * * * * * * "SS_STATE" INTEGER , * * * * * * * * * "BLOCK_SIZE" INTEGER , * * * * * * * * * "WRITTEN_LENGTH" BIGINT , * * * * * * * * * "REL_START_ADDR_SECTION" INTEGER , * * * * * * * * * "REL_START_ADDR_OFFSET" BIGINT , * * * * * * * * * "REL_NEXT_BYTE_ADDR_SECTION" INTEGER , * * * * * * * * * "REL_NEXT_BYTE_ADDR_OFFSET" BIGINT , * * * * * * * * * "LAST_READ" INTEGER , * * * * * * * * * "LAST_WRITE" INTEGER , * * * * * * * * * "CREATION" INTEGER , * * * * * * * * * "UPDATE" INTEGER ) * * * * * * * * *IN "STSGTAPE" INDEX IN "INDEXES" ; ALTER TABLE "HPSS * *"."STORAGESEGTAPE" * * * * ADD PRIMARY KEY * * * * * * * * ("SSID"); CREATE TABLE "HPSS * *"."STORAGESEGTAPEABSADDR" *( * * * * * * * * * "SSID" CHAR(32) FOR BIT DATA NOT NULL, * * * * * * * * * "ORDINAL" INTEGER NOT NULL , * * * * * * * * * "ADDR" CHAR(4) FOR BIT DATA , * * * * * * * * * "TYPE" CHAR(1) FOR BIT DATA ) * * * * * * * * *IN "STSGTAPE" INDEX IN "INDEXES" ; -- DDL Statements for primary key on Table "HPSS "."STORAGESEGTAPEABSADDR" ALTER TABLE "HPSS * *"."STORAGESEGTAPEABSADDR" * * * * ADD PRIMARY KEY * * * * * * * * ("SSID", * * * * * * * * *"ORDINAL"); ALTER TABLE "HPSS * *"."STORAGESEGTAPEABSADDR" * * * * ADD CONSTRAINT "TAPESEGABSADDRCON1" FOREIGN KEY * * * * * * * * ("SSID") * * * * REFERENCES "HPSS * *"."STORAGESEGTAPE" * * * * * * * * ("SSID") * * * * ON DELETE CASCADE * * * * ON UPDATE NO ACTION * * * * ENFORCED * * * * ENABLE QUERY OPTIMIZATION; |
If you are sure of the data you are loading is good and doesn't
violate any RI. Try
1. Drop all the Constraints for this table
2. Drop RI for this table
3. Load
4. Put all the constraints back
and see if this runs quick.....do a test
cheers
Shashi Mannepalli
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.