Why aren't UNION ALL updatable ?
del...@my-deja.com
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/
Sybrand Bakker
views are not updatable by definition (as defined in the sql reference
manual).
Your update statement updates a set, a set without a single underlying
table. This set is definitely not a key preserved one.
Also how would you think Oracle would be capable of distinguishing which
table the individual record comes from..
If you really want to do this (which IMHO violates most relational rules,
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), you need instead of triggers as
available from Oracle 8i.
Hth,
Sybrand Bakker, Oracle DBA
<del...@my-deja.com> wrote in message news:965opg$nkp$1...@nnrp1.deja.com...
del...@my-deja.com
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
> <del...@my-deja.com> wrote in message
news:965opg$nkp$1...@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.
Brian Peasland
<<snip>>
> > 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.
It is true that each row in the result set corresponds to exactly one
row from each of the tables. But the result set is in effect just that,
a set. Now that it is a set distinct from the source tables, it does not
have any true reference to the source tables. Oracle is not designed to
maintain the mappings from the result set to the original, source sets.
Therefore, you can not update the rows from a UNION ALL view since the
mapping back to the original source sets is lost!
> > 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;
Since the sets in pp1 and pp2 are real tables, there is a mapping back
to the rows. Since the set in ppv is not a real table, there is no
"mapping". If you want to update, you'll have to do it like your last
example.
I suppose Oracle could have written the RDBMS code so that you can
update across a UNION ALL. But they chose not to.
This all comes from set theory which is the basis of Relational Database
Systems. In tradtional set theory, there is no UNION ALL, only UNION
which removes duplicate elements of the set. If duplicates are removed,
then how can you determine which source set it came from? For instance:
set A contains {1,2,3}
set B contains {3,4,5}
A UNION B = {1,2,3,4,5}
Which set does '3' come from? A or B? If I update the UNION, which
source set gets updated, A, B, or both? To resolve any ambiguity,
updates are not allowed across a UNION view.
UNION ALL is an extension to UNION. It just leaves the duplicates. This
means that
A UNION ALL B = {1,2,3,3,4,5}
Which '3' corresponds to which source set? In set theory, none since
there is no true mapping due to the fact that this is a unique set all
on its own.
HTH,
Brian
--
========================================
Brian Peasland
Raytheons Systems at
USGS EROS Data Center
These opinions are my own and do not
necessarily reflect the opinions of my
company!
========================================