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
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: 'ORACLE-L_at_fatcity.com' <ORACLE-L_at_fatcity.com> Cc: 'jonathan_at_jlcomp.demon.co.uk' <jonathan_at_jlcomp.demon.co.uk> Date: 26 November 2002 18:16

>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! :)

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

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