Re: LOB columns and migrated rows

On 02/02/2006 10:59:43 PM, hitender chugh wrote:
> We have tables with LOB columns which are showing chained/migrated rows.
> I want to know how to reduce the chaining/migration of rows for such tables?

Disable storage in row.

> Will putting these tables on tablespace with bigger blocksize say 32k will
> help reducing migration and increase performance?

It's the rows in the original blocks that are migrated or chained. The problem is that when you have a LOB column, not only descriptor is stored in row, up to 3500 bytes can be stored there as well.

> Using multiple block size tablespace feature on 9i , the max blocksize is
> 32k. If avg row len is more than 100k even placing LOB column tables on 32k
> blocksize will not help.

Do you find it to be any faster? You will have to allocate a separate cache for 32k blocks which can not be used for anything else and will normally be underutilized.

> What are the performance implications of having tables spread out on
> different blocksize tablespace?

Don't tell me that you actually created a database with multiple block sizes without testing it first?

> I have not used this feature before so I want to know what is the
> experience/suggestions of other people on this.

Multiple block sizes are normally used to plug in tablespaces from another database, typically from an OLTP to DW. It's much faster then export and import. Multiple block sizes are practically useless for anything else.

-- 
Mladen Gogala
http://www.mgogala.com

--
http://www.freelists.org/webpage/oracle-l