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

From: Cyril Thankappan <cyril_thank_at_india.com>
Date: Sun, 30 Jul 2000 13:52:54 -0400 (EDT)
Message-Id: <10574.113318@fatcity.com>

Hi!

sorry if I am joining in later after the solution.

But the
following query

delete from ojs_main_multiple where docnum not in (select docnum from ojs_main);
>

won't use indexes on objs_main_multiple.

Can we re-write the query to replace "not in" with a in clause or use a outerjoin
and specify the primary key (docnum) in objs_main as null?

Rajagopal's idea is very good, but I really want to know how to disable archivelog
only for this table during delete!

Can you please let me know , how the query was finally executed?

Thanks
------Original Message------
From: Rajagopal Venkataramany <rajagopalvr_at_excite.com> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: July 30, 2000 5:36:42 PM GMT
Subject: 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

Say Bye to Slow Internet!
http://www.home.com/xinbox/signup.html

--
Author: Rajagopal Venkataramany
INET: rajagopalvr_at_excite.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 Sun Jul 30 2000 - 12:52:54 CDT