On Fri, Jul 31, 2015 at 10:55 AM, Ram N <yramiyer(at)gmail(dot)com> wrote:
>
> Thanks Qingqing for responding. That didn't help. It in fact increased the
> scan time. Looks like a lot of time is being spent on the NestedLoop Join
> than index lookups though I am not sure how to optimize the join.
>
Good news is that optimizer is right this time :-). The NLJ here does
almost nothing but schedule each outer row to probing the inner index.
So the index seek is the major cost.
Have you tried build a two column index on (b.start_date, b.end_date)?
Regards,
Qingqing