| 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: BULK BIND
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01C02FB1.95DB6838
Content-Type: text/plain;
charset="iso-8859-1"
Hi Jeff,
Thanks very much for your reply. Yes, it looks like the extra pain with the way the code must be written is well worth the performance boost.
Customers screwing up data? NEVER....
Have a great weekend.
Lisa
-----Original Message-----
From: Jeff Landers [mailto:jlanders_at_convergys.com]
Sent: Friday, October 06, 2000 12:56 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: BULK BIND
Koivu, Lisa wrote:
>
>
> good morning all -
>
> Has anyone used this statement? Seems to me that BULK BIND could
> cause a PL/SQL program to become excessively large while executing if
> too many records are returned. Any insights are appreciated, I'm just
> curious.
>
> Thanks
>
> Lisa Rutland Koivu
> Oracle Database Administrator
> Qode.com
> 4850 North State Road 7
> Suite G104
> Fort Lauderdale, FL 33319
>
> V: 954.484.3191, x174
> F: 954.484.2933
> C: 954.658.5849
> http://www.qode.com
>
> "The information contained herein does not express the opinion or
> position of Qode.com and cannot be attributed to or made binding upon
> Qode.com."
Hi Lisa
We have applications that perform deletes and updates of millions of
rows -- daily. We have
found the use of both bulk collect and bulk bind to provide a
significant performance improvement.
Yes, we try to design a solid partition scheme so that we can take
advantage of truncation, but
the situation often arises when the customer has dorked something up and
we must write PL/SQL
scripts to fix the data. For example, prior to bulk binds a deletion of
10 million rows would take
somewhere around 10+ hours. Bulking it we got about 5-6 million per
hour. Lot of other factors
involved, but bottom line is the elimination of all the context
switching between PL/SQL engine and
SQL engine provides a significant boost in performance.
Jeff
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeff Landers INET: jlanders_at_convergys.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- 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). ------_=_NextPart_001_01C02FB1.95DB6838 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> <HTML> <HEAD> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; = charset=3Diso-8859-1"> <META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = 5.5.2650.12"> <TITLE>RE: BULK BIND</TITLE> </HEAD> <BODY> <P><FONT SIZE=3D2>Hi Jeff, </FONT> </P> <P><FONT SIZE=3D2>Thanks very much for your reply. Yes, it looks = like the extra pain with the way the code must be written is well worth = the performance boost. </FONT></P> <P><FONT SIZE=3D2>Customers screwing up data? NEVER....</FONT> </P> <P><FONT SIZE=3D2>Have a great weekend.</FONT> </P> <P><FONT SIZE=3D2>Lisa</FONT> </P> <P><FONT SIZE=3D2>-----Original Message-----</FONT> <BR><FONT SIZE=3D2>From: Jeff Landers [<A = HREF=3D"mailto:jlanders_at_convergys.com">mailto:jlanders_at_convergys.com</A>= ]</FONT> <BR><FONT SIZE=3D2>Sent: Friday, October 06, 2000 12:56 PM</FONT> <BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L</FONT> <BR><FONT SIZE=3D2>Subject: Re: BULK BIND</FONT> </P> <BR> <P><FONT SIZE=3D2>Koivu, Lisa wrote:</FONT> </P> <P><FONT SIZE=3D2>></FONT> <BR><FONT SIZE=3D2>></FONT> <BR><FONT SIZE=3D2>> good morning all -</FONT> <BR><FONT SIZE=3D2>></FONT> <BR><FONT SIZE=3D2>> Has anyone used this statement? Seems to = me that BULK BIND could</FONT> <BR><FONT SIZE=3D2>> cause a PL/SQL program to become excessively = large while executing if</FONT> <BR><FONT SIZE=3D2>> too many records are returned. Any = insights are appreciated, I'm just</FONT> <BR><FONT SIZE=3D2>> curious.</FONT> <BR><FONT SIZE=3D2>></FONT> <BR><FONT SIZE=3D2>> Thanks</FONT> <BR><FONT SIZE=3D2>></FONT> <BR><FONT SIZE=3D2>> Lisa Rutland Koivu</FONT> <BR><FONT SIZE=3D2>> Oracle Database Administrator</FONT> <BR><FONT SIZE=3D2>> Qode.com</FONT> <BR><FONT SIZE=3D2>> 4850 North State Road 7</FONT> <BR><FONT SIZE=3D2>> Suite G104</FONT> <BR><FONT SIZE=3D2>> Fort Lauderdale, FL 33319</FONT> <BR><FONT SIZE=3D2>></FONT> <BR><FONT SIZE=3D2>> V: 954.484.3191, x174</FONT> <BR><FONT SIZE=3D2>> F: 954.484.2933</FONT> <BR><FONT SIZE=3D2>> C: 954.658.5849</FONT> <BR><FONT SIZE=3D2>> <A HREF=3D"http://www.qode.com" = TARGET=3D"_blank">http://www.qode.com</A></FONT> <BR><FONT SIZE=3D2>></FONT> <BR><FONT SIZE=3D2>> "The information contained herein does not = express the opinion or</FONT> <BR><FONT SIZE=3D2>> position of Qode.com and cannot be attributed = to or made binding upon</FONT> <BR><FONT SIZE=3D2>> Qode.com."</FONT> </P> <P><FONT SIZE=3D2> Hi Lisa</FONT> <BR><FONT SIZE=3D2>We have applications that perform deletes and = updates of millions of</FONT> <BR><FONT SIZE=3D2>rows -- daily. We have</FONT> <BR><FONT SIZE=3D2>found the use of both bulk collect and bulk bind to = provide a</FONT> <BR><FONT SIZE=3D2>significant performance improvement.</FONT> <BR><FONT SIZE=3D2>Yes, we try to design a solid partition scheme so = that we can take</FONT> <BR><FONT SIZE=3D2>advantage of truncation, but</FONT> <BR><FONT SIZE=3D2>the situation often arises when the customer has = dorked something up and</FONT> <BR><FONT SIZE=3D2>we must write PL/SQL</FONT> <BR><FONT SIZE=3D2>scripts to fix the data. For example, prior to = bulk binds a deletion of</FONT> <BR><FONT SIZE=3D2>10 million rows would take</FONT> <BR><FONT SIZE=3D2>somewhere around 10+ hours. Bulking it = we got about 5-6 million per</FONT> <BR><FONT SIZE=3D2>hour. Lot of other factors</FONT> <BR><FONT SIZE=3D2>involved, but bottom line is the elimination of all = the context</FONT> <BR><FONT SIZE=3D2>switching between PL/SQL engine and</FONT> <BR><FONT SIZE=3D2>SQL engine provides a significant boost in = performance.</FONT> </P> <P><FONT SIZE=3D2>Jeff</FONT> </P> <P><FONT SIZE=3D2>-- </FONT> <BR><FONT SIZE=3D2>Please see the official ORACLE-L FAQ: <A = HREF=3D"http://www.orafaq.com" = TARGET=3D"_blank">http://www.orafaq.com</A></FONT> <BR><FONT SIZE=3D2>-- </FONT> <BR><FONT SIZE=3D2>Author: Jeff Landers</FONT> <BR><FONT SIZE=3D2> INET: jlanders_at_convergys.com</FONT> </P> <P><FONT SIZE=3D2>Fat City Network Services -- (858) = 538-5051 FAX: (858) 538-5051</FONT> <BR><FONT SIZE=3D2>San Diego, = California -- Public Internet = access / Mailing Lists</FONT> <BR><FONT = SIZE=3D2>---------------------------------------------------------------= -----</FONT> <BR><FONT SIZE=3D2>To REMOVE yourself from this mailing list, send an = E-Mail message</FONT> <BR><FONT SIZE=3D2>to: ListGuru_at_fatcity.com (note EXACT spelling of = 'ListGuru') and in</FONT> <BR><FONT SIZE=3D2>the message BODY, include a line containing: UNSUB = ORACLE-L</FONT> <BR><FONT SIZE=3D2>(or the name of mailing list you want to be removed =Received on Fri Oct 06 2000 - 11:22:13 CDT
 |
 |