Re: help on indexes

Teoh Saipoh wrote: > > Hi, > > Need Help. > > I have the following SQL: > --------------------------------------------[SNIP] > ---------------------------------------------------------- > > 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? > The index TAB1_INDEXA can not be used, because the first column in this index is tab1_lot_number. OnLine could use one of the other two indexes. It prefers to take TAB1_NEW_IDXA, because it is a combined index. > b) How can I organise my SQL statement to perform better? > I think it depends on the number of rows in the tables. The objective is to reduce the number of returned rows as quick as possible. If there are more rows in table1 than in table2 I think your statement should look like this: SELECT t1.tab1_trans_dt, t1.tab1_trans_tm, t1.tab1_opn, t1.tab1_qty_old, t1.tab1_qty_new, t1.tab1_owner_new, t1.tab1_prod, t2.tab2_prd_grp_3 FROM table1 t1, table2 t2 WHERE t1.tab1_dept = "OEAT03" AND t1.tab1_transaction = 'MVOU' AND t1.tab1_HIST_DELETED = 'N' AND (t1.tab1_rw_old = 'N' OR t1.tab1_rw_new = 'N') AND ( (t1.tab1_trans_dt > 960701) OR (t1.tab1_trans_dt = 960701 AND t1.tab1_trans_tm >= 230000) ) AND ( (t1.tab1_trans_dt < 960702) OR (t1.tab1_trans_dt = 960702 AND t1.tab1_trans_tm <= 230000) ) AND t1.tab1_dept = t2.tab2_dept AND t1.tab1_prod = t2.tab2_product ORDER BY t1.tab1_opn, t2.tab2_prd_grp_3 For me the fields tab1_dept and tab1_transaction are looking identifying in table table1. In this case create a new index on table1, which contains these two fields. I don't think it's a great deal to add more fields to this index. But you can try it. If you don't want to create a new index on table1 you have to add something like t1.tab1_lot_number > 0 to the where clause. To be on the save side place it directly after the WHERE. This new condition won't limit your where clause, but makes it possible for OnLine to use the already existing index TAB1_INDEXA. If table2 is also a big one I think it's good idea to create an index on table2, which contains the fields (tab2_dept,tab2_product,tab2_prd_grp_3). If OnLine can apply this index it's a good thing, because it has to read just the index but not the table. All these new indexes will have no effect concerning the order by clause, because it contains one field of table1 and one of table2. So, there's no chance for an index. If the order by is the bottleneck select the rows in a temporary table first. Create an index on the two fields mentioned in the order by clause and do the select order by afterwards. Finally, just a small thing. You should use aliases for the tables, if you do a join. It's less effort for the optimizer to figure out, which fields belong to which table. Hope this helps. Cheers, Dirk --------------------------------------------------- Dirk Schmidtpott S I E M E N S EMail: schmd@dbm212.pdb.sni.de ============= Phone: 0049-5251-8-14922 N I X D O R F Fax : 0049-5251-8-27905 The above statements and opinions are my own and do not necessarily represent those of my employer. ---------------------------------------------------