RE: help on indexes
1) Standard questions: engine, ver? 2) Non standard question: Is this query something Jack (He works for hp, he has been in the far east, therefore...) has worked on? In that case, he may already know the answer! 3) At a quick glance, no matter the engine or version, I don't see how TAB1_INDEXA could be used. Try putting a reference to tab1_lot_number in your query! ciao, marco ____________________________________________________________________________ rem radioterapia, which I immeritately manage, seldom agrees with what I say marco greco (Catania, Italy) Work: marcog@ctonline.it rem radioterapia 39 95 447828 fax 446558 (was mar.greco@agora.stm.it) Achea 39 95 503117 --- On Thu, 18 Jul 96 11:42:57 MAL Teoh Saipoh <saipoh@hpmitd27.mal.hp.com> wrote: 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 -----------------End of Original Message-----------------