Sunday, June 14, 2009

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.

13 comments:

Jack van Zanen said...

Try this

MERGE INTO merge_target mt
USING (SELECT object_id, object_name, object_type, status
FROM user_objects
UNION ALL
SELECT object_id, object_name, object_type, 'VALID'
FROM merge_target a
WHERE NOT EXISTS (SELECT 'x'
FROM user_objects b
WHERE a.object_id = b.object_id)) 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 (mt.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';

Brgds

Jack

tony said...

Cool. I'm not sure if this is what you had in mind, but it works on the scenarios that I tested:

merge into merge_target mt
using ( select o.object_id, o.object_name, o.object_type, o.status
from user_objects o
union
select t.object_id, t.object_name, t.object_type, 'VALID'
from merge_target t
where not exists ( select 'x' from user_objects o2 where o2.object_id = t.object_id )
) 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'
;

Essentially by using an inline view you join the objects that no longer exist back into the mix with status VALID so that the delete statement will see it.

cheers,
Tony van Lingen

Flado said...

What's wrong with

create materialized view merge_target as select object_id, object_name, object_type, status
from user_objects
where status = 'INVALID';
begin dbms_mview.refresh('merge_target'); end;
/

Seems to provide all the functionality you require...
(That's cheating, I know)
Cheers,
Flado

Robert Freeman said...

Well, it does not address the purpose of the contest, which is to use the merge command. Props for trying though. Does not count.

Flado said...

Yeah. I was just trying to highlight the fact that the contest rules are in no way specified. Is it this:
"We have found another hole in our logic somewhere. Can you find the answer?"
-- That's easy: the answer is 42. To what question?

Or maybe it is this part:
"Let's take a case where we have a table that we want to store all objects with a status of INVALID in."
-- in that case my answer is correct. If you, as a customer, had given me this requirements spec, I'd have come up with this answer and insisted that it was a fine example of Oracle proficiency on my part :-)

I derive from the context of the post that the rules should be something along the lines of:

Write a (single?) MERGE statement that correctly handles inserts into, updates of, and deletes from the data source.


Please confirm.

Cheers,
Flado

Robert Freeman said...

Good grief, what do I look like a friggin' lawyer? I guess my mistake is to expect that people would be able to look at the CONTEXT of the blog post and know that I want a merge statement.

I'm not McDonald's, I don't have a friggin' troop of lawyers and editors to look over and nit pick every blasted thing I write. It's not a contest for 100,000 a month for life, it's over a bleeding book!

The post is about the Merge command, not a materialized view, not a trigger, not some 5000 line piece of cool PL/SQL. Don't write me C code, don't write me COBOL, don't write me an ASP web page that does all the work for you. I love APEX but don't write me an APEX application as the answer.

So, just to be clear I want a MERGE statement. The blog post is about the MERGE statement. It's only about the MERGE statement.

So much of life is about context.

Everyone is a critic.

Robert Freeman said...

By the way, if I were to use the MVIEW... I'd have made it fast refreshable.

:-P

Flado said...

Easy now, I'm not a lawyer either - I just have to deal with too many vague req specs for my own good, I guess.

Now that we have that sorted out, I can look into MERGE :-)

P.S. I wouldn't make the MVIEW fast refreshable, as that would require MVIEW logs on data dictionary tables.

Alisher Yuldashev said...

Hi Robert,
The following MERGE will do.

merge into merge_target mt
using
(select nvl(tf.object_id,ts.object_id) object_id,
tf.object_name,
tf.object_type,
nvl(tf.status, ts.status) status,
nvl2(tf.object_id,0,1) newrec
from user_objects tf full outer join merge_target ts on tf.object_id=ts.object_id) 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' or uo.newrec=1
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';

Unknown said...

I'm thinking you could use an outer join, and set status to 'VALID' if it's null... just guessing, though!

Robert Freeman said...

>>P.S. I wouldn't make the MVIEW
>>fast refreshable, as that would
>>require MVIEW logs on data
>>dictionary tables.

Touche!! You are correct of course!!

Flado said...

There you go:
merge into merge_target mt
using (select o.object_id, o.status, o.object_name, o.object_type, t.object_id tid
from merge_target t full outer join user_objects o
on t.object_id=o.object_id) uo
on (uo.object_id=mt.object_id or uo.tid=mt.object_id)
when matched then
update set
object_name=uo.object_name,
object_type=uo.object_type,
status=uo.status
delete where uo.object_name is null or 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';

One could replace the full outer join with a union of user_objects and (appropriately tagged) merge_target and get a probably simpler statement.
Also, if one wants a check constraint on the STATUS column (STATUS='INVALID'), it should be deferred.

I would still prefer the materialized view approach though: simple and fast (maybe even fast refreshable in real life).

Cheers,
Flado

Robert Freeman said...

Drum roll please!!! I know I'm a bit late on this, I so apologize. The winner is

Alisher Yuldashev !!! His solution, which is seen in the comments here, was the first one that I received that worked correctly. Alisher please send me your contact information (robertgfreeman@yahoo.com) and I'll get your book to you as soon as it's released!!

I have received other solutions. I will test each of them and post those that work. Thanks to everyone for participating in this contest!!

 
Subscribe in a reader