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

Hi!

THanks anderas for the reply!

I am one of those pre oracle8 souls and
with great effort I am trying to unlearn Oracle 7 solutions.. and
imagine my shock when I find that finally only pre Oracle 7 (not even pre Oracle 8) solutions work!!!!!

However, I plough on... looking for whether the Oracle 8.+ solutions actually work or whether it is a lacuna in my understanding.!!!!!!

Did you try the HASH_JOIN,ALWAYS_ANTI etc..?

I 'dread' using that...
cos manual says that hash_join brings data into PGA and I definitely can't afford all my processes having 'large PGA'!!!

Hellow gurus.. can someone please confirm my understanding!!! I personally am not convinced why HASH_JOIN,MERGE_JOIN would (or even should!) work better than NESTED_LOOPS

Finally, with so many of us 'disowning' Oracle, I wish we could 'invite' someone to 'hold a brief' for Oracle..

I find sometimes .. frankly.. it is easier to disown Oracle than to 'actually hold a brief for Oracle'!!!!!!!

Thanks for all

------Original Message------
From: Andreas Jung <ajung_at_sz-sb.de>
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: July 30, 2000 9:04:21 PM GMT
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).


______________________________________________