confience showned in explain

Database
Enthusiast

confience showned in explain

if the explain plan shows Index Join Confidence for a particular join condition is it better than High Confidence. and also interested to know that how exactly optimizer uses Index Join Confidence, what is the criteria to choose that. is it ok that if one of the columns don't have stats on a particular join column in which the optimizer using Index Join Confidence.
1 REPLY
Enthusiast

Re: confience showned in explain

Hi,

This open question is old, but it seems to be popular.

Let's go your questions:

1) if the explain plan shows Index Join Confidence for a particular join condition is it better than High Confidence.

 In following order: No Confidence < Low Confidence < High Confidence < Index Join Confidence Level

2) and also interested to know that how exactly optimizer uses Index Join Confidence, what is the criteria to choose that.

 For Index Join Confidence Level, there must be a unique index or a foreign key (not a soft RI).

3) is it ok that if one of the columns don't have stats on a particular join column in which the optimizer using Index Join Confidence.

 It is always better to have statistics calculated. But in this case, you would gain very little improvement. Unique indexes are expected to be distributed well, so their estimated sample statistics are good enough for most situations.

Best regards,

Ivo Spaleny