Re: Speed up massive delete

From: Jim Conboy <Jim.Conboy_at_trw.com>
Date: Thu, 28 Jun 2001 09:29:01 -0700
Message-ID: <F001.0033C135.20010628082825@fatcity.com>

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 o<SPAN 
style="FONT-SIZE: 133%">n

<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     
||        
|                       
||--------+----------------------->  
>-------------------------------------------------------------------|  
|

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 -- (858) 538-5051 FAX: (858) 538-5051San Diego, California -- Public Internet access / Mailing

Lists--------------------------------------------------------------------To