6 Replies Latest reply: Mar 30, 2007 9:56 AM by ushitaki RSS

    merge vs update statement

    ushitaki
      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
          Rob van Wijk
          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
            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
              Rob van Wijk
              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
                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
                  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
                    <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
                    ;