I am sure you got most of the suggestions already but may be you can think of splitting this outer join into a UNION query and run the plans. On Wed, 22 Dec 2004 14:10:04 -0500, Patricia Eyenga <patricia.e@xxxxxxxx> wrote: > I would agree with points 2-4.. > I try to avoid hints and trust the optimizer to find the best path.. The > problem with hints is when data changes (more data, different data), you'll > have to change the hint also. > Make sure you get accurate statistics on the table > Make sure you have appropriate indexes > > -----Original Message----- > From: askdba-bounce@xxxxxxxxxxxxx [mailto:askdba-bounce@xxxxxxxxxxxxx] On > Behalf Of Nisar Tareen > Sent: Wednesday, December 22, 2004 2:03 PM > To: askdba@xxxxxxxxxxxxx > Cc: dba Group > Subject: [askdba] Re: alternative to using outer join in query. > > Rick, > > I do not have specific suggestion to you query, but following may help. > > 1- Use sql Hints. > 2- Rebuild the index. > 3- Re-calculate the table statistic. > 4- use where as WHERE f.LEVEL_CODE = P.CPT_CODE(+) > Please, do mention with your problem which version of oracle and on which > platform? > > Good Luck. > > Nisar Tareen > > Rick_Cale@xxxxxxxxxxxxxx wrote: > > Hi DBAs, > > Is there a good way to rewrite a query that uses outer join? It runs slow > and I want to look at alternatives. Below is the query. I do have proper > indexes in place. I do know the outer join statement is killing this. I do > have stats run also. > > SELECT f.LEVEL_CODE, > f.DESCRIPTION, > f.master_code, > f.FACILITY_LEVEL_ID, > f.CHARGE, > P.ICD9_CODE, > P.CPT_CODE, > P.CPT_ICD9_ID, > FROM CPT_ICD9_PROCEDURES P, facility_level f > WHERE P.CPT_CODE(+) = f.LEVEL_CODE > AND f.FACILITY_NUMBER = :batch.facility_number > AND ACTIVE_FLAG = 'Y' > ORDER BY level_code,icd9_code; > > Thanks, > > Rick Cale > Database Administrator > Team Health, Inc. > 865-293-5425 > Rick_Cale@xxxxxxxxxxxxxx > > --------------------------------- > Do you Yahoo!? > The all-new My Yahoo! - Get yours free! > >