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

From: Jeffery Stevenson <jeff_at_mpv.com>
Date: Fri, 28 Jul 2000 15:14:48 -0500
Message-Id: <10572.113285@fatcity.com>

possible cartesian join situation in the select?????

-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of William_Dong_at_gelco.com
Sent: Friday, July 28, 2000 3:19 PM
To: Multiple recipients of list ORACLE-L Subject: RE: 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 "?

I think this is a interesting case. Had i have the tables, I will try rewrite
the sql and test it. Can you try the following sql to see if it works better?

DELETE FROM ojs_main_multiple

                  WHERE docnum IN
                                                        (select omm.docnum

from
ojs_main_multiple omm, ojs_main om

                                                          where omm.docnum

<>om.docnum);

Your db should use cost insted of rule to run this. See what happens.

--
Author:
  INET: William_Dong_at_gelco.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).

Received on Fri Jul 28 2000 - 15:14:48 CDT