Home » SQL & PL/SQL » SQL & PL/SQL » merge on view (oracle db 11.2 SE)
merge on view [message #563967] Sun, 19 August 2012 03:42 Go to next message
kamilp
Messages: 10
Registered: August 2012
Junior Member
Hello !

I have problem with merging rows into view. I have created "INSTEAD OF" triggers on the view for insert/delete/update of rows. However, the merge is not working and it needs rowid to work. Below I post simplified code to demonstrate the problem. Is there any way how to make MERGE working here ? Maybe some different apporach to make view, or something that would help me.
I must use merge, because I am changing the structure of database used for large java application. And it uses tons of merge commands, changing them to insert/update is inefficient because of development-time and of course resulting execution-time.

Any help appreciated. See below the code



create table val_00 (
  id number(10),
  data varchar2(100),
  constraint pk_val_00 primary key (id) validate
) organization index;

create table val_01 (
  id number(10),
  data varchar2(100),
  constraint pk_val_01 primary key (id) validate
) organization index;

create or replace view val as
  select id, data from (
    select id, data from val_00
    union all
    select id, data from val_01
  );


create or replace trigger trg_val_ins
  instead of insert on val
  referencing new as new
  for each row
begin
  case mod(:new.id, 2) 
    when 0 then insert into val_00 (id, data) values (:new.id, :new.data);
    when 1 then insert into val_01 (id, data) values (:new.id, :new.data);
  end case;
end;
/

create or replace trigger trg_val_upd
  instead of update on val
  referencing old as old new as new
  for each row
begin
  if updating('id') then 
    raise_application_error(-20999, 'you can not update primary key in val');
  end if;
  case mod(:new.id, 2) 
    when 0 then update val_00 set data = :new.data where id = :old.id;
    when 1 then update val_01 set data = :new.data where id = :old.id;
  end case;
end;
/

create or replace trigger trg_val_del
  instead of delete on val
  referencing old as old
  for each row
begin
  case mod(:old.id, 2) 
    when 0 then delete from val_00 where id = :old.id;
    when 1 then delete from val_01 where id = :old.id;
  end case;
end;
/

insert into val values (1, 'one');
insert into val values (2, 'two');
insert into val values (3, 'three');
insert into val values (4, 'four');
insert into val values (5, 'five');
delete from val where id = 2;
insert into val values (2, 'twooo');
update val set data = 'two' where id = 2;
commit;

merge into val using 
( select 10 as id, 'ten' as data from dual ) newdata
on (val.id = newdata.id) 
when matched then 
update set val.data = newdata.data
when not matched then 
insert (id, data) values (newdata.id, newdata.data);


ERROR at line 1:
ORA-01446: cannot select ROWID from, or sample, a view with DISTINCT, GROUP BY, etc.
Re: merge on view [message #563980 is a reply to message #563967] Sun, 19 August 2012 10:45 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1794
Registered: January 2010
Senior Member
Your issue is not MERGE related. Just try plain update:

SQL> update val
  2     set id = id + 0
  3  /
update val
       *
ERROR at line 1:
ORA-20999: you can not update primary key in val
ORA-06512: at "SCOTT.TRG_VAL_UPD", line 3
ORA-04088: error during execution of trigger 'SCOTT.TRG_VAL_UPD'


SY.
Re: merge on view [message #563996 is a reply to message #563967] Sun, 19 August 2012 13:26 Go to previous messageGo to next message
kamilp
Messages: 10
Registered: August 2012
Junior Member
I think, you have not noticed that updating PK I disabled in update trigger. I suppose it is OK since the MERGE command is not updating the PK
Re: merge on view [message #564073 is a reply to message #563967] Tue, 21 August 2012 05:03 Go to previous messageGo to next message
Kevin Meade
Messages: 1907
Registered: December 1999
Location: Connecticut USA
Senior Member
Last I new, it was not possible to use MERGE against a view with an instead-of-trigger. I checked error messages to see if anything has changed in 11g. Apparently not.

Quote:
Oracle® Database Error Messages
11g Release 2 (11.2)
Part Number E17766-02

ORA-38105: Delete not yet supported when Update row-migration is possible Cause: When Update Row-Migration is possible, Delete in MERGE is not yet supportedAction: None

ORA-38106: MERGE not supported on join view or view with INSTEAD OF trigger. Cause: The MERGE operation contained a join view or view with INSTEAD of trigger which is not supported.Action: When using MERGE to modify a view, you must only specify a single table in the view, and the view cannot have an INSTEAD OF trigger.


As you can see, MERGE and INSTEAD-OF-TRIGGERS are advanced features of Oracle. The reality is, Oracle's advanced features do not always play well with other advanced features. In the end, your database design has to make a choice:

1) do you want INSTEAD-OF-TRIGGERS to be part of your database design?
2) do you want MERGE to be part of your application toolset?

Consider these thoughts. Advanced features in Oracle (RAC,MERGE,INSTEAD-OF-TRIGGERS,MATERIALIZED VIEWS,PARTITIONING,PARALLEL QUERY etc.) were each created for a specific scenario. You need to match an advanced feature with its intended use if you wish to have the best chance of success in using it. Let me ask you... what use scenario was INSTEAD-OF-TRIGGER invented for? What use scenario was MERGE invented for? Let me help you with this...

Instead of Triggers were created to allow for sophisticated view layers to be built upon moderate volume OLTP systems. In this situation, it is possible to create different perspectives on the data and have them be updatable. Situations where this comes into play are:

1) historical perspective. The ability to capture changes to data over time and then to create schemas that will allow you to do historical queries against the old versions of data, WITHOUT CHANGING YOUR EXISTING APPLICATION CODE.

2) sophisticated auditing of data changes.

3) database consolidation. The ability to combine multiple databases together into a single schema that removes duplication of concepts and data, yet which can be decomposed back into its original data perspectives. This allows us to consolidate databases WITHOUT CHANGING EXISTING APPLICATION CODE.

I have done all three above over the years using instead-of-triggers. They work as advertised. Of course they have their limitations too like anything else. In particular is speed. You really must have database with moderate transactions rates to use these.

MERGE was created to support extreme speed in data warehouses. MERGE lets you do multiple types of changes to data without multiple statements and thus offers an speed advantage. Indeed, I have had comments from others on ORAFaq that their warehouses would not function without the speed granted by merge because of its ability to combine INSERT/UPDATE into a single step.

As you can see, these two features were created for opposite scenarios. INSTEAD-OF-TRIGGERS = moderate volume OLTP with some sophisticated database design strategy intended. MERGE = "EXTREME" need for speed in data warehouses.

In the end you cannot have both. You need to choose which one you want and dump the other. Match your scenario to the feature.

Kevin
Re: merge on view [message #564172 is a reply to message #564073] Tue, 21 August 2012 15:49 Go to previous message
kamilp
Messages: 10
Registered: August 2012
Junior Member
Thanks for perfect exhausting explanation.
We are running large application over the database warehouse thats why the application uses lots of MERGE commands. We benefint a lot from using one command to calculate values and insert/update them to tables. Our table desing uses organization index. Until now it was not problem, since we did not need to delete data and we stored data forever. However recent requirements are to store only short time - few weeks, and delete old data. That is problem without partitioning, and view with instead of triggers I intended to use as 'home-made' partitioning. We can not afford upgrading to enterprise edition and buying the partitioning option.
I need to find some other solution now...

Again, thanks a lot for explanation.

[Updated on: Tue, 21 August 2012 15:52]

Report message to a moderator

Previous Topic: dos2unix
Next Topic: Creating Oracle database to support chinese fonts
Goto Forum:
  


Current Time: Fri Apr 18 14:03:29 CDT 2014

Total time taken to generate the page: 0.29518 seconds