OraFAQ Forum: SQL & PL/SQL » Problem with performance

Home » SQL & PL/SQL » SQL & PL/SQL » Problem with performance (oracle 11g)

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

Problem with performance [message #574797]

Tue, 15 January 2013 21:51

aditya_4136
Messages: 2
Registered: January 2013
Location: Bangalore

Junior Member

Hi,

I wanted to display distinct no of jobs in a company so I wrote a query as shown below

select count(distinct job) from emp;

This query works fine but my client is asking me to write a query with better performance. Can anyone guide me in as to how this query performance is bad and what will be the other query for me to run this faster.

Thanks in Advance.

Report message to a moderator

Re: Problem with performance [message #574800 is a reply to message #574797]

Tue, 15 January 2013 22:08

BlackSwan
Messages: 20085
Registered: January 2009

Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

does JOB column have an index on it?

Report message to a moderator

Re: Problem with performance [message #574802 is a reply to message #574800]

Tue, 15 January 2013 22:58

aditya_4136
Messages: 2
Registered: January 2013
Location: Bangalore

Junior Member

Hi,

Thanks for the replay. The job column does not have an index on it. My clients says that he just needs another query which will get the work done faster for him. He requires another query and he is not getting convinced with what ever I tell him.

Regards,
Aditya

Report message to a moderator

Re: Problem with performance [message #574804 is a reply to message #574802]

Tue, 15 January 2013 23:06

BlackSwan
Messages: 20085
Registered: January 2009

Senior Member

ORAFAQ tuning below -
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: Problem with performance [message #574810 is a reply to message #574797]

Wed, 16 January 2013 00:23

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

Senior Member
Account Moderator

Quote:

select count(distinct job) from emp;

This query works fine but my client is asking me to write a query with better performance.

select job_name from jobs;

Regards
Michel

Report message to a moderator

Previous Topic:	XML : Union of 2 Select queries
Next Topic:	Sql for previous day/ day before previous day

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 18 07:12:18 CDT 2013

Total time taken to generate the page: 0.25814 seconds