[askdba] Re: alternative to using outer join in query.
- From: "Patricia Eyenga" <patricia.e@xxxxxxxx>
- To: <askdba@xxxxxxxxxxxxx>
- Date: Wed, 22 Dec 2004 14:10:04 -0500
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!
Other related posts:
