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. Received on Sun Jul 30 2000 - 14:50:45 CDT