Home » SQL & PL/SQL » SQL & PL/SQL » Question on how Merge works with a comibination of Update and Delete (Oracle 10g, Hp-UX)
Question on how Merge works with a comibination of Update and Delete [message #327989] Wed, 18 June 2008 09:33 Go to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I have tried my level best to explain it. If you have any questions or if you feel I have not given enough information please do let me know.

Thanks for reading my question.

Here it goes.

SQL> drop table test;

Table dropped.

SQL> create table test (sno number, date_val date, val1 number, val2 number, val3 number) nologging;

Table created.

SQL> insert into test values (1, to_date('01.04.2008','dd.mm.yyyy'),0,0,10);

1 row created.

SQL> insert into test values (1, to_date('01.03.2008','dd.mm.yyyy'),1,1,10);

1 row created.

SQL> insert into test values (2, to_date('01.04.2008','dd.mm.yyyy'),1,0,10);

1 row created.

SQL> insert into test values (2, to_date('01.02.2008','dd.mm.yyyy'),0,2,10);

1 row created.

SQL> commit;

Commit complete.


SQL> select * from test;

       SNO date_val                 VAL1       VAL2       VAL3
---------- ------------------ ---------- ---------- ----------
         1 01-APR-08                   0          0         10
         1 01-MAR-08                   1          1         10
         2 01-APR-08                   1          0         10
         2 01-FEB-08                   0          2         10


It is a very simple problem.

I want to update/merge the record sno-wise to the minimum date_val and
delete the record which is having the latest date_val.
You can assume that sno will always occur only in pair and they will have different dates.

So after update/delete my final output should be something like this.
SQL> select * from test;

       SNO date_val                 VAL1       VAL2       VAL3
---------- ------------------ ---------- ---------- ----------
         1 01-MAR-08                   1          1         20
         2 01-FEB-08                   1          2         20


Initially I thought it is a straight forward merge. So I tried the following
SQL> l
  1  merge into test t
  2  using (select sno, min(conn_dte) min_conn_date, max(conn_dte) max_conn_date,
  3                     sum(val1) val_1, sum(val2) val_2, sum(val3) val_3 from
  4         test
  5         group by sno
  6        ) t1
  7  on (t.sno = t1.sno)
  8  when matched
  9  then
 10    update set val1 = t1.val_1, val2 = t1.val_2, val3 = t1.val_3
 11    where conn_dte = min_conn_date
 12*   delete where conn_dte = max_conn_date and conn_dte != min_conn_date
SQL> /

2 rows merged.

SQL> select * from test;

       SNO CONN_DTE                 VAL1       VAL2       VAL3
---------- ------------------ ---------- ---------- ----------
         1 01-APR-08                   0          0         10 *****
         1 01-MAR-08                   1          1         20  
         2 01-APR-08                   1          0         10 *****
         2 01-FEB-08                   1          2         20  


To my surprise update has worked fine but I was struggling to understand why the delete was not working.

I slightly modified the merge and it worked. But I am still puzzled why earlier version of merge did not work.
SQL> l
  1  merge into test t
  2  using (select sno, min(conn_dte) min_conn_date, max(conn_dte) max_conn_date,
  3                     sum(val1) val_1, sum(val2) val_2, sum(val3) val_3 from
  4         test
  5         group by sno
  6        ) t1
  7  on (t.sno = t1.sno)
  8  when matched
  9  then
 10    update set val1 = t1.val_1, val2 = t1.val_2, val3 = t1.val_3
 11*   delete where conn_dte = max_conn_date 
SQL> /

4 rows merged.

SQL> select * from test;

       SNO CONN_DTE                 VAL1       VAL2       VAL3
---------- ------------------ ---------- ---------- ----------
         1 01-MAR-08                   1          1         20
         2 01-FEB-08                   1          2         20

I checked the documentation just to make sure are we allowed to use the where clause only after the last dml statement. But that's not the clause. As the document clearly states we can have individual where clause against respective DML. Either documentation is not correct or I have mis-understood merge.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/img_text/merge_update_clause.htm

Any thoughts ?

Regards

Raj
Re: Question on how Merge works with a comibination of Update and Delete [message #328001 is a reply to message #327989] Wed, 18 June 2008 10:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Any thoughts ?

Why are you using NOLOGGING clause?
What is your version with 4 decimals?

Regards
Michel
Re: Question on how Merge works with a comibination of Update and Delete [message #328005 is a reply to message #328001] Wed, 18 June 2008 10:29 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Apologies Michael. I thought I had posted the version. Sorry about that.
About nologging that's what we were advised. If we were not using it then we will be told off. I am guessing it's because of the volume we process everyday. Almost 99% of our jobs are re-startable from the beginning so they thought there is no necessity for point in time recovery. Becuase of that it has become more or less like a habit for me to use nologging in all my create table script. Anyways...
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
CORE    10.2.0.2.0      Production
TNS for HPUX: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production

Regards

Raj
Re: Question on how Merge works with a comibination of Update and Delete [message #328013 is a reply to message #328005] Wed, 18 June 2008 10:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe I miss something but here's what I get:
SQL> merge into test t
  2  using (select sno, min(conn_dte) min_conn_date, max(conn_dte) max_conn_date,
  3                     sum(val1) val_1, sum(val2) val_2, sum(val3) val_3 from
  4         test
  5         group by sno
  6        ) t1
  7  on (t.sno = t1.sno)
  8  when matched
  9  then
 10    update set val1 = t1.val_1, val2 = t1.val_2, val3 = t1.val_3
 11    where conn_dte = min_conn_date
 12    delete where conn_dte = max_conn_date and conn_dte != min_conn_date
 13  /
using (select sno, min(conn_dte) min_conn_date, max(conn_dte) max_conn_date,
                                                    *
ERROR at line 2:
ORA-00904: "CONN_DTE": invalid identifier


SQL> merge into test t
  2  using (select sno, min(conn_dte) min_conn_date, max(conn_dte) max_conn_date,
  3                     sum(val1) val_1, sum(val2) val_2, sum(val3) val_3 from
  4         test
  5         group by sno
  6        ) t1
  7  on (t.sno = t1.sno)
  8  when matched
  9  then
 10    update set val1 = t1.val_1, val2 = t1.val_2, val3 = t1.val_3
 11    delete where conn_dte = max_conn_date 
 12  /
using (select sno, min(conn_dte) min_conn_date, max(conn_dte) max_conn_date,
                                                    *
ERROR at line 2:
ORA-00904: "CONN_DTE": invalid identifier

Regards
Michel
Re: Question on how Merge works with a comibination of Update and Delete [message #328016 is a reply to message #328013] Wed, 18 June 2008 10:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ok, should add:
SQL> alter table test rename column  date_val to conn_dte;

Table altered.

The problem is not in the where clause of delete but in the where clause of update.
In the first query you first limit the update to "conn_dte = min_conn_date" rows, the delete can only happen to these rows.
In the second query you don't limit the update, so the delete happens to all rows.
This can be seen in SQL*Plus feedback:
SQL> merge into test t
  2  using (select sno, min(conn_dte) min_conn_date, max(conn_dte) max_conn_date,
  3                     sum(val1) val_1, sum(val2) val_2, sum(val3) val_3 from
  4         test
  5         group by sno
  6        ) t1
  7  on (t.sno = t1.sno)
  8  when matched
  9  then
 10    update set val1 = t1.val_1, val2 = t1.val_2, val3 = t1.val_3
 11    where conn_dte = min_conn_date
 12    delete where conn_dte = max_conn_date and conn_dte != min_conn_date
 13  /

2 rows merged.

SQL> rollback;

Rollback complete.

SQL> merge into test t
  2  using (select sno, min(conn_dte) min_conn_date, max(conn_dte) max_conn_date,
  3                     sum(val1) val_1, sum(val2) val_2, sum(val3) val_3 from
  4         test
  5         group by sno
  6        ) t1
  7  on (t.sno = t1.sno)
  8  when matched
  9  then
 10    update set val1 = t1.val_1, val2 = t1.val_2, val3 = t1.val_3
 11    delete where conn_dte = max_conn_date 
 12  /

4 rows merged.

The first query only merges 2 rows when the second one merges all rows.

If you apply the same to the first query you get what you want:
SQL> rollback;

Rollback complete.

SQL> merge into test t
  2  using (select sno, min(conn_dte) min_conn_date, max(conn_dte) max_conn_date,
  3                     sum(val1) val_1, sum(val2) val_2, sum(val3) val_3 from
  4         test
  5         group by sno
  6        ) t1
  7  on (t.sno = t1.sno)
  8  when matched
  9  then
 10    update set val1 = t1.val_1, val2 = t1.val_2, val3 = t1.val_3
 11    delete where conn_dte = max_conn_date and conn_dte != min_conn_date
 12  /

4 rows merged.

SQL> select * from test;
       SNO CONN_DTE          VAL1       VAL2       VAL3
---------- ----------- ---------- ---------- ----------
         1 01-MAR-2008          1          1         20
         2 01-FEB-2008          1          2         20

2 rows selected.


Regards
Michel

Re: Question on how Merge works with a comibination of Update and Delete [message #328018 is a reply to message #328013] Wed, 18 June 2008 11:05 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Thanks for correcting the table definition. Plan information for the merge statement.
SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1286957647

-------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | MERGE STATEMENT        |      |     8 |  1016 |     8  (25)| 00:00:01 |
|   1 |  MERGE                 | TEST |       |       |            |          |
|   2 |   VIEW                 |      |       |       |            |          |
|*  3 |    HASH JOIN           |      |     8 |  1144 |     8  (25)| 00:00:01 |
|   4 |     VIEW               |      |     4 |   280 |     4  (25)| 00:00:01 |
|   5 |      SORT GROUP BY     |      |     4 |   244 |     4  (25)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   6 |       TABLE ACCESS FULL| TEST |     4 |   244 |     3   (0)| 00:00:01 |
|   7 |     TABLE ACCESS FULL  | TEST |     4 |   292 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("T"."SNO"="T1"."SNO")

Note
-----

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   - dynamic sampling used for this statement

23 rows selected.

Elapsed: 00:00:00.23

As you can see in the explain plan it is not showing the filtering happening inside the merge.
Quote:
In the first query you first limit the update to "conn_dte = min_conn_date" rows, the delete can only happen to these rows.

I was always under the impression that update and delete can be independent of each other. The bit which I am not able to understand why they have to be dependent ? Since the row is not getting updated because of the where condition it cannot be selected for deletion. Is it ?

Regards

Raj

[Updated on: Wed, 18 June 2008 11:14]

Report message to a moderator

Re: Question on how Merge works with a comibination of Update and Delete [message #328029 is a reply to message #328018] Wed, 18 June 2008 11:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
They are not independent, you can only delete the rows you update.

From MERGE page in SQL Reference:
Quote:
Specify the DELETE where_clause to clean up data in a table while populating or updating it. The only rows affected by this clause are those rows in the destination table that are updated by the merge operation.


Regards
Michel
Re: Question on how Merge works with a comibination of Update and Delete [message #328030 is a reply to message #328029] Wed, 18 June 2008 11:36 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Thanks for that. I missed it completely. Smile

Regards

Raj
Previous Topic: How to delete rows from parent table without scanning child tables?
Next Topic: Transaction Count on a Database for a given day
Goto Forum:
  


Current Time: Fri Dec 09 07:52:11 CST 2016

Total time taken to generate the page: 0.07627 seconds