Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2007
    Posts
    34

    Unanswered: index order of columns

    I'm used to work with oracle and was suprised to see how some DB2 tables had indexes with the same columns but with different order (ver 7 on z/OS)

    example

    Table 1
    col1
    col2
    col3

    Oracle (latest version) only 1 index without performance lost :
    Index1 (col1,col2,col3)

    DB2 : indexes with same columns in different order
    Index1 (col1,col2,col3)
    Index2 (col3,col2,col1)
    Index3 (col2,col3,col1)
    Index4 (col1,col3,col2)
    ...

    Is there a newer DB2 version (> ver 7) which eliminates the need of all these
    indexes on same columns in different order without loosing performance ?

  2. #2
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    indexes 2, 3 and 4 are not required. I never create such an indexes. Did you create access plan to see which index is used?
    Grofaty

  3. #3
    Join Date
    Jun 2006
    Posts
    471
    indexes 2-3 could be needed : eg. if col1 is not specified in the predicate
    all depends on the type of predicates that are being used..
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  4. #4
    Join Date
    Jul 2007
    Posts
    34
    Quote Originally Posted by guyprzytula
    indexes 2-3 could be needed : eg. if col1 is not specified in the predicate
    all depends on the type of predicates that are being used..

    Was just reading C Mullins DB2 Developer's Guide ver 8 and believe he states that only one index, Index1 (col1,col2,col3) for instance, can fit the needs of many different access criteria .
    For instance a where clause with only col1 , only this first col1 of the index will be used, if col1 and col2 are in the where clause, than only these 2 first columns of the index will be used . But what about performance ?

  5. #5
    Join Date
    Jul 2007
    Posts
    34

    Matching index scan

    Quote Originally Posted by swiss01
    I'm used to work with oracle and was suprised to see how some DB2 tables had indexes with the same columns but with different order (ver 7 on z/OS)

    example

    Table 1
    col1
    col2
    col3

    Oracle (latest version) only 1 index without performance lost :
    Index1 (col1,col2,col3)

    DB2 : indexes with same columns in different order
    Index1 (col1,col2,col3)
    Index2 (col3,col2,col1)
    Index3 (col2,col3,col1)
    Index4 (col1,col3,col2)
    ...

    Is there a newer DB2 version (> ver 7) which eliminates the need of all these
    indexes on same columns in different order without loosing performance ?
    Could this possibly has something to do with "matching index scan" where the high order column in the index key plays a role ?
    The first predicate in the where clause should match the first column in the index .

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by swiss01
    Was just reading C Mullins DB2 Developer's Guide ver 8 and believe he states that only one index, Index1 (col1,col2,col3) for instance, can fit the needs of many different access criteria .
    For instance a where clause with only col1 , only this first col1 of the index will be used, if col1 and col2 are in the where clause, than only these 2 first columns of the index will be used . But what about performance ?
    If you have 2 columns in the index, then each index entry requires more space. With more space for each entry, you will get less index entries on a single data page. And with less entries on a page, you may have more pages in the index and - potentially - even a higher index tree. A higher tree implies one additional page access for each index access. More pages in the index implies that an index scan may have to access more pages. Accessing more pages implies more I/O and that could impact performance. Now, there are many things implemented in DB2 to reduce the mentioned impact.

    Also, creating specific indexes for each case may be beneficial (especially in read-only situations like warehouses), but it requires additional overhead for index maintenance, of course. It is just more effort to maintain 2 instead of 1 index.

    DB2 can/will use an index (col1, col2, col3) if you only have a predicate accessing "col1". But if you only have a predicate accessing "col2" and not "col1", this index won't help you (usually) because all index entries are sorted by the values in "col1", first. (DB2 could still do a leaf-node scan, though.)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    There are two ways that DB2 can use an index. It can use the b-tree, which requires that at least one the left most columns of the index must be in the predicate (WHERE clause). For example in index1, if the where clause references only col2 and col3, then DB2 cannot use b-tree access method with this index. In DB2 for z/OS, the number of matching columns (left-most columns) would be 0. However, DB2 could read the entire index (all the leaf pages) instead of the b-tree, but that would not be nearly as fast (but it may be faster than a table scan).

    Having all four indexes in your example is partially redundant. For example, even if the predicate contains col3 and col2, index2 should not have col1 in it. If all three columns are in the predicate, then DB2 could use index1. Infact, if the cardinality of col3 is high enough, then index2 might only need to contain col2 in it (even if col2 is in the predicate). The smaller the index, the more likely it will be in the bufferpool, and the faster it will likely be.

    Even though having multiple indexes with some of the same columns is often useful, your example makes no sense with the 4 indexes you have defined that have all the exact same columns. Although I have seen this before, it is poor physical design.

    It is not necessary to have DB2 do all the filtering via the index. If DB2 can filter the rows to a relatively small number with an index, and then filter the rest of the predicate with the columns on the table, that is often preferable than having a lot of large indexes (which are expensive to maintain and use up a lot of memory).

    It is all about cardinality and filter factor. DB2 caputres statistics on 1st key card, 2nd key card, 3rd key card, 4th key card, and full key card just for that purpose.

    Oracle has several different kinds of indexes, including hash indexes, that do not depend on the order of the columns. Oracle also has indexes that do depend on the order of the columns for performance just like DB2. DB2 also has Multi-Dmensional Clustering, which work differently than traditional DB2 indexing.

    DB2 and Oracle sometimes use different physical architectures, that might favor one or the other in a specific circumstance, but that usually tend to even out in the long run in terms of overall performance. I would not gloat too much about Oracle performance, since DB2 is generally accepted to be at least as fast (if not faster) than Oracle overall, assuming the DB2 DBA and the developer know what they are doing.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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