Results 1 to 4 of 4
  1. #1
    Join Date
    May 2003
    Posts
    7

    Unanswered: SELECT using views vs tables

    I am trying to determine if there is any difference in performance if you have an SQL Select that uses a base table view vs the base table itself?

    Environment is DB2 V6 for OS/390.

    I have read recommendations from RYC (Richard Yevich) that "A base table view should only be used in very special situations". Not sure what might qualify as a special situation. :-)

    And also have heard that if you use a base table view and you are selecting only a fraction of the columns, that DB2 still has to read all of the columns from the base table.

    But even if you were selecting against the base table, and not the view, isn't the read process the same by DB2 with regards to the pages that need to be read? I'm trying to understand the impacts of using base table views, when you're only selecting a few columns from the view.


    Thanks,
    Scott Abell
    Colorado Springs

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I have read recommendations from RYC (Richard Yevich) that "A base table view should only be used in very special situations". Not sure what might qualify as a special situation. :-)

    Using views helps isolate future database design changes from the application programs and end users. For example, you may decide later to split an existing table into 2 tables (and add some more columns on one of the tables). You could create a view that joined the 2 tables for that application and users that did not need to know the table was split, thus avoiding any changes to those applications or queries. Of course, some table structures never change and the benefits of using the view may never be achieved.

    And also have heard that if you use a base table view and you are selecting only a fraction of the columns, that DB2 still has to read all of the columns from the base table.

    But even if you were selecting against the base table, and not the view, isn't the read process the same by DB2 with regards to the pages that need to be read? I'm trying to understand the impacts of using base table views, when you're only selecting a few columns from the view.


    Not sure what is meant by "has to read all of the columns from the base table," but I don't think there is any difference between views and base tables in this regard.

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Originally posted by Marcus_A
    But even if you were selecting against the base table, and not the view, isn't the read process the same by DB2 with regards to the pages that need to be read? I'm trying to understand the impacts of using base table views, when you're only selecting a few columns from the view.[/i]

    Not sure what is meant by "has to read all of the columns from the base table," but I don't think there is any difference between views and base tables in this regard.
    I believe that the optimizer, when re-writing a query, actually substitutes view reference in the query with the statement defining the view, which means there should be no difference from the performance perspective. It's a bit more complex in the case of UPDATEs against a view, but the principle I think is the same.
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    You should consider using EXPLAIN to understand how your query works ...

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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