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 massive delete

RE: Speed up massive delete

From: Henry Poras <Henry.Poras_at_ctp.com>
Date: Thu, 28 Jun 2001 09:44:00 -0700
Message-ID: <F001.0033C308.20010628091043@fatcity.com> 







Wow, I didn't know you could 


write e-mail with lemon juice. It happened to me too.
 


Henry



  -----Original 


  Message-----From: Jim Conboy 


  [mailto:Jim.Conboy_at_trw.com]Sent: Thursday, June 28, 2001 12:28 
  PMTo: Multiple recipients of list ORACLE-LSubject: Re: 
  Speed up massive delete


  Not sure how this message looked to 


  everybody on the list.  When I rec'd it, all I could see was the ALTER 
  statements.  But I know that the create statements and direct loads work 
  also.  When I went to reply as such, lo and behold the missing 
  create/direct load statements appeared as they should.  Just wanted to 
  point that out, because Joe as provided the definitive answer, but only if you 
  can read his entire response!  Weird.


   


  Jim


  >>> JTESTA_at_longaberger.com 06/28/01 11:06AM 
  >>>


  ok lets cover this again on unrecoverable/nologging(yes i've 
  done the research and demo it in my logminer presentation):

   
   
    
  




  <DIV 


  style="mso-line-spacing: '100 20 0'; mso-margin-left-alt: 216; mso-char-wrap: 1; mso-kinsoku-overflow: 1"><SPAN 
  style="FONT-SIZE: 133%"><SPAN 
  style="LEFT: -3.65%; POSITION: absolute; mso-special-format: bullet">&#8226;<SPAN 
  style="FONT-SIZE: 133%">If I have my table as set to nologging, will my 


  DML still show up in the redo logs? 
  


  <DIV 


  style="mso-line-spacing: '100 20 0'; mso-margin-left-alt: 216; mso-char-wrap: 1; mso-kinsoku-overflow: 1"><SPAN 
  style="LEFT: -3.55%; POSITION: absolute; mso-special-format: bullet">&#8226;<SPAN 
  style="FONT-SIZE: 133%">Yes it will, which <SPAN 
  style="FONT-SIZE: 133%">options the nologging option has an effect 


  on 


  <DIV 



  style="mso-line-spacing: '100 20 0'; mso-margin-left-alt: 216; mso-char-wrap: 1; mso-kinsoku-overflow: 1"><SPAN 
  style="FONT-SIZE: 133%"> 


  <DIV 



  style="mso-line-spacing: '100 20 0'; mso-margin-left-alt: 216; mso-char-wrap: 1; mso-kinsoku-overflow: 1"><SPAN 
  style="FONT-SIZE: 133%">
  


  <DIV 


  style="mso-line-spacing: '100 20 0'; mso-margin-left-alt: 216; mso-char-wrap: 1; mso-kinsoku-overflow: 1"><SPAN 
  style="FONT-SIZE: 133%"><SPAN 
  style="LEFT: -3.33%; POSITION: absolute; mso-special-format: bullet">&#8226;<SPAN 
  style="FONT-SIZE: 133%">Only the following operations can make use of the 


  NOLOGGING option: 


  <DIV 


  style="mso-line-spacing: '100 20 0'; mso-margin-left-alt: 216; mso-char-wrap: 1; mso-kinsoku-overflow: 1"><SPAN 
  style="FONT-SIZE: 133%"><SPAN 
  style="LEFT: -3.96%; POSITION: absolute; mso-special-format: bullet">&#8226;<SPAN 
  style="FONT-SIZE: 133%"> alter 




  table...move partition 


  <DIV 


  style="mso-line-spacing: '100 20 0'; mso-margin-left-alt: 216; mso-char-wrap: 1; mso-kinsoku-overflow: 1"><SPAN 
  style="FONT-SIZE: 133%"><SPAN 
  style="LEFT: -3.96%; POSITION: absolute; mso-special-format: bullet">&#8226;<SPAN 
  style="FONT-SIZE: 133%"> alter 




  table...split partition 


  <DIV 


  style="mso-line-spacing: '100 20 0'; mso-margin-left-alt: 216; mso-char-wrap: 1; mso-kinsoku-overflow: 1"><SPAN 
  style="FONT-SIZE: 133%"><SPAN 
  style="LEFT: -3.96%; POSITION: absolute; mso-special-format: bullet">&#8226;<SPAN 
  style="FONT-SIZE: 133%"> alter 




  index...split partition 


  <DIV 


  style="mso-line-spacing: '100 20 0'; mso-margin-left-alt: 216; mso-char-wrap: 1; mso-kinsoku-overflow: 1"><SPAN 
  style="FONT-SIZE: 133%"><SPAN 
  style="LEFT: -3.96%; POSITION: absolute; mso-special-format: bullet">&#8226;<SPAN 
  style="FONT-SIZE: 133%"> alter 




  index...rebuild 


  <DIV 


  style="mso-line-spacing: '100 20 0'; mso-margin-left-alt: 216; mso-char-wrap: 1; mso-kinsoku-overflow: 1"><SPAN 
  style="FONT-SIZE: 133%"><SPAN 
  style="LEFT: -3.96%; POSITION: absolute; mso-special-format: bullet">&#8226;<SPAN 
  style="FONT-SIZE: 133%"> alter 




  index...rebuild partition 


  <SPAN 


  style="DISPLAY: none; mso-special-format: lastCR"> <SPAN 
  style="FONT-SIZE: 133%"><SPAN 
  style="LEFT: -5.19%; POSITION: absolute; mso-special-format: bullet">&#8226;<SPAN 
  style="FONT-SIZE: 133%">create table...as select 
  




  <DIV 


  style="mso-line-spacing: '100 20 0'; mso-margin-left-alt: 216; mso-char-wrap: 1; mso-kinsoku-overflow: 1"><SPAN 
  style="FONT-SIZE: 133%"><SPAN 
  style="LEFT: -5.19%; POSITION: absolute; mso-special-format: bullet">&#8226;<SPAN 
  style="FONT-SIZE: 133%"> create 




  index 


  <DIV 


  style="mso-line-spacing: '100 20 0'; mso-margin-left-alt: 216; mso-char-wrap: 1; mso-kinsoku-overflow: 1"><SPAN 
  style="FONT-SIZE: 133%"><SPAN 
  style="LEFT: -5.19%; POSITION: absolute; mso-special-format: bullet">&#8226;<SPAN 
  style="FONT-SIZE: 133%"> direct 




  load with SQL*Loader 


  <DIV 


  style="mso-line-spacing: '100 20 0'; mso-margin-left-alt: 216; mso-char-wrap: 1; mso-kinsoku-overflow: 1"><SPAN 
  style="LEFT: -5.19%; POSITION: absolute; mso-special-format: bullet">&#8226;<SPAN 
  style="FONT-SIZE: 133%"> direct 




  load INSERT 


  <DIV 



  style="mso-line-spacing: '100 20 0'; mso-margin-left-alt: 216; mso-char-wrap: 1; mso-kinsoku-overflow: 1"><SPAN 
  style="FONT-SIZE: 133%"> 


  <SPAN 



  style="DISPLAY: none; mso-special-format: lastCR">  


  <FONT 



  size=+0>Other SQL 


  statements (such as UPDATE, DELETE, conventional path INSERT, and various DDL 
  statements not listed above) are 


  unaffected by the NOLOGGING attribute of the schema object, so yes you DML 
  will appear in the redo/archive logs. 


  <SPAN 



  style="DISPLAY: none; mso-special-format: lastCR">


  <DIV 



  style="mso-line-spacing: '100 20 0'; mso-margin-left-alt: 216; mso-char-wrap: 1; mso-kinsoku-overflow: 1"><SPAN 
  style="FONT-SIZE: 133%"> 


  <DIV 



  style="mso-line-spacing: '100 20 0'; mso-margin-left-alt: 216; mso-char-wrap: 1; mso-kinsoku-overflow: 1"><SPAN 
  style="FONT-SIZE: 133%">joe


  <DIV 



  style="mso-line-spacing: '100 20 0'; mso-margin-left-alt: 216; mso-char-wrap: 1; mso-kinsoku-overflow: 1"><SPAN 
  style="FONT-SIZE: 133%"> 


   


   


  >>> MHately_at_etech-uk.com 06/28/01 10:24AM 
  >>>Ron,How does creating indexes as UNRECOVERABLE 
  save time when you insert data?As far as I'm aware the only operation it 
  will save time on is the 

  initialcreation.Regards,Mike|--------+----------------------->|        
  |          "Ron Rogers" 




  ||        


  |          


  <RROGERS_at_galo||        



  |          


  ttery.org>   ||        

  |                       
  ||        
  |          
  06/28/01     
  ||        
  |          02:51 
  PM     ||        
  |          
  Please       
  ||        
  |          respond to   
  ||        
  |          
  ORACLE-L     
  ||        
  |                       
  ||--------+----------------------->  
  >-------------------------------------------------------------------|  
  |                                                                   


  |  |       To:     


  Multiple recipients of list 


  ORACLE-L                



  |  |       


  <ORACLE-L_at_fatcity.com>                                      

  |  |       cc:     


  (bcc: Mike 

  Hately/ETECH)                            
  |  |       




  Subject:     Re: Speed up massive 

  delete                    




  |  

  >-------------------------------------------------------------------|JackDrop 


  the indexes first before you do the deletes will save you time by 
  notcreating logs for the indexes being deleted. On my large activity files 
  I havecreated the indexes as "Unrecoverable" saving time when I add data 
  or deletedata.ROR mªÖªm>>> nlzanen1_at_EY.NL 06/28/01 
  05:45AM >>>Hi All,I have to do some pretty big 
  deletes on a test environment and they areestimated to take for 
  ever.Are there any options to speed it 

  up?Jack=====================================================================De 


  informatie verzonden in dit e-mailbericht is vertrouwelijk en 
  isuitsluitend bestemd voor de geadresseerde. 
  Openbaarmaking,vermenigvuldiging, verspreiding en/of verstrekking van deze 
  informatie aanderden is, behoudens voorafgaande schriftelijke toestemming 
  van Ernst &Young, niet toegestaan. Ernst & Young staat niet in 
  voor de juiste envolledige overbrenging van de inhoud van een verzonden 
  e-mailbericht, nochvoor tijdige ontvangst daarvan. Ernst & Young kan 
  niet garanderen dat eenverzonden e-mailbericht vrij is van virussen, noch 
  dat e-mailberichtenworden overgebracht zonder inbreuk of tussenkomst van 
  onbevoegde derden.Indien bovenstaand e-mailbericht niet aan u is 
  gericht, verzoeken wij uvriendelijk doch dringend het e-mailbericht te 
  retourneren aan de verzenderen het origineel en eventuele kopieën te 
  verwijderen en te vernietigen.Ernst & Young hanteert bij de 
  uitoefening van haar werkzaamheden algemenevoorwaarden, waarin een 
  beperking van aansprakelijkheid is opgenomen. Dealgemene voorwaarden 
  worden u op verzoek kosteloos 

  toegezonden.=====================================================================The 


  information contained in this communication is confidential and isintended 
  solely for the use of the individual or entity to whom it isaddressed. You 
  should not copy, disclose or distribute this communicationwithout the 
  authority of Ernst & Young. Ernst & Young is neither liable forthe 
  proper and complete transmission of the information contained in 
  thiscommunication nor for any delay in its receipt. Ernst & Young does 
  notguarantee that the integrity of this communication has been maintained 
  northat the communication is free of viruses, interceptions or 
  interference.If you are not the intended recipient of this 
  communication please returnthe communication to the sender and delete and 
  destroy all copies.In carrying out its engagements, Ernst & Young 
  applies general terms andconditions, which contain a clause that limits 
  its liability. A copy ofthese terms and conditions is available on request 
  free of 

  charge.=====================================================================--Please 


  see the official ORACLE-L FAQ: <A 


  href="http://www.orafaq.com">http://www.orafaq.com--Author:  
  INET: nlzanen1_at_EY.NLFat 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).--Please see the official 
  ORACLE-L FAQ: <A 


  href="http://www.orafaq.com">http://www.orafaq.com--Author: Ron 
  Rogers  INET: RROGERS_at_galottery.orgFat 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).--Please see 
  the official ORACLE-L FAQ: <A 


  href="http://www.orafaq.com">http://www.orafaq.com--Author:  
  INET: MHately_at_etech-uk.comFat City Network Services    


Received on Thu Jun 28 2001 - 11:44:00 CDT












Original text of this message













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