Re: Very Strange Migrating Rows experience (Chained Rows)

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 24 Jan 2000 20:12:33 -0000
Message-ID: <948744922.5053.0.nnrp-06.9e984b29@news.demon.co.uk>

Answering the last bit first.

If you copy out, delete, copy back, commit then you will find that a completely new set of rowids gets taken. (It would be interesting to see if any of them were in the original blocks) since entries in the blocks row index do not get freed until after the commit.

Apart from that, I don't know what is going on, except that you have some inconsistent behaviour somewhere.

Have you tried to

    select

        nvl(vsize(col1),0) +
        nvl(vsize(col2),0) +

for all the chained rows ?

It may be the ANALYZE that is going wrong and claiming that the rows are 226 bytes when they are (say) 2048 226 bytes.

Other thoughts:

    There have been problems in the past on     tables with large numbers of columns that     resulted in spurious chaining.

    Direct load used to have problems getting     free space and average row length correct     when lots of columns were null. Are you using     NOLOGGING to extract the chained rows ?

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk Received on Mon Jan 24 2000 - 14:12:33 CST