Re: how to avoid table chain?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 27 Jun 1998 06:38:46 GMT
Message-ID: <01bda195$ca5132c0$0300a8c0@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.

David Shi <dshi_at_magpage.com> wrote in article <6muklq$rbs$2_at_204.179.92.143>...
> I am trying to simulate a 700 column table, each column is
varchar2(1). I
> created the data in a flat file and loaded into Oracle, looks like
all the rows
> are chained and one block only hold 1 row. The block size is 16k,
and average
> row length is 1409 from the "estimate statistics", I can't figure
out why Oracle
> has to chain (I used the "pctfree 0"). The following is the partial
"dba_tables"
> (I used "analyze table estimate statistics"):
>
> OWNER TABLE_NAME B NUM_ROWS BLOCKS EMPTY_BLOCKS
AVG_SPACE
> ---------- --------------- - ---------- ---------- ------------

---

> OPS$DSHI SIMULATE N 3297 3,300 3099
 14867
>
>
> CHAIN_CNT AVG_ROW_LEN DEGREE INSTANCES CACHE TBL_LOCK
> ---------- ----------- ---------- ---------- ----- --------
> 3297 1409 1 1 N ENABLED
>
> Thanks for you help.
>
> David
>
Received on Sat Jun 27 1998 - 01:38:46 CDT