what is mean by NO CONFIDENCE in teradata

Database
Enthusiast

what is mean by NO CONFIDENCE in teradata

Hello Experts:

what is mean by NO CONFIDENCE in teradata

In the Explain plan of query:

The size of Spool 1 is estimated with no confidence to be 2,430,428 rows

Tags (1)
2 REPLIES
Enthusiast

Re: what is mean by NO CONFIDENCE in teradata

You can have a look at the documentation in SQL Request and Transaction Processing.

Good read:

Confidence Levels For Single Table Retrieval Operations, Confidence Levels For Join Operations.

From the doc

The system 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. Reason  with example given from the doc: Any of the following situations exists:

• There are no statistics on the column or index sets 

specified in the predicate.

• The predicate contains complex expressions for 

which statistics cannot be collected.

For example, statistics cannot be collected for

either of the following two expressions:

• SUBSTR(col1,5,10)

• CASE

WHEN x=10 THEN x+10

ELSE x-10

• For an aggregate estimation, there are no statistics

on the grouping columns.

Enthusiast

Re: what is mean by NO CONFIDENCE in teradata

Thanks Raja