Guidelines for Collecting Statistics on Single-Table Join Indexes
For most applications, you should collect the statistics on base table columns rather than on single-table join index columns. See “Collecting Statistics on Base Table Columns Instead of Single-Table Join Index Columns” on page 596 for further information.
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
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);
Note that 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 would be 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);
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.