OraFAQ Forum: Performance Tuning » Need help for query tunning the below query

Home » RDBMS Server » Performance Tuning » Need help for query tunning the below query

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

Need help for query tunning the below query [message #531577]

Wed, 16 November 2011 20:50

shefalik
Messages: 3
Registered: November 2011

Junior Member

how to write this inner query filter cond in efficent way

select from

where
.....
and (
( pcsc.initial_date is null
and
not exists
(select STUD_CPNT_ID
from pa_cbt_stud_cpnt
where cpnt_id=pcsc.cpnt_id
and stud_id=pcsc.stud_id
and initial_date is not null
)
)
or
pcsc.initial_date =
(
select min(initial_date)
from pa_cbt_stud_cpnt
where cpnt_id=pcsc.cpnt_id
and stud_id=pcsc.stud_id
--and nvl(cmpl_stat_id,0)=nvl(pcsc.cmpl_stat_id,0)
)
)

[Updated on: Wed, 16 November 2011 20:56]

Report message to a moderator

Re: Need help for query tunning the below query [message #531578 is a reply to message #531577]

Wed, 16 November 2011 20:53

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof

Report message to a moderator

Re: Need help for query tunning the below query [message #531582 is a reply to message #531578]

Wed, 16 November 2011 21:12

shefalik
Messages: 3
Registered: November 2011

Junior Member

I am not sure I can post these details due to client confidentiality requirements. All I am looking for is if there is a better or atleast different way to write the above co-related sub-query.

Report message to a moderator

Re: Need help for query tunning the below query [message #531583 is a reply to message #531582]

Wed, 16 November 2011 21:20

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

You removed much of the posted code along with my addition of code tags.

>I am not sure I can post these details due to client confidentiality requirements.
This is your problem not ours.

>All I am looking for is if there is a better or atleast different way to write the above co-related sub-query.
There might be a better way, but it unreasonable to expect such a solution while declining to provide necessary details.
Since you choose to not cooperate, I choose to not waste more time trying to assist.
You're On Your Own (YOYO)!

Report message to a moderator

Re: Need help for query tunning the below query [message #531602 is a reply to message #531583]

Wed, 16 November 2011 23:57

shefalik
Messages: 3
Registered: November 2011

Junior Member

I have kept only the portion of query which needed help and I am looking for an alternate way of getting the same output.

I know it is my problem and I suppose thats what the forum was built for, so that people can help out in each other problem.

Help or no help, thanks for your time.

Report message to a moderator

Re: Need help for query tunning the below query [message #531639 is a reply to message #531602]

Thu, 17 November 2011 03:38

cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester

Senior Member

Efficient ways of writing queries depend on table structures, relationships between them, indexes and ammount of data (in total and to be retirieved) amongst other things.

We simply can not suggest better alternatives to the snippet you've given with no context.

Report message to a moderator

Previous Topic:	Slow running query -Explain plan attached-pls help!
Next Topic:	Tuning in Hierarchical queries

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Nov 24 12:40:30 CST 2024