Page 1 of 2 12 LastLast
Results 1 to 15 of 19

Thread: View indexing

  1. #1
    Join Date
    May 2003
    Posts
    12

    Unanswered: View indexing

    Hi,
    There is a massive view sitting on top of 0.5 billion records on Db2 database. We are facing maor performance issues while using this view

    There cannot be indexes created on a view on the DB2 version we are using. This view is referring to 5 TABLES behind it, on which there are indexes.

    Can someone tell me how beneficial are these indexes while querying on the view

    Thanks and regards
    Abhi

  2. #2
    Join Date
    Sep 2002
    Posts
    456

    Re: View indexing

    Can you do an explain and see what's going on?

    dollar

    Originally posted by abhigreen
    Hi,
    There is a massive view sitting on top of 0.5 billion records on Db2 database. We are facing maor performance issues while using this view

    There cannot be indexes created on a view on the DB2 version we are using. This view is referring to 5 TABLES behind it, on which there are indexes.

    Can someone tell me how beneficial are these indexes while querying on the view

    Thanks and regards
    Abhi

  3. #3
    Join Date
    May 2003
    Location
    Amsterdam
    Posts
    65
    A view is merely a SELECT statement that has been saved in the database. The indexes on the base tables might be necessary and perhaps you need extra indexes. DB2 utilities like Explain and db2advis (UDB only) might help to investigate your problem. Also take care that your have recent runstats for your tables and also check if a REORG might be needed (reorgchk command on UDB)
    Anton Versteeg
    IBM Netherlands

  4. #4
    Join Date
    May 2003
    Posts
    12
    The stupid DBA's have not created explain tables on my schema. I dont know why... Its some restricted access policy.

  5. #5
    Join Date
    Apr 2003
    Location
    Florida
    Posts
    79
    A few things you can check without explain are:

    Be certain that indexes are created on every column of the views JOIN predicates and where clause predicates.

    If your indexes are in DMS table spaces, hopefully you have seprate Bufferpools to hold the index data seprate from table data.

    Rick

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    As mentioned, there is no difference in performance as to whether you coded an SQL statement that joined 5 tables, or whether the 5-way join was in a view. Both statements will use the indexes created on the tables. There very slight amount of extra time that DB2 needs to convert the view to an SQL statement that accesses base tables, but that is insignificant in this case.

    But a 5-way join on a table with 500 million rows is usually going to be a challenge in any situation unless the answer set is relatively small and very precise indexes are used. Even then, don't expect miracles. A 5-way join is not "merely" 4 times as expensive as a 2-table join. The resource cost tends to go up exponentially as more tables are joined in a single SQL statement.

    That is one reason why large data warehouse implementations tend to be somewhat de-normalized (to prevent large number of joins). Given the number of rows (500 million) I assume this is some type of data warehouse.

  7. #7
    Join Date
    May 2003
    Posts
    12
    can anyone confirm Marcus' views on views? I think the processing overhead on a view is considerable through it ultimately translates it into a select.

    Yes, it is a dwh. Actually the view is just union of 4 tables (i.e. there is no where in the select which makes the view). Each table caters to different year. I think this was done because the table became too large so they decided to keep one table for a year.

  8. #8
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    I do not have any authentic info to tell you that Marcus views are right ... But that's what I have always thought about how views work

    Cheers

    Sathyaram



    Originally posted by abhigreen
    can anyone confirm Marcus' views on views? I think the processing overhead on a view is considerable through it ultimately translates it into a select.

    Yes, it is a dwh. Actually the view is just union of 4 tables (i.e. there is no where in the select which makes the view). Each table caters to different year. I think this was done because the table became too large so they decided to keep one table for a year.

  9. #9
    Join Date
    Mar 2003
    Posts
    343
    I agree with Marcus in his opinions on views. I also think the following affects the queries - union (all) of 4 large tables. It means that the query will do a full select on each table defined in the select statement of the view. I guess if someone is trying to go after data within a certain date range, it will be advantageous to go after the base table rather than the view and use db2advis to determine what indexes can benefit the query. We have done this and it has helped but I cannot speak for the specific situation in question.

  10. #10
    Join Date
    May 2003
    Posts
    12
    So that means, the indexes on the underlying table wont be used? right?

    By the way, how do I run db2advis. I am a novice when it comes to db related stuff. I tried running it on command line processor of db2 on
    db=> prompt but it wont work there. Also on winsql. Would really like a lesson into this

    Thanks everyoone

  11. #11
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you code the statement with the UNIONs using the base tables, does it perform better than using a view with the same UNIONs? I am assuming that the equivalent SQL statement is used in both cases and that the view does not have any "extra" logic than an SQL statement using base tables.

    There can be performance related issues with a complex UNION statement, but usually it would not be any different if used in a view vs. coding against base tables (but see the last sentence in this post about “view materialization”). If the view fails to use an index that is used when coding with base tables, I would open an APAR with IBM. This assumes that both statements are equivalent.

    If you are having performance problems because an index is not being used that you think should be used, I would regorg the tables and perform a complete runstats on each object. But this should have the same effect on the SQL statement using a view or using the base tables.

    Maybe it would help if you posted the view definition and the query that uses the view, along with table and index definitions.

    Check out this article from IBM on UNION in views:
    http://www7b.software.ibm.com/dmdd/l...202zuzarte.pdf
    There is a potential issue with “view materialization” with complex UNION statements as discussed in the article.

  12. #12
    Join Date
    May 2003
    Posts
    12

    View definition

    Create View VDW04.SE_BOUNDS as
    Select * from dw04.se_bounds_yr1
    union all
    Select * from dw04.se_bounds_yr2
    union all
    Select * from dw04.se_bounds_yr3
    union all
    Select * from dw04.se_bounds_yr4

    The indexes on each of these tables
    SE_BOUNDS_YR1_I02
    +SE_CTRY_ID
    SE_BOUNDS_YR1_IP +SE_NO+SOURCE_SYS_ID+SE_SUBM_CURR_CD+CM_CTRY_ID+GM APS_PROD_CD+RPT_PER_DT+SE_CTRY_ID
    The queries will use most of the parameters of 2nd index while querying

  13. #13
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    In order for an index to be used most efficiently, the query should supply the "leading" columns of compound index. That is, starting from the left, the number of consecutive columns supplied makes a big difference in effective use of the index.

    If the first 4 columns of the 6 column index are supplied by the query, then “matching columns” is said to be 4. If the 2-5 columns of the index are supplied by the query, “matching columns” is 0, and the b-tree of the index will not be used (an entire scan of the index will be required) or the index may not be used at all (depending on what DB2 thinks is fastest).

    But getting back to the original question, if a query using the view is compared to one using base tables (with the same UNION statements), is there a performance difference?

  14. #14
    Join Date
    May 2003
    Posts
    12
    Guys (Marcus),
    I checked running the query on the union of the tables directly and the same query on the view that is based on the union of the tables. The view takes considerablly more time

    269 rows fetched in 1.422 sec compared to 41.516 for the select on the view

    That means the view doesnt use the index created on the tables underlying it efficiently

    Can I run db2advis through winsql?

    Thanks

  15. #15
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    A UNION is definitely different from a Join. When using a UNION in a view I think you should look at the article I mentioned previously (link repeated below).

    Here is a quote from the article:

    "The check constraint is also necessary for DB2 query rewrite to improve the performance of the query against the all_sales view by ensuring that only the relevant monthly sales tables are accessed, as is described in more detail in Section 4.

    Another option to achieve the same result is to define a WHERE clause on every table in the UNION ALL view. You can use this option if there is a screening process in place before data is loaded into the table to ensure that data is loaded to the proper table. Notice that they include a where clause (for the date range of each table) in the view for each UNION."


    So it looks like you need either of the following to make DB2 query rewrite (conversion from a view to base tables) work as well as a select against a base table:

    - WHERE clauses in the view, or
    - Check constraint on the tables

    I think it is possible to make this work the way you want.

    http://www7b.software.ibm.com/dmdd/l...202zuzarte.pdf

Posting Permissions

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