confidence showned in explain plan

UDA
Enthusiast

confidence showned in explain plan

Hi
i want to know that, what exactly the difference b/w NO, Low, High and Index Join Confidence showned in the explain plan. And how exactly we convert No Confidence to High confidence.

Any Suggestions on this is greatly appreciated.

Dixon
7 REPLIES
Enthusiast

Re: confidence showned in explain plan

Hi Dixondixon,
the difference between these types of confidences is in the way the optimizer succeeds to estimate the dimension and the number of rows for each step of the query.

No and Low confidence indicate that there are no statistics collected on tables; the suggestion is to collect statistics on the most used columns in order to improve the plan. You'll see that if you collect them in a good way, your explain plan will become better and lot of no/low confidence might become high.

BTW, on certain TD versions you can execute the following sql in a queryman window after executing explain:

diagnostic helpstats on for session; at the end of your explain, you'll see all the statistics that Td recommends. (pay attention that sometimes it's enough collecting only some of them! besides, tuning an sql by your own is a very good excercise.).

Hope this helps,
Bye,
TDUser
Enthusiast

Re: confidence showned in explain plan

Hi TDuser

thanks for the reply especially last tip helped me lot, can u tell me something on Index Join Confidence, how the optimizer shows this kind of confidence.i checked the explain plan step which is showing this index join confidence but when i verified stats on the joined columns, either left or right table join column, don't have stats. is it ok?. and i also heard that index join confidence is better than low confidence or NO confidence.

please guide on this.
Enthusiast

Re: confidence showned in explain plan

Hi dixon,
if I were you I would collect stats on the joined columns of both right and left table.

I think index join confidence is bettere than no confidence; I've heard that it is worst than low confidence... but I don't really know about this.

Anyone in the forum could spread light on this argument??

Bye,
TDUser
Enthusiast

Re: confidence showned in explain plan

Hi TDUser

i researched that an index join confidence occurs(in an EXPLAIN PLAN) when the join happenning between primary index column of one table with the primary or secondary index column of the other table.

It is the best join than nested-join or merge-join. but it is also becomes worst join, when no stats have been collected on either one of the joined columns and in such case HIGH or LOW confidence is better than INDEX JOIN confidence.

Thanks, for your i/p's
Enthusiast

Re: confidence showned in explain plan

Great!
I've always supposed that index join confidence was not so good, due to my direct experience; but this was probably due to having poor statistics...

Thank you for you explanation, have a nice evening,
TDUser

Re: confidence showned in explain plan

High confidence:
restricting condstatistics.
– Restricting conditions exist on index(es) having no statistics, but estimates can be based upon a sampling of the index(es).

Low confidence:

– Restricting conditions exist on index(es) or column(s) that have collected
statistics but are “AND-ed” together with conditions on non-indexed
columns.
– Restricting conditions exist on index(es) or column(s) that have collected
statistics but are “OR-ed” together with other conditions.

No confidence:

– Conditions outside the above.

Re: confidence showned in explain plan

No Confidence indicates that cardinality is a pure guess.

FK Confidence indicates that the cardinality estimate is based on the optimizer's belief that a foreign key join is being performed. It's assumedto be better than a pure guess.

Low Confidence indicates that the cardinality is based on the estimates supplied by the AMPs.Its better than a pure guess most of the time.

High Confidence indicates that the cardinality is based on collected statistics or unique indexes.