RE: Preparing a table to reorg
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <nkodner@xxxxxxxxx>, "'oracle-l-freelists'" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 16 Jun 2010 15:54:28 -0400
Have you considered having a standard 2K (or so) CLOB to slap in there when
there row is born?
Depending on the aggregate vsize of the other columns in a row and the
details of the block overhead of your tablespace and segment specifications,
you're probably getting 3 rows per block. If your skew around that average
of 2099 is not too bad, this will minimize row migration at a cost of the
extra redo on the insert.
If you elect to leave the standard filler CLOB in place, that will cost you
an extra 1/6th storage for where you have existing nulls. Whether it would
be cheaper overall to whack those to NULL in your process depends on your
cost of storage and the redo to whack it.
That is only a win if the overhead from the row migration is actually a
problem to you on the access side, and it treats your outlier bigger than
average clobs as noise.
Your details matter, and it might be possible to set pctfree, pctused, and
the size of the default CLOB so that a single row takes the block off the
free list and when its clob gets updated to the real value it only puts the
row back on the free list if it is likely another row will fit. (I'm not
saying 2K is the right size to do that. Your mileage will vary.
Another reasonable thing to do that comes to mind is having another table
altogether for the CLOB. Existing queries could be supported via a view.
Whether that would be an improvement over your current situation with many
migrated rows is open to question and measurement.
I could see using partitioning to solve this as well, where you let the
current "period" get built ugly and then create a nice clean image when
current becomes previous and use partition exchange to swap that back in for
the ugly version of the same data.
If you don't have partitioning, you could try some hybrid where you're
building new rows into two tables union-all'd with the permanent table and
cycle the rows into the permanent table periodically. That only works if
your operational schedule has the right opportunties or you're willing to do
the "poor man's partitioning" shell game with views and synonyms.
Or maybe someone else has some better ideas,
mwf
_____
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Neil Kodner
Sent: Wednesday, June 16, 2010 2:42 PM
To: oracle-l-freelists; Neil Kodner
Subject: Preparing a table to reorg
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:
