Re: advice for massive delete querry

I use prebuilt MVs (8.1.7 to deal with the rows inserted into source while you were creating target. THis is a very similar mechanism as you effectively get a CTAS (nologging), then recreate all indexes on the new table, using different names (*_new). fast_refresh often and then when complete, drop synonyms, revoke privs etc, rename source objects to source_old, drop MV and log, rename source_new objects to source, recreate synonyms, grant privs etc and away we go  The main benefit for this site is that 'source' objects are online for the greatest possible time. if you script this then the table is offline for seconds or minutes only. got this technique from tkyte. seems to work for us here.
 HTH
Russell

 On 9/1/05, John Clarke <jclarke_at_centroidsys.com> wrote:
>
> I'm with Mark - create a scratch table (nologging of course, PCTAS maybe)
> with all the data you want to keep, truncate the original table, and
> direct-path insert rows from the scratch table back into the original.
> Drop
> the scratch table when done.
>
> Depending on all the stuff associated with the table (indices, triggers,
> grants, etc), the truncate/re-insert thing works well b/c you don't have
> to
> remember much of anything, except maybe drop and rebuild indexes when
> done.
> Of course, this approach will require space that you may not have, as well
> as downtime you may not have.
>
> Just an idea ...
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of Mark W. Farnham
> Sent: Wednesday, August 31, 2005 9:47 PM
> To: bobmetelsky_at_comcast.net; oracle-l
> Subject: RE: advice for massive delete querry
>
> Is it possible to copy the keepers instead and then drop the original
> table
> and do the requisite renames?
>
> Delete has to copy the entire row to rollback. Copying the keepers in a
> particular order *may* have an extreme benefit if there is a dominant
> order
> of access to the table. The result set has no air left behind, which is
> good
> for relatively static data, less good for high rate of collisions in block
> updates.
>
> If (as it appears from your delete values) this is essentially unhooking a
> years' worth of data, then think seriously about partitioning next time
> around. A year at a time is usually thought to be a pretty big chunk, your
> mileage may vary.
>
> Or even use "poor man's partitioning." If your granularity is a year, then
> the inserts go into a table tabname2005 currently, and next year you'll
> switch the insert synonym to point at tabname2006. Then your select and
> update view is tabnameu and your insert view is tabnamei (or whatever
> nameing convention you want). When you unhook a year, you redefine the
> synonyms and views at a quiescent moment. (That's the way dinosaurs
> handled
> it before there was partitioning.)
>
> On the con side of course, you'll transiently need more space, and you'll
> need to build any indexes from scratch at full sort overhead, excepting
> possibly one index if you copy the keepers in the requisite order.
>
> Usually when you add up the overheads (test a reasonable sized sample, but
> remember that indexes cost about n log n so you'll get an underestimate
> for
> a sample). If you have lead time you can create full size indexes on a
> test
> database copy to more accurately size the effort.
>
> Thumbrule: If you're keeping less than half it's not even close - copy the
> keepers. (oh I hated to type in a thumbrule. there are always exceptions,
> like if you have "number of columns in table factorial" indexes.)
>
> Regards,
>
> mwf
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of bobmetelsky
> Sent: Wednesday, August 31, 2005 6:13 PM
> To: oracle-l
> Subject: advice for massive delete querry
>
>
> Guys I have this statement from a developer
>
> DELETE target
> WHERE (1_num,2_num,3_num,4_num)
> IN
> (SELECT 1_num,2_num,3_num,4_num)
> FROM source s
> WHERE s.4_num BETWEEN 20040101 AND 20041231;
>
> The statement is excepted to delete around 150M rows, the sub query
> returns about 300M rows. !!!
> all datatypes are number(n)
>
> I'm not a performance guru but I suggested to use the commit_every(n)
> package - from Steve Adams site, and CTAS
> e.g.
>
> begin
> commit_every(100);
> create table t as select * from source where 4_num < 20040101 AND 4_num >
> 20041231;
> end;
>
> What are the options for a massive delete statement such as this?
> I've googled and poked around orafaq but didn't come up with much.
>
> I recall the helpful mindset from Wolfgang Brietling (sp) as "the
> quickest way to do something is often not to do it", so Im thinking ctas
> with commit_every() as a viable alternative to the delete.
>
> Ideas from the more experienced?
>
> thanks!
>
> bob
> --
> "Oracle error messages being what they are, do not
> highlight the correct cause of fault, but will identify
> some other error located close to where the real fault lies."
> --
> http://www.freelists.org/webpage/oracle-l
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>

--
http://www.freelists.org/webpage/oracle-l