Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Speed up Truncate tables

RE: Speed up Truncate tables

From: Grabowy, Chris <cgrabowy_at_fcg.com>
Date: Wed, 15 Aug 2001 13:10:25 -0700
Message-ID: <F001.0036C068.20010815132027@fatcity.com>

Don't
forget to rebuild your indexes...

<FONT face=Tahoma

  size=2>-----Original Message-----From: Mercadante, Thomas F   [mailto:NDATFM_at_labor.state.ny.us]Sent: Wednesday, August 15, 2001   4:35 PMTo: Multiple recipients of list ORACLE-LSubject:   RE: Speed up Truncate tables
<SPAN

  class=571542819-15082001>Cool!  I just tried it - works like a   charm!
   
  Tom Mercadante <FONT face=Arial
  size=2>Oracle Certified Professional   

    <FONT face=Tahoma
    size=2>-----Original Message-----From: Riyaj_Shamsudeen_at_i2.com     [mailto:Riyaj_Shamsudeen_at_i2.com]Sent: Wednesday, August 15, 2001     4:02 PMTo: Multiple recipients of list     ORACLE-LSubject: RE: Speed up Truncate     tablesNot true, at
    least in 8i. Using 'alter table move ' you could change the initial extent     size.. SQL>alter table test
    move storage (initial 2M); Table
    altered. Only catch here is that
    'move' command doesn't apply to tables having long ,long raw, LOB     etc..Initial extent can be changed for the remaining tables..     ThanksRiyaj "Re-yas"
    ShamsudeenCertified Oracle DBAi2 technologies       www.i2.com     

      
      
        
        Christopher Spence 
          <cspence_at_FuelSpot.com> <FONT face=sans-serif 
          size=1>Sent by: root_at_fatcity.com 
          08/15/01 02:22 PM <FONT 
          face=sans-serif size=1>Please respond to ORACLE-L 
                
                  
          To:        Multiple recipients of list ORACLE-L 
          <ORACLE-L_at_fatcity.com> <FONT face=sans-serif 
          size=1>        cc:       
                  
            Subject:        RE: Speed up Truncate 
          tables<FONT face="Courier New" 
    size=2>You are correct, unless your using 9i, you cannot alter the initial     extentwithout dropping the table."Do not criticize someone until     you walked a mile in their shoes, that waywhen you criticize them, you     are a mile a way and have their shoes."Christopher R. Spence     Oracle DBAPhone: (978) 322-5744Fax:    (707)     885-2275Fuelspot73 Princeton StreetNorth, Chelmsford     01863-----Original Message-----Sent: Wednesday,     August 15, 2001 3:07 PMTo: Multiple recipients of list     ORACLE-LThomas,are your sure you can change the initial     extent?  My senior dba told me onceit is not possible; you have to     drop and recreate table if there is a needto change Initial extent. I am     going to play with it today.-----Original Message-----Sent:     Wednesday, August 15, 2001 1:27 PMTo: Multiple recipients of list     ORACLE-LChuan,Kevin is correct.  If your truncate     table is taking a *long* time (and thetable is not locked by another     process), it's because your storage paramsare incorrect for the amount     of data you are holding.Look at initial and next in comparison with     the number of extents(DBA_EXTENTS view) for the table in question, and     modify them before youload the data. You can modify the INITIAL extent     by issuing an 'alter tableallocate extent(size x)' command to grow the     INITIAL extent.  You can also modify the NEXT extent by issuing an     'alter table storage (nextx)' command to change the NEXT     extent.hope this helps.Tom MercadanteOracle Certified     Professional-----Original Message-----Sent: Wednesday,     August 15, 2001 11:02 AMTo: Multiple recipients of list     ORACLE-LI had the same problem when truncating a huge table (24     Mill rows).   Itturned out that the reason my table was taking so     long was the amount ofextents I had on it.  I could look at what     was actually happening during atruncate and it had to go and take each     individual block and put them backin the available lists.      Well, after changing the settings on the table to make larger     extents (andtherefore fewer) the truncates on that table went hundreds     of times faster(we had real bad settings on that table     before).You might investigate your storage parms and see just how     many extents youdo have on that table.-----Original     Message-----Sent: Wednesday, August 15, 2001 3:10 AMTo: Multiple     recipients of list ORACLE-LHi All,   Is there     any way to speed up the truncating a big table with 12     millionrows?Basically, I implemented truncating that big table     on Production, but itaffected the performance much, so I had to stop it     in the middle of way. Allthe rows were truncated but the HWM was not     shrunk at all. I want to do itagain to get the space back. Is there any     way to speed up this process?Platform: Oracle EE8.0.6 and Solaris     2.7Thanks a lot in advance.Chuan-- Please see     the official ORACLE-L FAQ: http://www.orafaq.com-- Author: Chuan     Zhang INET: chuan_at_asiaonline.netFat City Network Services        -- (858) 538-5051  FAX: (858) 538-5051San Diego,     California        -- Public Internet access / Mailing
    Lists--------------------------------------------------------------------To 
    REMOVE yourself from this mailing list, send an E-Mail messageto:     ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in     themessage BODY, include a line containing: UNSUB ORACLE-L (or the name     ofmailing list you want to be removed from).  You may also send the     HELPcommand for other information (like subscribing).-- Please     see the official ORACLE-L FAQ: http://www.orafaq.com-- Author: Kevin     Lange INET: kgel_at_ppoone.comFat City Network Services        -- (858) 538-5051  FAX: (858) 538-5051San Diego, California            -- Public Internet access / Mailing
    Lists--------------------------------------------------------------------To 
    REMOVE yourself from this mailing list, send an E-Mail messageto:     ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in     themessage BODY, include a line containing: UNSUB ORACLE-L (or the name     ofmailing list you want to be removed from).  You may also send the     HELPcommand for other information (like subscribing).-- Please     see the official ORACLE-L FAQ: http://www.orafaq.com-- Author:     Mercadante, Thomas F INET: NDATFM_at_labor.state.ny.usFat City     Network Services    -- (858) 538-5051  FAX: (858)     538-5051San Diego, California        -- Public     Internet access / Mailing
    Lists--------------------------------------------------------------------To 
    REMOVE yourself from this mailing list, send an E-Mail messageto:     ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in     themessage BODY, include a line containing: UNSUB ORACLE-L (or the name     ofmailing list you want to be removed from).  You may also send the     HELPcommand for other information (like subscribing).-- Please     see the official ORACLE-L FAQ: http://www.orafaq.com-- <FONT     face="Courier New" size=2>Author:  INET:     lhoska_at_calibresys.comFat City Network Services    -- (858)     538-5051  FAX: (858) 538-5051San Diego, California            -- Public Internet access / Mailing
    Lists--------------------------------------------------------------------To 
    REMOVE yourself from this mailing list, send an E-Mail messageto:     ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in     themessage BODY, include a line containing: UNSUB ORACLE-L (or the name     ofmailing list you want to be removed from).  You may also send the     HELPcommand for other information (like subscribing).-- Please     see the official ORACLE-L FAQ: http://www.orafaq.com-- Author:     Christopher Spence INET: cspence_at_FuelSpot.comFat City     Network Services    -- (858) 538-5051  FAX: (858)     538-5051San Diego, California        -- Public     Internet access / Mailing
    Lists--------------------------------------------------------------------To 
    REMOVE yourself from this mailing list, send an E-Mail messageto:     ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and inthe     message BODY, include a line containing: UNSUB ORACLE-L(or the name of     mailing list you want to be removed from).  You mayalso send the     HELP command for other information (like   subscribing). Received on Wed Aug 15 2001 - 15:10:25 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US