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

Well, if everything was initialized in the beginning as far as the init.ora file goes, you're probably doing a Nested Loop anti-join. I'd suggest inserting ALWAYS_ANTI_JOIN=HASH (or MERGE)

Just a little tip from your Uncle Lyall!

Of course to do this.... do you need a bounce, or can you do ALTER SESSION.... I think you can do alter session. I know you can use a hint in your delete /*+ HASH_AJ */

HTH
Lyall

------Original Message------
From: Andreas Jung <ajung_at_sz-sb.de>
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: July 28, 2000 9:05:58 AM GMT
Subject: Why does a simple delete takes 12 hours and longer ?

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


______________________________________________
FREE Personalized Email at Mail.com