General Guidelines
You should collect statistics on appropriate columns of a hash index frequently just as you would for any base table or join index. For most applications, you should collect the statistics on base table columns rather than on hash index columns. See “Collecting Statistics on Base Table Columns Instead of Hash Index Columns” on page 561 for further information.
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.