Interview Questions   Tutorials   Discussions   Programs   

DB2 - What is index cardinality?




375
views
asked mar August 21, 2014 12:40 PM  

What is index cardinality?


           

1 Answers



 
answered By Mswami   0  
Indexes and Column Cardinality:

Column cardinality, that is the number of distinct values for a column, is an important consideration when building composite indexes. You should analyze column cardinality for columns in a multi-column index. Cardinality for multi-column indexes is obtained by performing RUNSTATS on the indexes with the KEYCARD option. If cardinality on a specific group of columns other than 1 to n combinations of an index which contains n columns, then the RUNSTATS option with the COLGROUP option should be used.

DB2 records and stores column cardinality in the DB2 Catalog and then uses this information when optimizing SQL to determine access paths. The following cardinality columns are used by the DB2 optimizer:

COLCARDF: contains the number of distinct values for this column. The optimizer uses this column to calculate the filter factor for a composite index when equals predicates are not specified for each column. Found in SYSIBM.SYSCOLUMNS; partition level information is found in SYSIBM.SYSCOLSTATS.

FIRSTKEYCARDF: contains the number of distinct values of the first column in an index. This information is captured only for the first column in a multi-column index. It will be used by the DB2 optimizer when calculating the filter factor for a predicate on a single column index. Found in SYSIBM.SYSINDEXES; partition level information is found in SYSIBM.SYSINDEXSTATS.

FULLKEYCARDF (SYSIBM.SYSINDEXES): contains the number of distinct values for the combined, entire key (all columns) of an index. The optimizer uses this column to calculate the filter factor for a composite index when an equality predicate is specified for each column of a multi-column index. Found in SYSIBM.SYSINDEXES; partition level information is found in SYSIBM.SYSINDEXSTATS.

CARDF, COLGROUPCOLNO, NUMCOLUMNS (SYSCOLDIST): contains the number of distinct values for the combinations of 1 to n columns of a multi-column index. This information is gathered when RUNSTATS is performed with the KEYCARD option or COLGROUP option. Partition level statistics are stored in SYSCOLDISTSTATS table.
flag   
   add comment

Your answer

Join with account you already have

FF

Preview

 Write A Tutorials
Online-Classroom Classes
www.writeabc.com


  1 person following this question

  1 person following this tag

  Question tags

Asked 2 years and 3 months ago ago
Number of Views -375
Number of Answers -1
Last updated
3 years and 3 months ago ago

Ready to start your tutorial with us? That's great! Send us an email and we will get back to you as soon as possible!

Alert