Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: Is this redudant index?

    Hi,
    on my system DB2 v8 fp9 on Windows XP SP2 I run db2advis command for one particular SQL.

    The output recommending indexes was:
    Code:
    CREATE INDEX ADMIN.IDX710250925460000 ON ADMIN.TAB1 
    (COL1 ASC, COL2 ASC, COL3 ASC) ALLOW REVERSE SCANS ;
    
    CREATE INDEX ADMIN.IDX710250926010000 ON ADMIN.TAB1
    (COL1 ASC, COL2 ASC, COL3 ASC, COL4 ASC) ALLOW REVERSE SCANS ;
    This output surprised me. Isn't first index redundant?

    Thanks,
    Grofaty

  2. #2
    Join Date
    Jun 2006
    Posts
    471
    yes, according my opinion first index is included in second
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Right, it is included.

    However, the 2nd index may be bigger because index keys are longer and less keys may fit in an index page, requiring more pages. More pages may lead to a higher index tree. Thus, it could be beneficial to have both indexes - depending on what you do with the database.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    IMO db2advis sucks. I have seen it generate indexes with descending sequence on columns for absolutely no good reason.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    thanks for your comments. I have made an explain and with two indexes explain was better in third decimal. I have executed SQL with two indexes and with only with second one and the execution time was the same (differences is about a second). So I dropped first index.

    Marcus_A, I have seen some strange suggestions from db2advis too. But sometimes I like to see second opinion to get some extra idea. Specially if there is a complex SQL to handle.

    Thanks,
    Grofaty

Posting Permissions

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