OS: AIX 5.3
Database: DB2 V8.2

Query Performance:
The query below usually executes in about 45min. But after the reorg/runstats it is taking 4hrs. When I did a comparison of the old and new access plans, I find one index of table MASTPRD as the culprit for this increase in cost.

select unpd_offstcd_no as stcdnbr,
unpd_offstcd_da_id as stcddash,
unpd_prod_fac_ty as fac_type,
unpd_prod_fac_no as fac_nbr,
unpd_prod_dt as prod_dt,
unpd_prod_turn_in as turn,
unpd_adj_gross_am as onweight,
unpd_lrts_seq_no as lrts_seq,
unpd_onstcd_no as onscdnbr,
unpd_onstcd_da_id as onscdash,
unpd_onmfgord_id as mfgordid,
unpd_setoff_in as setoff,
unpd_bu_bd_in as bubd_in,
mspd_scrap_wgt_am as crscrap,
mspd_ctd_scrap_am as ctdscrap,
mspd_net_wgt_am as netwgt,
mspd_linft_am as lin_ft,
MSPD_START_RUN_TM as start_tm,
MSPD_END_RUN_TM as end_tm,
MSPD_RUN_MN as run_mn,
MSPD_START_RUN_DT as start_dt,
MSPD_GAUGE_E_AM as gauge_e,
MSPD_WIDTH1_E_AM as width1,
MSPD_HOLD_CD as hold_cd,
MSPD_PASS_NO as pass_no,
MSPD_DEF_SRC_UNIT as def_srcu,
pstep_main_op as main_op,
pstep_nbr_pieces as pieces,
LIFT_GRADE as grade,
LIFT_HEAT_NBR as heat_nbr,
LIFT_HEAT_SEQ_NBR as heat_seq,
lift_current_shape as curshape,
stcd_route_id as route_id,
stcd_item_no as item_nbr,
stcd_promise_duns as duns,
stcd_promise_dash as dash,
stcd_que_mill_in as que_mill,
cust_cosh_duns_no as coshduns,
fac_plant as plant,
UNPD_PROD_COST_AM as prodcost,
UNPD_BSCR_COST_AM as bscrcost,
UNPD_ACCM_COST_AM as accmcost
from userid.untprod, userid.mastprd, userid.pstep,
userid.lift, userid.stckcrd, userid.cusacct,
userid.faciltg
where unpd_prod_dt between ‘2008-02-16’ and ‘2008-03-26’
and unpd_prod_fac_ty = mspd_prod_fac_ty
and unpd_prod_fac_no = mspd_prod_fac_no
and unpd_offstcd_no = mspd_stckcrd_no
and unpd_offstcd_da_id = mspd_stckcrd_da_id
and unpd_prod_dt = mspd_prod_dt
and unpd_prod_turn_in = mspd_prod_turn_in
and unpd_lrts_seq_no = mspd_lrts_step_no
and unpd_offstcd_no = pstep_stckcrd_nbr
and unpd_offstcd_da_id = pstep_stckcrd_dash
and unpd_pstep_seq_no = pstep_seq_nbr
and unpd_offstcd_no = lift_stckcrd_nbr
and unpd_offstcd_da_id = lift_stckcrd_dash
and unpd_onmfgord_id = stcd_nbr
and stcd_promise_duns = cust_duns
and unpd_prod_fac_ty = fac_type
and unpd_prod_fac_no = fac_nbr
optimize for 1000 rows;

The old plan was using the unique index of MASTPRD table (MSPD_PROD_FAC_TY+MSPD_PROD_FAC_NO+MSPD_PROD_DT+MS PD_PROD_TURN_IN+MSPD_BOX_LINE_NO+MSPD_STCKCRD_NO+M SPD_STCKCRD_DA_ID) was consuming about 75.03 timerons for the index scan.

The new plan also uses the above unique index but consumes about 130,450 timerons.

So while trying different options I created another index on MASTPRD using just MAST_PROD_DT column (as being used in the JOIN and BETWEEN clauses). The access plan this time didn’t use the unique index instead used the new one I created on MAST_PROD_DT. And that did bring down the index scan cost to 1665 (not as much as 130,450 but not as small as 75).

One more option I tried was apart from the unique index I created index on MSPD_PROD_DT and rest of the columns in the where clause (MSPD_PROD_FAC_TY, MSPD_PROD_FAC_NO, MSPD_STCKCRD_NO, MSPD_STCKCRD_DA_ID, MSPD_PROD_TURN_IN and MSPD_LRTS_STEP_NO). It was again taking about 1665 timerons for the index scan just like above when index was created only on MSPD_PROD_DT.

Any one have any suggestions as to whether creating another index should be the way to resolve this? If so what columns should the index have? And is there a way we can bring the cost closer to 75 like before. Would cluster indexes be of any help?

Clearly no one can tell you how to tune your query without being able to see it in action, unless it is something really basic. But some valuable lessons can be learned from this:
a) do not compare timerons from two different explains - timerons are only good for comparisons of parts of the same query to each other;
b) do not do RUNSTATS if you have no performance problems.

Why are you doing that?
What happens if you remove this?

I have never seen OPTIMIZE FOR n ROWS do anything **UNLESS** n=1. This was put in first on MVS to overcome the problem which list prefetch caused when it was originally introduced. Then the syntax was propagated to other platforms for compatibility. On some of the developing platforms it can actually affect access path like it does on the mainframe, but not always.