RE: update performance

From: Amar Kumar Padhi <TS2017_at_emirates.com>
Date: Wed, 10 Jan 2001 14:21:18 +0400
Message-Id: <10737.126242@fatcity.com>

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_01C07AEF.121FA4D0
Content-Type: text/plain;

charset="iso-8859-1"

Use pl/sql, open a cursor and commit data after every 10,000 or more/less records are updated. This will prevent contention of the rollback segments.

Thanks,
Amar Kumar Padhi
Software Cosultant.

-----Original Message-----
From: Krishna Prasad [mailto:krishna_at_nehanet.com] Sent: Wednesday, January 10, 2001 11:56 AM To: Multiple recipients of list ORACLE-L Subject: update performance

I ahve a requirement to update a table using a joing - details: Table A: (colA, colB,...): 2 million rows Table B: (colA, colC,...): 2 milion rows

update cmd:

update tableA A
set colA = (select B.colC fdrom tableB B where B.tableB.colA = A.tableA.colA )

I tried it in PL/SQL and as the straight SQL but it takes FOREVER without coming back...
should I be doiing something better?

(I have an index on the two columns on TableB)

Thanks
-Krishna.

------_=_NextPart_001_01C07AEF.121FA4D0
Content-Type: text/html;

charset="iso-8859-1"

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<TITLE>RE: Date comparison question</TITLE>

<META content="MSHTML 5.00.3019.2500" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><FONT color=#0000ff face="Courier New" size=2><SPAN
class=466141110-10012001>Use pl/sql, open a cursor and commit data after every 10,000 or more/less records are updated. This will prevent contention of the rollback segments.</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face="Courier New" size=2><SPAN
class=466141110-10012001></SPAN></FONT> </DIV>
<DIV><FONT color=#0000ff face="Courier New" size=2><SPAN
class=466141110-10012001>Thanks,</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face="Courier New" size=2><SPAN
class=466141110-10012001>Amar Kumar Padhi</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face="Courier New" size=2><SPAN
class=466141110-10012001>Software Cosultant.</SPAN></FONT></DIV>
<BLOCKQUOTE style="MARGIN-RIGHT: 0px">

  <DIV class=OutlookMessageHeader><FONT face="Times New Roman"><FONT   size=2>-----Original Message-----<BR><B>From:</B> Krishna Prasad   [mailto:krishna_at_nehanet.com]<BR><B>Sent:</B> Wednesday, January 10, 2001 11:56   AM<BR><B>To:</B> Multiple recipients of list ORACLE-L<BR><B>Subject:</B>   update performance<BR><BR></FONT></DIV></FONT>   <DIV><FONT color=#0000ff face=Arial size=2><SPAN class=125045507-10012001>I   ahve a requirement to update a table using a joing -   details:</SPAN></FONT></DIV>
  <DIV><FONT color=#0000ff face=Arial size=2><SPAN   class=125045507-10012001>Table A: (colA, colB,...): 2 million   rows</SPAN></FONT></DIV>
  <DIV><FONT color=#0000ff face=Arial size=2><SPAN   class=125045507-10012001>Table B: (colA, colC,...): 2 milion   rows</SPAN></FONT></DIV>
  <DIV><FONT color=#0000ff face=Arial size=2><SPAN   class=125045507-10012001></SPAN></FONT> </DIV>

  <DIV><FONT color=#0000ff face=Arial size=2><SPAN 
  class=125045507-10012001>update cmd:</SPAN></FONT></DIV>
  <DIV><FONT color=#0000ff face=Arial size=2><SPAN

  class=125045507-10012001></SPAN></FONT> </DIV>   <DIV><FONT color=#0000ff face=Arial size=2><SPAN   class=125045507-10012001>update tableA A</SPAN></FONT></DIV>   <DIV><FONT color=#0000ff face=Arial size=2><SPAN class=125045507-10012001>set   colA = (select B.colC fdrom tableB B where B.tableB.colA = A.tableA.colA   )</SPAN></FONT></DIV>
  <DIV><FONT color=#0000ff face=Arial size=2><SPAN   class=125045507-10012001></SPAN></FONT> </DIV>   <DIV><FONT color=#0000ff face=Arial size=2><SPAN class=125045507-10012001>I   tried it in PL/SQL and as the straight SQL but it takes FOREVER without coming   back...</SPAN></FONT></DIV>
  <DIV><FONT color=#0000ff face=Arial size=2><SPAN   class=125045507-10012001>should I be doiing something   better?</SPAN></FONT></DIV>
  <DIV><FONT color=#0000ff face=Arial size=2><SPAN   class=125045507-10012001></SPAN></FONT> </DIV>

  <DIV><FONT color=#0000ff face=Arial size=2><SPAN class=125045507-10012001>(I 
  have an index on the two columns on TableB)</SPAN></FONT></DIV>
  <DIV><FONT color=#0000ff face=Arial size=2><SPAN

class=125045507-10012001></SPAN></FONT> </DIV> <DIV><FONT color=#0000ff face=Arial size=2><SPAN class=125045507-10012001>Thanks</SPAN></FONT></DIV> <DIV><FONT color=#0000ff face=Arial size=2><SPAN class=125045507-10012001>-Krishna.</SPAN></FONT></DIV> <DIV><FONT color=#0000ff face=Arial size=2><SPAN class=125045507-10012001></SPAN></FONT> </DIV></BLOCKQUOTE></BODY></HTML> Received on Wed Jan 10 2001 - 04:21:18 CST