Interview Questions   Tutorials   Discussions   Programs   

Teradata - What is collect State in Teradata ? What it use and how it works?

asked mar September 20, 2014 06:38 AM  

What is collect State in Teradata ? What it use and how it works?


1 Answers

answered By vishnoiprem   0  
Collect stats collects demographic data about the columns or indces of a table.This statistics is used by the PE to optimize the plan which can be viewed by explain command.
When collect stats is executed,the statistics is stored in data for use of PE.

The Optimizer plans an execution strategy for every SQL query submitted to it. We have also seen that the execution strategy
for any query may be subject to change depending on various factors. For the Optimizer to consistently choose the optimum strategy,
it must be provided with reliable, complete, and current demographic information regarding all of these factors. The best way to assure that
the Optimizer has all the information it needs to generate optimum
execution strategies is to COLLECT STATISTICS.

Statistics tell the Optimizer how many rows/ value there are.
The Optimizer uses statistics to plan the best way to access data.
May improve performance of complex queries and joins.
NUSI Bit Mapping requires collected statistics.
Helpful in accessing a column or index with uneven value distribution.
Stale statistics may mislead the Optimizer into poor decisions.
Statistics remain valid across a reconfiguration of the system.
COLLECT is resource intensive and should be done during off hours.
   add comment

Your answer

Join with account you already have



Ready to start your tutorial with us? That's great! Send us an email and we will get back to you as soon as possible!