UDB V8.2 OS . AIX


select
rh.dun_rules_syntadun
,rh.row_id
,case when ou.name = 'Global Account' then 1 else 0 end as ACCT_PRIORITY
from
paras.s_org_edunt ou
left join paras.malas_org ro on ro.dun_accnt_id = ou.row_id
left join paras.malas_header rh on rh.row_id = ro.dun_rules_hdr_id
where
current timestamp >= rh.dun_effect_date
and (current timestamp <= rh.dun_edunpira_date or rh.dun_edunpira_date is null)
and upper(rh.dun_val_status) = 'Y'
and (ou.name = 'Promise Checks' or ou.name = 'Global Account')
and (rh.dun_bus_unit ='HBS' or rh.dun_bus_unit = 'Both')
and rh.dun_event_cd in (3)
and rh.dun_order_type in ('All','Both','New')
and (rh.dun_all_ord_meth = 'Y' or rh.dun_methods_syntadun like '%Phone%')
order by
ACCT_PRIORITY, ro.dun_seq_number


Can some on suggest me the Where i can create indexes
and do i have to suggest some other options for the predicates in and LIke.

I have read it some where that they will cause bad access plan.

thanks

use db2advis with input file, containing the sql
db2 will recommend index if needed

consider using

and rh.dun_val_status in ('y' , 'Y' )

instead of

and upper(rh.dun_val_status) = 'Y'


as a scalar function on a column ist slower than an IN () in most cases

As a general rule, try to avoid "OR" constructs.
Specifically, replace "ou.name = 'Promise Checks' or ou.name = 'Global Account'" by "ou.name IN ('Promise Checks','Global Account')"
and "rh.dun_bus_unit ='HBS' or rh.dun_bus_unit = 'Both')" by "rh.dun_bus_unit IN ('HBS','Both')"

B.T.W., does somebody know whether the v8 or v9 optimiser performs this conversion (from "f=v1 or f=v2" to "f IN (v1,v2)") by itself? AFAIK, v7 did not yet do this.