Re: Need help identifying an index to speed up query

From: Malcolm Dew-Jones <yf110_at_vtn1.victoria.tc.ca>
Date: 16 Mar 2006 16:21:43 -0800
Message-ID: <441a0117$1@news.victoria.tc.ca>

johnnynine_at_hotmail.com wrote:
: I'm sure this is fairly simple but I need help speeding up a query
: which is taking far too long to run. I'm joining 2 tables and
: retrieving a single value. I would think a proper index(es) would
: solve the performance issue.

: Can anyone please let me know what index(es) would speed this query up?

: SELECT B.AMOUNT

: FROM MASTER A, DETAIL B

: WHERE A.MASTERID1 = B.MASTERID1
: AND A.MASTERID2 = B.MASTERID2
: AND A.MASTERID3 = B.MASTERID3
: AND (A.SUBID = '' OR A.SUBID IS NULL)
: AND A.MASTERID1 ='AA'
: AND A.MASTERID2 ='AB'
: AND B.DETAILID = 'ABC'
define

an index on MASTERID1, MASTERID2, MASTERID3 on A and B

an index on A.MASTERID2

an index on B.DETAILID

	if A.SUBID is very often null then do something with indexes on 
	that as well (not sure if a normal index will help)

then run tests using explain plan and autotrace with good samples of analysed data and examine which index(es) oracle chose to use.

Delete the indexes that didn't help. Received on Thu Mar 16 2006 - 18:21:43 CST