OS: AIX UDB 8.2

All,

I am having some tough time with the SQL tuning. I have a couple of Reporting SQLs which are performing very slow.

I took the explain of the SQLs . But I can't understand it .

I see a table is using and TBSCAN , even though it has indexes. what could be the raason.

How do i change HSJOIN to NLJOIN.


I See a few more TBSCANs after the SORT operation. How do i figure out which is causing TBSCAN in this scenario.




thanks

How about posting the SQL? Include what your intent of the SQL.

Andy

Here is the SQL.





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.

Tutorial on Visual Explain:

http://users.sdsc.edu/~jrowley/db2/V...l%20explain%22

Can you post the DDL for the tables? Have you done RUNSTATS in the tables?

Andy

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

HTH

Sathyaram

The link below may provide some details

http://publib.boulder.ibm.com/infoce...n/c0005414.htm

Istikhar

Satya Ram . Thanks for u r reply.

Currently i am working on other SQL , which is taking a longer time than usual time.


I am attaching the explain out put of it here. Damn these sqls are killing me .