Use the COLLECT STATISTICS statement to define the table columns on which you want to collect statistics, for example:
COLLECT STATISTICS ON database_name.table_name COLUMN (column_1, column_2 ...);
At minimum, collect statistics on the primary index (PI) of each table. For small tables (<100 rows per AMP) PI statistics may be sufficient.
Recommendations for Large Tables
For best results on large tables, collect SUMMARY statistics only on non-partitioned tables. Other recommendations for large tables include collecting statistics on the following:
- All indexed columns
- All frequently joined columns
- Columns frequently referenced in WHERE clauses, particularly if the column contains skewed, nonunique data
- The system-derived column PARTITION (for all row and column-partitioned tables)