Guidelines for Collecting Statistics on Single-Table Join Indexes - Teradata Vantage

For most applications, collect the statistics on base table columns rather than on single-table join index columns.

You can collect and drop statistics on the primary index columns of a single-table join index using an INDEX clause to specify the column set. For example, suppose a join index has the following definition.

CREATE JOIN INDEX OrdJIdx8 AS
SELECT o_custkey, o_orderdate
FROM Orders
PRIMARY INDEX (o_custkey, o_orderdate)
ORDER BY (o_orderdate);

Then you can collect statistics on the index columns as a single object as shown in the following example.

COLLECT STATISTICS ON OrdJIdx8 INDEX (o_custkey, o_orderdate);

You can only use columns specified in a PRIMARY INDEX clause definition if you use the keyword INDEX in your COLLECT STATISTICS statement.

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

COLLECT STATISTICS ON OrdJIdx8 COLUMN (o_custkey);
COLLECT STATISTICS ON OrdJIdx8 COLUMN (o_orderdate);

Collecting the statistics for a multicolumn index on the index itself is preferable to collecting statistics individually on its component columns, because 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 collected individually on column x and column y.

The same restrictions hold for the DROP STATISTICS statement.