Home » RDBMS Server » Performance Tuning » Need help for query tunning the below query
Need help for query tunning the below query [message #531577] Wed, 16 November 2011 20:50 Go to next message
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 Go to previous messageGo to next message
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
Re: Need help for query tunning the below query [message #531582 is a reply to message #531578] Wed, 16 November 2011 21:12 Go to previous messageGo to next message
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.
Re: Need help for query tunning the below query [message #531583 is a reply to message #531582] Wed, 16 November 2011 21:20 Go to previous messageGo to next message
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)!
Re: Need help for query tunning the below query [message #531602 is a reply to message #531583] Wed, 16 November 2011 23:57 Go to previous messageGo to next message
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.
Re: Need help for query tunning the below query [message #531639 is a reply to message #531602] Thu, 17 November 2011 03:38 Go to previous message
cookiemonster
Messages: 13917
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.
Previous Topic: Slow running query -Explain plan attached-pls help!
Next Topic: Tuning in Hierarchical queries
Goto Forum:
  


Current Time: Thu Mar 28 06:30:18 CDT 2024