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.).
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.
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.
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).
– 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.