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: '[EMAIL PROTECTED]' <[EMAIL PROTECTED]>
Cc: '[EMAIL PROTECTED]' <[EMAIL PROTECTED]>
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: [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).
