Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2010
    Posts
    40

    Unanswered: Index Cardinality

    I have an index with 4 columns.When I queried SYSCAT.INDEXES I see that FIRSTKEYCARD is small(In thousands) and FIRST2KEYCARD is more(in Billions) and so on for the other 2 obviously.

    I have another index with the same 4 columns with first and second columns places swapped and the FIRSTKEYCARD is much bigger(In billions) and obviously FIRST2KEYCARD is much bigger.

    Now the question is does DB2 optimizer always choose the second over first one?

    RUNSTATS are upto date and both are clustered Indexes and PCTFREE is 0 for both.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    First of all, you can only have one clustered index for a table.

    Which index is used depends on the SQL being processed.

    Andy

  3. #3
    Join Date
    Aug 2010
    Posts
    40
    Quote Originally Posted by ARWinner View Post
    First of all, you can only have one clustered index for a table.

    Which index is used depends on the SQL being processed.

    Andy
    Yes that is correct sorry...One is a clustered one and other one is not.

  4. #4
    Join Date
    Aug 2010
    Posts
    40
    Actually what I am trying to find out is...Why optimizer is chosing an index which has first key cardinality very less over the one which has high cardinality. does Cardinality factor plays any role in chosing the index?

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by Rajesh1203 View Post
    Actually what I am trying to find out is...Why optimizer is chosing an index which has first key cardinality very less over the one which has high cardinality. does Cardinality factor plays any role in chosing the index?
    Yes, but so does the query.

    Andy

Posting Permissions

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