15.00 - Guidelines for Collecting Statistics on Single-Table Join Indexes - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

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 
      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);

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.