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

Home -> Community -> Mailing Lists -> Oracle-L -> RE: tuning a massive delete

RE: tuning a massive delete

From: Richard Ji <Richard.Ji_at_ztango.com>
Date: Wed, 17 Sep 2003 08:04:56 -0800
Message-ID: <F001.005D03B4.20030917080456@fatcity.com>


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: <rgaffuri_at_cox.net
  INET: rgaffuri_at_cox.net

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).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Richard Ji
  INET: Richard.Ji_at_ztango.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 - 11:04:56 CDT

Original text of this message

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