help on indexes

Hi, Need Help. I have the following SQL: -------------------------------------------- SELECT tab1_trans_dt, tab1_trans_tm, tab1_opn, tab1_qty_old, tab1_qty_new, tab1_owner_new, tab1_prod, tab2_prd_grp_3 FROM table1,table2 WHERE tab1_dept = "OEAT03" AND tab1_HIST_DELETED = 'N' AND ((tab1_trans_dt > 960701) OR (tab1_trans_dt = 960701 AND tab1_trans_tm >= 230000)) AND tab1_transaction = 'MVOU' AND ((tab1_trans_dt < 960702) OR (tab1_trans_dt = 960702 AND tab1_trans_tm <= 230000)) AND tab1_dept = tab2_dept AND tab1_prod = tab2_product AND (tab1_rw_old = 'N' OR tab1_rw_new = 'N') ORDER BY tab1_opn,tab2_prd_grp_3 ---------------------------------------------------------- I also hv these indexes on my table tab1. Index Type Cluster Columns TAB1_INDEXA dupls No tab1_lot_number tab1_dept tab1_transaction TAB1_NEW_IDXA dupls No tab1_transaction tab1_opn tab1_prod TAB1_NEW_IDXB dupls No tab1_dept tab1_lot_number ------------------------------------------------ When I do a Set explain on... I found that the index TAB1_NEW_IDXA is used rather than index TAB1_INDEXA (1) Index Keys: tab1_transaction tab1_opn tab1_prod Lower Index Filter: tab1_transaction = 'MVOU' QUESTIONS : a) As there are values for tab1_dept and tab1_transaction, isn't it better to use TAB1_INDEXA? b) How can I organise my SQL statement to perform better? Pls help. Thanks rgds, saipoh e-mail : saipoh@hpmitd27.mal.hp.com