- 3,733,706 Users
- 2,246,810 Discussions
- 7,856,852 Comments
Forum Stats
Discussions
Howdy, Stranger!
Categories
- 380.9K All Categories
- 2.1K Data
- 203 Big Data Appliance
- 1.9K Data Science
- 446.1K Databases
- 220.4K General Database Discussions
- 3.7K Java and JavaScript in the Database
- 22 Multilingual Engine
- 506 MySQL Community Space
- 459 NoSQL Database
- 7.7K Oracle Database Express Edition (XE)
- 2.8K ORDS, SODA & JSON in the Database
- 438 SQLcl
- 3.9K SQL Developer Data Modeler
- 185.4K SQL & PL/SQL
- 20.8K SQL Developer
- 291.3K Development
- 6 Developer Projects
- 117 Programming Languages
- 288K Development Tools
- 96 DevOps
- 3K QA/Testing
- 645.2K Java
- 18 Java Learning Subscription
- 36.9K Database Connectivity
- 148 Java Community Process
- 104 Java 25
- 22.1K Java APIs
- 137.7K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 12 Java Essentials
- 138 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 195 Java User Groups
- 22 JavaScript - Nashorn
- Programs
- 177 LiveLabs
- 34 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 165 Deutsche Oracle Community
- 1.2K Español
- 1.9K Japanese
- 225 Portuguese
merge vs update statement
ushitaki
Member Posts: 1,128
I try to tune SQL in another [url http://forums.oracle.com/forums/message.jspa?messageID=1762095#1762095]thread.
And then I find out one of difference between 'merge' and 'update'
that is number of execution.
Update : Rows * Rows
Merge : Rows
Can we tune my update statement up to near merge?
Here is tkprof results.
There are only 300 rows in mytable for test.
/**** UPDATE ****/
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 27 (USHI) (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=3 pr=0 pw=0 time=125 us)
300 TABLE ACCESS FULL MYTABLE (cr=3 pr=0 pw=0 time=338 us)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: HINT: ALL_ROWS
1 SORT (AGGREGATE)
300 TABLE ACCESS (FULL) OF 'MYTABLE' (TABLE)
********************************************************************************
update mytable t1
set flag =
(select 1+rn-last_value(rn2 ignore nulls) over (order by id) new_flag
from
(select
rowid row_id
,id
,row_number() over (order by id) rn
,case when col1 = lag(col1) over (order by id)
and col2 = lag(col2) over (order by id)
and col3 = lag(col3) over (order by id)
then null
else row_number() over (order by id)
end rn2
from mytable
) t2
where t2.row_id = t1.rowid
)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 2 0 0
Execute 1 0.50 0.50 0 903 606 300
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.50 0.50 0 905 606 300
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 27 (USHI)
Rows Row Source Operation
------- ---------------------------------------------------
0 UPDATE MYTABLE (cr=903 pr=0 pw=0 time=502019 us)
300 TABLE ACCESS FULL MYTABLE (cr=3 pr=0 pw=0 time=635 us)
300 WINDOW BUFFER (cr=900 pr=0 pw=0 time=494110 us)
300 VIEW (cr=900 pr=0 pw=0 time=488138 us)
90000 WINDOW SORT (cr=900 pr=0 pw=0 time=431450 us)
90000 TABLE ACCESS FULL MYTABLE (cr=900 pr=0 pw=0 time=94585 us)
Rows Execution Plan
------- ---------------------------------------------------
0 UPDATE STATEMENT MODE: ALL_ROWS
0 UPDATE OF 'MYTABLE'
300 TABLE ACCESS (FULL) OF 'MYTABLE' (TABLE)
300 WINDOW (BUFFER)
300 VIEW
90000 WINDOW (SORT)
90000 TABLE ACCESS (FULL) OF 'MYTABLE' (TABLE)
********************************************************************************
/**** MERGE ****/
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 27 (USHI) (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=3 pr=0 pw=0 time=113 us)
300 TABLE ACCESS FULL MYTABLE (cr=3 pr=0 pw=0 time=328 us)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: HINT: ALL_ROWS
1 SORT (AGGREGATE)
300 TABLE ACCESS (FULL) OF 'MYTABLE' (TABLE)
********************************************************************************
merge into mytable t1
using
(select row_id, 1+rn-last_value(rn2 ignore nulls) over (order by id) new_flag
from
(select
rowid row_id
,id
,row_number() over (order by id) rn
,case when col1 = lag(col1) over (order by id)
and col2 = lag(col2) over (order by id)
and col3 = lag(col3) over (order by id)
then null
else row_number() over (order by id)
end rn2
from mytable
)
) t2
on (t2.row_id = t1.rowid)
when matched then
update set flag = new_flag
when not matched then
insert values (null,null,null,null,null)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.02 0 4 0 0
Execute 1 0.00 0.00 0 6 308 300
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.03 0.03 0 10 308 300
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 27 (USHI)
Rows Row Source Operation
------- ---------------------------------------------------
2 MERGE MYTABLE (cr=6 pr=0 pw=0 time=9505 us)
300 VIEW (cr=6 pr=0 pw=0 time=6530 us)
300 HASH JOIN OUTER (cr=6 pr=0 pw=0 time=5930 us)
300 VIEW (cr=3 pr=0 pw=0 time=4164 us)
300 WINDOW BUFFER (cr=3 pr=0 pw=0 time=3557 us)
300 VIEW (cr=3 pr=0 pw=0 time=3284 us)
300 WINDOW SORT (cr=3 pr=0 pw=0 time=2080 us)
300 TABLE ACCESS FULL MYTABLE (cr=3 pr=0 pw=0 time=346 us)
300 TABLE ACCESS FULL MYTABLE (cr=3 pr=0 pw=0 time=352 us)
********************************************************************************
Comments
-
This type of query is better handled with a merge instead of an update, because the inner view (the one starting with (select rowid row_id ...) is not mergeable because of the use of the analytic functions.
That's why the update has to compute the entire result set for each row to be updated.
So no, I cannot make this query more efficient using an update statement. This will only work if the inner query can be merged.
You can see this behaviour in my test as well, where the addition of the rownum in the inner query prevents the merging:********************************************************************************
Regards,
update mytable t1
set filler =
( select to_char(rownum)
from mytable t2
where t2.rowid = t1.rowid
)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.02 0.02 0 307 319 300
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.03 0.02 0 307 319 300
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 9833
Rows Row Source Operation
------- ---------------------------------------------------
0 UPDATE
300 TABLE ACCESS FULL MYTABLE
300 COUNT
300 TABLE ACCESS BY USER ROWID MYTABLE
********************************************************************************
merge into mytable t1
using ( select to_char(rownum) new_filler
, rowid rid
from mytable
) t2
on (t1.rowid = t2.rid)
when matched then
update set filler = new_filler
when not matched then
insert values (null,null)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.02 0.01 0 14 306 300
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.02 0.02 0 14 306 300
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 9833
Rows Row Source Operation
------- ---------------------------------------------------
2 MERGE
300 VIEW
300 HASH JOIN OUTER
300 VIEW
300 COUNT
300 TABLE ACCESS FULL MYTABLE
300 TABLE ACCESS FULL MYTABLE
********************************************************************************
update mytable t1
set filler =
( select to_char(r)
from ( select rowid rid
, rownum r
from mytable
) t2
where t2.rid = t1.rowid
)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.12 0.14 0 2107 308 300
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.13 0.14 0 2107 308 300
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 9833
Rows Row Source Operation
------- ---------------------------------------------------
0 UPDATE
300 TABLE ACCESS FULL MYTABLE
300 VIEW
90000 COUNT
90000 TABLE ACCESS FULL MYTABLE
********************************************************************************
merge into mytable t1
using ( select to_char(r) new_filler
, rid
from ( select rowid rid
, rownum r
from mytable
)
) t2
on (t1.rowid = t2.rid)
when matched then
update set filler = new_filler
when not matched then
insert values (null,null)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.02 0 0 0 0
Execute 1 0.02 0.02 0 14 306 300
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.05 0.05 0 14 306 300
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 9833
Rows Row Source Operation
------- ---------------------------------------------------
2 MERGE
300 VIEW
300 HASH JOIN OUTER
300 VIEW
300 COUNT
300 TABLE ACCESS FULL MYTABLE
300 TABLE ACCESS FULL MYTABLE
********************************************************************************
Rob. -
Rob van Wijk,
Thank you very much for your clear explanations.
I understand that
even if those syntax is like similarity,
the meaning and the evaluation-order of those subqueries is different.
If Oracle did permit the analysis function and order-by-clause in updatable subquery,
it might become being able to update with once scaning. -
It's the same process as takes place when hard parsing a query. When a query is not found in the library cache, Oracle starts optimizing it and generates a plan.
One of the steps that takes place during optimization, is view merging and predicate pushing, as can be read [url http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14211/optimops.htm#sthref1246]here.
There are several constructs that prevent these optimizations from happening: union, rownum, analytic function (not always).
It is prevented when it would change the semantics of the query. Sometimes the queries look equivalent, but when
you start to think about it, they really are not.
An example:SQL> explain plan
So, instead of first computing the join from emp and dept, the predicate "empno = 7839" is pushed inside the view.
2 for
3 select *
4 from ( select emp.empno
5 , emp.ename
6 , dept.dname
7 from emp
8 , dept
9 where emp.deptno = dept.deptno
10 )
11 where empno = 7839
12 /
Uitleg is gegeven.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 36 | 3 (34)|
| 1 | NESTED LOOPS | | 1 | 36 | 3 (34)|
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 24 | 2 (50)|
|* 3 | INDEX UNIQUE SCAN | EMP_PK | 1 | | 2 (50)|
| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 12 | 2 (50)|
|* 5 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EMP"."EMPNO"=7839)
5 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
17 rijen zijn geselecteerd.
It allows a faster access path and it is semantically equivalent to the following query:SQL> explain plan
But sometimes the queries may look equivalent, they really are not as can be seen in the next example:
2 for
3 select emp.empno
4 , emp.ename
5 , dept.dname
6 from emp
7 , dept
8 where emp.deptno = dept.deptno
9 and emp.empno = 7839
10 /
Uitleg is gegeven.
SQL>
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 36 | 3 (34)|
| 1 | NESTED LOOPS | | 1 | 36 | 3 (34)|
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 24 | 2 (50)|
|* 3 | INDEX UNIQUE SCAN | EMP_PK | 1 | | 2 (50)|
| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 12 | 2 (50)|
|* 5 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EMP"."EMPNO"=7839)
5 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
17 rijen zijn geselecteerd.SQL> explain plan
The only difference is the addition of a rownum to the inner select.
2 for
3 select *
4 from ( select emp.empno
5 , emp.ename
6 , dept.dname
7 , rownum
8 from emp
9 , dept
10 where emp.deptno = dept.deptno
11 )
12 where empno = 7839
13 /
Uitleg is gegeven.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 588 | 6 (34)|
|* 1 | VIEW | | 14 | 588 | |
| 2 | COUNT | | | | |
|* 3 | HASH JOIN | | 14 | 504 | 6 (34)|
| 4 | TABLE ACCESS FULL| DEPT | 4 | 48 | 3 (34)|
| 5 | TABLE ACCESS FULL| EMP | 14 | 336 | 3 (34)|
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("from$_subquery$_001"."EMPNO"=7839)
3 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
17 rijen zijn geselecteerd.
Big deal, you'd say, why didn't Oracle optimize this one the same way it did the first query?
Because it changed the semantics of the query:SQL> select *
Just look at the rownum column.
2 from ( select emp.empno
3 , emp.ename
4 , dept.dname
5 , rownum
6 from emp
7 , dept
8 where emp.deptno = dept.deptno
9 )
10 where empno = 7839
11 /
EMPNO ENAME DNAME ROWNUM
-------------------------------------- ---------- -------------- --------------------------------------
7839 KING ACCOUNTING 9
1 rij is geselecteerd.
SQL> pause
SQL> select emp.empno
2 , emp.ename
3 , dept.dname
4 , rownum
5 from emp
6 , dept
7 where emp.deptno = dept.deptno
8 and emp.empno = 7839
9 /
EMPNO ENAME DNAME ROWNUM
-------------------------------------- ---------- -------------- --------------------------------------
7839 KING ACCOUNTING 1
1 rij is geselecteerd.
Hope this example helps.
Regards,
Rob. -
Well it is possible by UPDATE of view but it is not immediately obvious.
Assume we have an analytic UPDATE requirement, e.g.
Oracle Database 10g Express Edition Release 10.2.0.1.0 - ProductionSQL> SELECT e.ename, e.sal, 2 DENSE_RANK () OVER ( 3 ORDER BY e.sal) sal_rank 4 FROM emp e; ENAME SAL SAL_RANK ---------- ---------- ---------- JAMES 1150 1 ADAMS 1300 2 WARD 1450 3 MARTIN 1450 3 TURNER 1700 4 ALLEN 1800 5 MILLER 2000 6 CLARK 2650 7 SMITH 3000 8 BLAKE 3050 9 JONES 3175 10 FORD 3200 11 SCOTT 3200 11 KING 5200 12 14 rows selected. SQL>
We are not permitted (as has been observed) to directly UPDATE the view, e.g.SQL> ALTER TABLE emp ADD (sal_rank NUMBER); Table altered. SQL> UPDATE (SELECT e.sal_rank, 2 DENSE_RANK () OVER ( 3 PARTITION BY e.deptno 4 ORDER BY e.sal) new_sal_rank 5 FROM emp e) 6 SET sal_rank = new_sal_rank; UPDATE (SELECT e.sal_rank, * ERROR at line 1: ORA-01732: data manipulation operation not legal on this view SQL>Neither are we allowed to UPDATE the base table joined to the view, but notice that we are now prevented by key-preservation restriction.SQL> UPDATE ( 2 SELECT a.sal_rank, b.new_sal_rank 3 FROM emp a, 4 (SELECT b.empno, 5 DENSE_RANK () OVER ( 6 PARTITION BY b.deptno 7 ORDER BY b.sal) new_sal_rank 8 FROM emp b) b 9 WHERE b.empno = a.empno) 10 SET sal_rank = new_sal_rank; SET sal_rank = new_sal_rank * ERROR at line 10: ORA-01779: cannot modify a column which maps to a non key-preserved table SQL>The latter being a restriction we can workaround with the 'lightly' documented BYPASS_UJVC hint.SQL> UPDATE /*+ BYPASS_UJVC */ ( 2 SELECT a.sal_rank, b.new_sal_rank 3 FROM emp a, 4 (SELECT b.empno, 5 DENSE_RANK () OVER ( 6 PARTITION BY b.deptno 7 ORDER BY b.sal) new_sal_rank 8 FROM emp b) b 9 WHERE b.empno = a.empno) 10 SET sal_rank = new_sal_rank; 14 rows updated. SQL>
Note that 1.) this may be less efficient than MERGE because of the additional join and 2.) I am not entirely sure that this is not a bug and 3.) I am not recommending you use this over MERGE if you have required Oracle version. -
Rob,
Thank you for useful additional information.
But no need to care about those for only me.
However, in the measurement of my reports, I forgot considering it.
I had never flushed cache then.
Thanks a lot. -
<s></s>
James
That's very good information!
I study detail later.
But this query works with good performance.
Thanks a lot.
update /*+ BYPASS_UJVC */
(select t1.flag, t2.new_flag
from mytable t1,
(select row_id, 1+rn-last_value(rn2 ignore nulls) over (order by id) new_flag
from
(select
rowid row_id
,id
,row_number() over (order by id) rn
,case when col1 = lag(col1) over (order by id)
and col2 = lag(col2) over (order by id)
and col3 = lag(col3) over (order by id)
then null
else row_number() over (order by id)
end rn2
from mytable
)
) t2
where t2.row_id = t1.rowid
)
set flag = new_flag
;
This discussion has been closed.