All Forums Database
ispaleny 11 posts Joined 12/04
30 Jul 2007
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

sachinp17 53 posts Joined 11/06
31 Jul 2007

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

foxbat 27 posts Joined 06/07
31 Jul 2007

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

Jim Chapman 449 posts Joined 09/04
31 Jul 2007

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.

You must sign in to leave a comment.