Archives of the TeradataForum

Message Posted: Mon, 15 Dec 2003 @ 16:41:13 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

We just had an NCR consultant in last week to look at our system performance. One of the things we discussed was table statistics. He told me that we did not need to collect statistics on table columns that did not have values that were less than 5% of the total of values. So if you only had two distinct values in a column, you would not need to collect statistics on that column because the parser would ignore them anyway. But if you had 20 evenly distributed values for that column, collecting statistics would help.

I opened an incident with NCR for a query that was running out of spool. They told me that the problem was caused from no statistics being collected on one of the columns in the where clause of the query. The column has 2 values, one occurs 1,374,164 times, the other 562,945 times.

I have heard other people say that statistics should be collected on all columns and indexes in a table.

Does anyone have any feedback on this question?

Also, here is the query that is failing in case anyone is interested. The statistics they tell me to collect are on the generic_preferred column of the dss_tables.preferred_sales_roll1 table.

SELECT country_code,
r.product_code,
product_code_descr,
r.sim_mfr_no,
r.sim_item_no,
sum(total_sales),
sum(total_cost)
FROM dss_tables.preferred_sales_roll2 r LEFT OUTER JOIN
dss_tables.product_code p
ON r.product_code = p.product_code_id
WHERE (r.generic_preferred <> 'N'
OR r.generic_preferred is null)
AND (xref_group NOT IN (SELECT xref_group
FROM dss_tables.preferred_sales_roll1
WHERE r.generic_preferred = 'N'
AND xref_group is not null
GROUP BY 1)
OR xref_group is null)
GROUP BY 1,2,3,4,5;

	<Prev	Next>		<<First	<Prev	Next>	Last>>