Results 1 to 5 of 5

Thread: db2 indexes

  1. #1
    Join Date
    May 2002
    Posts
    47

    Wink Unanswered: db2 indexes

    I want to know when an index is not used.
    I have a composite index on columns abc.

    I run several queries that attempt to scan the following columns:

    a,b,c (of course the index is used)
    a,b (still uses index)
    a,c (I don't think the index will be used)
    b,c (I don't think the index will be used)

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    There is no guarantee that DB2 will use any particular index, but if there is only one index on a table and it is on columns (a, b, c) then the following predicates will use the index as follows:

    a,b,c - matching index scan on 3 columns using b-tree

    a,b - matching index scan on 2 columns using b-tree

    a,c - matching index scan on 1 columns using b-tree

    b,c - non-matching index scan (reads entire index sequentially)

    Note that on (a,c) that it depends on what the cardinality (number of unique values) of what the first column is. If there are only a few unique values of column a, then DB2 might choose not to use the index because it would not likely reduce the number of (usually 4K) data pages in the table that have to be accessed.

    DB2 keeps statistics in the catalog (updated with runstats) on the cardinality of the columns and of the index taken as a whole (fullkeycard), and the first column of a multi-column index (firstkeycard).
    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
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: db2 indexes

    Originally posted by ksexton
    I want to know when an index is not used.
    I have a composite index on columns abc.

    I run several queries that attempt to scan the following columns:

    a,b,c (of course the index is used)
    a,b (still uses index)
    a,c (I don't think the index will be used)
    b,c (I don't think the index will be used)
    Your guesses are correct.

    Use dynexpln utility to generate expain plans and see for yourself. It's described in detail in "Administration guide: Performance".

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Your guesses are not necessarily correct, because they depend on the cardinality of the columns, the distribution of the data (number of occurrences of each value), and the clustering of 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

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Originally posted by Marcus_A
    Your guesses are not necessarily correct, because they depend on the cardinality of the columns, the distribution of the data (number of occurrences of each value), and the clustering of the table.
    That's right. I'm sounding a bit peremptory on Fridays

Posting Permissions

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