IC SunsetThe developerWorks Connections platform will be sunset on December 31, 2019. On January 1, 2020, this forum will no longer be available. More details available on our FAQ.
Topic
  • 2 replies
  • Latest Post - ‏2017-07-20T09:38:24Z by bogeybetsy
bogeybetsy
bogeybetsy
16 Posts

Pinned topic Clustered index on a VIEW

‏2017-06-09T20:59:44Z |

I see this definition of a clustered view:

 

Designing Indexed Views. ... If such views are frequently referenced in queries, you can improve performance by creating a unique clustered index on the view. When a unique clustered index is created on a view, the result set is stored in the database just like a table with a clustered index is stored.

 

Is it possible to create such a view in DB2 for i?  The view will join multiple tables.  

I have an SQL statement that joins multiple tables and sometimes it takes more than 3 minutes to get the result set. 

 

What are the pros and cons?

Will performance improve?  Can we make more than 1 unique clustered index on the view? Is there a concern on the amount of memory it will use up?

 

 

Updated on 2017-06-09T21:14:08Z at 2017-06-09T21:14:08Z by bogeybetsy
  • B.Hauser
    B.Hauser
    320 Posts
    ACCEPTED ANSWER

    Re: Clustered index on a VIEW

    ‏2017-06-10T15:07:33Z  

    In DB2 for i it is not possible to create an Indexed view nor is it possible the define a clustered index.

    DB2 for i works differently from all other databases and even from the other DB2 databases.

     

    If you have some performance issues you need to analyze your SQL Statement with the Database Performance Tools in either Client Access or event better in Access Client Solution.

    You may get index advices, for binary radix tree indexes and/or encoded vector indexes.

    With out having seen your SQL Statement and without knowing your environment we can not even guess.

     

    Birgitta

     

  • B.Hauser
    B.Hauser
    320 Posts

    Re: Clustered index on a VIEW

    ‏2017-06-10T15:07:33Z  

    In DB2 for i it is not possible to create an Indexed view nor is it possible the define a clustered index.

    DB2 for i works differently from all other databases and even from the other DB2 databases.

     

    If you have some performance issues you need to analyze your SQL Statement with the Database Performance Tools in either Client Access or event better in Access Client Solution.

    You may get index advices, for binary radix tree indexes and/or encoded vector indexes.

    With out having seen your SQL Statement and without knowing your environment we can not even guess.

     

    Birgitta

     

  • bogeybetsy
    bogeybetsy
    16 Posts

    Re: Clustered index on a VIEW

    ‏2017-07-20T09:38:24Z  
    • B.Hauser
    • ‏2017-06-10T15:07:33Z

    In DB2 for i it is not possible to create an Indexed view nor is it possible the define a clustered index.

    DB2 for i works differently from all other databases and even from the other DB2 databases.

     

    If you have some performance issues you need to analyze your SQL Statement with the Database Performance Tools in either Client Access or event better in Access Client Solution.

    You may get index advices, for binary radix tree indexes and/or encoded vector indexes.

    With out having seen your SQL Statement and without knowing your environment we can not even guess.

     

    Birgitta

     

    The SQL statement was so large and complext that the optimizer was probably having difficulty optimizing.  Our DBE/DBA had been working on finding proper indexes for it...and still performance was bad.  We decided to break down the large query into smaller ones. The result: a 500% increase in speed.