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? No. The optimizer is doing the right thing. SInce you actually have a value for transaction (vs lot_number) the best index will be the one which can drive off of that. As the optimizer my second choice would therefore be TAB1_NEW_IDXB before TAB1_INDEXA (you also have a dept). Remember that a composite index on (tab_dept, tab_opn) for example will also serve as an index on tab_dept - though NOT for tab_opn by itself. } b) How can I organise my SQL statement to perform better? ORs are expensive. Experiment with replacing the ORs with UNION. You might be able to use BETWEEN - depends on your criteria. What is the estimated cost reported by set explain? cheers j. _____________________________________________________________________________ Jack Parker - Hewlett Packard, DMD/IS Boise, Idaho, USA jparker@boi.hp.com <--- New address Currently on loan to PLD/PE _____________________________________________________________________________ If anything can go wrong, fix it. To hell with Murphy. _____________________________________________________________________________ Any opinions expressed herein are my own and not those of my employers. _____________________________________________________________________________