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: '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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Khedr, Waleed
  INET: Waleed.Khedr_at_FMR.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).