Re: Why does a simple delete takes 12 hours and longer ?

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