| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: table reorganizations
Rachel,
Thanks for the idea but the system is running 8i. I'll remember it for the future.
Chris
Quoting Rachel Carmichael <wisernet100_at_yahoo.com>:
> Chris,
>
> Have you considered using dbms_redefinition for your second case? That
> would allow you to reorg and swap the tables without locking for any
> length of time.
>
> Rachel
>
>
> --- chris_at_thedunscombes.f2s.com wrote:
> > Richard,
> >
> > I agree there are a number of reasons for reorganising tables. LMTs
> > remove the
> > need to reorganise a tablespace but not to reorganise a table. Two
> > further real-
> > ilfe examples of table reorgs:
> >
> > 1) The purge programs have at last been written and run deleting data
> > > 2 years
> > old. The system's been running for 4 years. So in simple terms most
> > of the
> > tables are approx 50% empty. You need to reorg in this case.
> >
> > 2) A "transaction log" table is inserted to throughout the day and
> > most of the
> > night. A clear down processing job runs at the end of the day and
> > deletes all
> > the rows its processed, but more rows are being added. So the table
> > is now < 1%
> > full. Not good for FTS. So instead of a conventional reorg we
> > implemented a
> > nightly "table-swap". This meant locking the source table, copying
> > it's
> > contents to a replica empty single extent table, target table. The
> > names of the
> > target and source tables are swapped, hence "table-swap". The new
> > source table
> > is now available to the application and the original source is
> > truncated and
> > ready to be the target in 24 hrs time.
> >
> > Cheers,
> >
> > Chris Dunscombe
> >
> >
> >
> > Quoting Richard Foote <richard.foote_at_bigpond.com>:
> >
> > > MessageHi Thomas,
> > >
> > > Never say never (oh bugger, I've just gone and done it myself).
> > >
> > > A large table accessed via a FTS for various important reporting
> > requirements
> > > has permanently shrunk in size from 10G to 100M (say list of
> > Informix
> > > customers ;)
> > >
> > > Business requirements have changed and you need to add some columns
> > to a
> > > table resulting in mucho row migration.
> > >
> > > You were told (incorrectly) that rows would grow significantly
> > after loading
> > > (honestly) but now the 80 pctfree value you've set is causing
> > problems for
> > > other really important reports.
> > >
> > > There are of course other cases but you get my point ;)
> > >
> > > Cheers
> > >
> > > Richard
> > > ----- Original Message -----
> > > From: Mercadante, Thomas F
> > > To: Multiple recipients of list ORACLE-L
> > > Sent: Thursday, January 08, 2004 6:34 AM
> > > Subject: RE: table reorganizations
> > >
> > >
> > > Jolene,
> > >
> > > Tables should never *need* to be reorganized. This is an old
> > falacy. If
> > > you know how big a table is going to grow, say in a year, then
> > place it in a
> > > Locally Managed tablespace with extent sizes to hold enough data
> > for one year
> > > (say 1M).
> > >
> > > You should never have to reorganize a table.
> > >
> > > Tom Mercadante
> > > Oracle Certified Professional
> > > -----Original Message-----
> > > From: Shrake, Jolene [mailto:JEShrake_at_Pella.com]
> > > Sent: Wednesday, January 07, 2004 2:39 PM
> > > To: Multiple recipients of list ORACLE-L
> > > Subject: table reorganizations
> > >
> > >
> > > What SQL statement do you use to identify tables that need
> > > reorganization?
> > >
> > > How do you identify tables that are used in full table scans?
> > How often
> > > do you run this query?
> > >
> > > Thanks,
> > > Jolene
> > >
> >
> >
> > Chris Dunscombe
> >
> > chris_at_thedunscombes.f2s.com
> >
> > -------------------------------------------------
> > Everyone should have http://www.freedom2surf.net/
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author:
> > INET: chris_at_thedunscombes.f2s.com
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting services
> > ---------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
>
>
> __________________________________
> Do you Yahoo!?
> Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes
> http://hotjobs.sweepstakes.yahoo.com/signingbonus
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Rachel Carmichael
> INET: wisernet100_at_yahoo.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
Chris Dunscombe
chris_at_thedunscombes.f2s.com
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: chris_at_thedunscombes.f2s.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Mon Jan 12 2004 - 03:59:33 CST
 |
 |