| 'DISTINCT' vs 'GROUP by' [message #313300] |
Fri, 11 April 2008 06:11  |
vkrn
Messages: 18
Registered: March 2008
|
Junior Member |
|
|
Hi all,
 . 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 #313349 is a reply to message #313300] |
Fri, 11 April 2008 08:15  |
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 |
rleishman
Messages: 3728
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 |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
.kindly help me.