Preparing a table to reorg

  • From: Neil Kodner <nkodner@xxxxxxxxx>
  • To: oracle-l-freelists <oracle-l@xxxxxxxxxxxxx>, Neil Kodner <nkodner@xxxxxxxxx>
  • Date: Wed, 16 Jun 2010 14:41:40 -0400

I'm looking to use dbms_redefinition to reorganize a table that is affected
from migrated rows.  The table has an average row length of 2131 on a
tablespace with an 8k block size.  Nearly half the records in the table are
migrated.

The table contains an inline CLOB with an average length of 2099.  Out of 6M
rows, 1M clob values are null, and out of the remaining 5M, its longer than
4000 characters about 10,000 times.

When a row in this table is created, the clob is empty and later populated
with an UPDATE statement.  The actual values for the clob field aren't
determined until a few days after the row has been inserted.  At that point
the clob is written along with modifiy user/date fields.  Not much else gets
updated for each row.

The table has pctfree 40 and its pctused is null.

I would imagine the update is what's causing the migration.  My gut tells me
that pctfree is wrong but I'm not sure how to tune it.

Since I'm looking at redefining the table, I'm also considering moving the
clob out of line because 1) it's not referenced in the majority of the
queries and 2) it has the opportunity to be greater than 4000 characters but
it's not all that likely that it will be.

I'd love to hear thoughts and opinions on the pctfree/pctused  and clob
issues before moving forward with the rebuild in a test environment.

And finally, with an average row length of 2131, would I benefit from
rebuilding this table on a tablespace with a larger block size?

Other related posts: