Hi Rick, I don't think that the (+) is to be blamed...really!? 1. How much time it is taking? 2. How many rows in CPT_ICD9_PROCEDURES and facility_level 3. Indexes on which columns? 4. Column ACTIVE_FLAG is part of which table? I suspecting an index not designed properly... Regis -----Original Message----- From: askdba-bounce@xxxxxxxxxxxxx [mailto:askdba-bounce@xxxxxxxxxxxxx] On Behalf Of Rick_Cale@xxxxxxxxxxxxxx Sent: 22 December 2004 17:22 To: askdba@xxxxxxxxxxxxx Subject: [askdba] alternative to using outer join in query. 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