update statement takes too long [message #632462] |
Sun, 01 February 2015 06:19 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/04be50cd0e98b78f02d8b82f9456b619?s=64&d=mm&r=g) |
OraFerro
Messages: 433 Registered: July 2011
|
Senior Member |
|
|
Hi All,
I have an update query that takes too long as shown in the below explain plan despite the fact that the normal select statement that joins the same two tables needed for the update runs fast.
Below is the plan for both cases showing the cost of each statement:
EXPLAIN PLAN
SET STATEMENT_ID = 'journal_update_inv'
INTO plan_table
FOR UPDATE iv_table CPA SET CPA.FK_JOURNAL_ENTRY =
(
SELECT JOR.JOURNAL_SERIAL FROM ac_jor_table JOR
WHERE CPA.fk_v_date = JOR.fk_v_date
AND CPA.fk_v_ser = JOR.fk_v_ser
AND JOR.fk_maj_no = 1
AND JOR.fk_act =4--
and sign(cpa.AMOUNT) = sign(jor.AMOUNT)
)
WHERE CPA.TYPE_ID = 1;
SELECT LPAD(' ',2*(LEVEL-1))||operation operation, options,
object_name, position
FROM plan_table
START WITH id = 0 AND statement_id = 'journal_update_inv'
CONNECT BY PRIOR id = parent_id AND statement_id = 'journal_update_inv';
and the result is:
OPERATION OPTIONS OBJECT_NAME POSITION
UPDATE STATEMENT (null) (null) 97397628
UPDATE (null) iv_table 1
TABLE ACCESS FULL iv_table 1
TABLE ACCESS FULL iv_table 1
TABLE ACCESS FULL iv_table 1
TABLE ACCESS FULL ac_jor_table 2
TABLE ACCESS FULL ac_jor_table 2
TABLE ACCESS FULL ac_jor_table 2
UPDATE (null) iv_table 1
TABLE ACCESS FULL iv_table 1
TABLE ACCESS FULL iv_table 1
TABLE ACCESS FULL iv_table 1
TABLE ACCESS FULL ac_jor_table 2
TABLE ACCESS FULL ac_jor_table 2
TABLE ACCESS FULL ac_jor_table 2
UPDATE (null) iv_table 1
TABLE ACCESS FULL iv_table 1
TABLE ACCESS FULL iv_table 1
TABLE ACCESS FULL iv_table 1
TABLE ACCESS FULL ac_jor_table 2
TABLE ACCESS FULL ac_jor_table 2
TABLE ACCESS FULL ac_jor_table 2
UPDATE STATEMENT (null) (null) 97397628
UPDATE (null) iv_table 1
TABLE ACCESS FULL iv_table 1
TABLE ACCESS FULL iv_table 1
TABLE ACCESS FULL iv_table 1
TABLE ACCESS FULL ac_jor_table 2
TABLE ACCESS FULL ac_jor_table 2
TABLE ACCESS FULL ac_jor_table 2
UPDATE (null) iv_table 1
TABLE ACCESS FULL iv_table 1
TABLE ACCESS FULL iv_table 1
TABLE ACCESS FULL iv_table 1
TABLE ACCESS FULL ac_jor_table 2
TABLE ACCESS FULL ac_jor_table 2
TABLE ACCESS FULL ac_jor_table 2
UPDATE (null) iv_table 1
TABLE ACCESS FULL iv_table 1
TABLE ACCESS FULL iv_table 1
TABLE ACCESS FULL iv_table 1
TABLE ACCESS FULL ac_jor_table 2
TABLE ACCESS FULL ac_jor_table 2
TABLE ACCESS FULL ac_jor_table 2
UPDATE STATEMENT (null) (null) 97397628
UPDATE (null) iv_table 1
TABLE ACCESS FULL iv_table 1
TABLE ACCESS FULL iv_table 1
TABLE ACCESS FULL iv_table 1
TABLE ACCESS FULL ac_jor_table 2
TABLE ACCESS FULL ac_jor_table 2
TABLE ACCESS FULL ac_jor_table 2
UPDATE (null) iv_table 1
TABLE ACCESS FULL iv_table 1
TABLE ACCESS FULL iv_table 1
TABLE ACCESS FULL iv_table 1
TABLE ACCESS FULL ac_jor_table 2
TABLE ACCESS FULL ac_jor_table 2
TABLE ACCESS FULL ac_jor_table 2
UPDATE (null) iv_table 1
TABLE ACCESS FULL iv_table 1
TABLE ACCESS FULL iv_table 1
TABLE ACCESS FULL iv_table 1
TABLE ACCESS FULL ac_jor_table 2
TABLE ACCESS FULL ac_jor_table 2
TABLE ACCESS FULL ac_jor_table 2
and the plan for the select statement is:
EXPLAIN PLAN
SET STATEMENT_ID = 'select_with_my_tables'
INTO plan_table
FOR
select jor.*
from iv_table cpa, ac_jor_table jor
WHERE cpa.type_id = 1
and CPA.fk_v_date = JOR.fk_v_date
AND CPA.fk_v_ser = JOR.fk_v_ser
AND JOR.fk_maj_no = 1
AND JOR.fk_act =4--
and sign(cpa.AMOUNT) = sign(jor.AMOUNT);
SELECT LPAD(' ',2*(LEVEL-1))||operation operation, options,
object_name, position
FROM plan_table
START WITH id = 0 AND statement_id = 'select_with_my_tables'
CONNECT BY PRIOR id = parent_id AND statement_id = 'select_with_my_tables';
OPERATION OPTIONS OBJECT_NAME POSITION
SELECT STATEMENT (null) (null) 1738
HASH JOIN (null) (null) 1
TABLE ACCESS FULL ac_jor_table 1
TABLE ACCESS FULL iv_table 2
Thanks,
Ferro
[Updated on: Sun, 01 February 2015 06:21] Report message to a moderator
|
|
|
|
|
|
|
|
|
Re: update statement takes too long [message #632477 is a reply to message #632470] |
Sun, 01 February 2015 09:14 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8931 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Quote:Oracle believes that ac_jor_table contains only 1 row based upon what EXPLAIN PLAN shows.
No. Oracle believes that one row will be returned by the scan of ac_jor_table, after applying the filter. This is quite possible.
The problem is that you may be iterating the scan of ac_jor_table many times, in the worst case 155k times. You need to create a compound index that covers all the filter columns, and then see if you get index access.
|
|
|
|
|
Re: update statement takes too long [message #632505 is a reply to message #632494] |
Mon, 02 February 2015 02:08 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8931 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Received as a PM:Quote:Hi John,
Can you please have a look on the questions I added in reply to your last post? I really need help in this situation as I have to wait another day at least to be able to run the trace and provide results.
Thanks,
Ferro Are you asking for consultancy services? I can ask my boss to send you a quotation.
|
|
|
|
Re: update statement takes too long [message #632623 is a reply to message #632515] |
Tue, 03 February 2015 13:11 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/56289.jpg) |
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
Let us stop and think a little about what might happen in an update and where the costs could come from.
UPDATE iv_table CPA SET CPA.FK_JOURNAL_ENTRY =
(
SELECT JOR.JOURNAL_SERIAL FROM ac_jor_table JOR
WHERE CPA.fk_v_date = JOR.fk_v_date
AND CPA.fk_v_ser = JOR.fk_v_ser
AND JOR.fk_maj_no = 1
AND JOR.fk_act =4--
and sign(cpa.AMOUNT) = sign(jor.AMOUNT)
)
WHERE CPA.TYPE_ID = 1;
Here are some thoughts.
1. first you need to find the rows you want to update (WHERE CPA.TYPE_ID = 1). Is this being done efficiently? If it is < 2% of the rows the use an index, otherwise you likely should be doing a table scan.
2. then you need to get the value to update with
(
SELECT JOR.JOURNAL_SERIAL FROM ac_jor_table JOR
WHERE CPA.fk_v_date = JOR.fk_v_date
AND CPA.fk_v_ser = JOR.fk_v_ser
AND JOR.fk_maj_no = 1
AND JOR.fk_act =4--
and sign(cpa.AMOUNT) = sign(jor.AMOUNT)
)
Is this being done efficiently? Optimizing this query depends upon how many rows you find in the main query, and also what indexing exists. You either need to treat this query as a precision query or a warehouse query (and this will partly depend upon how man rows you update). If you are treating it as a precision query then indexing is important and you will need the right indexing to generate the right query plan for this sub-query. this query is simple so I would suggest the following index on JOR (fk_maj_no,fk_act,fk_v_ser,vk_v_date,sign(amount),journal_serial). This gives you a covering index that also exploits the maximum amount of ACCESS possible. Read up on covering indexes and function based indexes if you want to know more.
As an aside, this is one of those places where Oracle's default query results cache could make a big difference. It would keep results of the sub-query lookups so that duplicate lookups don't need to happen more than once (up to some limit anyway). This works regardless of if you are using indexes or not, but is dependent upon the order in which value combinations come off the main query. But you can't control it really so it is just an interesting piece of information that you can't do much with here.
3. then there is logging for the update. this however is system tuning so you maybe not easily handled in this post.
4. then there is consistent read costs. Each time you update a row, the block that rows sits on changes. If your update was reading the same table you were updating, you could incur lots of consistent gets as the update creates more blocks that need to be "reconstructed". But that is not the case here so we can ignore this cost for this update.
Please forward some additional info:
1. how many rows are you planning on updating?
2. how long does the sub-query take to run?
3. how long does the update take to run?
4. do you have the suggested index or not?
Kevin
|
|
|
Re: update statement takes too long [message #632803 is a reply to message #632504] |
Thu, 05 February 2015 18:55 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
Roachcoach wrote on Mon, 02 February 2015 18:57Try rewriting as a merge, I usually have better luck getting good results out of that with unindexed updates.
+1
The problem with that type of UPDATE statement is that it MUST nest. ie. It re-runs the sub-query for EVERY matching row in the updated table. This is fine if there are not many rows, but not so good if there are lots. A MERGE on the other hand can run the UPDATE as a proper join, which can be optimised for higher volumes.
**Untested code**
MERGE INTO (SELECT * FROM iv_table WHERE TYPE_ID = 1) cpa
USING (
SELECT fk_v_date, JOR.fk_v_ser, jor.AMOUNT, JOR.JOURNAL_SERIAL
FROM ac_jor_table JOR
WHERE JOR.fk_maj_no = 1
AND JOR.fk_act = 4
) JOR
ON CPA.fk_v_date = JOR.fk_v_date
AND CPA.fk_v_ser = JOR.fk_v_ser
AND sign(cpa.AMOUNT) = sign(jor.AMOUNT)
WHEN MATCHED THEN SET CPA.FK_JOURNAL_ENTRY = JOR.JOURNAL_SERIAL
You may need to make some tweaks to handle:
- Cases where the output from the USING clause is many-to-one,
- Cases where there is no matching row in the USING clause.
I leave those as an exercise for you.
Ross Leishman
|
|
|
Re: update statement takes too long [message #632804 is a reply to message #632803] |
Thu, 05 February 2015 21:09 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/56289.jpg) |
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
You guys have explained it way better then I did.
To understand what Ross and Roachcoach were saying, consider the differences in query plans between the two alternative updates (update vs. merge). In some sense, everything is a join. That goes for updates too since you have to pair together two rows in order to update one of them. With that in mind, note that the simple update is (though it does not explicitly show it) doing a NESTED LOOPS JOIN from outer table to inner table, whereas the merge code is doing a HASH JOIN in order to pair up rows. For lots of rows, the hash join will be more efficient. In my testing I updated stats of both tables to make them look like they each had 1 million rows (with no indexes or constraints on either).
TECHNICALLY HOWEVER: one must keep in mind that MERGE and UPDATE are not identical in behavior when it comes to missing matched rows. This update and the merge provided by Ross will not do the same thing when there is no match on AC_JOR_TABLE. The update will set FK_JOURNAL_ENTRY=null whereas the merge will simply skip the row in IV_TABLE and leave FK_JOURNAL_ENTRY alone. This is part of what Ross was pointing out when he noted there were details you (OP) would have to account for. In some cases (as seen here in fact?), there is no variation of MERGE that will do what the UPDATE does. Thus you need to know your data and your constraint rules before you decide to substitute merge for update. It is not just about performance. Unless an update is guaranteed to have a match for every row it seeks to update, a merge cannot be substituted for an update, and in the case of this update, there is no constraint setup between these two tables that would enforce this requirement. Therefore, replacing this update with a merge is invalid and can lead to incorrect (or at least different (one has to question if the update is actually correct to set its target to null)) results.
However to the however, I would expect that under the right circumstances, Oracle would internally convert the query plan for the update to a HASH JOIN type of plan since they are all equivalent if certain restrictions are applied which means eventually we would want to write the update and let oracle do more magic. But I cannot prove with my installed versions that it will do this today.
Boy this stuff can get deep...
21:36:46 SQL> explain plan for UPDATE iv_table CPA SET CPA.FK_JOURNAL_ENTRY =
21:37:12 2 (
21:37:12 3 SELECT JOR.JOURNAL_SERIAL FROM ac_jor_table JOR
21:37:12 4 WHERE CPA.fk_v_date = JOR.fk_v_date
21:37:12 5 AND CPA.fk_v_ser = JOR.fk_v_ser
21:37:12 6 AND JOR.fk_maj_no = 1
21:37:12 7 AND JOR.fk_act =4--
21:37:12 8 and sign(cpa.AMOUNT) = sign(jor.AMOUNT)
21:37:12 9 )
21:37:12 10 -- WHERE CPA.TYPE_ID = 1
21:37:12 11 /
Explained.
Elapsed: 00:00:00.00
21:37:12 SQL>
21:37:12 SQL> @showplan11gshort
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
Plan hash value: 2536411157
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1000K| 45M| 45 (36)| 00:00:01 |
| 1 | UPDATE | IV_TABLE | | | | |
| 2 | TABLE ACCESS FULL| IV_TABLE | 1000K| 45M| 45 (36)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| AC_JOR_TABLE | 1 | 74 | 50 (42)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("JOR"."FK_V_DATE"=:B1 AND "JOR"."FK_V_SER"=:B2 AND
"JOR"."FK_MAJ_NO"=1 AND "JOR"."FK_ACT"=4 AND
SIGN("JOR"."AMOUNT")=SIGN(:B3))
17 rows selected.
Elapsed: 00:00:00.04
21:37:12 SQL> explain plan for MERGE INTO (SELECT * FROM iv_table WHERE TYPE_ID = 1) cpa
21:37:25 2 USING (
21:37:25 3 SELECT fk_v_date, JOR.fk_v_ser, jor.AMOUNT, JOR.JOURNAL_SERIAL
21:37:25 4 FROM ac_jor_table JOR
21:37:25 5 WHERE JOR.fk_maj_no = 1
21:37:25 6 AND JOR.fk_act = 4
21:37:25 7 ) JOR
21:37:25 8 ON (
21:37:25 9 CPA.fk_v_date = JOR.fk_v_date
21:37:25 10 AND CPA.fk_v_ser = JOR.fk_v_ser
21:37:25 11 AND sign(cpa.AMOUNT) = sign(jor.AMOUNT)
21:37:25 12 )
21:37:25 13 WHEN MATCHED THEN UPDATE SET CPA.FK_JOURNAL_ENTRY = JOR.JOURNAL_SERIAL
21:37:25 14 /
Explained.
Elapsed: 00:00:00.01
21:37:25 SQL>
21:37:25 SQL> @showplan11gshort
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
Plan hash value: 3488386518
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 1 | 39 | 97 (41)| 00:00:02 |
| 1 | MERGE | IV_TABLE | | | | |
| 2 | VIEW | | | | | |
|* 3 | HASH JOIN | | 1 | 147 | 97 (41)| 00:00:02 |
|* 4 | TABLE ACCESS FULL| AC_JOR_TABLE | 100 | 7400 | 49 (41)| 00:00:01 |
|* 5 | TABLE ACCESS FULL| IV_TABLE | 10000 | 712K| 47 (39)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("IV_TABLE"."FK_V_DATE"="FK_V_DATE" AND
"IV_TABLE"."FK_V_SER"="JOR"."FK_V_SER" AND
SIGN("IV_TABLE"."AMOUNT")=SIGN("JOR"."AMOUNT"))
4 - filter("JOR"."FK_MAJ_NO"=1 AND "JOR"."FK_ACT"=4)
5 - filter("TYPE_ID"=1)
21 rows selected.
Elapsed: 00:00:00.04
And this is when there are no indexes.
Add some constraints between tables or at least indexes on some of the columns and you could end up with a merge that does this.
22:07:28 SQL> @showplan11gshort
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1151325015
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 1 | 39 | 48 (40)| 00:00:01 |
| 1 | MERGE | IV_TABLE | | | | |
| 2 | VIEW | | | | | |
| 3 | NESTED LOOPS | | 1 | 147 | 48 (40)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | IV_TABLE | 10000 | 712K| 47 (39)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID| AC_JOR_TABLE | 1 | 74 | 0 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | SYS_C0015204 | 1 | | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("TYPE_ID"=1)
5 - filter("JOR"."FK_MAJ_NO"=1 AND "JOR"."FK_ACT"=4 AND
SIGN("IV_TABLE"."AMOUNT")=SIGN("JOR"."AMOUNT"))
6 - access("IV_TABLE"."FK_V_DATE"="FK_V_DATE" AND
"IV_TABLE"."FK_V_SER"="JOR"."FK_V_SER")
22 rows selected.
Elapsed: 00:00:00.03
Which just puts you back where you started, a NESTED LOOPS JOIN from outer to inner. Fun stuff eh?
This of course takes us back to what I said earlier, everything is a join. You can readily see this in the two variations of the MERGE query plans. In order to update table IV_TABLE you have to pair together a row from IV_TABLE with a row from AC_JOR_TABLE. That means a join which is almost always a choice between HASH JOIN or a NESTED LOOPS JOIN, and Oracle will decide which is best based on its CARDINALITY ESTIMATES and available access methods, since joins are semantically equivalent. This in turn supports the assertion that an UPDATE can use HASH JOIN too, we just don't have a test case in front of us to show it.
Wait, found one.
Following the example from the link one needs to consider this variation of the update which is identical to the merge in functionality:
22:37:16 SQL> explain plan for UPDATE iv_table CPA SET CPA.FK_JOURNAL_ENTRY =
22:37:25 2 (
22:37:25 3 SELECT JOR.JOURNAL_SERIAL FROM ac_jor_table JOR
22:37:25 4 WHERE CPA.fk_v_date = JOR.fk_v_date
22:37:25 5 AND CPA.fk_v_ser = JOR.fk_v_ser
22:37:25 6 AND JOR.fk_maj_no = 1
22:37:25 7 AND JOR.fk_act =4--
22:37:25 8 and sign(cpa.AMOUNT) = sign(jor.AMOUNT)
22:37:25 9 )
22:37:25 10 WHERE CPA.TYPE_ID = 1
22:37:25 11 and exists
22:37:25 12 (
22:37:25 13 SELECT JOR.JOURNAL_SERIAL FROM ac_jor_table JOR
22:37:25 14 WHERE CPA.fk_v_date = JOR.fk_v_date
22:37:25 15 AND CPA.fk_v_ser = JOR.fk_v_ser
22:37:25 16 AND JOR.fk_maj_no = 1
22:37:25 17 AND JOR.fk_act =4--
22:37:25 18 and sign(cpa.AMOUNT) = sign(jor.AMOUNT)
22:37:25 19 )
22:37:25 20 /
Explained.
Elapsed: 00:00:00.01
22:37:25 SQL>
22:37:25 SQL> @showplan11gshort
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------
Plan hash value: 3336212106
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 122 | 100 (42)| 00:00:02 |
| 1 | UPDATE | IV_TABLE | | | | |
|* 2 | HASH JOIN RIGHT SEMI| | 1 | 122 | 100 (42)| 00:00:02 |
|* 3 | TABLE ACCESS FULL | AC_JOR_TABLE | 100 | 6100 | 50 (42)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | IV_TABLE | 10000 | 595K| 49 (41)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | AC_JOR_TABLE | 1 | 74 | 50 (42)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CPA"."FK_V_DATE"="JOR"."FK_V_DATE" AND
"CPA"."FK_V_SER"="JOR"."FK_V_SER" AND SIGN("CPA"."AMOUNT")=SIGN("JOR"."AMOUNT"
))
3 - filter("JOR"."FK_MAJ_NO"=1 AND "JOR"."FK_ACT"=4)
4 - filter("CPA"."TYPE_ID"=1)
5 - filter("JOR"."FK_V_DATE"=:B1 AND "JOR"."FK_V_SER"=:B2 AND
"JOR"."FK_MAJ_NO"=1 AND "JOR"."FK_ACT"=4 AND SIGN("JOR"."AMOUNT")=SIGN(:B3))
23 rows selected.
From an optimization perspective, predicate line #5 is the most interesting since the row it seeks to find is the one it has just joined to (if any) and hence means there is no actual need to do this steps even though it is shown in the query plan. I wonder if Oracle knows this?, in which case we have achieved are result of using HASH JOIN without additional work. Indeed, this might even be a case where some kind of sub-query caching at the statement level (which oracle has been doing for years) would negate the cost of predicate #5 even if it is done. How can we test this?
Then again, I am still not satisfied since if a HASH JOIN RIGHT SEMI will do the job of the original update, why did Oracle not use it in the first place on the original update? Maybe stats?
So this is all cool stuff and suggests that eventually where we want to be is
1. have a fully defined data model so that constraints describe the data
2. have properly coded SQL
3. then let Oracle do the driving as it will decide what access and join strategies are best
Kevin
[Updated on: Thu, 05 February 2015 21:46] Report message to a moderator
|
|
|
Re: update statement takes too long [message #632916 is a reply to message #632804] |
Fri, 06 February 2015 20:47 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
Kevin Meade wrote on Fri, 06 February 2015 14:09Unless an update is guaranteed to have a match for every row it seeks to update, a merge cannot be substituted for an update, and in the case of this update, there is no constraint setup between these two tables that would enforce this requirement. Therefore, replacing this update with a merge is invalid and can lead to incorrect (or at least different (one has to question if the update is actually correct to set its target to null)) results.
Once again, untested code. But I think it should work. The OP would still need to address the possibility of duplicate rows on the join key in ac_jor_table.
MERGE INTO iv_table cpa
USING (
SELECT iv.ROWID AS ROW_ID, JOR.JOURNAL_SERIAL
FROM iv_table iv
LEFT JOIN ac_jor_table JOR
ON iv.fk_v_date = JOR.fk_v_date
AND iv.fk_v_ser = JOR.fk_v_ser
AND sign(iv.AMOUNT) = sign(jor.AMOUNT)
WHERE JOR.fk_maj_no = 1
AND JOR.fk_act = 4
AND iv.TYPE_ID = 1
) NEW
ON CPA.ROWID = NEW.ROW_ID
WHEN MATCHED THEN SET CPA.FK_JOURNAL_ENTRY = JOR.JOURNAL_SERIAL
Kevin Meade wrote on Fri, 06 February 2015 14:09
22:37:16 SQL> explain plan for UPDATE iv_table CPA SET CPA.FK_JOURNAL_ENTRY =
22:37:25 2 (
22:37:25 3 SELECT JOR.JOURNAL_SERIAL FROM ac_jor_table JOR
22:37:25 4 WHERE CPA.fk_v_date = JOR.fk_v_date
22:37:25 5 AND CPA.fk_v_ser = JOR.fk_v_ser
22:37:25 6 AND JOR.fk_maj_no = 1
22:37:25 7 AND JOR.fk_act =4--
22:37:25 8 and sign(cpa.AMOUNT) = sign(jor.AMOUNT)
22:37:25 9 )
22:37:25 10 WHERE CPA.TYPE_ID = 1
22:37:25 11 and exists
22:37:25 12 (
22:37:25 13 SELECT JOR.JOURNAL_SERIAL FROM ac_jor_table JOR
22:37:25 14 WHERE CPA.fk_v_date = JOR.fk_v_date
22:37:25 15 AND CPA.fk_v_ser = JOR.fk_v_ser
22:37:25 16 AND JOR.fk_maj_no = 1
22:37:25 17 AND JOR.fk_act =4--
22:37:25 18 and sign(cpa.AMOUNT) = sign(jor.AMOUNT)
22:37:25 19 )
22:37:25 20 /
Explained.
Elapsed: 00:00:00.01
22:37:25 SQL>
22:37:25 SQL> @showplan11gshort
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------
Plan hash value: 3336212106
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 122 | 100 (42)| 00:00:02 |
| 1 | UPDATE | IV_TABLE | | | | |
|* 2 | HASH JOIN RIGHT SEMI| | 1 | 122 | 100 (42)| 00:00:02 |
|* 3 | TABLE ACCESS FULL | AC_JOR_TABLE | 100 | 6100 | 50 (42)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | IV_TABLE | 10000 | 595K| 49 (41)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | AC_JOR_TABLE | 1 | 74 | 50 (42)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CPA"."FK_V_DATE"="JOR"."FK_V_DATE" AND
"CPA"."FK_V_SER"="JOR"."FK_V_SER" AND SIGN("CPA"."AMOUNT")=SIGN("JOR"."AMOUNT"
))
3 - filter("JOR"."FK_MAJ_NO"=1 AND "JOR"."FK_ACT"=4)
4 - filter("CPA"."TYPE_ID"=1)
5 - filter("JOR"."FK_V_DATE"=:B1 AND "JOR"."FK_V_SER"=:B2 AND
"JOR"."FK_MAJ_NO"=1 AND "JOR"."FK_ACT"=4 AND SIGN("JOR"."AMOUNT")=SIGN(:B3))
23 rows selected.
I believe that this plan is hash joining at steps 3 and 4, but nesting to Line 5. Since Line 5 is a FULL scan, it would be pretty easy to test (though I am not going to) and demonstrate the results with TKPROF. In my experience, UPDATE SET will ALWAYS nest. I would be delighted to be proven wrong, though it would also mean I need to go back and edit some of my old articles.
|
|
|
Re: update statement takes too long [message #632929 is a reply to message #632916] |
Sat, 07 February 2015 09:20 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/56289.jpg) |
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
NO I am pretty sure you are right Ross. The documentation suggests that the hash join is reserved for the CORRECLATED SUBQUERY and its variations. I was merely pointing out two possibilities:
1. it can always change in the future. There is no real reason why the update could not use hash join too.
2. these are duplicate operations and so there may be opportunity internally for query execution to skip the second one using sub-query caching results.
As you point out, neither of these have I tested. It was fun to explore possibilities.
Kevin
|
|
|