Home » SQL & PL/SQL » SQL & PL/SQL » update query performance (Oracle10g)
update query performance [message #282768] Fri, 23 November 2007 06:47 Go to next message
ednms
Messages: 39
Registered: November 2007
Member
Hi. I need opinion about my sql query.
For test data, maybe the performance is ok but for real data (about > 100,000), i'm not sure because my understanding is update statement will take long time for large data.
Here is my test data and my sql query

SQL> SELECT * from ACCT_BILL_INFO;

ACCT_NO LAST_BILL CURR_OUTSTANDING_AMT LAST_PAYMENT_DATE
-------------- --------- -------------------- ---------
F921338300506 28-MAY-07 -200.2 16-APR-07
F921338300105 01-MAY-07 1000.42 01-JUN-07
S503377200110 01-MAY-07 -100.32 01-JUN-07
P931946400102 25-JAN-07 500.3 22-FEB-07


SQL> SELECT ACCT_NO, TXN_DATE, NPCS_FILE_SEQ_NUM from PAYMENT;

ACCT_NO TXN_DATE NPCS_FILE_SEQ_NUM
-------------- --------- -----------------
20030078320608 13-JUL-07 326
CELCOM 03-JAN-07 367
F921338300105 26-DEC-06 305
F921338300105 10-JUL-07 324
86534852 03-JAN-07 170
20030078320608 26-DEC-06 305
S503377200110 03-JAN-07 305
S503377200110 03-JAN-07 305
S503377200110 03-JAN-07 305
86534852 03-JAN-07 305
H912279980109 03-JAN-07 305



UPDATE acct_bill_info t1
SET t1.curr_outstanding_amt =
( t1.curr_outstanding_amt
+ (SELECT SUM (t2.pay_amt)
FROM payment t2, acct_bill_info t1
WHERE t1.acct_no = t2.acct_no AND t2.npcs_file_seq_num = 305)
),
t1.last_payment_date =
(SELECT MAX (txn_date)
FROM payment t2, acct_bill_info t1
WHERE t1.acct_no = t2.acct_no AND t2.npcs_file_seq_num = 305)
WHERE acct_no IN (SELECT t2.acct_no
FROM payment t2
WHERE t1.acct_no = t2.acct_no)


I need to update CURR_OUTSTANDING_AMT, LAST_PAYMENT_DATE in ACCT_BILL_INFO.
For CURR_OUTSTANDING_AMT, i need to sum(PAY_AMT)
and for LAST_PAYMENT_DATE, i need to get max(TXN_DATE). Is there any way to do update statement besides use subquery Confused

Please advice.
Thanks

[Updated on: Fri, 23 November 2007 06:53]

Report message to a moderator

Re: update query performance [message #282773 is a reply to message #282768] Fri, 23 November 2007 06:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
update t set (a,b) = (select sum(), max() from...)

"acct_bill_info t1" must not be repeated in subquery.

Regards
Michel
Re: update query performance [message #282905 is a reply to message #282773] Sat, 24 November 2007 23:09 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
For more than 100,000 rows you MUST unnest your SQL.

When you perform
UPDATE .. SET cols = (sub-query)
, the sub-query is run once separately for EVERY ROW that needs to be updated.

So if you update 100K rows, you effectively run 100K SQLs.

There are three good ways of doing it without nesting:
- Simple MERGE statement
- Updateable Join View
- Load all of the changes you want into a Global Temporary Table and then MERGE with the base-table.

Ross Leishman
Re: update query performance [message #282912 is a reply to message #282773] Sun, 25 November 2007 00:27 Go to previous messageGo to next message
ednms
Messages: 39
Registered: November 2007
Member
Thanks.
So instead using
UPDATE ACCT_BILL_INFO T1
SET (t1.curr_outstanding_amt, t1.last_payment_date) =
(SELECT t1.curr_outstanding_amt + SUM (t2.pay_amt),
MAX (txn_date)
FROM payment t2
WHERE t1.acct_no = t2.acct_no AND t2.npcs_file_seq_num = 305
GROUP BY t1.curr_outstanding_amt)
WHERE acct_no IN (
SELECT t2.acct_no
FROM payment t2
WHERE t1.acct_no = t2.acct_no
AND t2.npcs_file_seq_num = 305)

i better use

MERGE INTO ACCT_BILL_INFO ABF USING
(SELECT t2.acct_no, t1.curr_outstanding_amt + SUM (t2.pay_amt) pay_amt,
MAX (txn_date)
FROM acct_bill_info t1, payment t2
WHERE t1.acct_no = t2.acct_no AND t2.npcs_file_seq_num = 305
group by t2.acct_no,t1.curr_outstanding_amt) PAY
ON (ABF.ACCT_NO = PAY.ACCT_NO)
WHEN MATCHED THEN
UPDATE SET
ABF.curr_outstanding_amt = PAY.pay_amt

for high volume data.

Re: update query performance [message #282929 is a reply to message #282912] Sun, 25 November 2007 03:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You don't need acct_bill_info in PAY, just make the addition in the set clause.
Are you sure you "group by" is on "curr_outstanding_amt" and not "acct_no"?

Regards
Michel
Re: update query performance [message #282982 is a reply to message #282912] Sun, 25 November 2007 20:28 Go to previous messageGo to next message
ednms
Messages: 39
Registered: November 2007
Member
Ok. Thanks.
so i just write

MERGE INTO ACCT_BILL_INFO ABF USING
(SELECT t2.acct_no, SUM (t2.pay_amt) pay_amount,MAX (txn_date)
FROM payment t2
WHERE t2.npcs_file_seq_num = 305
group by t2.acct_no) PAY
ON (ABF.ACCT_NO = PAY.ACCT_NO)
WHEN MATCHED THEN
UPDATE SET
ABF.curr_outstanding_amt = ABF.curr_outstanding_amt + PAY.pay_amount

In term of performance, this is better than conventional update right? I already try inner update and it perform much better but for my case i cannot use that.
But thanks anyway.
Re: update query performance [message #283020 is a reply to message #282768] Sun, 25 November 2007 23:32 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

BTW :

@ednms:

No need to refer to the table acct_bill_info in case of you original suquery . try to use Correlated subquery instead.

Thumbs Up
Rajuvan.
Previous Topic: Usage of Bulk bind in Select SQL
Next Topic: Dynamic Column Selection
Goto Forum:
  


Current Time: Fri Dec 09 09:52:57 CST 2016

Total time taken to generate the page: 0.25701 seconds