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
- 14693 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
Explain the term 'primary key' related to relational database management system?
What is the difference between union and union all in teradata?
Mention the procedure via which, we can run Teradata jobs in a UNIX environment.
What is teradata? Also, list out some of the primary characteristics of teradata.
What are the different functions included in the server software?
How to identify ppi columns?
Is it necessary to add? Quit statement after a bteq query when I am calling it in a unix environment?
What exactly do you know about catching in teradata?
List out some of the primary characteristics of teradata.
What do you mean by teradata sql assistant?
Backup Script was blocked you are unable to archive the data now. how do you analyze it and where do you identify ?
Explain the new features of teradata?
Describe primary index in teradata?
What is spool space?
How teradata makes sure that there are no duplicate rows being inserted when its a set table?
