Forcing an HJOIN

[jefftyzzer]

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

[Serge Rielau]

jefftyzzer wrote:

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?

http://www.ibm.com/developerworks/db...hen/index.html
Oh you are on V8.1 FP9 ?
http://publib.boulder.ibm.com/infoce...n/r0012554.htm

Do the math...

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[jefftyzzer]

On Oct 11, 7:37 pm, Serge Rielau <[e-mail]:

jefftyzzer wrote:

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?

http://www.ibm.com/developerworks/db...le/dm-0612chen...
Oh you are on V8.1 FP9 ?http://publib.boulder.ibm.com/infoce...x.jsp?topic=/c...

Do the math...

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Thanks for your reply, Serge, but I'm afraid there's a subtlety here
that I'm missing.

In your *second* link (since we're on v8 FP 10), you refer to
REG_LIST_VARIABLES. Are you saying that there's an env var I'm
overlooking, or are you suggesting I take a cue from the *first* link
and give optimization profiles a try? The article is written for V9,
but its author says the functionality was introduced in In v8 FP9, or,
lastly, are you suggesting I drop the optimization level to, e.g., 2.

You know, I was re-reading the APAR I mention above, and it says that
the issue doesn't arise when the MSJOIN is a self-join. Mine is, so
perhaps it's not that bug I'm bumping-up against (but it sure seems
like it though: bad plan...joining on character columns...incorrect
cardinality estimates).

As another aside, I'm not sure if I should look to resolving this by
trying to get the MSJOIN to work correctly or by going down the path
I'm on presently and trying to always get an HJOIN. Any pointers on
that?

Thanks,

--Jeff

[jefftyzzer]

On Oct 11, 7:37 pm, Serge Rielau <[e-mail]:

jefftyzzer wrote:

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?

http://www.ibm.com/developerworks/db...le/dm-0612chen...
Oh you are on V8.1 FP9 ?http://publib.boulder.ibm.com/infoce...x.jsp?topic=/c...

Do the math...

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Thanks for your reply, Serge, but I'm afraid there's a subtlety here
that I'm missing.

In your *second* link (since we're on v8 FP 10), you refer to
REG_LIST_VARIABLES. Are you saying that there's an env var I'm
overlooking, or are you suggesting I take a cue from the *first* link
and give optimization profiles a try? The article is written for V9,
but its author says the functionality was introduced in In v8 FP9, or,
lastly, are you suggesting I drop the optimization level to, e.g., 2.

You know, I was re-reading the APAR I mention above, and it says that
the issue doesn't arise when the MSJOIN is a self-join. Mine is, so
perhaps it's not that bug I'm bumping-up against (but it sure seems
like it though: bad plan...joining on character columns...incorrect
cardinality estimates).

As another aside, I'm not sure if I should look to resolving this by
trying to get the MSJOIN to work correctly or by going down the path
I'm on presently and trying to always get an HJOIN. Any pointers on
that?

Thanks,

--Jeff

[jefftyzzer]

On Oct 11, 7:37 pm, Serge Rielau <[e-mail]:

jefftyzzer wrote:

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?

http://www.ibm.com/developerworks/db...le/dm-0612chen...
Oh you are on V8.1 FP9 ?http://publib.boulder.ibm.com/infoce...x.jsp?topic=/c...

Do the math...

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Thanks for your reply, Serge, but I'm afraid there's a subtlety here
that I'm missing.

In your *second* link (since we're on v8 FP 10), you refer to
REG_LIST_VARIABLES. Are you saying that there's an env var I'm
overlooking, or are you suggesting I take a cue from the *first* link
and give optimization profiles a try? The article is written for V9,
but its author says the functionality was introduced in In v8 FP9, or,
lastly, are you suggesting I drop the optimization level to, e.g., 2.

You know, I was re-reading the APAR I mention above, and it says that
the issue doesn't arise when the MSJOIN is a self-join. Mine is, so
perhaps it's not that bug I'm bumping-up against (but it sure seems
like it though: bad plan...joining on character columns...incorrect
cardinality estimates).

As another aside, I'm not sure if I should look to resolving this by
trying to get the MSJOIN to work correctly or by going down the path
I'm on presently and trying to always get an HJOIN. Any pointers on
that?

Thanks,

--Jeff

[Serge Rielau]

jefftyzzer wrote:

On Oct 11, 7:37 pm, Serge Rielau <[e-mail]:

>jefftyzzer wrote:

>>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?

http://www.ibm.com/developerworks/db...le/dm-0612chen...
Oh you are on V8.1 FP9 ?http://publib.boulder.ibm.com/infoce...x.jsp?topic=/c...

Do the math...

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Thanks for your reply, Serge, but I'm afraid there's a subtlety here
that I'm missing.

In your *second* link (since we're on v8 FP 10), you refer to

I didn't see the reference to DB2 V8.1 FP9 when I posted.
The intent was to show that DB2_OPTPROFILE=yes is available.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab