Hi All,

I'm tuning query but this query not use index

ex.


Index
I also attached explain plan.

as there is no where clause - it has to open a table and this with a complete scan and use index for join

The join method db2 chose is hsjoin ,which can not use indexes at all.

Can you explain why hash join cannot use indexes?

Are statistics updated? What happens to the plan if you:

runstats on table <schema>.L_CONSULTING with distribution and detailed indexes all;
runstats on table <schema>.L_CUSTOMER with distribution and detailed indexes all;

That's the way hashing algorithm works.
www.devx.com/assets/ibm/3145.pdf
From the doc:
"The inner table (called the build table) is scanned first, and the rows are copied into memory buffers. These buffers are divided into partitions based on a “hash code,” which is computed from the column(s) in the join predicate(s).
Then the outer table (called the probe table) is scanned. For each row in the probe table the same hash algorithm is applied to the join column(s). If the hash code obtained matches the hash code of an build row, the
actual join columns are compared."

There are many things that may influence optimizer's decision to choose a particular join method. Take a look at above mentioned pdf, I find it quite informative.

The scanning of the data could actually be an index scan if suitable. So an index would be used, although not for the actual join itself because the sorting of the index doesn't provide much benefits.

Because
1、There is no local predicate ,so db2 have to scan all rows ......
2、HSjoin need not to sort data，so it can't benifit from the sorted data of indexes.
3、the INDEX IDX_L_CONSULTING_CCCUSTID and L_CUSTOMER_PK does not cover the selected column so db2 can not use index only access to do index hash join .......