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

Home -> Community -> Usenet -> c.d.o.tools -> Re: Can someone please help "tune" this script?

Re: Can someone please help "tune" this script?

From: Mark D Powell <markp7832_at_my-deja.com>
Date: 2000/04/13
Message-ID: <8d4t5p$oab$1@nnrp1.deja.com>#1/1

In article <04dfa585.95dfeeb0_at_usw-ex0105-037.remarq.com>,   Andy <abruskoNOabSPAM_at_binney-smith.com.invalid> wrote:
> I am trying to do what I think is a common thing. I need to
> update one table from the values in another table based upon the
> join of a few columns. Below is the script that I am running,
> but it seems to run forever. The table that I am updating
> (frt.freight) has about 200,000 rows and the table with the
> values that I am using for the update (frt.mpay_working) has
> about 2,000 rows. Can this be made to run faster? Thanks alot
> for your help...I appreciate it!
> Andy
> Update frt.freight a
> set (charged$, remit, batch, paid_weight, audit_reason_code)
> = (select b.charged$, b.remit, b.batch, b.paid_weight,
> b.audit_reason_code
> from frt.mpay_working b
> where b.pro = a.pro and
> b.frt_order = a.frt_order and
> b.accessorial_rc = a.accessorial_rc and
> b.carrier = a.carrier and
> b.bl = a.bl);
>
>

I do not write serious SQL very often so I sometimes make a mistake, but the way I read your SQL is:

Your select is a coordinated sub-query so for every row in freight, query mpay_working and update freight. If no matching row is found then the freight columns will be set to null.

If you wish to update only those rows in freight that have a matching row in mpay_working then I believe you need to add " and exists (select ..as above..) as another where clause condition. At least this is how I write my updates and I did find in the SQL manual under Update subqueries "If the subquery returns no rows, then the column is assigned a null." If you are updating every row when you wanted to update a sub-set then this may be why your query is slow.

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Thu Apr 13 2000 - 00:00:00 CDT

Original text of this message

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