Re: Why aren't UNION ALL updatable ?

Dear Sybrand,

thank you for your kind answer.

Embedded below are my notes on your answer.

In article "Sybrand Bakker" wrote:

> Your view is a join of two tables, including an implicit order by.

That's true for UNIONs but not for UNION ALLs. The implicit order by is performed only for UNIONs as an efficient way to eliminate duplicates in the result set, but since UNION ALL returns all the rows (duplicated included), the sorting is not necessary and is not performed.

I've checked it both by issuing an EXPLAIN PLAN and by making a select from ppv (which has returned unsorted data).

> Those views are not updatable by definition (as defined in the sql
> reference manual).

Yes, I've read the manual (and other books, and this newsgroup too) before posting, and I know that the manual says you can't; I would like to know "why".

Is it a relational heresy, or simply a special case of a UNION ALL that Oracle doesn't care to handle ?

> Your update statement updates a set, a set without a single underlying
> table. This set is definitely not a key preserved one.

But if you don't eliminate duplicates, each row in the set corresponds exactly with a row from the table.

> Also how would you think Oracle would be capable of distinguishing
> which table the individual record comes from..

For example, by generating the rowid when it fetches the row from the table and attaching it to the set.

In principle, one could even rewrite the update from

   update ppv set x = 1;
to

   update pp1 set x = 1; update pp2 set x = 1;

and then perform the two updates (as an atomic operation, with the same read consistent behaviour, of course).

> If you really want to do this (which IMHO violates most relational
> rules,

May you please further elaborate on this point ? That's exactly what I would like, knowing the relational rationale behind the non-updatability of UNION ALLs.

> and you could also question why you have two *identical* tables, if I
 were
> acting as a DBA on your server, you would have to explain this to me,
> because you will inevitably create havoc),

Simply because we have a third-party application that implements a poor-man partitioning that way. I can't modify that behaviour; if I could, i would use partitioning or even a big table with carefully designed indexes. But, i'm not a free man on that.

> you need instead of triggers as
> available from Oracle 8i.

Thank you, I appreciate your advice (as I appreciate your postings in general).

Thank you for your time,
Alberto

> <dellera_at_my-deja.com> wrote in message
 news:965opg$nkp$1_at_nnrp1.deja.com...
> > 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.

Sent via Deja.com
http://www.deja.com/ Received on Sun Feb 11 2001 - 09:05:02 CST