|
Home > Archive > Oracle Server > May 2005 > Does IN perform a implicit distinct ?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Does IN perform a implicit distinct ?
|
|
| bobde6@hotmail.com 2005-05-31, 7:23 am |
| Hi All
I was wondering if anybody knew if 'IN' performs a implicit distinct?
For instance say if i had the query below:
select stat from orders where account_id in (select distinct account_id
from accounts)
Do i need to perform a distinct lookup of the account_id ??? Is this
just a duplicate - which would require an additional sort?
I have constructed a benchmark with the same value repeated in the IN
caluse and I see no difference in response time / CPU time
Test 1
select count(*) from accounts where account_id in ('15')
Test 2
select count(*) from accounts where account_id in
('15','15','15','15'
,'15')
Test 3
select count(*) from accounts where account_id in
('15','15','15','15'
,'15','15','15','15'
,'15','15')
Test 4
select count(*) from accounts where account_id in
('15','15','15','15'
,'15','15','15','15'
,'15','15',
'15','15','15','15',
'15','15','15','15',
'15','15',
'15','15','15','15',
'15','15','15','15',
'15','15',
'15','15','15','15',
'15','15','15','15',
'15','15',
'15','15','15','15',
'15','15','15','15',
'15','15')
Bob
| |
| sybrandb@yahoo.com 2005-05-31, 7:23 am |
| The IN operator refers to a *SET*. According to relational calculus, a
set never has duplicates!
So the distinct in your first statement is, by design, fully redundant.
In the past (pre 9i), the optimizer definitely wasn't smart enough to
notice the distinct was redundant, so it performed two sorts.
One would wish people would do something to learn relational concepts!
--
Sybrand Bakker,
Senior Oracle DBA
|
|
|
|
|