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

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

Patricia, 
 
Yes, Hints are not everyone game, but I had made the suggestion as to try this 
out to get over the hump , and then work on long term solution. I had seen some 
miricles in qury times by using hints.  I will suggest if DBAs are cornered 
they should try using Hints when the obvious soultions do not work.
 
Nisar Tareen 

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! 






                
---------------------------------
Do you Yahoo!?
 The all-new My Yahoo! ? Get yours free!    


Other related posts: