Re: Need help identifying an index to speed up query

Hi guys, thank you for the replies.

I have had indexes on the following with the slow performance (30 seconds to finish the query):

MASTER A: MASTERID1, MASTERID2, MASTERID3 and
DETAIL B: MASTERID1, MASTERID2, MASTERID3, SUBID Yes, table B is relatively large and the number of rows in B that have a value of 'ABC' in the detailid column is probably around 5% to 10%.

A.SUBID is very often null.

So it sounds like I should try these 4 indexes:

MASTER A:  MASTERID1, MASTERID2, MASTERID3
DETAIL B:  MASTERID1, MASTERID2, MASTERID3, SUBID
MASTER A:  MASTERID2
DETAIL B:  B.DETAILID

Johnny

Mark D Powell wrote:
> 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 - 21:40:46 CST