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 - rebuild index

Re: Speed up Truncate tables - rebuild index

From: Igor Neyman <ineyman_at_perceptron.com>
Date: Thu, 16 Aug 2001 08:48:48 -0700
Message-ID: <F001.0036D4E0.20010816085750@fatcity.com>

Lisa,
 

I think Chris referred to 'alter table ... move', in which case you need to rebuild indexes (ROWIDs changed).
 

Igor Neyman, OCP DBAPerceptron, Inc.(734)414-4627<A href="mailto:ineyman_at_perceptron.com">ineyman_at_perceptron.com 

<BLOCKQUOTE
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">

  Sent: Thursday, August 16, 2001 12:20   PM
  Subject: RE: Speed up Truncate tables -   rebuild index   

  Actually Chris if you truncate a
  table, the indexes on the table are truncated right along with it.  You   don't end up with a sparse index, like when you delete many rows.  Try   it.  I did (8.1.6)  and was very happy to see this behavior.    

  Lisa Koivu Oracle Database
  Administrator Fairfield Resorts, Inc.   954-935-4117   

    -----Original Message----- <FONT

    face=Arial size=1>From:   <FONT face=Arial 
    size=1>Grabowy, Chris [SMTP:cgrabowy_at_fcg.com] <FONT face=Arial 
    size=1>Sent:   Wednesday, 

    August 15, 2001 5:20 PM <FONT face=Arial
    size=1>To:     <FONT face=Arial 
    size=1>Multiple recipients of list ORACLE-L <FONT face=Arial 
    size=1>Subject:        <FONT 
    face=Arial size=1>RE: Speed up Truncate tables 
    Don't forget to rebuild your
    indexes...     
      -----Original Message-----<FONT 
      face=Tahoma size=2>From: Mercadante, 
      Thomas F [<A 
      href="mailto:NDATFM_at_labor.state.ny.us">mailto:NDATFM_at_labor.state.ny.us]<FONT 
      face=Tahoma size=2>Sent: Wednesday, 
      August 15, 2001 4:35 PM<FONT face=Tahoma 
      size=2>To: Multiple recipients of list 
      ORACLE-LSubject:<FONT 
      face=Tahoma size=2> RE: Speed up Truncate tables<FONT 
      face=Arial color=#0000ff size=2>Cool!  I just tried it - works like a 
      charm!   
      Tom Mercadante<FONT 
      face=Arial>Oracle Certified 
      Professional 
      
        -----Original 
        Message-----From:<FONT 
        face=Tahoma size=2> Riyaj_Shamsudeen_at_i2.com [<A 
        href="mailto:Riyaj_Shamsudeen_at_i2.com">mailto:Riyaj_Shamsudeen_at_i2.com]<FONT 
        face=Tahoma size=2>Sent: Wednesday, 
        August 15, 2001 4:02 PM<FONT face=Tahoma 
        size=2>To: Multiple recipients of 
        list ORACLE-L<FONT face=Tahoma 
        size=2>Subject: RE: Speed up 
        Truncate tables
        Not true, at least in 8i. Using 'alter 
        table move ' you could change the initial extent size..<FONT 
        face=Arial>SQL>alter 
        table test move storage (initial 2M);<FONT 
        face=Arial>Table 
        altered.<FONT face=sans-serif 
        size=2>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..<FONT 
        face=sans-serif size=2>ThanksRiyaj "Re-yas" ShamsudeenCertified 
        Oracle DBAi2 technologies   www.i2.com<FONT 
        face=Arial>

            <FONT
    face=sans-serif size=1>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=Arial>Please respond to
    ORACLE-L
           <FONT
    face=sans-serif size=1>        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        

      
        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. SpenceOracle 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: 
        <A target=_blank 
        href="http://www.orafaq.com">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: <A 
        target=_blank 
        href="http://www.orafaq.com">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: <A 
        target=_blank 
        href="http://www.orafaq.com">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: <A 
        target=_blank 
        href="http://www.orafaq.com">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: <A 
        target=_blank 
        href="http://www.orafaq.com">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 Thu Aug 16 2001 - 10:48:48 CDT

Original text of this message

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