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

  • From: Regis Biassala <Regis.Biassala@xxxxxxxxxx>
  • To: askdba@xxxxxxxxxxxxx
  • Date: Wed, 22 Dec 2004 17:56:56 -0000

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


Other related posts: