MERGE ME BABY!! A Contest for a new book!!

At the end of this post I'm starting a contest. So read the post and maybe you will win a book!!

So, I've had occasion to work with the MERGE command of late. In my travels I've found the MERGE command to be a way under utilized command. It's been a while since I used it, to be honest, and I had to re-learn a few things. I think in the process I have a few interesting tid-bits to share.

Let's take a case where we have a table that we want to store all objects with a status of INVALID in. For the sake of keeping things clear later, we will call the table MERGE_TARGET. Here is the DDL for this table:

Drop table merge_target;
create table merge_target
as select object_id, object_name, object_type, status
from user_objects
where status = 'INVALID';
Alter table merge_target
Add constraint pk_merge_target primary key (object_id);

Now, let's create an object and invalidate it. We will create a table, then create a view dependent on that table. Finally we drop the table making the view invalid.

Drop table dodo;
Create table dodo (id number);
Create view view_dodo as select * from dodo;
Drop table dodo;

Excellent, now we have an invalid object called view_dodo. We can see this by querying the user_objects view as seen here:

select object_name, status
from user_objects
where status='INVALID';

OBJECT_NAME STATUS
-------------------- -------
VIEW_DODO INVALID

The problem is that our merge_target table is not up to date:

select * from merge_target;
no rows selected

How do we update it. Clearly we can use the insert command but what happens as time goes on and records change ... we will have to do inserts, updates and deletes. This is where the merge command comes in. With one simple command we can get the table updated. Here is our first crack at a merge command. Don't stop here though, becuase this does not take care of every possible situation that might occur:

merge into merge_target mt
using user_objects uo
on (uo.object_id=mt.object_id)
when matched then
update set mt.object_name=uo.object_name,
mt.object_type=uo.object_type,
mt.status=uo.status
where uo.status='INVALID'
when not matched then
insert (mt.object_id, mt.object_name, mt.object_type, mt.status)
values(uo.object_id, uo.object_name, uo.object_type, uo.status)
where uo.status='INVALID';

Now look at merge_target:
SQL> select * from merge_target;

OBJECT_ID OBJECT_NAME OBJECT_TYPE STATUS
---------- -------------------- ------------------- -------
70803 VIEW_DODO VIEW INVALID

So what if the view is renamed, but still invalid?

rename view_dodo to dodo_view;
select object_name, status
from user_objects
where status='INVALID';

OBJECT_NAME STATUS
-------------------- -------
DODO_VIEW INVALID

select * from merge_target;


OBJECT_ID OBJECT_NAME OBJECT_TYPE STATUS
---------- -------------------- ------------------- -------
70803 VIEW_DODO VIEW INVALID

Let's issue the MERGE command again and see what happens!

merge into merge_target mt
using user_objects uo
on (uo.object_id=mt.object_id)
when matched then
update set mt.object_name=uo.object_name,
mt.object_type=uo.object_type,
mt.status=uo.status
where uo.status='INVALID'
when not matched then
insert (mt.object_id, mt.object_name, mt.object_type, mt.status)
values(uo.object_id, uo.object_name, uo.object_type, uo.status)
where uo.status='INVALID';
commit;

select * from merge_target;

OBJECT_ID OBJECT_NAME OBJECT_TYPE STATUS
---------- -------------------- ------------------- -------
70803 DODO_VIEW VIEW INVALID

Wow! Cool. It seems that Merge does an insert, and then also does updates!
So what happens if the view becomes valid again? Let's see:

Create table dodo (id number);
alter view dodo_view compile;
select object_name, status
from user_objects
where status='INVALID';
no rows selected

Sweet, now there is nothing in USER_OBJECTS that is invalid. What about the merge_target view though, it still has VIEW_DODO in it. What do we do? We issue the MERGE command again!

merge into merge_target mt
using user_objects uo
on (uo.object_id=mt.object_id)
when matched then
update set mt.object_name=uo.object_name,
mt.object_type=uo.object_type,
mt.status=uo.status
where uo.status='INVALID'
when not matched then
insert (mt.object_id, mt.object_name, mt.object_type, mt.status)
values(uo.object_id, uo.object_name, uo.object_type, uo.status)
where uo.status='INVALID';

SQL> select * from merge_target;

OBJECT_ID OBJECT_NAME OBJECT_TYPE STATUS
---------- -------------------- ------------------- -------
70803 DODO_VIEW VIEW INVALID

Woops, that didn't work so well. If you are not running Oracle Database 10g and later, this is a problem you will need to deal with manually. However, there is a solution in Oracle Database 10g. To use it, we have to update the Merge command just slightly to that seen here:

merge into merge_target mt
using user_objects uo
on (uo.object_id=mt.object_id)
when matched then
update set mt.object_name=uo.object_name,
mt.object_type=uo.object_type,
mt.status=uo.status
where uo.status='VALID' or uo.status= 'INVALID'
delete where (uo.status='VALID')
when not matched then
insert (mt.object_id, mt.object_name, mt.object_type, mt.status)
values(uo.object_id, uo.object_name, uo.object_type, uo.status)
where uo.status='INVALID';

Now, let's see what happens!

select * from merge_target;
no rows selected

Great!! Note that we added a delete clause under the update clause. Also note that the where clause in the update clause is now changed to include updates of both VALID and INVALID records. That's the rub with the delete clause, it ONLY works on records that have actually passed through the update clause. So we have to process all of the VALID records as an update, before we can actually process them for a delete. That was something that tripped me up for a while before I got it figured out, so learn from my mistake. :-)

Finally, there is still a problem. What happens if we do this?

-- we drop the table, invalidating the view
drop table dodo;

-- Re-populate merge_target with the invalid view again
merge into merge_target mt
using user_objects uo
on (uo.object_id=mt.object_id)
when matched then
update set mt.object_name=uo.object_name,
mt.object_type=uo.object_type,
mt.status=uo.status
where uo.status='VALID' or uo.status= 'INVALID'
delete where (uo.status='VALID')
when not matched then
insert (mt.object_id, mt.object_name, mt.object_type, mt.status)
values(uo.object_id, uo.object_name, uo.object_type, uo.status)
where uo.status='INVALID';

select * from merge_target;

OBJECT_ID OBJECT_NAME OBJECT_TYPE STATUS
---------- -------------------- ------------------- -------
70803 DODO_VIEW VIEW INVALID

Now, what happens if we drop the view completely?

drop view dodo_view;

select object_name, status
from user_objects
where status='INVALID';

no rows selected

Let's update merge_target now. Once this is done, it should be empty, right?

merge into merge_target mt
using user_objects uo
on (uo.object_id=mt.object_id)
when matched then
update set mt.object_name=uo.object_name,
mt.object_type=uo.object_type,
mt.status=uo.status
where uo.status='VALID' or uo.status= 'INVALID'
delete where (uo.status='VALID')
when not matched then
insert (mt.object_id, mt.object_name, mt.object_type, mt.status)
values(uo.object_id, uo.object_name, uo.object_type, uo.status)
where uo.status='INVALID';

select * from merge_target;

OBJECT_ID OBJECT_NAME OBJECT_TYPE STATUS
---------- -------------------- ------------------- -------
70803 DODO_VIEW VIEW INVALID

Oh BLAST! We have found another hole in our logic somewhere. Can you find the answer? The answer results in a somewhat more sophisticated version of the merge command.

So, here is what I'm going to do.

For the next 90 days, I'm not going to post the answer to this question. For the first person who posts a comment to this blog with the correct answer to this question will go one free copy of our 11g RMAN Backup and Recovery book when it comes out!! I will not post comments that have the correct answer until the contest is over. Once the contest is over, I'll post the winners name and you will need to send me your mailing address so I can ship you your book.

All shipping costs on me unless you live outside the current solar system! ON September 15th in a separate BLOG post I will post the answer to this query. If you have an urgent need for the answer you can write me at robertgfreeman@yahoo.com, but you will not be eligible for the book. Of course this offer does not apply anywhere that it is illegal to make such an offer, you are responsible for the tax ramifications, etc...etc.... This is a personal contest and has no association with Oracle, Oracle Press, Santa Clause, the North Pole, any of my employers past or present or anyone else in the world living, dead, half-dead, in Zombie format, who may live in the future or who might be a fantasy in someones head. My family is not eligible (like they know anything about Oracle) nor is anyone at any of my employers within the last 5 years. Any employee of Oracle Corp. is also not eligible or anyone with Oracle Press.