Re: Newbie has Oracle questions…

> Also I don't remember if you can update a UNION ALL-ed set of
> tables (certainly you can't update a UNION-ed table), but you
> of course can (and very efficiently) update a partitioned
> table. A quick test should solve all doubts, but I don't have
> an Oracle instance handy, sorry.

Now I have one handy and I can confirm that you can't update neither UNIONed nor UONIN ALLed tables.
Here's the "proof" (Oracle 8.0.5, but the version should be irrelevant):

SQL> create table pp1 (x number);

Table created.

SQL> create table pp2 (x number);

Table created.

SQL> create view ppv as select * from pp1 union 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

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

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

---

You have no choice: you must use partitioning.

HTH
Alberto


Sent via Deja.com
http://www.deja.com/