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

From: Jesse, Rich <Rich.Jesse_at_qtiworld.com>
Date: Tue, 26 Nov 2002 10:17:03 -0800
Message-ID: <F001.0050C497.20021126101703@fatcity.com>

Hey Jonathon,

Two questions about your response:

1. Yes, you are obviously correct. My test was flawed. So, if NULLs use no space, then why does many NULL rows cause a table to extend? Is it because of the row directory in the data block header? Egad...going back to DBA school here. <blush> My apologies to my Oracle DBA Instructor! I've tried testing this theory, but I'm not having any luck.
2. The update works fine, except that each iteration takes progressively longer to run to the point that it's not feasible to run in production. So, what's wrong with the counted cursor loop, other than the possibility of ORA-1555?

Thx! :)

Rich

Rich Jesse                           System/Database Administrator
Rich.Jesse_at_qtiworld.com              Quad/Tech International, Sussex, WI USA

> -----Original Message-----
> From: Jonathan Lewis [mailto:jonathan_at_jlcomp.demon.co.uk]
> Sent: Monday, November 25, 2002 6:00 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Mass updates to production tables (NULL to non-NULL)
>
>
>
> You may have to worry about chaining.
>
> NULL columns use no space, even when
> they are CHAR() types.
>
> If you are planning a counted cursor loop, don't.
> You can update by rowid ranges (the slightly
> harder way) but one simple option is:
>
> update tableX
> set col_name = ' '
> where col_name is null
> and rownum <= 10000;
>
> repeat until rows updated < 10,000
>
>
>
>
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Next Seminar dates:
> (see http://www.jlcomp.demon.co.uk/seminar.html )
>
> ____Denver_______December 2/4
> ____England______January 21/23
>
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
>
>
>
> -----Original Message-----
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Date: 25 November 2002 21:37
>
>
> >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
>

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

Received on Tue Nov 26 2002 - 12:17:03 CST