Re: Need help identifying an index to speed up query

There is no reason to create an index on SUBID because you are interested in rows with this value being NULL. Nulls are not stored in single column indexes so an index on SUBID would only make sense for equility conditions on a vaue, that is, not null condition.

You should have one composite index on each table that includes all three join columns listed so that id1 and id2 are the leading columns in the index since these values are specified in the where clause. Then you should have an index on B.DETAILID.

Refresh the object statistics after adding the indexes.

If the CBO drives on the new index and query performance is still too slow you could try rebuilding the index to include the MASTERID1 and MASTERID2 columns as well as DETAILD. You would now have all three composit index values in your where clause: B.MASTERID = 'AA' instead of A.MASTERID = 'AA'.

HTH -- Mark D Powell -- Received on Fri Mar 17 2006 - 11:24:54 CST