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

  • From: Rajesh Puneyani <rajpuneyani@xxxxxxxxx>
  • To: askdba@xxxxxxxxxxxxx
  • Date: Wed, 22 Dec 2004 14:21:12 -0500

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!
> 
>

Other related posts: