Of 3 join conditions first is equi join, second is = join. First two conditions use primary index of left table. I would like to know if the primary index would be useful in this join (ie one column equi join and other column <= join). Though stats are collected the confidence is low. Could someone suggest me some method to boost the confidence?
it's difficult to suggest something without knowing the actual joins / cardinailty /PIs and the explains.
Most of the time when you have a non-equi join in the condition, the optimizer will chose to duplicate the smaller table / spool and do a prod join. which is good generally.
Having low confidence / High confidence by itself is no guarantee about the performance, you may have a low confidence and still get an efficient plan. Confidence levels just signify how comfortable optimizer is with the cost estimates it did for the plan. It doesn't necessarily reflect the actuals.