Re: how to avoid table chain?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sun, 28 Jun 1998 21:46:02 GMT
Message-ID: <01bda2dd$817a7400$0300a8c0@WORKSTATION>

Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote in article <01bda195$ca5132c0$0300a8c0_at_WORKSTATION>...
>
> It looks like a very small table, so
> analyze table compute statistics;
>
> Your num_blocks and empty_block (which are always computed)
> show 201 blocks used. 700 x varchar2(1) should give a rows size
> of around 1409, so about 12 rows per block.
>
> Assuming your num_rows is about right, the figures below are
roughly
> consistent with your row description apart from the chain_count -
so
> I would suspect the chain count.
>
>

This is, of course, total garbage --

        as they say - to err is human, to really foul things up you need a computer.

I have done a few block dumps in Oracle 8, and tentatively come to the following conclusion:
Oracle 8 seems to allow only 152 columns per stored row segment, this means a row of 700 columns has to be stored as 5 row segments:

	152 columns x 4
plus	92 columns x 1,

and this is why every row is chained !!!

In fact the tests I did with this left all 5 pieces in the same block, so the chaining
effect was not as serious as you might first think.

My second hypothesis is that under certain circumstances which I have not entirely
pinned down, Oracle 8 will not start a new row in a block into which another row has
already been chained if the chaining has taken place across multiple blocks- and this
would (nearly) explain why you get only one real row per block - something about
the stats of your data ensure that Oracle keeps starting each row in a new block
because it finds 'lots' of chaining in the block currently at the top of the free list.

Jonathan Lewis Received on Sun Jun 28 1998 - 16:46:02 CDT