This discussion is archived
6 Replies Latest reply: Mar 30, 2007 7:56 AM by ushitaki RSS

merge vs update statement

ushitaki Newbie
Currently Being Moderated
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)

********************************************************************************
  • 1. Re: merge vs update statement
    RobvanWijk Oracle ACE
    Currently Being Moderated
    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:
    ********************************************************************************

    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

    ********************************************************************************
    Regards,
    Rob.
  • 2. Re: merge vs update statement
    ushitaki Newbie
    Currently Being Moderated
    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.
  • 3. Re: merge vs update statement
    RobvanWijk Oracle ACE
    Currently Being Moderated
    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
      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.
    So, instead of first computing the join from emp and dept, the predicate "empno = 7839" is pushed inside the view.
    It allows a faster access path and it is semantically equivalent to the following query:
    SQL> explain plan
      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.
    But sometimes the queries may look equivalent, they really are not as can be seen in the next example:
    SQL> explain plan
      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.
    The only difference is the addition of a rownum to the inner select.
    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 *
      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.
    Just look at the rownum column.

    Hope this example helps.

    Regards,
    Rob.
  • 4. Re: merge vs update statement
    94799 Explorer
    Currently Being Moderated
    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 - Production
    SQL> 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.
  • 5. Re: merge vs update statement
    ushitaki Newbie
    Currently Being Moderated
    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.
  • 6. Re: merge vs update statement
    ushitaki Newbie
    Currently Being Moderated
    <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
    ;