Results 1 to 6 of 6

Thread: Cluster Indexes

  1. #1
    Join Date
    Feb 2008
    Posts
    4

    Unanswered: Cluster Indexes

    Hi
    Could any one please tell me the procedure for creating cluster indexes on a table in order to enhance the performance of a DB.

    Thanks,

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    In the CREATE INDEX statement, you can define an index as "clustered". This affects the order of the rows in the table, and not the index (all indexes are in the exact order of the index columns). For this reason, only one index per table may be defined as clustered because the table can only be in one order.

    Clustering determines the order of the rows in the following circumstances:

    1. During a table reorg, the rows will be placed in the exact order of the clustering index (unless you are doing an offline reorg and choose to specify a different index to order the rows). If no clustering index is defined, and no index is specified in the reorg table command, the rows will be reorged without any specific order.

    2. During an insert, DB2 will "attempt" to place the new row in the correct page, according to the clustering index defined, so that the rows in the table will be in the approximate order of the clustering index. It will not necessarily reorder the rows in a page to get an exact order (only worries about inserting to the correct page), and if no space on the correct page is available, it may put the row on a nearby page, or on any other page where space is available. The pupose of leaving percent free on a table is to leave enough room on the page for DB2 to insert rows on the correct page during an insert where a clustering index is defined. This means that reorgs must be coordinated with the insert frequency to make this work in an optimal manner.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    When would you want to create a clustered index?

    Consider the employee table as an example. Assume that empno is the primary key and has a unique index. No one probably cares about the order of the rows with reference to empno, since there are not likely to be an queries that ask for employee rows where empno is between 400 and 500.

    But there probably are queries that ask for all employees in a specific workdept or rows with a specific lastname. So workdept or lastname should probably have indexes and one of them should be clustered (depending on which query is the most important or most frequent). By ordering the rows on the table pages by a clustering index, DB2 can retrieve the rows for certain queries faster if the clustering reduces the number of pages that DB2 must access.

    DB2 performance is all about reducing the number of pages accessed to satisfy a query, and clustering indexes can sometimes be used for that purpose. However, you must consider the cost of specifying a clustering index and the affect it has on insert performance, and making sure there is freespace available on the correct page for it to work optimally.
    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
    Another benefit besides reducing the number of pages being accessed is that clustered data can be more likely read in via prefetching instead of random access to the disk.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by stolze
    Another benefit besides reducing the number of pages being accessed is that clustered data can be more likely read in via prefetching instead of random access to the disk.
    If DB2 determines that a table scan is necessary, would it not use prefetching on the table pages regardless of whether a clustered index is defined on the table?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by Marcus_A
    If DB2 determines that a table scan is necessary, would it not use prefetching on the table pages regardless of whether a clustered index is defined on the table?
    Yes, that's correct.

    However, if you have the data clustered based on an index, and you access a bunch of rows via this index (like in range predicates), DB2 can switch from random page accesses to prefetching because it knows that the rows should generally be on contiguous pages.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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