RE: Deleting from a huge table

you could also take a look at the PCTUSED of the table, maybe it's set too high !

you could also try inserting the ROWID's of the rows to be deleted in another table and then use delete where rowid in ...

> ----------
> From: Surya Rao[SMTP:surya.rao_at_reuters.com]
> Sent: Tuesday, August 29, 2000 8:32 PM
> To: mirji_at_crd.ge.com; oracledba_at_lazydba.com
> Subject: Re: Deleting from a huge table
> Sensitivity: Confidential
>
> have you tried deleting from an inline view
>
> Have a PK on P_APPS.APP_ID
> Have a FK on P_HISTORY.APP_ID referencing P_APPS.APP_ID
>
> then
>
> delete from
> (
> select a.*
> from P_HISTORY a, P_APPS B
> where a.APP_ID=b.APP_ID
> and b.parameter_name not in ('blah','blah')
> )
> /
>
> I haven't tried this with HUGE tables, but feel that inline
> views should perform better in this case
>
> Can you try and let me know. I would like to know if this works...
>
>
> surya
> -----
>
> Hi,
>
> We have a table p_history which is very huge. around 80 million records
> It has four fields. History_id, instance_id, timestamp, app_id
> There is a unique index on history_id
> I have to delete records from this history table
> using another table p_apps , for the some parameter names.
> The delete query that i use is
> delete from p_history
> where exits (SELECT 1 FROM P_APPS a , p_history h
> WHERE h.app_id = a.app_id
> AND a.parameter_name NOT IN( some 40 names are here)
>
> If i use IN(operator) then 287 names are there.
>
> for this we are using the big rollback segment. This query takes a hell
> lot of
> time.
> Also the number of records getting added to p_history every day is near to
> half
> a million.
> Is there any way to fasten this delete process.
>
> Ashish
>
>
> --------
> If you're bored, then visit the list's website: http://www.lazydba.com
> (updated
> daily)
> to unsubscribe, send a blank email to oracledba-unsubscribe_at_quickdoc.co.uk
> to subscribe send a blank email to oracledba-subscribe_at_quickdoc.co.uk
>
> -----------------------------------------------------------------
> Visit our Internet site at http://www.reuters.com
>
> Any views expressed in this message are those of the individual
> sender, except where the sender specifically states them to be
> the views of Reuters Ltd.
>
> --------
> If you're bored, then visit the list's website: http://www.lazydba.com
> (updated daily)
> to unsubscribe, send a blank email to oracledba-unsubscribe_at_quickdoc.co.uk
Received on Tue Aug 29 2000 - 00:45:55 CDT