Thread: DB2 Views versus UnionAll
06-23-06, 10:37 #1Registered User
- Join Date
- Jan 2006
Unanswered: DB2 Views versus UnionAll
I have inheritance hierachies represented as a table for each entity.
So if Employee inherits from Person there will be a table for Employee and a table for a Person.
The employee table has the same columns as person and then some extra ones of it's own. I know this could be argued as some redundancy, but the database model cannot be changed.
To select all Persons (i.e. all entities in Person table and all entities in derived tables), in the one SQL statement, can achieved two ways.
By using a "Union All" two combine a select from Person and Employee. Two select statements are created, one is padded out (the person one) and they are then created into the one statement using UNION ALL.
By creating a View which will amalgamate Person and Employee entities.
Now, views are generally considered bad, so I opted for option 1.
Everything fine, until my Queries got more complicated.
For example, suppose I have an inheritance hierarchy of depth 5. It is involved in a join which also has a depth of five.
This means 25 statements are amalgamated using union all.
The query goes slow.
I run the explain plan, and notice a few table scans. I readjust my query to get rid of table scans, it now only includes index scans, it still goes slow.
I am noticing that if I use method 1 (the union all method), I get far more index scans than if I use method 2 (the view method).
So it would appear that using views to perform this type of inheritance query is quicker.
Now I am afraid I am missing something obvious here, as my DB2 experience is moderate at best. I would appreciate if anybody has any intelligent advice on this.
06-24-06, 09:18 #2Registered User
- Join Date
- Sep 2004
First of all you should mention your DB2 version and platform; also the sizes of your tables (and other statistics) could be important to judge optimality of certain query variants.
Which kind of view definition do you have in mind? If the view consists of "UNION ALL"s, I don't see the difference between the two approaches.--_Peter Vanroose,
__IBM Certified Database Administrator, DB2 9 for z/OS
__IBM Certified Application Developer
__ABIS Training and Consulting
06-26-06, 10:26 #3Registered User
- Join Date
- Aug 2004
Why do you say that views are considered bad?