Message-Id: <10572.113284@fatcity.com> From: Chuck Hamilton Date: Fri, 28 Jul 2000 13:00:10 -0700 (PDT) Subject: Re: Why does a simple delete takes 12 hours and longer ? --0-1804289383-964814410=:20675 Content-Type: text/plain; charset=us-ascii Look at the explain plan. It's probably doing a full table scan on ojs_main for every row in ojs_main_multiple. Rewrite it like this and see if you don't get a better execution plan. delete from ojs_main_multiple a where exists (select 'x' from ojs_main where docnum = a.docnum) -or- delete /*+hash_aj(ojs_main) */ from ojs_main_multiple where docnum not in (select docnum from ojs_main); Another thing to check is whether or not you've got a delete trigger on ojs_main. Andreas Jung 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). --------------------------------- Do You Yahoo!? Kick off your party with Yahoo! Invites. --0-1804289383-964814410=:20675 Content-Type: text/html; charset=us-ascii

Look at the explain plan. It's probably doing a full table scan on ojs_main for every row in ojs_main_multiple. Rewrite it like this and see if you don't get a better execution plan.

delete from ojs_main_multiple a where exists (select 'x' from ojs_main where docnum = a.docnum)

-or-

delete /*+hash_aj(ojs_main) */ from ojs_main_multiple where docnum not in (select docnum from ojs_main);

Another thing to check is whether or not you've got a delete trigger on ojs_main.

  Andreas Jung <ajung@sz-sb.de> 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).


Do You Yahoo!?
Kick off your party with Yahoo! Invites.