Messages

1

PSOUG / Parallel Updates of Partitioned Tables

« on: March 17, 2009, 09:21:49 am »

I'm new to parallel processing and I'm looking for guidance/ suggestions.

Problem - A partitioned table (approximately 3 TB total) needs to be updated to add several fields, set the value of those fields, establish a default value, and set the fields to not null.

The original statement reads like:

Code: [Select]

ALTER TABLE XYZ ADD (
  FIELD1 DECIMAL(14,2) DEFAULT 0 NOT NULL, plus 7 others...)
The following approach is being considered:

Code: [Select]

ALTER SESSION SET PARALLEL DML TRUE;
ALTER SESSION SET PARALLEL DDL TRUE;

ALTER TABLE XYZ ADD (
  FIELD1 DECIMAL(14,2) NULL, plus 7 others...)

UPDATE TABLE XYZ /* +PARALLEL */ SET
FIELD1=0,
plus 7 others....

ALTER TABLE XYZ MODIFY (
FIELD1 DECIMAL(14,2) DEFAULT 0 NOT NULL, plus 7 others...)
One thing that was suggested that I'm trying to find further information on would be:

Code: [Select]

UPDATE TABLE XYZ /* +PARALLEL */ PARTITION(partition_name) SET
FIELD1=0, plus 7 others...
and there was mention of a MULTIPLE SQL SESSIONS parameter.

I'm looking for guidance and ideas for how to approach this problem.  Fortunately I  do have some lead time and environments that I can test in for comparing the various methods to determine which one is most effective.

Your feedback will be appreciated!