1) ois.ord_item_id has an index . But it is not being used. This index was created on two columns one being ois.ord_item_id.
2) Is this a good idea to create indexes on all the columns of GROUP by function.
3) I see few TBSCANS after a sort operation in Graph. What does it mean.
Can some one refer me good documentation for understanding Visual grapsh.
HSJOIN is better than NSLOOP JOIN. If you have two big table i will prefer to have HSJOIN. Only problem is for doing HSJOIN we need more memory. Apart from that check the predicates of all the HSJOIN, if you create the index on that it might improve the performance. Moreover try to do runstats on all the tables with indexes all and distribution which might also improve the performance.
You may also want to consider posting the explain plan and the statistics ... output of db2exfmt with the -g option may do good ...
Having a quick look at your query (at 2 am ;-) ) , these are my observations :
a) You have a couple of outer joins ... So, no wonder you get tablescans
b) I think it may be a good idea to 'apply' some of your conditions in the where clause before you do the left outer join (or may be, db2 already does this optimization)
For your questions :
1) previous paragraph answers this
2) no, given that you have too many columns in the group by.
3) the sorted table is being read
Nageswaran, in my opinion, trying to make your NSLOOP joins HSJOINS or vice-versa is not advisable ... Many people have an opinion that Nested-Loop joins are bad, which I generally differ with .. They get really bad if the join columns are not indexed ... To me HSJOINs on base tables mean that there is a scope for index improvements ...
Visit the new-look IDUG Website , register to gain access to the excellent content.