Collecting Statistics on Hash Index Columns - Advanced SQL Engine - Teradata Database

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 a hash index column appears frequently in WHERE clause predicates, you should consider collecting statistics on it as well, particularly if that column is the sort key for a value-ordered hash index.
• Consider collecting statistics on the base table columns that are also part of the hash index rather than collecting statistics on the hash index itself.

General Guidelines

When you create a hash index with a BY clause, you can collect and drop statistics on those columns using an INDEX clause to specify the column set. For example, suppose a hash index has the following definition.

     CREATE HASH INDEX OrdHIdx8 (o_custkey, o_orderdate) ON orders
     BY (o_custkey, o_orderdate)
     ORDER BY (o_orderdate);

Then you can collect statistics on the partitioning columns as shown in the following example.

     COLLECT STATISTICS ON OrdHIdx8 INDEX (o_custkey, o_orderdate);

Note that you can only use columns specified in a BY clause definition if you use the keyword INDEX in your COLLECT STATISTICS statement.

A poorer choice would be to collect statistics using the column clause syntax. To do this, you must perform two separate statements.

     COLLECT STATISTICS ON OrdHIdx8 COLUMN (o_custkey);

     COLLECT STATISTICS ON OrdHIdx8 COLUMN (o_orderdate);

It is always better to collect the statistics for a multicolumn index on the index itself rather than individually on its component columns because its selectivity is much better when you collect statistics on the index columns as a set.

For example, a query with a predicate like WHERE x=1 AND y=2 is better optimized if statistics are collected on INDEX (x,y) than if they are collected individually on column x and column y.

The same restrictions hold for the DROP STATISTICS statement.

Collecting Statistics on Base Table Columns Instead of Hash Index Columns

The Optimizer substitutes base table statistics for hash index statistics when no demographics have been collected for its hash indexes. Because of the way hash index columns are built, it is generally best not to collect statistics directly on the index columns and instead to collect them on the corresponding columns of the base table. This optimizes both system performance and disk storage by eliminating the need to collect the same data redundantly.

Collect statistics on a hash index or non-sparse join index directly in the following scenarios:

• If you decide not to collect the statistics for the relevant base table columns for some reason, then you should collect them directly on the corresponding hash index columns.
• If the hash index is row or column partitioned, collect single-column and multicolumn PARTITION statistics directly on the hash index.
• Collect SUMMARY statistics directly on the hash index as the summary attributes such as database block size, row size, etc. differ from the underlying base table.