3 Replies Latest reply: Jul 24, 2011 7:04 AM by user3206995 RSS

    Parallel degree of parallelism (DOP) equals the number of partitions or sub

      hi all

      I wanted to parallel update a nonpartitioned table . I thought this is in Oracle 11g possible, however the statement runs sequential:


      UPDATE /*+ PARALLEL (B, 8) */ test b
      b.address_1 = NULL,
      b.address_2 = NULL
      WHERE address_1 IS NOT NULL
      OR address_2 IS NOT NULL

      UPDATE STATEMENT Cost = 1418 16 MRows 397 MB--ALL_ROWS
      UPDATE of 'test'
      PX SEND (QC (RANDOM)) of 'SYS.:TQ10000' 16 MRows 397 MB [P->S]
      PX BLOCK (ITERATOR) 16 MRows 397 MB [PCWC]
      TABLE ACCESS (FULL) of 'test' (TABLE) 16 MRows 397 MB [PCWP]{B@UPD$1}--ANALYZED


      Is this because of the following:
      For tables that do not have the parallel DML itl invariant property (tables created before Oracle Database release 9.2 or tables that were created with the COMPATIBLE initialization parameter set to less than 9.2), the degree of parallelism (DOP) equals the number of partitions or subpartitions. That means that, if the table is not partitioned, the query runs in serial. To see what tables do not have this property, issue the following statement:

      SELECT u.name, o.name FROM obj$ o, tab$ t, user$ u
      WHERE o.obj# = t.obj# AND o.owner# = u.user#
      AND bitand(t.property,536870912) != 536870912;


      will recreating the table really help this and does the PCTFREE and INITRANS play a part


      If my DOP is 6 does it help to recreate my table with initrans set to a value of 6 and above?

      Thanks in advance