Results 1 to 2 of 2

Thread: Views

  1. #1
    Join Date
    Feb 2005
    Posts
    118

    Unanswered: Views

    Hi All !

    lets say I have a SQL such as

    SELECT c1,c2,c3,c4,c5,c6
    FROM tab0
    JOIN tab1 ON t1c1 =.... t1c2 = ....
    JOIN tab2 ON t2c1 =.... t2c2 = ....
    JOIN tab3 ON t3c1 =.... t3c2 = ....
    JOIN tab4 ON t4c1 =.... t4c2 = ....
    Where condition....

    I noticed a significant performance enhancement by coding it as

    SELECT c1,c2,c3,c4,c5,c6
    FROM view1
    JOIN tab4 ON t4c1 =.... t4c2 = ....
    Where condition....

    where view1 is defined as a view on
    SELECT c1,c2,c3,c4,c5,c6
    FROM tab0
    JOIN tab1 ON t1c1 =.... t1c2 = ....
    JOIN tab2 ON t2c1 =.... t2c2 = ....
    JOIN tab3 ON t3c1 =.... t3c2 = ....

    Now, in both cases, the actual materializtion takes place at run time (execution of the sql), then what would be the reason for performance enhancement.
    I have seen views been used to simplify queries (coding) and implement security for end users. but not used views for performance enhancements
    I would like to hear what others have experienced with views especially in the context of performance.

    thank you

    Anil

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    It should not work that way, but sometimes things do not work as well as they should. You could try to do an explain and see the optimized SQL for both options. You could also experiment with different query optimization levels (try 7 instead of default of 5) and do an explain.

    But I notice that the SQL your posted is probably not the real SQL where you noticed the problem. Sometimes, posting the exact SQL and DDL (table and indexes) will reveal the reason for the difference in performance.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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