Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2002
    Posts
    42

    Question Unanswered: Views and performance

    Hey all,

    I'm pretty sure of what you're gonna say, but I have to ask anyways.

    Is it possible to index a view?

    As I understand it, when used in a query, the view is generated then used as a table and joined with the other tables/views in the query. So it is easy to optimize the generation of the view, but when used in a query with other tables/views, I can't index the view for better response, can I?

    Thanks.

    JFS.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Nope, no index on a view.

    Andy

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by svrider
    Hey all,

    I'm pretty sure of what you're gonna say, but I have to ask anyways.

    Is it possible to index a view?

    As I understand it, when used in a query, the view is generated then used as a table and joined with the other tables/views in the query. So it is easy to optimize the generation of the view, but when used in a query with other tables/views, I can't index the view for better response, can I?

    Thanks.

    JFS.
    You are not understanding how DB2 works with views. Views are not materialized and it would not help to index them. When you have a view, the SQL is re-written (and optimized) by DB2 when the statement is compiled to a new SQL statement without the view against the base tables, and any indexes on the base tables will be used.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    A view in SQL (not just DB2's dialect) is just like a macro. You can think of it as being some simple text replacement occurring to resolve the view before the actual execution of the statement starts. (It is not that simple because of things like privileges, but you should get the idea.)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by svrider
    Is it possible to index a view?
    Yes, it is. That is to say, "it depends":
    With DB2 9 for z/OS you can define an index on an expression, hence a view having a column which is not just a base table column cannot be indexed in earlier versions of DB2 but can be in DB2 9.
    (So it's not the view itself which gets indexed, but the result is the same when the view is going to be queried.)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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