| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Why does a simple delete takes 12 hours and longer ?
Hi!
Thanks rajagopal,
Hope 'someday' Oracle comes up with
toggling between archivelog and noarchivelog
at object level.
The fact that in a 'limited' way they have implemented it, makes me hopeful.
Thanks
------Original Message------
From: Rajagopal Venkataramany <rajagopalvr_at_excite.com>
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
Sent: August 1, 2000 4:05:42 AM GMT
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_at_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 <ORACLE-L_at_fatcity.com>
> 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_at_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_at_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_at_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_at_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_at_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_at_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_at_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
-- Author: Rajagopal Venkataramany INET: rajagopalvr_at_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_at_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 mayReceived on Tue Aug 01 2000 - 00:37:36 CDT
 |
 |