Author Topic: Parallel Updates of Partitioned Tables  (Read 3265 times)

harveyb

  • Newbie
  • *
  • Posts: 1
    • View Profile
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: newbielink:javascript:void(0); [nonactive]
ALTER TABLE XYZ ADD (
  FIELD1 DECIMAL(14,2) DEFAULT 0 NOT NULL, plus 7 others...)

The following approach is being considered:

Code: newbielink:javascript:void(0); [nonactive]
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: newbielink:javascript:void(0); [nonactive]
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!
« Last Edit: March 17, 2009, 11:10:23 AM by Mike »