how to delete duplicate records in multi set table without using any tables

Question Posted / rajeshsaamantula9

2 Answers
13135 Views
Accenture, Nest, South Nests Software Solution, Spiro Solutions, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

how to delete duplicate records in multi set table without using any tables..

Answer / nidhi

make use of Qualify, though it wont delete duplicate..but
also wont show u duplicate as well in answer set.

Is This Answer Correct ?

5 Yes

7 No

how to delete duplicate records in multi set table without using any tables..

Answer / 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