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:
The following approach is being considered:
One thing that was suggested that I'm trying to find further information on would be:
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!
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!