Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: delete from a view
Interesting thread. Conceptualy there is nothing wrong with SQL deleting
and updating through a UNION ALL. Any row clearly comes from one leg of
the union all and that's where it ought to be updated/deleted.
This has nothing to do with keys. It has to do with the DBMS remembering
a rows origin (table id) in the same way it remembers a row's physical
row id (to do update/delete).
Using an instead of trigger does indeed work around the DBMS limitation,
however it is semantically different when statement triggers come into
play. The DELETE statements in the instead of trigger fires once per row.
Also branch-elimination (as known in Oracle from partitioned tables I
guess) is not possible in this case.
This in turn begs the question:
Why not use a partitioned table in the first place?
Are the base tables heterogenious?
Cheers
Serge
-- Serge Rielau DB2 SQL Compiler Development IBM Toronto LabReceived on Thu Apr 29 2004 - 09:34:43 CDT