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

I am sorry to say that there are no such things as stupid databases

feel free to complete the sentence.

Oracle can be complicated at times and even infuriating but 99.9% of the time it does exactly what it says it will.

Phil West
Unix Sys Admin and Oracle Financials DBA +44 (0) 20 7623 5222 x5221

-----Original Message-----
From: Andreas Jung [mailto:ajung_at_sz-sb.de] Sent: 30 July 2000 22:04
To: Multiple recipients of list ORACLE-L Subject: Re: Why does a simple delete takes 12 hours and longer ?

On Sun, Jul 30, 2000 at 09:36:42AM -0800, Rajagopal Venkataramany wrote:
> 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...

Well meanwhile I wrote a small application that fetches the docnums to be deleted and executes a single delete on every row. This takes within 6 oder 7 minutes .

Sometimes I ask myself why Oracle is market leader when I see such a stupid behaviour of a database.

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


*******************************************************************************
The information in this Internet e-mail is confidential and may be legally privileged.  It is intended solely for the addressee.  Access to this Internet e-mail by anyone else is unauthorised and any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it, is prohibited and may be unlawful. 
 When addressed to our clients any opinions or advice contained in this Internet e-mail are subject to the terms and conditions expressed in any applicable documentation or market practices governing the relationship between Garban Intercapital plc and its clients.

Any views expressed in this message are those of the individual sender except where they are stated to be the views of Garban Intercapital plc.