Home » RDBMS Server » Performance Tuning » 'DISTINCT' vs 'GROUP by'
'DISTINCT' vs 'GROUP by' [message #313300] Fri, 11 April 2008 06:11 Go to next message
vkrn
Messages: 18
Registered: March 2008
Junior Member
Hi all,
Smile. I am confused with the usuage of DISTINCT and groupby.
Is it good practise to use DISTINCT for performance related issues.can anyone help me in this regard.

Thanks,
kiran
Re: 'DISTINCT' vs 'GROUP by' [message #313315 is a reply to message #313300] Fri, 11 April 2008 06:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use DISTINCT when you have to get distinct value, use GROUP BY when you have to aggregate values.

Regards
Michel
Re: 'DISTINCT' vs 'GROUP by' [message #313318 is a reply to message #313315] Fri, 11 April 2008 06:43 Go to previous messageGo to next message
vkrn
Messages: 18
Registered: March 2008
Junior Member
i tried to use groupby ,to remove distinct in my queries,it was working.....but i am not understanding the logic behind it....... Confused .kindly help me.

Thanks,
kiran.
Re: 'DISTINCT' vs 'GROUP by' [message #313326 is a reply to message #313318] Fri, 11 April 2008 07:00 Go to previous messageGo to next message
manoj12
Messages: 185
Registered: March 2008
Location: India
Senior Member
No Message Body

[Updated on: Fri, 11 April 2008 07:02]

Report message to a moderator

Re: 'DISTINCT' vs 'GROUP by' [message #313327 is a reply to message #313326] Fri, 11 April 2008 07:03 Go to previous messageGo to next message
manoj12
Messages: 185
Registered: March 2008
Location: India
Senior Member
search google..
Re: 'DISTINCT' vs 'GROUP by' [message #313345 is a reply to message #313318] Fri, 11 April 2008 07:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"Work" is not the correct word, it gives the result but it is not the correct way to do it.
You can pull out a nail from a wall with a screwdriver but the correct tool is a pair of tincers.

Regards
Michel
Re: 'DISTINCT' vs 'GROUP by' [message #313349 is a reply to message #313300] Fri, 11 April 2008 08:15 Go to previous messageGo to next message
vkrn
Messages: 18
Registered: March 2008
Junior Member
I have two tables supplier and product.
Supplier is the parent table while products is the child table.
In products i have a column called description which gives describes the product(say excellent,good).

i tried the query like this.

select s.name from supplier s ,product p where s.id=p.supplier_id and p.description='Excellent'

After executing this query the result is 3 rows with name 'Samsung',but i wanted to remove duplicate rows.

for that can i write like this:

select distinct s.name from supplier s ,product p where s.id=p.supplier_id and p.description='Excellent'

'or'

select s.name from supplier s ,product p where s.id=p.supplier_id and p.description='Excellent' group by s.name
Re: 'DISTINCT' vs 'GROUP by' [message #313360 is a reply to message #313349] Fri, 11 April 2008 08:44 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Or, of course, you could do it the way Relational Logic makes sense:

select s.name 
from supplier s
where s.id in (
    select supplier_id
    from   product
    where  description='Excellent')


There is no difference in your 2 queries for Oracle versions up to 10.1. Oracle introduced HASH GROUP BY and HASH DISTINCT execution plans in 10.2 which make them potentially (subtly) different. Still, performance should be similar.

My query above will be superior in versions 10.1 and prior, as it does not (necessarily) require a SORT. In 10.2 and beyond, it will perform similarly to your two queries in most cases, but there are some distributions of data where the sub-query will be superior.

Use the sub-query.

Ross Leishman
Re: 'DISTINCT' vs 'GROUP by' [message #313389 is a reply to message #313349] Fri, 11 April 2008 10:48 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you want to use "group by" when you don't aggregate.
It is simple.
You want to get distinct values use DISTINCT.
You have to aggregate values use GROUP BY.
There is nothing to think about.

Regards
Michel
Previous Topic: full tablescan though indexes exist
Next Topic: Performance tuning with indexes
Goto Forum:
  


Current Time: Sun Dec 04 04:35:14 CST 2016

Total time taken to generate the page: 0.04074 seconds