Non equi join condition

Database
Enthusiast

Non equi join condition

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?
3 REPLIES
Enthusiast

Re: Non equi join condition

if your PI consists of only the first column, then you (might) still get the benefit of getting an AMP local join.
Enthusiast

Re: Non equi join condition

I agree to that.
The problem lies in the second and third joins where non equi joins are used.
Is there a way to break up this? Or an alternate way to implement this.

FYI:-All three joins are inner joins.
Enthusiast

Re: Non equi join condition

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.