Search This Blog

Monday, March 22, 2010

Collect Statistics In Teradata

Collect stats is an important concept in teradata.
collect stats gives PE to come up with a plan with least cost for an requested query.
Collect stats defines the confidence level of PE in estimating "how many rows it is going to access ? how many unique values does a table have , null values etc and all this info is stored in data dictionary. Once you submit a query in TD the parsing engine checks if the stats are avaiable for the requested table , if it has collected stats earlier PE generates a plan with "high confidence" .
in absence of collect stats plan will PE with "low confidence" . however teradata's optimizer is very robust and intelligent, even if you do not collect stats on a table,column,indexes PE does an "Dynamic Amp sampling " which means it will select a random amp and this random amp comes up with the info about the table data it has , based upon this PE ( knows datademographics & available system componets) estimates the workload and generates a plan.

No comments:

Post a Comment