Access Plan - Operator question

[UDBDBA]

Hi:

First, thank you for looking into this posting...
Running UDB V8 FP11.

The question is around the below query branch. I would like to know
what it means and if there are ways to avoid it.

What does the operations above HSJOIN do?
HSJOIN --> SORT --> TBSCAN

Thanks!

Vijay

-------------\
12480.9
NLJOIN
( 5)
26445.8
3144.57
/------+------\
116206 0.107404
TBSCAN FETCH
( 6) ( 17)
12101.8 25.1948
2040.15 2.36492
| /---+---\
116206 0.364922 1.71589e+07
SORT IXSCAN TABLE: STELLA
( 7) ( 18) DIMCLIENTDATE
11769.9 21.3097
2040.15 2
| |
116206 1.71589e+07
HSJOIN INDEX: STELLA
( 8) DIMCLIENTDATE_6
11426.3
2040.15

[Ian]

UDBDBA wrote:

The question is around the below query branch. I would like to know
what it means and if there are ways to avoid it.

What does the operations above HSJOIN do?
HSJOIN --> SORT --> TBSCAN

These operations are feeding the outer leg of a nested loop join.
While it's certainly not a requirement for the outer leg of an NL join
to be sorted, DB2 may elect to perform the sort to make the join more
efficient, or because the output from the NLJOIN will already be sorted
(thus avoiding a bigger sort later in the execution plan).

[UDBDBA]

Thanks Ian.

Ian wrote:

UDBDBA wrote:

The question is around the below query branch. I would like to know
what it means and if there are ways to avoid it.

What does the operations above HSJOIN do?
HSJOIN --> SORT --> TBSCAN

These operations are feeding the outer leg of a nested loop join.
While it's certainly not a requirement for the outer leg of an NL join
to be sorted, DB2 may elect to perform the sort to make the join more
efficient, or because the output from the NLJOIN will already be sorted
(thus avoiding a bigger sort later in the execution plan).