DISTINCT May Remove Sorting From Pre-Sorted Data

Distinct는 원래 Sort가 발생되도록 되어 있지만, Query 결과가 Sorted되지 않은 상태로 나오는 경우가 있습니다.

그 원인과 해결 방법에 대해서 아래와 같이 정리합니다.

이 문제는 Oracle DB Version : 10.2.0.1 ro 11.1.0.7 까지 모든 Platform에서 발생합니다.

Symptoms

Including a DISTINCT clause in a query may re-sort data into an un-sorted order. 

For  example:

select distinct username 
from ( select username 
from all_users 
order by username ) 

USERNAME 
------------------------------ 
MDSYS 
TSMSYS 
SYSMAN 
ORDPLUGINS 
ORDSYS 
SYS

Cause

Distinct does not guarantee that data will be returned in sorted order since methods that do not feature sort algorithms (such as hashing) can be used to evaluate this operation. 

All that distinct guarantees is that no duplicates will be included. If the method used to remove duplicates happens to be a sort operation then the data may be sorted, but if not the data may not be sorted. Indeed, if the data was sorted by a previous step, it may be un-sorted by a distinct operation.

Since there is no order by clause on the query block featuring the distinct, there is no requirement that the data must be returned in a sorted order.

Solution

If a sorted order is required, an order by must be included in the relevant query block.

select distinct username 
from ( select username 
from all_users 
order by username ) 
order by username;

개발할 때, 간과할 수 있는 소지가 다분한 Issue이므로 꼭 기억해야만 원하는 Data를 얻게 됩니다.


Reference : Oracle Support