| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: RE: tuning a massive delete
I agree with Dennis Williams. Large deletes do have an overhead on
indexes. Try this.
- Create table new_table nologging as select * from existing_table where
[condition]
OR you may use COPY command in lieu of that
- get the index definitions - exp grants,constraints,indexes=n ,rows=n for existing table - rename existing_table to old - set sort area size 1M (chk for your env) - create index ... nologging ... parallel if possible - rename new_table to existing_table - imp grants,constraints
HTH
GovindanK
<-----Original Message----->
From: DENNIS WILLIAMS
Sent: 9/17/2003 9:39:49 AM
To: ORACLE-L_at_fatcity.com
Ryan - One solution that is often suggested would be to copy the rows
you
wish to retain to another table, then truncate the table. Oracle is
relatively slow at deletes compared to inserts. Would this method work
for
you?
Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com
-----Original Message-----
Sent: Wednesday, September 17, 2003 11:25 AM
To: Multiple recipients of list ORACLE-L
i explain planned it and it was much worse than not exists.
my understanding is hash_aj is faster when the table in the sub-query returns results that are significantly less than the one in the outer table.
ill try it, but i think exists is faster. we dont want to do an index
scan
here and my hash_area_size isnt real big.
>
> From: "Richard Ji"
> Date: 2003/09/17 Wed PM 12:04:56 EDT
> To: Multiple recipients of list ORACLE-L
> Subject: RE: tuning a massive delete
>
> Try anti hash join.
>
> -----Original Message-----
> Sent: Wednesday, September 17, 2003 11:45 AM
> To: Multiple recipients of list ORACLE-L
>
>
> i have a table with 27 million records that is about 1.2 GB in size. I
have a 'staging table' with 18 million records. 16 million records have
a
'delete' flag. I have indexed the column in staging with a delete flag.
both
tables have indexed primary keys. Is the following my fastest option or
would an 'IN' be faster? Im concerned because this has been running for
a
while and have alot of consistent gets but no 'writes' yet which tells
me
its still building the join. Our sort_area_size is rather small and Im
not
allowed to change it which tells me we are swapping to the temp
tablespace.
>
> anyway to speed this up? or is this the fastest we got?
>
>
> create table ani_prx_new parallel (degree 5) nologging
> as select *
> from ani_prx b
> where not exists (select 1 from bo_owner_stage.ani_prx a where
ba_reccode
= 'V' and a.cusip = b.cusip
> and a.fund_no = b.fund_no and a.add_cymd = b.add_cymd)
>
> --
>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Govindan K INET: gkatteri_at_omanmail.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- 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).Received on Wed Sep 17 2003 - 14:24:46 CDT
 |
 |