Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: need help tuning a very large delete

Re: need help tuning a very large delete

From: Ryan Gaffuri <rgaffuri_at_cox.net>
Date: 19 Sep 2003 12:44:30 -0700
Message-ID: <1efdad5b.0309191144.f1d45f2@posting.google.com>


"Burt Peltier" <burttemp1ReMoVeThIs_at_bellsouth.net> wrote in message news:<_yvab.1968$D8.342_at_bignews3.bellsouth.net>...
> Just in case you haven't got enough ways to try... here is 1 more suggestion
> to test/try...
>
> Note: It would be nice to know the size of the tables in Megs/Gigs instead
> of records.
>
> And the other suggestions would probably be better 1st tries... but worth
> testing/trying next might be:
>
> Export with a where clause (assuming 8i or newer) what you want to keep to a
> local disk (separate physical disk than where the table is stored) on the
> server, trunc the table, disable/drop constraints/indexes/triggers, import
> the data, and re-enable/create constraints/indexes/triggers.
>
> --
> "Ryan Gaffuri" <rgaffuri_at_cox.net> wrote in message
> news:1efdad5b.0309171145.77216dac_at_posting.google.com...
> > Stage Table has 17 million records with Delete_column = 'Y';
> >
> > MAster has 27 million records.
> >
> > so I need to
> > delete from master
> > where master.pk = stage.pk
> > and stage.pk = 'Y';
> >
> > i tried writing a 'create table as' with a 'not exists' and was
> > running for 8 hours. killed it. didnt even go to 'killed' status which
> > means the DML hadnt even started it.
> >
> > I tried doing the following: but cant get the outer join right. Is
> > that faster than a minus?
> >
> > any ideas?

the listing of delete deltas are in a seperate table. We recieve deltas from a data warehouse nightly.

Our master table is about 4.2 GB in size and about 40m rows. Our staging table is about 2.5 GB in size and about 18m rows. About 16.8 million delete records.

I think the fastest way to do it is some kind of create table as with an anti-join. Now 'not in' is out of the question because that is optimized when the 'not' table is smaller than the outer table AND you have hash_area_size to blow. I dont have 2.5GB in hash_area_size.

I would think some way with outer joins will work, but Im having trouble properly writing the query?
Minus wont work because the deltas do not match what is in the master table. The delete field is flagged differently. Would a delete with an intersect work? Do large intersects perform well if you can size up sort_area_size?

create table as new_Table
select cols
from master_table m, stage_table s
where s.pk (+) = m.pk
and s.delete = 'Y'
and s.pk is null;

that doesnt give me the correct results. any idea on how to write this? not every record in stage is flagged for delete? Received on Fri Sep 19 2003 - 14:44:30 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US