What is collect State in Teradata ? what it use and how it
works??
Answers were Sorted based on User's Feedback
Answer / siri
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.
Is This Answer Correct ? | 7 Yes | 0 No |
Answer / yuvaevergreen
-->"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.
Is This Answer Correct ? | 5 Yes | 1 No |
Answer / tdguy
Stores the demographic data about the columns. Very
important for better performance.
Is This Answer Correct ? | 2 Yes | 0 No |
How many codd's rules are satisfied by teradata database?
IBM QUESTION As all we know PE takes the SQL Request and it will give responce for that query. what is the internal process happening.how it is taking that sql from the system????? thanks in Advance
2 Answers Cap Gemini, IBM, Infosys,
Highlight the need for Performance Tuning.
what is object level locking ? where do appear this type of locking ?
can we have an unconnected lkp to lookup a DB2 record against a Teradata record?
If I wanted to run a TPump job only once per day - basically working on a file that is produced once per day - how would you set up the parameters for that sort of job ?
what is use of PI(primary index) AND SI(secondary index)
What is spool space? Why do you get spool space errors? How do trouble-shoot them?
can we load 10 millions of records into target table by using tpump?
Why FLOAD does not support multiset tables??
What is the difference between global temporary tables and volatile temporary tables?
Please tell me a query to find the Primary key,Foreign key,primary Index,PPI for the Database?