[askdba] Re: alternative to using outer join in query.

  • From: Nisar Tareen <ntareen@xxxxxxxxx>
  • To: askdba@xxxxxxxxxxxxx
  • Date: Wed, 22 Dec 2004 11:03:05 -0800 (PST)

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!    


Other related posts: