OraFAQ Forum: Performance Tuning » Querry performance

Home » RDBMS Server » Performance Tuning » Querry performance

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

Querry performance [message #360113]

Wed, 19 November 2008 11:51

sreenu80
Messages: 50
Registered: July 2006
Location: bangalore

Member

Hi Friends,

I have a senario like this

select decode(name,'smith','Manager','N/A') from emp where
deptno = 10
union
select decode(name,'smith','Accotent','N/A') from emp where deptno = 10

I need to pick these two records in one single select statement. I mean with out union condition.

Please let me know.

thanks
sreenu

Report message to a moderator

Re: Querry performance [message #360122 is a reply to message #360113]

Wed, 19 November 2008 12:19

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Join one query with a "table" of 2 rows, for instance:
select 1 from dual union all select 2 from dual

Regards
Michel

Report message to a moderator

Re: Querry performance [message #360134 is a reply to message #360113]

Wed, 19 November 2008 13:12

sreenu80
Messages: 50
Registered: July 2006
Location: bangalore

Member

hi Michel,

Thanks for quick replay. sorry i am not understand your explonation.

Let me tell my senario is

select decode(name,'smith','Manager','N/A') from emp where
deptno = 10 and mgr is null
union
select decode(name,'smith','Accotent','N/A') from emp where deptno = 10 and nvl(mgr,0)=0

My issue is if smit is exist in department no 10 and is mgr is null i need to get the record says " Manger" along with that if smith is exists in department no 10 and if mgr is null supress with "0" and compare with the "0" is mgr i need this record also.

empno name mgr deptno
1 smith 10
2 smith 0 10
3 smith 1 10

i need to get the first two rows. my issue is similar to this just i changed to simple example.

thanks

Report message to a moderator

Re: Querry performance [message #360136 is a reply to message #360134]

Wed, 19 November 2008 13:35

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

Let me tell my senario is

This is NOT the same as the first post.
Different scenarii, different solutions.

Quote:

if mgr is null supress with "0"

What does this mean?

"nvl(mgr,0)=0" means is manager is null or 0. Is this what you want?

In the end, post a representative test case: create table and insert statements along with the result you want with these data and explain this result.

Regards
Michel

Report message to a moderator

Re: Querry performance [message #360146 is a reply to message #360113]

Wed, 19 November 2008 15:40

sreenu80
Messages: 50
Registered: July 2006
Location: bangalore

Member

If mgr values is null supress with "zero" and check the condtion where mgr equla to "zero"

nvl function will supress the null value with "zero" where null in the mgr column and then it will check where deptno = 10 and mgr=0

Report message to a moderator

Re: Querry performance [message #360163 is a reply to message #360146]

Wed, 19 November 2008 22:58

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Michel Cadot wrote on Wed, 19 November 2008 20:35

In the end, post a representative test case: create table and insert statements along with the result you want with these data and explain this result.

Regards
Michel

Report message to a moderator

Previous Topic:	parallel hint
Next Topic:	Tuning a small query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 26 09:52:49 CST 2024