Re: Need help identifying an index to speed up query

Johnny, what indexes currently exist on these tables if any?

Having indexes on the join conditions of each table would be the starting point for adding indexes if indexes do not exist on these tables. Both sets of join conditions appear like they might be the PK of their respective tables. As such these columns should already be indexed.

If table B is relatively large and if the number of rows in B that have a value of 'ABC' in the detailid column is a very small percentage of the table rows then an index on detalid would provide an entry point into B.

Which table should be the driver for the join will depend on the distinctiveness of the columns that appear in the where clause and which set of values will get you the smallest row set to use to access the other table.

So make sure you statistics are current. Take a look at the column information in dba_tab_columns Take a look at the indexes in dba_indexes Take a look at the columns in the indexes using dba_ind_columns

Run an explain plan to see what Oracle is doing. Compare that to your best determination based on the above checks.

HTH -- Mark D Powell -- Received on Thu Mar 16 2006 - 18:30:34 CST