RE: Mass updates to production tables (NULL to non-NULL)

And that was 9iR2, right (I'm on 8.1.7 in "real" life)? The docs are rather amusing. From the 9iR2 documentation the New Features doc says "You can now use parallel DML on nonpartitioned tables". This wasn't mentioned in the 9iR1 new features doc. And then the DW Guide for 9iR2 says "Tables must be partitioned in order to support parallel UPDATE and DELETE". Oh well ;-)

Decided to run a quick test. On 9iR2 watched v$sess_io.block_changes rack up the numbers for each parallel process. When repeating the test against 9iR1, I watched the physical reads and consistent gets increase for each process, but the only process racking up block gets and block changes was the parent process.

Similar thing with V$OPEN_CURSOR. Using parallel 4, in 9iR1 I would see the 4 parallel selects (e.g. SELECT /*+ Q1000 NO_EXPAND ROWID(A1) */ .....) but only one update statement. Against 9iR2 you would see 4 updates in v$open_cursor, e.g. "UPDATE (SELECT /*+ NO_EXPAND ROWID(A1) ......".

So it looks like it came along with 9iR2, can't really trust the docs on this one ;-) But curious what others have seen.

Regards,

Larry G. Elkins
elkinsl_at_flash.net
214.954.1781

> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Connor
> McDonald
> Sent: Tuesday, November 26, 2002 3:29 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Mass updates to production tables (NULL to non-NULL)
>
>
> pdml on non-partitioned tables is there in 9i
>
> hth
> connor
>
> --- "Deshpande, Kirti" <kirti.deshpande_at_verizon.com>
> wrote: > How true !! I just ignored the 'writing' part
> of the
> > parallelized 'reading'. Sorry.
> > Thanks for catching it, Waleed.
> > Our own update process, that I am baby sitting, was
> > on my mind that involves a few partitioned tables...
> >
> >
> > Regards,
> >
> > - Kirti
> >
> > -----Original Message-----
> > Sent: Monday, November 25, 2002 9:09 PM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > PDML can be used only on partitioned segments. When
> > PQO is used during an
> > update on a non-partitioned segment, the parallel
> > processes (slaves) work
> > together to scan (read) the segment and find the
> > rows that need to be
> > updated. these rows get communicated back to the
> > master process using the
> > rowid. The master process starts to update rows
> > serially using the rowid for
> > update and this process could be slow and resources
> > intensive specially when
> > you are updating most of the rows in the table (you
> > will see tons of db file
> > sequential read).
> >
> > Regards,
> >
> > Waleed

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: elkinsl
  INET: elkinsl_at_flash.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).