Show Posts

This section allows you to view all posts made by this member. Note that you can only see posts made in areas you currently have access to.


Messages - harveyb

Pages: [1]
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!

Pages: [1]