Understanding Confidence in Teradata Explain Plans

I'm a big fan of Carrie Ballinger's Tech Support column in the Teradata Magazine. Carrie is a Teradata Certified Master and works in the Teradata Performance Centre in Los Angeles.

Her latest article came as a revelation to me because I've spent many years working on Teradata systems and I thought I new about Confidence until I read the article. The following is an edited summary, the full article is available here.

Confidence levels indicate the degree to which Statistics are (or are not) available for a given query step. Confidence is driven by statistics and the more complete the statistics, the higher the confidence level will be. Both high and low confidence are perfectly acceptable - both are treated the same for optimizer costing purposes.

Confidence takes the form of four discrete values

High Confidence

High confidence will appear on a step that performs database access if a single predicate exists in the query and statistics exist on the predicate AND the primary index.

Multiple columns in the predicate are allowed, as long as multi-column statistics cover them.

Low Confidence

You get Low Confidence if usable statistics are present (either from collected statistics or random sampling).

You'll also get low confidence if;

• Multiple predicates appear in the query for the table being accessed.
• No PI statistics are collected
• The step is a JOIN step

Index Join Confidence

Index Join Confidence will appear in instances when an index is present on one side of the join, but no index or statistics are on the other side.

No Confidence

No Confidence is assigned when no usable statistics exist for a step. No confidence trickles down to subsequent dependant steps, even if they have usable statistics. Subsequent steps may therefore display as No Confidence when in fact they are making use of available statistics.


Thanks to Carrie for shedding light on this.