This topic describes how to determine data demographics for multicolumn objects such as composite indexes. Composite indexes are also referred to as multicolumn indexes.
No index of any kind can be based on columns typed as XML, BLOB, or CLOB.
Sources for the Required Information
The following table points to the sources for the multicolumn data demographics requested by the Table form. With the exception of the information documented here, sources are identical to those documented by Sources for the Required Information.
Information | Source |
---|---|
Distinct values | See the following topics. |
Maximum rows/value | |
Rows/null | |
Typical rows/value |
Example: Form for Two Multicolumn Index Candidates
This example form, which continues the form illustrated in Table Form Example indicates two multicolumn index candidates.
Information acquisition for the second of the two multicolumn specifications is made more simple because the specified columns constitute the primary key for the table.
Because the index candidates are both composite, you cannot use an identity column to define them.
- All demographics must be unique.
- No demographics can be null.
Therefore, the value for Rows Null is always 0.
- The value for Distinct Values always matches the Cardinality for the table and the value for Maximum Rows/Value is always 1.
Note that the value for Change Rating is also 0 by default because primary key values should never be changed.
(Multicolumn index considerations)
Page: of |
ELDM Page:
System: Patient Tracking |
||
Table Name:
Patient_Service_History |
Table Type: History | Cardinality: 730,000 | Data Protection: RAID 5 |
Column Name | Patient_ID Service_ID |
Patient_ID Service_ID Timestamp |
|
PK/FK/ID | FK | PK | |
Constraint Number | |||
Value Access Frequency | 120,000 | ||
Join Access Frequency | 2,200 | 300,000 | |
Join Access Rows | 200,000 | 300,000 | |
Distinct Values | 8,000 | 730,000 | |
Maximum Rows/Value | 365 | 1 | |
Maximum Rows Null | 0 | 0 | |
Typical Rows/Value | 90 | 1 | |
Change Rating | 0 | 0 | |
PI/SI |
Demographic Trends as a Function of the Number of Columns
Certain characteristics of the demographics of a candidate index change in predictable ways as more columns are added to the index definition.
- The number of distinct values increases because each additional index column further enhances the singularity of the row.
- The number of rows per value decreases because the number of values increases proportionately.
- The selectivity of the candidate index increases because any set of index column values points to fewer rows.
The following table summarizes these trends:
Index Columns | Number of Distinct Values | Number of Rows Per Value | Selectivity |
---|---|---|---|
State | Least | Most | Lowest |
State + Zip Code | ? | ? | ? |
State + Zip Code + Last Name | Most | Least | Highest |
Calculating Multicolumn Demographics
You must have an understanding of the underlying data before you can undertake a legitimate calculation of the demographics for multicolumn situations.
The following table provides some calculation guidelines for determining the number of distinct values in a binary (two column) situation. The guidelines scale as more columns are added to the candidate index.
IF distinct column_1 values … | THEN the total number of distinct values in the two-column index equals … | ELSE the number of distinct values is … |
---|---|---|
can be paired with distinct values from column_2 | the product of the number of distinct values in column_1 and the number of distinct values in column_2 | approximately equal to the product of the number of distinct values in column_1 and the average number of values in column_2 that can be paired with a single column_1 value. |
Example Cases for These Guidelines
The following examples illustrate the guidelines stated in Calculating Multicolumn Demographics.
- Consider the following table fragment. The candidate index for which multicolumn demographics are to be estimated is (ProductCode, ColorCode).
… | ProductCode | ColorCode | … | |
… | … | … | … | |
… | … | … | … |
The product (ProductCode) * (ColorCode) is true if every product is available in every color.
- Consider the following table fragment The candidate index for which multicolumn demographics are to be estimated is (StateCode, ZipCode).
… | StateCode | ZipCode | … | |
… | … | … | … | |
… | … | … | … |
- The product (StateCode) * (ZipCode) is not an accurate estimate of the number of distinct values in the multicolumn index because the resulting product has fewer distinct values than the product of distinct StateCode and distinct ZipCode values.
- If you cannot pair every value in the StateCode column with every value in the ZipCode column, then the number of distinct values in the multicolumn index is approximately equal to (Number of distinct StateCode values) * (Average number of ZipCode values that can be paired with a single StateCode value).
For example, the number of distinct values is the product of the number of states in the US and the average number of zip codes per state, which we estimate to be 20.
Therefore, the number of distinct values for the StateCode + ZipCode index would be roughly 50 * 20, or 1,000.
- The same considerations hold most of the other rows per value calculations.
For example, if every value of column_1 can be paired validly with every value for column_2, then the result of the division is a fairly accurate estimate.
For each value estimated, the minimum is never less than the number of column_1 rows per value divided by the number of distinct column_2 values.
The exception is the count of Rows Null (labeled Rows/Null). See the next section for information on handling multicolumn indexes that are wholly or partially null.
Treating the Rows Null Counts for Composite Indexes
An index can be partly or wholly null. This is almost never a good idea, however. See Designing for Missing Information for information about SQL nulls and the many problems they cause with database integrity.
Consider the following example:
employee_number | department_number |
PK | |
FK | FK |
103794 | 7012 |
? | 7012 |
105378 | ? |
? | ? |
- The first row has both employee and department numbers.
- The second row has a null employee number and a department number.
- The third row has an employee number and a null department number.
- The fourth row has nulls for both its employee number and its department number.
All four rows are valid primary index rows. Should they all contribute to the count for the Rows Null value or should they be treated differently?
The following table provides recommended treatment guidelines for partially and wholly null composite indexes. Composite indexes are also referred to as multicolumn indexes.
IF a composite index value is … | THEN you should … |
---|---|
wholly null | add it to the Rows Null count. |
partially null | not add it to the Rows Null count. |
The Optimizer can detect the number of distinct values from the rows that are partially null and it tracks them separately from the rows that are wholly null. If a column has many nulls, but is not usually specified in predicates, you probably should not include that column in the multicolumn statistics you collect.
For example, suppose you are considering collecting multicolumn statistics on columns 1, 2, and 3 of a table, and their values are like the following, where a QUESTION MARK character represents a null.
column 1 | column 2 | column 3 | |
1 | 2 | 3 | |
? | 2 | 3 | |
1 | ? | 3 | |
1 | 2 | ? | |
? | ? | ? | |
1 | 2 | ? | |
1 | 2 | ? | |
1 | 2 | ? | |
1 | 2 | ? | |
1 | 2 | ? |
You would probably not want to collect multicolumn statistics that include column 3 unless it is specified in a large number of predicates.