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

1. In a few trials on 8K blocks, I've been able to get a maximum 662 rows of CHAR(1) (and CHAR(2) and CHAR(4))populated with spaces in a single block. As these weren't exactly scientific, I don't remember if I had kept PCTFREE at 2 thru my trials. Is this info documented somewhere? The only non-v7 reference I've found on Metalink was Oracle Support saying in a forum post that they pulled the info from Metalink because they got tired of complaints that the algorithm wasn't accurate enough to estimate table sizes.
2. Problem solved here. Since you've steered me in the right direction (I *do* have to worry about row chaining), I'm rebuilding the table and it's many, icky indexes (that's another story). On the rebuild, I'll populate the NULL columns.

Thanks, Jonathan! :)
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: Tuesday, November 26, 2002 1:25 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Mass updates to production tables (NULL to non-NULL)
>
>
>
> Point (1)
> As Larry Elkins pointed out to me in an offline post,
> I had forgotten to highlight the fact that even null CHAR
> columns do still use a length byte (unless they are
> trailing nulls - i.e. there are no following non-null columns).
> Apart from this, a row still needs:
> two byte entry in the row index in the block
> one lock byte
> one byte column count - (guess how Oracle
> manages rows with more than 255 columns)
> one byte flags
>
> Also, Oracle assumes that a row MAY have to
> migrate at some time, requiring enough space
> to be reserved for a 6-byte rowid. So the maximum
> rowcount in a block is (roughly) blocksize / 11.
>
> Point (2)
> Each iteration through the outer loop, or each
> iteration of the 'update 10,000'. Apart from the
> 1555, the main problem with a counted loop is
> that (in theory) it does a lot more work to achieve
> the same result as a 'proper' update statement.
> In practice, it may be possible to introduce side-effects
> on bulk update strategies that cause worse problems
> than the loop, though. For example, the 'each iteration
> takes longer than the last' is likely to be related to
> a mixture of delayed block cleanout (particularly
> in indexes), attempts at read-consistency, and
> cyclic block flushing.
>
> Note - ORA-01555 need not matter, if you have
> a mechanism that can respond to it gracefully.
>
>
> Regards
>
> Jonathan Lewis

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