Results 1 to 3 of 3
  1. #1
    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.

  2. #2
    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

  3. #3
    Join Date
    Aug 2004
    Why do you say that views are considered bad?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts