From Jim.Conboy@trw.com Thu, 28 Jun 2001 09:29:01 -0700 From: "Jim Conboy" Date: Thu, 28 Jun 2001 09:29:01 -0700 Subject: Re: Speed up massive delete Message-ID: MIME-Version: 1.0 Content-Type: text/plain 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@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):      
•If I have my table as set to nologging, will my DML still show up in the redo logs?
•Yes it will, which options the nologging option has an effect on
 
•Only the following operations can make use of the NOLOGGING option:
• alter table...move partition
• alter table...split partition
• alter index...split partition
• alter index...rebuild
• alter index...rebuild partition  •create table...as select
• create index
• direct load with SQL*Loader
• direct load INSERT
    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.
 
joe
      >>> MHately@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" ||        |             ||        |                       ||        |          06/28/01     ||        |          02:51 PM     ||        |          Please       ||        |          respond to   ||        |          ORACLE-L     ||        |                       ||--------+----------------------->  >-------------------------------------------------------------------|  |                                                                   |  |       To:     Multiple recipients of list ORACLE-L                |  |                                             |  |       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@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: http://www.orafaq.com--Author:  INET: nlzanen1@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@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: http://www.orafaq.com--Author: Ron Rogers  INET: RROGERS@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@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: http://www.orafaq.com--Author:  INET: MHately@etech-uk.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@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).