Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

merge vs update statement

ushitakiMar 30 2007 — edited Mar 30 2007
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

Christian.Shay -Oracle

I'm having trouble reproducing the autocommit issue. Did you see the words "Commit complete" in the results window after you executed the SQL?
Maybe you can help me reproduce this. Could you please provide me with some SQL that reproduces on your machine including any CREATE statements needed?
As for the connections open issue, we can't reproduce that either. Could you please execute:
 select count(*) from v$session where username = 'yourusername';
And then let me know how many VS Code tabs associated with our extensipon are open on your machine and how many connection nodes are in Oracle Explorer at the time you run that.

MagnusMG

I will try to collect this information.

MagnusMG

I have not had problems with failing autocommit again, so we can close this, at least for now.
VSCode still leaves a lot of connections open. When I executed your statement I got a count of 8 connections, even though I only had one tab open, and one database connected in the Oracle Explorer pane. (This is a bit annoying sometimes, but not a big problem.)
Thank you.

Thanks! We are considering a connection sharing feature for a future release for those customers with a limited number of connections available to them. In the meantime, if you run into issues with too many connections despite tabs being closed, you should try to right click on the connection in database explorer and select "Disconnect".
If that does not solve the problem, you could also try to close and reopen VS code itself. Let me know if any of that helps.

MagnusMG

The problem reappeared today. The message in VSC when I issue a commit is 'Commit complete.'
I have 5 connections open according to V$SESSION, 2 tabs open in VSC and 1 database connection open in VSC Oracle Explorer. (So, 2 connections that seems unnecessary.)
It is not easy to reproduce, and there are many SQL statements behind this. In this particular case I first create a table, fill it with data, and then add more data using three MERGE statements. When I checked the table in SQL Developer, the merge statements had had not effect. After I issued a commit, I could see all the changes in SQL Developer.
I tried a simple repeat of this structure using the same tab in VSC:
CREATE TABLE FOO (
a NUMBER NULL,
b VARCHAR2(50) NULL
);
Issuing this I got the response 'table created' and 'commit complete'. I checked in SQL Developer and could see thet table FOO there.
So I inserted som data:
INSERT INTO FOO VALUES (1, 'aaa');
Again, I got the message data inserted and commit complete. I could see the data in SQL Developer.
Then I tried a merge:
MERGE INTO FOO
USING (
SELECT 1 AS a, 'bbb' AS b FROM dual
) SRC ON (FOO.a=SRC.a)
WHEN MATCHED THEN UPDATE SET
FOO.b=SRC.b
;
I got the response '1 row merged', but no 'commit complete'. I could not see the change in SQL Developer.
I issued a commit in VSC, got 'commit complete', and could see the change in SQL Developer.

Is there a problem with MERGE statements?

Christian.Shay -Oracle

Yes, this is a bug. I will report it. Thanks for tracking this down! Please let me know if you come across anything else that causes autocomplete to fail.

Christian.Shay -Oracle

Hi, I forgot to address the connection count issue you raised:
If the two tabs you mention are files (sql scripts) then a count of 5 in V$SESSION is expected, because we use 2 connections per file (one for the script, and one to do autocomplete/intellisense), plus the one connection in Oracle Database explorer. Please let me know if the two tabs are sql script files.
If a tab is opened via an object in Oracle Explorer, eg, "Open Package Body", then we use two connections total, since we share a connection for all code objects to do autocomplete/intellisense. Eg, if you had 10 code objects open for the same connection in DB Explorer, we would still only use 2 connections total.
In the next release we hope to use connection sharing for files as well so you should see your connection count cut nearly in half.
Thanks,
Christian

MagnusMG

Hi,
Yes , they were SQL script files.
Glad I could help with tracking a bug down! I should also say that since I now know that the message 'commit complete' is crucial and not always there, it is quite easy to keep an eye out for it. I doubt that counts as a work-around, but still...
Thanks,
MagnusMG

We have reduced the connection usage in version 21.5 (available now) and are looking into reducing them further in future releases.

We have reduced connection usage even further in version 21.6. Please give it a try and let us know what you think.

1 - 10
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Apr 27 2007
Added on Mar 30 2007
6 comments
21,317 views