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 <ajung_at_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_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).


---------------------------------
Do You Yahoo!?
Kick off your party with Yahoo! Invites.
--0-1804289383-964814410=:20675
Content-Type: text/html; charset=us-ascii



<P> 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.</P>

<P>delete from ojs_main_multiple a where exists (select 'x' from ojs_main where docnum = a.docnum)</P>

<P>-or-</P>

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

<P>Another thing to check is whether or not you've got a delete trigger on ojs_main.<BR><BR></P>

<P>&nbsp; <B><I>Andreas Jung &lt;ajung_at_sz-sb.de&gt;</I></B> wrote: <BR>

<BLOCKQUOTE style="BORDER-LEFT: #1010ff 2px solid; MARGIN-LEFT: 5px; PADDING-LEFT: 5px"><BR>Yesterday evening I started the following delete:<BR><BR>delete from ojs_main_multiple where docnum not in (select docnum from ojs_main);<BR><BR>ojs_main contains about 250.000 rows and ojs_main_multiple about 300.000 rows.<BR>docnum is a unique primary key of ojs_main. This morning after about 12 hours<BR>the delete was still running. Any idea why this happens ? Should I believe<BR>in "In Oracle we trust "?<BR><BR>(our env.: Oracle 8i EE/Solaris on a Sun E450 with 4 CPUs)<BR><BR>Andreas<BR><BR>-- <BR>Author: Andreas Jung<BR>INET: ajung_at_sz-sb.de<BR><BR>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051<BR>San Diego, California -- Public Internet access / Mailing Lists<BR>--------------------------------------------------------------------<BR>To REMOVE yourself from this mailing list, send an E-Mail message<BR>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and !

in<BR>the message BODY, include a line containing: UNSUB ORACLE-L<BR>(or the name of mailing list you want to be removed from). You may<BR>also send the HELP command for other information (like subscribing).</BLOCKQUOTE><p><br><hr size=1><b>Do You Yahoo!?</b><br>
Kick off your party with Yahoo! Invites.