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 [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA > -----Original Message----- > From: Jonathan Lewis [mailto:[EMAIL PROTECTED]] > 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 <[EMAIL PROTECTED]> > 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
