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)
********************************************************************************