Friends:
Please indulge my flurry of postings lately.
I've been struggling with a problem for weeks: Within an SP, I'm self-
joining a table three times (self-join 1 UNION self-join 2 UNION self-
join 3), on different criteria in each case (in lieu of doing a single
self-join on complex OR predicates).
The optimizer insists on doing a MSJOIN for each of the three joins,
which is proving disastrous, execution-wise. The execution flow for
each is SORT leg 1, SORT leg 2 then MSJOIN. The optimizer always
insists on re-sorting the data, i.e., having to do that never seems to
discourage it from using an MSJION and use an HJOIN instead. I want it
to use the latter operator (HSJOIN) instead, though, as when it does,
performance is stellar.
I'm on 8.2 FixPak 10, and may be bumping up against APAR IY78984.
Upgrading to a later FixPak is not an option.
Note that the self-joined table is a DGTT. Further, I always get the
MSJOINs after running statistics on the DGTT. Perversely, If I run no
statistics after populating the DGTT, I almost always get HJOINs and
hence the better performance. Incidentally, I spent the better part of
a day investigating column-group statistics to no avail.
As you can imagine "almost always" getting HJOINs isn't going to work,
as that means the SP will only "almost always" work.
As we don't have optimizer hints to work with, anyone have any ideas
of how I can force the optimizer to always use an HJOIN?
Thanks,
--Jeff
Please indulge my flurry of postings lately.
I've been struggling with a problem for weeks: Within an SP, I'm self-
joining a table three times (self-join 1 UNION self-join 2 UNION self-
join 3), on different criteria in each case (in lieu of doing a single
self-join on complex OR predicates).
The optimizer insists on doing a MSJOIN for each of the three joins,
which is proving disastrous, execution-wise. The execution flow for
each is SORT leg 1, SORT leg 2 then MSJOIN. The optimizer always
insists on re-sorting the data, i.e., having to do that never seems to
discourage it from using an MSJION and use an HJOIN instead. I want it
to use the latter operator (HSJOIN) instead, though, as when it does,
performance is stellar.
I'm on 8.2 FixPak 10, and may be bumping up against APAR IY78984.
Upgrading to a later FixPak is not an option.
Note that the self-joined table is a DGTT. Further, I always get the
MSJOINs after running statistics on the DGTT. Perversely, If I run no
statistics after populating the DGTT, I almost always get HJOINs and
hence the better performance. Incidentally, I spent the better part of
a day investigating column-group statistics to no avail.
As you can imagine "almost always" getting HJOINs isn't going to work,
as that means the SP will only "almost always" work.
As we don't have optimizer hints to work with, anyone have any ideas
of how I can force the optimizer to always use an HJOIN?
Thanks,
--Jeff
Comment