Help with merge 1

[dbomrrsm]

DDL:

CREATE TABLE table_a ( pid VARCHAR2(13), d_code VARCHAR2(3), s_date DATE);

CREATE TABLE table_b (pid VARCHAR2(13), d_code VARCHAR2(3), s_date DATE , s_code VARCHAR2(3),m_s VARCHAR2(1));

Script:

insert into table_b (pid,d_code,s_date,s_code,m_s) values('dave_bell_upi','001',sysdate,'158','P');

MERGE INTO table_a a
   USING (SELECT b.pid, b.d_code, b.s_date, b.s_code
            FROM table_b b
           WHERE b.m_s = 'P' AND b.s_code IN ('158', '160')) x
   ON (a.pid = x.pid)
   WHEN NOT MATCHED THEN
      INSERT (a.pid, a.d_code, a.s_date)
      VALUES (x.pid, x.d_code, x.s_date)
         WHERE x.s_code = '158'
   WHEN MATCHED THEN
      UPDATE
         SET a.d_code = x.d_code, a.s_date = x.s_date
         WHERE x.s_code = '158'
      DELETE
         WHERE (x.s_code = '160');
         
delete from table_b;
commit;

The insert at the top of the script puts a 158 s_code into table_b that is then merged into table_a as expected. However, if I then change the s_code to 160 and reprocess I was expecting to see the initially inserted row deleted by the delete clause at the end of the script.

However, the only way I can get the delete to work and also update if processing another 158 is by changing the scrip as follows:

insert into table_b (pid,d_code,s_date,s_code,m_s) values('dave_bell_upi','001',sysdate,'160','P');

MERGE INTO table_a a
   USING (SELECT b.pid, b.d_code, b.s_date, b.s_code
            FROM table_b b
           WHERE b.m_s = 'P' AND b.s_code IN ('158', '160')) x
   ON (a.pid = x.pid)
   WHEN NOT MATCHED THEN
      INSERT (a.pid, a.d_code, a.s_date)
      VALUES (x.pid, x.d_code, x.s_date)
         WHERE x.s_code = '158'
   WHEN MATCHED THEN
      UPDATE
         SET a.d_code = x.d_code, a.s_date = x.s_date
         WHERE x.s_code in ( '158','160')
      DELETE
         WHERE (x.s_code = '160');
         
delete from table_b;
commit;

The original script reads to me:

If it is 158 and doesn't exist insert it
If its 158 and it does exist update it
If its 160 delete it

However, the merge doesn't seem to be working in this way !!!

Can anyone explain what I am doing wrong.

Many thanks


[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[Dagon]

I think the purpose of the delete clause is to remove some of the rows you are updating if they go beyond a certain threshold. You can't use it to remove rows that you are not updating, which is what you are trying to do in your first merge.

 

[dbomrrsm]

Dagon

Thanks for your response. I looked up the merge and found a delete example at

http://www.oracle-base.com/articles/10g/MergeEnhancements10g.php

It has the following

MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN MATCHED THEN
    UPDATE SET a.status = b.status
    WHERE  b.status != 'VALID'
  DELETE WHERE (b.status = 'VALID');

Which indicates that the delete can remove data that isnt being updated.

It also states: "Only those rows which match both the ON clause and the DELETE WHERE clause are deleted" which I think my original query conforms to.

Still confused

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[Dagon]

Have you tried it? If I do:

create table test1 as select * from all_objects

virtually everything in test1 has a status of 'VALID'. If it works the way you think it does, the merge should remove all of these. However, if I run the merge, all the rows are still there. But if I just run:

MERGE INTO test1 a
USING all_objects b
ON (a.object_id = b.object_id)
WHEN MATCHED THEN
UPDATE SET a.status = b.status
DELETE WHERE (b.status = 'VALID');

they all disappear.

 

[dbomrrsm]

Dagon

I hadn't tried it but from what your test shows to enable the delete it appears that you must be updating the row to also be able to delete it.

My first example above isn't updating rows where s_code = 160 hence none of the rows are deleted. As in your test above there is no where clause on the update the delete will work for all rows, however, this in itself cant be that efficient as to be able to delete rows you have to update ALL rows.

My guess is that oracle is just doing Insert, update and delete commands in the background so why when you can conditionally insert and update cant you conditionally delete without having to first update a lot of unnecessary rows.

Cheers

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[Dagon]

I think that what happens is Oracle identifies the subset of rows it is going to update using a combination of the on and where clauses. It then applies the update and possibly a delete to that set of rows. It wouldn't be possible for it to suddenly jump out of that set of records to start deleting other records in the table. It would have to come up with an entirely new query plan for the delete part of the merge to be able to locate those rows.

 

[dbomrrsm]

Dagon

I suspect you are very correct - suppose the best way to stop it having to update ALL is to include the rows you want to delete in the update where clause.

Thanks for your comments

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]