Row chaining

[Sameer]

Hi,

I have a table with four columns

1. id NUMBER
2. text1 VARCHAR2(4000)
3. text2 VARCHAR2(4000)
4. text3 VARCHAR2(4000)


pctfree 10

My db_block_size is 8192,

Does this mean, with above parameters, row chainging will always take place, if data is inserted to full capacity of column.

How pctfree parameter will help if my row length exceeds the db_block_size parameter as in above table.

Thanks

Sameer


[Edited by Sameer on 08-24-2002 at 11:27 AM]

[grjohnson]

You'll have constant chaining for this table with an 8k block. If you are using 9i, you could create a tablespace with a larger blocksize.

Cheers

[Sameer]

This means in versions before 9i, if the rowlength exceeds db_block_buffers then row chaing will take place if data is inserted to full capacity of column..

[grjohnson]

If inserted larger than db_block_size, then yes. The idea is to have a block size large enough to accomidate your rows. Remember, storage and performance go hand in hand.

[Sameer]

Originally posted by grjohnson
If inserted larger than db_block_size, then yes. The idea is to have a block size large enough to accomidate your rows. Remember, storage and performance go hand in hand.

Thanks for the reply..

But in case of large and wide (many columns especially with with varchar2) tables it is difficult to control row length below db_block_size.

I have few tables which stores medicine procedures and diagnosis, and it difficult to chop table in two different tables.
and these tables row length is more than db_block_size..

Any suggestion in this scenario..

Thanks

Sameer

[grjohnson]

Unfortunatly not, if using Oracle server < 9i, your only alternative (if it really is affecting performance is create a new database with a larger block_size). It depends on the impact of the system, sure it's very undesirable. How many records are in the table etc?

[Zaggy]

It all depends on the avg. row length, if the entries are lite then they should fit. If not, then you will have chaining.