Collection of statistics on DBC database

Database
Enthusiast

Collection of statistics on DBC database

Hi,

What are recommendations for collection of statistics on DBC database?

The DB system is Teradata V2 R6.1 MPP.

Thanks in advance,

Ivo Spaleny
3 REPLIES
Enthusiast

Re: Collection of statistics on DBC database

1. Collect the statistics which contribute to more than 10-15% of total rows.
2. All SI columns.
3. UPI having >1000 rows/amp

Regds,
sachin khade

Enthusiast

Re: Collection of statistics on DBC database

Statistics are not usually collected on dictionary tables. One possible reason is the information stored there changes frequently (example: DBC.ALLRights) and may therefore give scope for poor performance when the statistics goes stale.

In the absence of statistics, the estimates are sampled randomly on a AMP and these are fairly good in most of the cases (for dictionary tables).

-Foxbat
Enthusiast

Re: Collection of statistics on DBC database

Unless you regularly query dictionary tables, collecting statistics on them serves no purpose. The system-initiated dictionary retrievals that occur during SQL compilation do not benefit from statistics because they don't pass through the optimizer.