query tunning

[mike73]

any one had suggestion on this query ??



[Edited by mike73 on 11-07-2001 at 11:50 AM]

[dknight]

Current:

A.BA_NO = QCTRL_BA_USAGE.BA_NO AND
A.BA_SUF = QCTRL_BA_USAGE.BA_SUF AND
QCTRL_BA_USAGE.BA_USAGE_CD = 'P' AND
A.INACTIVE_IND = 0 AND

( A.BA_NO = '1' AND A.BA_SUF = 0)
OR (A.BA_NO = '1' AND A.BA_SUF = 0)
OR (A.BA_NO = '1' AND A.BA_SUF = 0)
OR (A.BA_NO = '2' AND A.BA_SUF = 0)
OR (A.BA_NO = '2' AND A.BA_SUF = 0)
OR (A.BA_NO = '2' AND A.BA_SUF = 0)

The condition

A.BA_SUF = 0

must be satisfied for all members of the result set. Perhaps it should appear just once.

Hope that helps.

[roobaron]

Check to see the distribution of values within each column in the query.

If the distribution is well spread a normal index will help in most case. But you may need to add a histogram for the CBO to use the index. Look up histrograms in the doco for more info.

Also if a lot of those columns are flag type columns, i.e they have a low number of distinct values compared to the no of rows in the table (less than 1%)
eg
QCTRL_BA_USAGE.BA_USAGE_CD = 'P'
A.INACTIVE_IND = 0
A.BA_NO = '1'
A.BA_SUF = 0

These columns might benefit from adding a bitmap index, especially considering you are using the OR function.

To understand bitmap indexes more, there is a very good explanation in the concepts part of the manual.

Have Fun

[jmodic]

Originally posted by mike73
any one had suggestion on this query ??


[Edited by mike73 on 11-07-2001 at 11:50 AM]

Nope....

It should fly on any mainframe or large cluster or even tiny PC.