Search This Blog

Showing posts with label collect stats. Show all posts
Showing posts with label collect stats. Show all posts

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.