But in order to alter the table and rebuild it, i'd have to take it offline, which wont work in our 24x7 shop. The problem is I have no real way of estimating how long an offline rebuild would in fact take. Dropping a column took about 4.5 hours. On Thu, Jun 17, 2010 at 9:43 AM, Ian Cary <ian.cary@xxxxxxxxxxxxxx> wrote: > Wouldn't storing the CLOB out of line be what you are looking for. You > could do this with a simple alter table command e.g. > > alter table your_table move lob(your_clob_column) store as (tablespace > your_lob_tablespace); > > This statement will move the lob and the lob index to a separate tablespace > but will also rebuild the table itself. > > This should give the same effect as moving the clob to a separate table but > without much effort or view requirement. > > Cheers, > > Ian > > > > > nkodner@xxxxxxxxx > Sent by: To: mwf@xxxxxxxx > oracle-l-bounce@fr cc: > oracle-l@xxxxxxxxxxxxx > eelists.org Subject: Re: Preparing a > table to reorg > > > 17/06/2010 11:40 > Please respond to > nkodner > > > > > > > Out of your ideas, I like the default 2k(or so) best. I've approached the > devs with the idea of moving the CLOB to a separate table but they say it > can't be done without a lot of effort. They also dont want me to 'fake it' > behind the scenes with views, etc. > > Inserting a default clob makes sense though. > > On Wed, Jun 16, 2010 at 3:54 PM, Mark W. Farnham <mwf@xxxxxxxx> wrote: > 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? > > > > > > > > > > > This email was received from the INTERNET and scanned by the Government > Secure Intranet anti-virus service supplied by Cable&Wireless Worldwide in > partnership with MessageLabs. (CCTM Certificate Number 2009/09/0052.) In > case of problems, please call your organisation?x02019;s IT Helpdesk. > Communications via the GSi may be automatically logged, monitored and/or > recorded for legal purposes. > > For the latest data on the economy and society consult National Statistics > at http://www.ons.gov.uk > > > ********************************************************************************* > > > Please Note: Incoming and outgoing email messages are routinely monitored > for compliance with our policy on the use of electronic communications > > ********************************************************************************* > > > Legal Disclaimer : Any views expressed by the sender of this message are > not necessarily those of the Office for National Statistics > > ********************************************************************************* > > > The original of this email was scanned for viruses by the Government Secure > Intranet virus scanning service supplied by Cable&Wireless Worldwide in > partnership with MessageLabs. (CCTM Certificate Number 2009/09/0052.) On > leaving the GSi this email was certified virus free. > Communications via the GSi may be automatically logged, monitored and/or > recorded for legal purposes. >