Collecting Statistics on Hash Index Columns
When there are no statistics for a hash index, the Optimizer uses the statistics of the corresponding base table rows, just as it does for a single-table join index.
The guidelines for selecting hash index columns on which to collect statistics are similar to those for base tables and join indexes. The primary factor to consider in all cases is whether the statistics provide better access plans. If they do not, consider dropping them. If the statistics you collect produce worse access plans, you should always report the incident to Teradata support personnel.
When you consider collecting statistics for a hash index, it might help to think of the index as a special kind of base table that stores a derived result. For example, any access plan that uses a hash index must access it with a direct probe, a full table scan, or a range scan.
With this in mind, consider the following when deciding which columns to collect statistics for.
|
IF an execution plan might involve … |
THEN collect statistics on the … |
|
search condition keys |
column set that constitutes the search condition predicate. |
|
joining the hash index with another table |
join columns to provide the Optimizer with the information it needs to best estimate the cardinalities of the join. |
|
IF a hash index is defined … |
THEN you should collect statistics on the … |
|
with a BY clause or ORDER BY clause (or both) |
primary index and order keys specified by those clauses. |
|
without a BY clause |
primary index column set of the base table on which the index is defined. |
|
without an ORDER BY clause and the order key column set from the base table is not included in the column_name_1 list |
order key of the base table on which the index is defined. This action provides the Optimizer with several essential baseline statistics, including the cardinality of the hash index. |