create table ani_prx_faster parallel (degree 5) nologging
as
select b.*
from bo_owner_master.ani_prx b,
bo_owner_stage.ani_prx a
where a.cusip = b.cusip (+)
and a.fund_no = b.fund_no (+)
and a.add_cymd = b.add_cymd (+)
and nvl(b.ba_reccode, 'X') != 'V'
and b.cusip is null
This query I got from here I got only 638k, and I have 27m records in my file and
there are 17m deletes. so 12m records left. Also all the records are null????
what do i need to change?
>
> From: "Mladen Gogala" <[EMAIL PROTECTED]>
> Date: 2003/09/17 Wed PM 02:19:40 EDT
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Subject: RE: RE: tuning a massive delete
>
> Well, large sort area size would certainly help, as well as the parallel
> hints.
> As for the bitmap index, I din't know what was the percentage. It probably
> wouldn't
> help much.
>
> --
> Mladen Gogala
> Oracle DBA
>
>
>
> > -----Original Message-----
> > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On
> > Behalf Of [EMAIL PROTECTED]
> > Sent: Wednesday, September 17, 2003 1:25 PM
> > To: Multiple recipients of list ORACLE-L
> > Subject: Re: RE: tuning a massive delete
> >
> >
> > i tested the minus in the explain plan and the estimate was
> > very large. dont i need a large sort_area_size for that?
> >
> > bitmap index on all columns right? not just one?
> > >
> > > From: "Mladen Gogala" <[EMAIL PROTECTED]>
> > > Date: 2003/09/17 Wed PM 12:59:40 EDT
> > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> > > Subject: RE: tuning a massive delete
> > >
> > > How abuout:
> > > create table ani_prx_new parallel (degree 5) nologging
> > > as
> > > ( select * from ani_prx
> > > minus
> > > select * from from bo_owner.ani_prx
> > > where ba_recode='V')
> > > /
> > >
> > > I assume that the description of the bo_owner.ani_prx is identical
> > > to the description of the ani_prx. Furthermore, a bitmap index on
> > > ba_owner.ani_prx(ba_recode) would speed things up quite a bit under
> > > favorable circumstances.
> > >
> > > --
> > > Mladen Gogala
> > > Oracle DBA
> > >
> > >
> > >
> > > > -----Original Message-----
> > > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On
> > > > Behalf Of [EMAIL PROTECTED]
> > > > Sent: Wednesday, September 17, 2003 11:45 AM
> > > > To: Multiple recipients of list ORACLE-L
> > > > Subject: tuning a massive delete
> > > >
> > > >
> > > > 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: <[EMAIL PROTECTED]
> > > > INET: [EMAIL PROTECTED]
> > > >
> > > > 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: [EMAIL PROTECTED] (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).
> > > >
> > >
> > >
> > >
> > >
> > > Note:
> > > This message is for the named person's use only. It may contain
> > > confidential, proprietary or legally privileged information. No
> > > confidentiality or privilege is waived or lost by any
> > mistransmission.
> > > If you receive this message in error, please immediately
> > delete it and
> > > all copies of it from your system, destroy any hard copies
> > of it and
> > > notify the sender. You must not, directly or indirectly, use,
> > > disclose, distribute, print, or copy any part of this
> > message if you
> > > are not the intended recipient. Wang Trading LLC and any of its
> > > subsidiaries each reserve the right to monitor all e-mail
> > > communications through its networks. Any views expressed in this
> > > message are those of the individual sender, except where
> > the message
> > > states otherwise and the sender is authorized to state them
> > to be the
> > > views of any such entity.
> > >
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > > --
> > > Author: Mladen Gogala
> > > INET: [EMAIL PROTECTED]
> > >
> > > 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: [EMAIL PROTECTED] (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: <[EMAIL PROTECTED]
> > INET: [EMAIL PROTECTED]
> >
> > 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: [EMAIL PROTECTED] (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).
> >
>
>
>
>
> Note:
> This message is for the named person's use only. It may contain confidential,
> proprietary or legally privileged information. No confidentiality or privilege is
> waived or lost by any mistransmission. If you receive this message in error, please
> immediately delete it and all copies of it from your system, destroy any hard copies
> of it and notify the sender. You must not, directly or indirectly, use, disclose,
> distribute, print, or copy any part of this message if you are not the intended
> recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to
> monitor all e-mail communications through its networks.
> Any views expressed in this message are those of the individual sender, except where
> the message states otherwise and the sender is authorized to state them to be the
> views of any such entity.
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Mladen Gogala
> INET: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: <[EMAIL PROTECTED]
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
