Glad you're enjoying yourself. :)
Jared
"Khedr, Waleed" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
11/26/2002 01:29 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: RE: Mass updates to production tables (NULL to non-NULL)
Iterations takes longer because you have to (provided we will update all
the
rows):
1) read 10,000 rows to update the first 10,000.
2) read 20,000 rows to update the second 10,000.
3) so on
If we have N runs, each updates R rows, then
The number of rows need to be read =
1 * R + 2 * R + 3 * R+ .....+ N * R = R * (1+2+....+N)= R * (N / 2) * (N
+
1)
And N * R = total number of tows in the table (T)
So the number of rows need to be read to update the whole table =
(T / N) * (N /2) * (N + 1) = (T / 2) * (N + 1)
the number of rows need to be read to update the whole table = T * (N + 1)
/
2
T = Table rows number
N = number of iterations to update the whole table.
This was fun!
Waleed
-----Original Message-----
Sent: Tuesday, November 26, 2002 2:25 PM
To: Multiple recipients of list ORACLE-L
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Khedr, Waleed
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
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).
