Re: primary and foregin keys

>> Serge Rielau advocates this approach ("Updatable UNION ALL Views in
DB2 Universal Database Version 8") <<

He showed you how to do it in the simple case; it is a DB2 trick and needs to be used with caution. There is a HEAVY overhead under the covers with the UNIONed VIEW partitioning. DB2 attempts to insert any given row into each of the tables underlying the UNION ALL view. It then counts how many tables accepted the row. It has to process the entire view, one table at a time and collect the results. And you need to worry about locks if other sessions are using the base tables at the same time.

1. If exactly one table accepts the row, the insert is accepted.
2. If no table accepts the row, a "no target" error is raised.
3. If more than one table accepts the row, then an "ambiguous target" error is raised.

Think about three tables, A, B and C. Table C is disjoint from the other two. Tables A and B overlap. So I can insert into C if I meet the filter criteria and may or may not be able to insert into A and B if I hit overlapping rows.

Going back to my Y2K consulting days, I ran into a version of such a partitioning by calendar periods. Their version of Table C was set up on Fiscal quarters and got leap year wrong because the fiscal quarters ended on the last day of February. Sure messes up interest calculations on millions of dollars if you are one day off :)

The goal of a partitioning like this is parallelism. Session #1 can work on the first subset, while Session #2 works on the second subset, etc. Teradata got parallelism by using hashing -- one hash bucket gets a processor and hash buckets are a partitioning enforced by mathematics. That works because the engine is doing the partitioning at the PHYSICAL level without a human being to screw it up. Oracle has something else that i have not looked at.

Univac did some work about 10 years ago on what they called Logical Concurrency Control. You look at the job queue, pull out the jobs that do not overlap and let them run against the database at the same time. This will require no locks and reduce overhead greatly.

It never got out of the lab. What is the **general** algorithm for determining that two sessions have no overlap? It has to handle VIEWS, base tables, trigger code, DRI actions, stored procedures, dynamic SQL, etc.

But more to the point, the UNION-ed VIEW is presented as a single table and the components are hidden from the users. It does not try to look like separate tables (mag tape files) to the user. Received on Tue Dec 28 2004 - 22:31:17 CET