| |
 |
|
SQL Server
Tips by Burleson |
UNION ALL Views
In the last few years, The Big Three have worked specifically on
enhancing their ability to do UPDATE, DELETE, and INSERT statements
on views based on a UNION ALL operator.
Obviously this is good because, as Codd's Rules (quoted at the start
of this article) state: Users should expect that views are like base
tables. But why specifically are The Big Three working on UNION ALL?
UNION ALL views are important because they work with range
partitioning. That is, with a sophisticated DBMS, you can split one
large table into n smaller tables, based on a formula. But what will
you do when you want to work on all the tables at once again,
treating them as a single table for a query? Use a UNION ALL view:
CREATEVIEW View1 AS
SELECT a FROM Partition1
UNION ALL
SELECT a FROM Partition2
SELECT a FROM View1
UPDATE View1 SET a = 5
DELETE FROM View1 WHERE a = 5
INSERT INTO View1 VALUES (5)
Since View1 brings the partitions together, the SELECT can operate
on the conceptual "one big table". And, since the view isn't using a
straight UNION (which would imply a DISTINCT operation), the
data-change operations are possible too. But there are some issues:
The issues arise because a typical partition
will be based on some formula, for example: "when a < 5 then use
Partition1, when a > 5 use Partition2". So it makes sense for the
DBMS to combine UNION ALL view updates with the range partitioning
formulas, and position new or changed rows accordingly.
Unfortunately, when there are many partitions, this means that each
partition's formula has to be checked to ensure that there is one
(and only one) place to put the row.
An old "solution" was to disallow changes, including INSERTs, which
affected the partitioning (primary) key. Now each DBMS has a
reasonably sophisticated way of dealing with the problem; most
notably DB2, which has a patented algorithm that, in theory, should
handle the job quite efficiently.
Updatable UNION ALL views are useful for federated data, which (as I
tend to think of it) is merely an extension of the range
partitioning concept to multiple computers.
This is a book excerpt from:
Advanced SQL Database Programmer Handbook
Donald K. Burleson, Joe Celko, John Paul Cook, Peter Gulutzan
ISBN: 0-9744355-2-X
http://www.rampant-books.com/ebook_dbazine_SQL_prog.htm
|
