update query performance [message #282768] |
Fri, 23 November 2007 06:47  |
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
Please advice.
Thanks
[Updated on: Fri, 23 November 2007 06:53] Report message to a moderator
|
|
|
|
Re: update query performance [message #282905 is a reply to message #282773] |
Sat, 24 November 2007 23:09   |
rleishman
Messages: 3728 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   |
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 #282982 is a reply to message #282912] |
Sun, 25 November 2007 20:28   |
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.
|
|
|
|