Message-Id: <10575.113442@fatcity.com> From: Rajagopal Venkataramany Date: Mon, 31 Jul 2000 19:58:57 -0700 (PDT) Subject: Re: Why does a simple delete takes 12 hours and longer ? Cyril, What I meant about disabling archival is for the db instance. I did mention that that if this approach is used, we have to take a db backup before we proceed... Hope this answers your question... Regards Rajagopal Venkataramany On Sun, 30 Jul 2000 11:05:14 -0800, ORACLE-L@fatcity.com wrote: > Hi! > > sorry if I am joining in later after the solution. > > But the > following query > > delete from ojs_main_multiple where docnum not in (select docnum from > ojs_main); > > > > won't use indexes on objs_main_multiple. > > Can we re-write the query to replace "not in" > with a in clause or use a outerjoin > and specify the primary key (docnum) in objs_main > as null? > > Rajagopal's idea is very good, but I really > want to know how to disable archivelog > only for this table during delete! > > Can you please let me know , how the query > was finally executed? > > Thanks > ------Original Message------ > To: Multiple recipients of list ORACLE-L > Sent: July 30, 2000 5:36:42 PM GMT > > > Andreas, > > The optimizer translates IN into OR condition during parsing. If > the OR list expands to along list, the OPTIMIZER decides to go > for a FULL TABLE scan. > > Looking at the table volume, the database takes considerable > amount of time to write the detail into the redo files also. > Since the number of rows that can be deleted is significantly > high, oracle needs a sufficient rollback.. > > It is advisable to ensure that such jobs are designed to affect > limited set of rows at a time and probably it can be in a "loop" > till the entire operation is completed. > > ** Can we not schedule this job in database stand-alone, disable > archival and run the job. (A backup is needed before u start the > operation) > > Alternatively, > Convert the delete into a SELECT operation and generate a SQL > stmt as the output which would be like "delete from > ojs_main_multiple where rowid = ....', generated for every single > row that matches the selection criteria. (use set pages 0; set > echo on) > > Since this is a SELECT operation, u would not have other overheads. > Spool the details to a file. Depending on the volume of the details > the file can be split into multiple jobs which can be scheduled > independently with a periodic "commit". Again the chances of > the entire operation at any point of time is very less and if it > happens, u have to fire that last job which failed... > > Let me know if this works for u... > > Regards > Rajagopal Venkataramany > > On Fri, 28 Jul 2000 01:05:59 -0800, ORACLE-L@fatcity.com wrote: > > > > > Yesterday evening I started the following delete: > > > > delete from ojs_main_multiple where docnum not in (select docnum from > ojs_main); > > > > ojs_main contains about 250.000 rows and ojs_main_multiple about 300.000 > rows. > > docnum is a unique primary key of ojs_main. This morning after about 12 > hours > > the delete was still running. Any idea why this happens ? Should I > believe > > in "In Oracle we trust "? > > > > (our env.: Oracle 8i EE/Solaris on a Sun E450 with 4 CPUs) > > > > Andreas > > > > -- > > Author: Andreas Jung > > INET: ajung@sz-sb.de > > > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > > San Diego, California -- Public Internet access / Mailing Lists > > -------------------------------------------------------------------- > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like subscribing). > > > Regards > Rajagopal Venkataramany > > > > > > _______________________________________________________ > Say Bye to Slow Internet! > http://www.home.com/xinbox/signup.html > > -- > Author: Rajagopal Venkataramany > INET: rajagopalvr@excite.com > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California -- Public Internet access / Mailing Lists > -------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > > > ______________________________________________ > FREE Personalized Email at Mail.com > Sign up at http://www.mail.com/?sr=signup > > -- > Author: Cyril Thankappan > INET: cyril_thank@india.com > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California -- Public Internet access / Mailing Lists > -------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). Regards