OraFAQ Forum: Performance Tuning » Performance Degrads

Home » RDBMS Server » Performance Tuning » Performance Degrads

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Performance Degrads [message #285079]

Mon, 03 December 2007 05:58

subbu_tce
Messages: 98
Registered: July 2007
Location: pune

Member

The below query is taking time to execute and the records in the
tables are like in crores.....
Kindly suggests an alternate to the below query without using joins........

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;

Report message to a moderator

Re: Performance Degrads [message #285083 is a reply to message #285079]

Mon, 03 December 2007 06:07

dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai

Senior Member

if you had formatted your code ,then it would have been much better.By the way what do you mean by

Quote:

Kindly suggests an alternate to the below query without using joins

regards,

Report message to a moderator

Re: Performance Degrads [message #285084 is a reply to message #285083]

Mon, 03 December 2007 06:13

subbu_tce
Messages: 98
Registered: July 2007
Location: pune

Member

I have used left outer join and because of this it taking time.
So,how to avoid left outer join?

Report message to a moderator

Re: Performance Degrads [message #285085 is a reply to message #285084]

Mon, 03 December 2007 06:28

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

select the line and press on the "del" key.

Regards
Michel

Report message to a moderator

Re: Performance Degrads [message #285086 is a reply to message #285084]

Mon, 03 December 2007 06:29

dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai

Senior Member

Quote:

I have used left outer join and because of this it taking time.

how did you arrive at this conclusion?there might be other reasons for your query's slow performence.

regards,

Report message to a moderator

Re: Performance Degrads [message #285087 is a reply to message #285079]

Mon, 03 December 2007 06:30

kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India

Senior Member

Check an index for columns CPT_CODE,FACILITY_NUMBER,ACTIVE_FLAG

Kiran.

Report message to a moderator

Re: Performance Degrads [message #285088 is a reply to message #285079]

Mon, 03 December 2007 06:32

kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India

Senior Member

Funny Michel Laughing

Report message to a moderator

Re: Performance Degrads [message #285093 is a reply to message #285085]

Mon, 03 December 2007 06:47

subbu_tce
Messages: 98
Registered: July 2007
Location: pune

Member

Removed bad comments.

[Edited by Taj]

[Updated on: Mon, 03 December 2007 07:41] by Moderator

Report message to a moderator

Re: Performance Degrads [message #285096 is a reply to message #285079]

Mon, 03 December 2007 07:08

kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India

Senior Member

@Subbu,

Before you post read this: http://www.orafaq.com/forum/t/88153/0/

And also how to format the codes section.

Kiran.

[Updated on: Mon, 03 December 2007 07:09]

Report message to a moderator

Re: Performance Degrads [message #285101 is a reply to message #285084]

Mon, 03 December 2007 07:43

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

>I have used left outer join and because of this it taking time.
>So,how to avoid left outer join?
According your provide details Micheal answer was very clear and right.

Remove left outer join in your sql statement.

If you want our help & suggestion then please provide us more details. and Never Ever use Bad comments which I removed.

Report message to a moderator

Re: Performance Degrads [message #285117 is a reply to message #285101]

Mon, 03 December 2007 10:42

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

Hi Mohammad,
should you really be editing someone's post in this way? I can see that editing a post to improve readability (removing IM speak, adding tags etc) can certainly be useful and valid, even removing abusive language I can see and agree with. But I'm not sure that censoring the above post is a valid use of this power. I saw the post and don't believe that it was particularly abusive, obnoxious yes, bleating yes but not abusive (although there was, what appeared to be, some foreign language text at the start of the post so I could be wrong and apologise if this is the case)

Report message to a moderator

Re: Performance Degrads [message #285202 is a reply to message #285117]

Mon, 03 December 2007 22:38

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

Hi,
Actually OP comments is abusive lanaguage in Hindi but written in english lanaguage. That is why I have to removed that comments

Report message to a moderator

Re: Performance Degrads [message #285222 is a reply to message #285079]

Tue, 04 December 2007 00:27

kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India

Senior Member

I agree with Mohammad. Well done Mohammad.

Regards,
Kiran.

Report message to a moderator

Re: Performance Degrads [message #285247 is a reply to message #285202]

Tue, 04 December 2007 01:31

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

Then fair play and as I said before, I apologise if this is the case.

Report message to a moderator

Previous Topic:	sql performance
Next Topic:	Shows difference in EXPLAIN PLAN result for same query.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jul 01 09:44:51 CDT 2024