<오라클 성능 고도화 원리와 해법2> Ch05-02 소트를 발생시키는 오퍼레이션

Posted May 15, 2024

By Mijong Go 7 min read

오라클 성능 고도화 원리와 해법2 - Ch05-02 소트를 발생시키는 오퍼레이션

(1) Sort Aggregate

sort aggregate는 전체로우를 대상으로 집계를 수행할 때 나타나는데, ‘sort’라는 표현을 사용하지만 실제 소트가 발생하지는 않는다.

(2) Sort Order By

데이터 정렬을 위해 order by 오퍼레이션을 수행할 때 나타난다.

(3) Sort Group By

sort group by는 소팅 알고리즘을 사용해 그룹별 집계를 수행할 때 나타난다.

Hash Group By와 비교

10gR2에서 hash group by 방식이 도입되면서, order by 절을 함께 명시하지 않으면 대부분 hash group by 방식으로 처리된다.

hash group by는 정렬을 수행하지 않고 해싱 알고리즘을 사용해 데이터를 그룹핑한다. 읽는 로우마다 group by 컬럼의 해시 값으로 해시 버킷을 찾아 그룹 별로 집계 항목을 갱신하는 방식이다.

sort group by라고 해서 모든 데이터를 정렬하고 나서 집계하는 것은 아니며, hash group by와 마찬가지로 읽는 로우마다 그룹 별로 집계 항목을 갱신한다. 다만, 그룹을 찾아가는 방식이 해싱 알고리즘이냐 소팅 알고리즘이냐의 차이만 있을 뿐이다. 집계할 대상 로우가 아무리 많아도 그룹 개수(즉, group by 기준 컬럼의 distinct value 개수)가 소수일 때는 두 방식 모두 디스크 소트가 전혀 발생하지 않는 것을 통해 이를 알 수 있다.

참고로, hash group by 기능을 비활성화하고 싶으면 아래와 같이 하면 된다.

  
alter system set "_gby_hash_aggregation_enabled" = false;

Group By 결과의 정렬 순서

10gR2에서 hash group by가 도입되면서 정렬 순서를 보장하지 않게 되었다고 많은 분들이 알고 있지만, 사실 오라클은 9i부터 이미 group by 결과가 보장되지 않는다고 여러 문서를 통해 공식적으로 밝혔다. (심지어 그 이전에도 보장한 적은 없다고 말한다.)

소팅 알고리즘을 사용해 sort group by된 결과 집합은 논리적인 정렬 순서를 따라 포인터로 연결돼 있을 것이다? 하지만 물리적인 순서는 논리적 순서와 다를 수 있고, order by를 명시하지 않았을 때는 논리적 순서를 무시하고 물리적 순서에 따라 결과를 출력하기 때문에 정렬을 보장하지 않는 것이라고 짐작해볼 수 있다.

요컨대, 실행 계획에서 “sort group by”의 의미는 “소팅 알고리즘을 사용해 값을 집계한다”는 뜻일 뿐 결과의 정렬을 의미하지는 않는다. 물론 쿼리에 order by 절을 명시하면 정렬 순서가 보장 (-> 논리적 정렬 순서를 따라 결과 출력되지만, 이때도 실행 계획은 똑같이 “sort group by”로 표시된다.

결론적으로 말해 정렬된 group by 결과를 얻고자 한다면, 실행 계획에 설령 ‘sort group by’라고 고 표시되더라도 반드시 order by를 명시해야 한다.

한 가지 첨언하자면, group by만 기술한 기존 쿼리에 order by를 추가하면 성능이 나빠질 텐 데도 무책임하게 어느 날 갑자기 결과를 보장하지 않는다는 공식 입장을 발표했다”며 오라클을 비판하는 글을 본 적이 있는데, 그것은 오해다. 이미 설명한 것처럼 group by를 위해 내부적으로 사용하는 알고리즘이 바뀔 뿐이며, order by 절을 추가한다고 해서 정렬 작업이 두 번 일어나지는 않는다. 물론 알고리즘 차이 때문에 생기는 성능 저하는 있을 수 있다.