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

Larry,
We are using partitioned tables in our update process, and I did not get enough time to think about the partition exchange solution (I recall you had mentioned this before). I was out of office for too long and Developers had already tested the 'cursor' based approach for these updates. Any new method at the 11th hour would have pushed the implementation date..

May be next time..

Regards,

• Kirti

-----Original Message-----
Sent: Monday, November 25, 2002 7:19 PM
To: Multiple recipients of list ORACLE-L

Kirti,

The snapshot too old error you mention really hits home. I guess it's because the error message is somewhat misleading, leading to a knee-jerk reaction for the developer (and some DBA's) to start committing *more* frequently, thus increasing the chances of the 1555 error. 99% of the time, because I've normally had the rollbacks to support it, the cause of the 1555 errors were the intermediate commits freeing up the slots to be overwritten and blowing the read consistent view out of the water (and the developers were trying to be good). You tell them to stop intermediate committing and everything straightens out. I guess I'm preaching to the choir ;-)

And now that they don't do the intermediate commits, you convince them to do it in pure SQL, maybe even parallelized ;-) And that 1.5 hour insert, for example, once you take the row at a time cursor based approach out of the mix, then finishes in 3 minutes. And they want to know about restartability. Well, run the whole transaction again -- everything went or nothing went. You can do it 10 - 30 or more times in the same amount of time it takes to do one pass using the cursor based and intermediate commit approach.

And another approach to consider is the update as an insert approach. I've used this in the past and currently use it for mass "updates", and then exchange partition the data back in. Take a few hundred million row cursor based update that projected to run for days/years (killed after 4 days), changed to a parallel insert, and then exchanged partition back in. Just a few minutes required. Of course you need the space for temporarily having two copies, and the window to exchange back in without blowing existing queries out of the water, but an effective approach nonetheless. Simply a twist on the mass delete approach of CTAS'ing out the rows you want to keep, and then truncating and inserting back in (or exchange partition back in).

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 Deshpande,
> Kirti
> Sent: Monday, November 25, 2002 6:19 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Mass updates to production tables (NULL to non-NULL)
>
>
> I would consider PDML to get the job done faster, provided there
> are enough resources.
> Using a cursor seems like a good idea, but avoid fetching across
> commits. We are going through a similar exercise, adding 10000000
> to a cust_id field to denote the source of the data, and
> Developers complained about ORA-1555. Asked them not to commit as
> existing rollback segments and space were adequate ;)
>
> Good Luck,
>
> - Kirti
>
> -----Original Message-----
> Sent: Monday, November 25, 2002 3:04 PM
> To: Multiple recipients of list ORACLE-L
>
>
> I've got a real hot project (8.1.7.2 on HP/UX 11.0) that needs to
> have NULLs
> converted to spaces on three different columns. Each is a CHAR, so I
> shouldn't need to worry about chaining, since that column's full size has
> already been allocated in the block, right? But the first column has 1.2M
> NULLs out of 1.45M rows.
>
> My first test was to just UPDATE mytable SET mycol = ' ' WHERE mycol IS
> NULL, after removing the index on that column. Seeing as there were many
> more rows updated than I had anticipated, I was going to test the UPDATE
> using a cursor, and committing at every 10K rows (~120 total commits) to
> reduce rollback and locking issues.
>
> Thoughts? Since this table is used for time-and-attendance and directly
> affects payroll, downtime isn't possible.
>
> TIA!
>
> Rich
>
>
> Rich Jesse System/Database Administrator
> Rich.Jesse_at_qtiworld.com Quad/Tech International,
> Sussex, WI USA
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Jesse, Rich
> INET: Rich.Jesse_at_qtiworld.com
>
> 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).
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Deshpande, Kirti
> INET: kirti.deshpande_at_verizon.com
>
> 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).
>

-- 
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).



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deshpande, Kirti
  INET: kirti.deshpande_at_verizon.com

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).