Interested to Buy Any Domain ? << Click Here >> for more details...
how to delete duplicate records in multi set table without using any tables
- 2 Answers
- 14580 Views
- Accenture, Nest, South Nests Software Solution, Spiro Solutions, I also Faced
- E-Mail Answers
Answer Posted / liji anooj
Confidence Levels For Single‑Table Retrieval Operations
The following table lists the meaning of each confidence level in context and some of the reasons why each confidence level is assigned for single‑table retrieval operations.
Confidence Level
Meaning
Reason
No
Teradata Database has neither Low nor High confidence in the cardinality and distinct value estimates for the relation.
The Optimizer pursues conservative strategies to optimize the relevant steps.
Any of the following situations exists.
• No statistics have been collected on the column or index sets specified in the predicate.
• The predicate contains complex expressions for which base table statistics cannot be collected.
For example, you cannot collect base table statistics for either of the following two expressions:
• SUBSTR(col1,5,10)
• CASE
WHEN x=10 THEN x+10
ELSE x-10
You can collect statistics on a single‑table join index or hash index defined using a complex expression (see “Using Join Index Statistics to Estimate Single‑Table Expression Cardinalities”), and the optimizer can use those statistics to make single‑table cardinality estimates. If you have collected such statistics, then a cardinality estimate can be made with high confidence.
• For an aggregate estimation, no statistics have been collected on the grouping columns.
Low
Teradata Database is moderately certain that the estimated cardinality and distinct value estimates for the relation are accurate.
The Optimizer pursues more aggressive strategies to optimize the relevant steps.
One of the following states exists for the relation.
• There are conditions in the query on an index set for which no statistics have been collected.
Cardinality estimates can be made based on sampling the index set.
• There are conditions in the query on an index or column set with collected statistics that is ANDed with conditions on unindexed columns.
• There are conditions in the query on an index or column set with collected statistics that is ORed with other conditions.
• For an aggregate estimation, there are statistics on single columns of the grouping column set or statistics on some, but not all, of the grouping columns.
The confidence for single‑AMP dynamic AMP statistical samples is always Low.
EXPLAIN reports always express No confidence in estimates where no statistics have been collected, but Teradata Database always samples dynamically from at least one AMP in such cases when the query is actually executed.
High
Teradata Database is fairly certain that the estimated cardinality and distinct value estimates for the relation are accurate.
The Optimizer pursues more aggressive strategies to optimize the relevant steps.
• Retrieval from a single relation with no predicates:
• There are conditions in the query on the primary index and statistics have been collected on the primary index.
• There are conditions in the query on the primary index, but are no statistics have been collected on the primary index.
The confidence is High under any of the following situations:
• 5 or more AMPs are sampled dynamically.
• Rows per value are sampled using a dynamic
AMP sample.
• No skew is detected.
• Retrieval from a single relation with predicates:
• Statistics have been collected on the predicate columns or indexes and there is no skew.
• There are multiple equality predicates on which covering multitable statistics have been collected.
• Single‑table join index or hash index statistics have been collected on a complex expression specified in a predicate written against the underlying base table of the hash or single‑table join index.
• Retrieval from a single temporal relation with predicates and a unique join index that can be used as an access path:
• Statistics have been collected on the join index that match the query predicate columns.
• For an aggregate estimation, the confidence is high under any of the following situations:
• The grouping columns are constants.
• The grouping columns have equality predicates.
• The grouping columns are all covered by a set of single multicolumn statistics.
• Statistics have been collected one the single grouping column.
| Is This Answer Correct ? | 0 Yes | 2 No |
Post New Answer View All Answers
What are some primary characteristics of teradata?
What is bteq script in teradata?
What is the opening step in basic teradata query script?
Can we have an unconnected lkp to lookup a db2 record against a teradata record? Doesnt seem to work. I could be wrong
How to run a query in teradata?
Describe the between keyword in teradata?
What is collect statistics?
What are the functions of a Parser?
What is node? How many nodes and amps used in your previous project?
Give a justifiable reason why Multi-load supports NUSI instead of USI.
What is a clique?
List the logical and conditional operators that are used with teradata along with their meanings?
Can any one explain me the difference between BTEQ and MLOAD,TUMP. All canbe used for same purpose but still differnt methods. why ?
My table got locked during mload due to a failed job. What do I do to perform other operations on it?
Explain parsing engine in teradata?
