Db2 On Premise and Cloud

Expand all | Collapse all

Question about multiple named indexes on same set of columns

  • 1.  Question about multiple named indexes on same set of columns

    Posted Fri May 10, 2019 08:45 AM
    I'm new to a database that I'm finding is rampant with duplicate indexes. One table that has 18 columns has 57 named indexes, with 20 indexes on the same 3 columns!

    This seems like crazy making to me, but I don't know enough about DB2 internals. Is DB2 smart enough to only maintain a single index under the hood of all that? Or is each atomic change (CUD) hitting 57 different indexes?


    ------------------------------
    Quickdraw
    ------------------------------


  • 2.  RE: Question about multiple named indexes on same set of columns

    Posted Mon May 13, 2019 10:39 AM
    The answer depends on the DB2 you refers to.  In the case of DB2 for IBM i, depending on the order indexes are created, it may avoid duplication.  Specifically, if you create an indexes on columns A, B, and C, and then, create an index on columns A, and B, they will share the access path (the internal index structure).  Anyway, the best practice is having discipline on index creation.

    ------------------------------
    DANIEL JOSE LEMA GUANZIROLI
    ------------------------------