oracle 소트 연산과 오퍼레이션 및 union

oracle 소트 연산

oracle 소트연산과 각종 오퍼레이션에 대해서 알아보려 한다. oracle 소트연산은 많은 비용이 발생되는 작업이다. PGA의 sort area에서 소트작업을 진행하는 것을 '메모리 소트', 작업양이 많아 디스크의 물리적인 temp 영역에서 소트작업을 하게 되면 '디스크 소트'라 한다. 또한 oracle 소트 작업은 sort aggregate, sort order & group by 등의 여러 오퍼레이션이 있다.

oracle 소트 연산

oracle 소트 연산은 order by, group by 등의 소트 작업이 필요한 경우, oracle 메모리 영역인 PGA의 sort area에서 수행된다. 하지만 메모리 영역인 PGA의 sort area에 공간이 없어 소트 연산을 할 수 없게 되면 물리적인 공간인 디스크의 temp 테이블스페이스를 사용하여 진행하게 된다. 이렇게 소트 연산을 어디서 진행하느냐에 따라 메모리 소트와 디스크 소트로 나뉜다. 메모리 소트는 'in-memory-sort'라고도 하며 소트 작업을 메모리 내에서 완료하는 것을 말하며, 'internal sort'라고도 한다. 디스크 소트는 'to-disk'sort'라고 하며, PGA의 sort area에 공간이 없어 물리적인 디스크 공간까지 사용하는 것을 말하며, 'external sort'라고도 한다. 소트 연산의 전체적인 프로세스는 다음과 같다. 우선 SGA의 버퍼캐시에서 소트연산 대상이 되는 값들을 읽어 PGA의 sort area에서 정렬 작업을 시작한다. 정렬할 데이터가 많은 경우 완료되지 않은 정렬된 집합들을 temp 테이블스페이스의 임시 세그먼트에 저장하는데, 이때 정렬된 집합들을 sort run이라 한다. temp 테이블스페이스에서 정렬작업이 끝나게 되면 결과 값을 merge 해야 하는데 오름차순, 내림차순에 맞춰 값들을 PGA로 읽어 들인다.

oracle 소트 오퍼레이션

소트 연산은 메모리와 CPU 사용이 많으며, 데이터가 많은 경우 temp 테이블 스페이스를 사용하기 때문에 디스크 I/O까지 발생하여 쿼리 성능이 나빠진다. 또한, 부분범위 처리를 불가능하게 되기 때문에 되도록 소트가 발생하지 않거나, 불가피하다면 메모리 소트로 소트작업을 마쳐야 한다. 소트를 발생시키는 오퍼레이션은 여러 가지가 있고 다음과 같다. sort aggregate는 sum, max, min, avg 등의 집계를 수행할 때 나타나며 실제로 정렬작업은 하지 않지만 sort area를 사용한다. sort order by의 경우는 order by 작업을 진행할 때 나타나며, sort group by 또한 group by 작업을 진행할 때 나타난다. 또 하나의 오퍼레이션은 sort unique이며 서브쿼리를 unnesting 한 후 메인 쿼리와 조인하기 전에 중복 레코드를 제거하게 되는데 이때 발생하는 오퍼레이션이다. 또한, intersect, minus, union과 같은 집합 연산자를 사용할 때도 sort unique 오퍼레이션이 발생하며, distinct 연산자를 사용할 때도 발생하게 된다. sort join의 경우 소트 머지 조인을 수행할 때 나타난다. 마지막으로 윈도우 함수를 수행하는 경우 window sort가 수행된다.

oracle union과 union all

SQL을 작성할 때는 필요하지 않은 소트작업이 발생하지 않도록 SQL을 작성해야 한다. 그 이유는 앞에서 설명한 것과 같이 소트작업은 메모리와 CPU 집약적이며, I/O까지 발생할 수 있는 작업이기 때문이다. 그렇기 때문에 소트연산을 피할 방법이 있는지 확인해 봐야 한다. union의 경우 위, 아래 두 집합들에 중복되는 값이 있는 경우 그 중복을 제거하기 위해서 소트작업을 진행하게 되는데, 이 자체가 많은 비용을 발생시킨다. 반면에 union all의 경우 중복 데이터를 확인하지 않고 단순히 두 집합을 결합하기 때문에 따로 소트 작업이 수행되지 않는다. 그렇다고 무작정 union을 소트작업을 하지 않는 union all으로 변경하다가는 결과 집합이 달라질 수 있기 때문에 주의가 필요하다. 위, 아래 SQL 집합사이 중복되는 데이터가 없는 경우는 바로 union all으로 변경하면 큰 문제가 발생하지는 않는다. 이와 같은 경우는 중복되는 값이 없는데 굳이 소트 작업이 불필요하기 때문에 union을 쓰는 것 대신 union all을 사용하는 것이 바람직하다. 하지만 기존 union 절에 중복되는 값이 있는 경우, 소트 작업을 피하기 위해 union all을 사용하면 중복되는 값이 산출된다. 이 경우 아래 있는 SQL 집합의 where 절을 변경하여 중복되는 값이 없게 만들어 union all을 사용해도 중복되는 데이터가 나오지 않게 사용하면 된다.