Why aren't UNION ALL updatable ?

Dear all,

I was wondering why it's not possible to update a view which is the UNION ALL (please note: UNION ALL, not UNION) of two identical tables:

 SQL> create table pp1 (x number);

 Table created.

 SQL> create table pp2 (x number);

 Table created.

 SQL> create or replace view ppv as

         select * from pp1
      union all
         select * from pp2
      ;

 View created.

 SQL> update ppv set x = 1;
 update ppv set x = 1

         *
 ERROR at line 1:
 ORA-01732: data manipulation operation not legal on this view

IMHO, "update ppv set x = 1;" may be seen equivalent to "update pp1 set x = 1; update pp2 set x = 1;", and so rewritten on the fly by the Oracle engine.

Of course I know that I can use partitioning, and I also know that there is the possibility to use PARTITIONED VIEWS (even if the latter is a deprecated feature); my question is more theoretical, namely if there's any underlying strong reason to avoid this. For instance, I would call this view a key-preserved one ..

Any idea to clear my puzzled mind ?

Thanks in advance
Alberto

Sent via Deja.com
http://www.deja.com/ Received on Sun Feb 11 2001 - 04:16:18 CST