새롭게 안 사실들
전부...?
공부한 내용들
제 2장 조인의 최적화 방안
대부분의 데이터 처리 작업에서는 하나 이상의 테이블을 엮는 조인이 필요하다. 특히 관계형 데이터베이스는 정규화를 중시하기 때문에, 정보를 다수의 테이블에 분산 저장하고 이들 간의 관계를 바탕으로 조인을 수행해야만 데이터를 재구성할 수 있다. 조인은 단순히 테이블을 연결하는 방식에 그치지 않으며, 실행 계획 및 처리 방식에 따라 성능이 크게 좌우된다.
과거의 파일 시스템이나 망형/계층형 DBMS에서는 배열로 데이터를 저장하면서도 큰 성능 문제가 없었다. 그러나 관계형 데이터베이스에서는 서로 다른 테이블을 연결해야 하기 때문에, 조인을 제대로 이해하고 최적화하는 것이 중요하다. 특히 2차원 테이블 구조를 기반으로 설계된 관계형 데이터베이스는 조인을 거치지 않고는 원하는 데이터를 얻기 어렵기 때문에, 효율적인 설계를 위해 반드시 조인이 필요하다.
조인을 이해하는 가장 기본적인 방법은 요리 비유를 통해 설명할 수 있다. 단순한 요리에는 재료가 적게 들지만, 복잡한 요리를 만들기 위해서는 다양한 재료가 필요하고, 그만큼 조리 과정도 복잡해진다. 마찬가지로 단일 테이블에서 데이터를 가져오는 것은 쉽지만, 다양한 테이블을 조합하여 데이터를 재구성해야 할 경우, 조인의 설계가 핵심이 된다.
조인을 단순히 equal 조건으로만 생각하는 경향은 위험하다. 이는 조인의 다양한 형태를 무시하고 단일 방식으로만 처리하는 실수를 초래할 수 있다. 또한 관계형 DBMS에서는 집합 연산의 연장선에서 조인을 다루어야 하며, 필요에 따라 인라인 뷰(Inline View) 등을 활용해 조합 순서를 바꿔야 한다. 이때 SQL을 논리적으로 재구성함으로써 더 나은 성능을 이끌어낼 수 있다.
현실적으로는 조인에 의한 SQL 최적화가 전체 시스템 성능에 큰 영향을 미칠 수 있으며, 실제로 배치 프로그램에서 조인의 구조를 잘 설계함으로써 수 시간 걸리던 작업을 수 분 내로 단축하는 사례도 존재한다. 이는 조인이 단순한 연산이 아니라 중요한 성능 최적화의 대상임을 방증한다.
이 장에서는 조인의 단순한 유형 소개를 넘어서, 조인을 통해 실제 데이터가 어떻게 효율적으로 추출되는지를 분석하고, 실행 계획에 따른 실제 처리 결과의 차이를 이해하는 데 중점을 둔다. 또한 조인을 집합 연산으로 확장해 접근하는 방식과 내부 처리 절차를 분석함으로써, 액세스 효율에 영향을 미치는 요인을 상세히 조명할 예정이다.
2.1 조인과 반복연결(Loop query)의 비교
조인을 사용하지 않고도 두 개의 테이블을 연결하는 다양한 방법이 존재한다. 2권에서 다룬 것처럼 UNION, GROUP BY, 서브쿼리(Subquery), 저장 함수(Stored Function) 등을 활용할 수 있으나, 여기서 말하는 방법은 보다 절차적이고 원시적인 방식이다. 이 방식은 과거 절차형 애플리케이션에서 주로 사용되던 것으로, 기존 테이블에서 로우를 하나씩 읽고 이를 통해 다른 테이블을 찾아가는 방식이다.
이러한 방식은 DECLARE CURSOR로 테이블을 하나씩 패치(Fetch)하고, FOR LOOP에서 다시 SQL을 실행하여 다른 테이블을 조회하는 방식이다. 이런 반복적 연결을 반복연결(Loop Query)라고 부른다. 이름 그대로, SQL이 반복적으로 실행되며 연결되는 구조이기 때문이다.
실제로 조인보다 Loop Query 방식이 더 유리하다고 생각하여 이를 고수하는 사용자들도 있는데, 이는 대부분 조인을 제대로 이해하지 못한 채 개발을 해왔기 때문이라고 볼 수 있다. 하지만 결과적으로는 조인이 훨씬 효율적인 경우가 많다.
Loop 방식이 널리 쓰인 이유는, 상황에 따라 유리해 보이는 경우가 있고, 기존 절차형 언어(예: COBOL, C) 기반의 애플리케이션에서 익숙했던 처리 패턴이기 때문이다. 특히 Nested Loops 조인과 반복연결 방식은 그 구조가 유사하여 혼동하기 쉬운데, 중요한 차이는 조인의 경우 옵티마이저에 의해 적절한 순서와 방식으로 실행된다는 점이다.
조인을 구성할 때는 집합 연산 개념을 따르며, 특정 집합이 반드시 주(主)가 되는 개념은 아니다. 예를 들어 2 * 3 * 9와 같은 연산은 순서에 관계없이 결과는 같듯, 조인 역시 어떤 테이블이 먼저 액세스될지는 옵티마이저의 판단에 따라 달라진다. 처리 방식은 달라도 결과는 동일하지만, 처리 순서에 따라 실제 수행 속도는 매우 큰 차이를 보일 수 있다.
즉, 우리가 단순히 형태만 바꿨다고 해도 순서가 바뀌면 성능에 큰 영향을 줄 수 있다는 것이다.
이처럼 Loop 방식은 SQL이나 인덱스 구성 측면에서도 커다란 차이를 만들어낸다. 특히 옵티마이저가 조인을 적절히 선택하지 못하는 경우, 성능이 매우 나빠질 수 있다. 실제로 DBMS에 따라 자동 조인 경로가 최적화되지 않는 경우도 있으며, SQL 튜닝이 제대로 되지 않으면 예기치 않게 비효율이 발생한다.
또한 현실에서는 SQL을 직접 작성하지 않고 애플리케이션에서 반복 실행 구조를 사용하는 경우가 많아 이런 성능 문제를 체감하지 못하고 넘어가는 경우도 많다. 결국 조인을 잘 모르면 원인을 조인 탓으로 돌리는 오류가 생긴다.
반복연결 방식은 조인에 비해 실행 순서를 직관적으로 알 수 있어 디버깅이 쉽고, 처리 순서가 명확하다는 장점이 있다. 그러나 이러한 방식은 조인의 방향을 잘못 선택하거나 잘못된 순서로 실행될 경우 큰 비효율을 초래할 수 있다.
많은 경우, 실제 애플리케이션에서는 DECLARE CURSOR → FETCH → FOR LOOP의 형태로 처리되며, 이 방식은 Nested Loops 방식의 조인과 유사하다. 그러나 우리가 조인을 명확히 설정하지 않으면 옵티마이저가 적절한 경로를 찾지 못해 비효율로 이어질 수 있다.
실제로 필자는 과거 은행의 배치 프로그램에서 6개의 수천 라인짜리 스크립트를 단 8개의 SQL로 통합하여 8시간짜리 작업을 20분으로 줄인 사례도 있다고 한다. 이처럼 SQL의 집합 연산을 활용해 반복연결 방식의 문제를 극복하는 것이 핵심이다.
결론적으로, 데이터 처리는 하나 이상의 집합을 액세스하여 필요한 정보를 얻는 것이며, 이를 위해 적절한 집합 연결 방식을 택하는 것이 중요하다. SQL을 분리하지 않고 최대한 통합하여 처리하는 것이 효율적인 방법임을 잊지 말아야 한다.
2.1.1 전체범위 처리 방식에서의 비교
전체범위 처리를 하는 SQL, 예를 들어 GROUP BY나 배치 SQL처럼 전체 범위를 스캔해야 하는 쿼리는 내부적으로 어떤 방식으로 처리되는지 살펴보면, 결국 조인을 완전히 피할 수는 없다는 점이 드러난다.
조인 방식은 전체범위 처리와 반복연결 방식으로 나뉘는데, 전체범위 처리는 조인 대상 테이블을 한번만 스캔하고 인덱스를 통해 연결된 테이블을 접근한다. 반면, 반복연결 방식은 커서를 통해 한 줄씩 읽어들여 각 행마다 다른 테이블에 반복적으로 접근하는 방식이다.
예를 들어 TAB1 테이블의 1,000건을 처리하면서 TAB2와 조인을 한다면, 전체범위 방식은 1,000건에 대해 단 한 번의 SQL로 처리하고, 반복연결 방식은 1,000번의 SQL을 수행하게 된다. 이처럼 반복연결 방식은 실제 처리 건수가 많을수록 성능상 불리할 수 있다.
하지만 모든 상황에서 전체범위 방식이 항상 유리한 건 아니다. 예를 들어 TAB1의 전체를 처리하되 TAB2는 극히 일부만 필요하다면 반복연결 방식이 훨씬 효율적이다. 특히 애플리케이션에서 리포트나 일부 행만 처리할 경우에는 반복연결 방식이 더 적합할 수 있다.
한편, 반복연결은 내부적으로 FOR LOOP를 통해 수행되므로, 마치 '드라이빙' 역할을 하는 테이블의 범위를 먼저 정리한 뒤, 필요한 대상만 다른 테이블에 연결한다. 이때 SQL을 분리하여 처리하면 매우 유연하고 효율적인 계획을 세울 수 있다. 예를 들어 DECLARE CURSOR로 일부 조건을 먼저 처리하고, 그 결과에 대해 FOR LOOP로 반복 연결하면 필요 최소한의 액세스만으로 결과를 얻을 수 있다.
또한, 전체범위 처리를 지나치게 하나의 SQL에 몰아넣으면 오히려 성능이 나빠질 수 있으며, 일부를 먼저 가공하고 조인을 수행하는 것이 훨씬 효율적인 경우도 많다. 특히 조인이 많고 집합 간의 관계가 복잡할수록 이런 전략은 유용하다.
결론적으로 SQL 처리 전략은 조인의 형식보다 처리 순서와 방식에 따라 성능 차이가 극명하게 발생할 수 있으므로, 단순히 하나의 SQL로 묶는 것보다 상황에 따라 쿼리를 분리하고 적절히 LOOP와 조합하여 처리하는 전략이 필요하다
2.1.2. 부분범위 처리방식에서의 비교
부분범위 처리 방식의 SQL은 처리해야 할 전체 범위가 크더라도 매우 빠른 실행 속도를 보일 수 있다는 점에서 상당히 매력적인 특징을 갖고 있다. 이는 특정 조건을 만족하면 전 범위를 액세스하지 않아도 원하는 데이터만 부분적으로 처리할 수 있기 때문이다. 즉, 처리 범위가 넓더라도 ‘필요한 만큼만’ 처리하면 된다는 점이 핵심이다.
이러한 방식은 반복연결 방식과도 관련이 깊다. 그림 2-2-5에서는 조인 처리와 반복연결 처리의 차이를 보여주고 있는데, 조인 방식은 전체 범위를 대상으로 SQL을 실행하고 필요한 결과를 패치하는 반면, 반복연결 방식은 반복문(LOOP) 안에서 각 값마다 SQL을 실행하여 해당 범위만 처리하게 된다. 하지만 부분범위 처리가 가능하다면, 반복연결 방식에서도 원하는 범위만큼만 처리하면 되므로 조인과 큰 차이 없이 실행된다.
즉, 처리 대상이 크지만 부분 처리할 수 있다면 반복연결 방식도 충분히 빠를 수 있고, 그림에서도 그 차이는 거의 미미함을 확인할 수 있다. 반복연결은 SQL이 여러 번 수행되므로 약간의 부하가 있지만, 처리 건수가 적다면 그 차이는 거의 없다.
조인 방식의 장점은 SQL 내에서 연결된 테이블의 모든 컬럼을 자유롭게 사용할 수 있다는 점이다. 반면 반복연결 방식은 별도의 언어로 각 SQL을 실행해 결과를 조합해야 하므로 애플리케이션 개발의 부담이 있다. 그러나 SQL을 나누어 처리해야 하는 경우가 종종 있으며, 특히 DBMS가 제공하는 병렬처리 기능 등을 활용하지 못하는 상황이라면 반복연결 방식이 오히려 유리해질 수 있다.
이처럼 SQL을 분리해 처리할 수 있다면 SQL 형태나 액세스 경로만 바꿔도 전체적인 실행 성능을 향상시킬 수 있다. 특히 반복연결 방식은 단순한 구조이지만 효율적이고, 유지보수 시에도 코딩 변경이 단순해 생산성을 높일 수 있다.
결국 조인을 사용한다고 해서 항상 불리한 것은 아니며, 중요한 것은 실행 계획을 어떻게 통제하느냐이다. 단순히 하나의 SQL로 결과를 얻겠다는 생각보다는, 상황에 따라 조인을 분리하거나 반복연결로 처리하는 유연한 접근이 더 효과적일 수 있다. 실제로 많은 실무에서는 이러한 방법을 통해 효율적인 처리를 도모하고 있다.
마지막으로, 조인 방식에 대해 막연히 어렵고 복잡하다고 생각해 피하기보다는, 다양한 반복연결과 조인 방식의 원리를 정확히 이해하고 상황에 따라 적절히 선택하는 능력을 기르는 것이 중요하다는 점을 강조하고 있다
2.2. 연결고리 상태가 조인에 미치는 영향
조인이란 결국 집합을 연결고리(조인 조건)를 통해 서로 연결하는 것이기 때문에, 연결고리 상태는 조인에 매우 큰 영향을 미친다는 것은 자명하다. 여기서 말하는 연결고리 상태란 단순히 조인 조건의 연산자 형태뿐 아니라, 그 조건이 걸린 인덱스의 존재 여부도 포함된다.
어떤 연산자가 사용되었는지, 인덱스가 있는지 없는지에 따라 실제 연결 작업이 가능해지는지, 혹은 전혀 불가능해지는지가 결정된다. 예를 들어, 두 사람이 서로 멀리 떨어져 있지만 각자 휴대전화를 갖고 있다면 언제든 연락이 가능하다. 이는 한쪽만 연결을 원해도 연결이 가능한 상황이다. 반면, 한쪽이 휴대전화를 갖고 있지 않다면 연락 자체가 어려워진다. 이처럼 연결 수단(인덱스)의 유무는 조인의 가능성과 효율성에 결정적인 영향을 미친다.
조인에서도 같은 원리가 적용된다. 한쪽 테이블에는 인덱스가 존재해 쉽게 접근할 수 있지만, 반대쪽은 그렇지 않은 경우 조인의 성능이 극단적으로 저하될 수 있다. 이러한 비대칭 구조에서는 정렬(Sort)과 병합(Merge)을 사용하는 방식이 유리할 수 있으며, 이처럼 정렬 병합 방식으로 수행되는 조인을 Sort Merge 조인이라고 부른다.
또 다른 방식은 자신의 위치를 사전에 정해놓는 방법, 즉 해시(Hash) 방식이다. 특정 조건이 주어지면, 그 조건에 따라 해시 함수를 적용하여 해시 테이블을 만들고, 그 위치에 데이터를 저장하거나 검색하는 방식이다. 이 방식은 인덱스의 유무와 무관하게 조인 조건만 명확하다면 매우 빠르고 효율적인 조인이 가능해진다. 특히 랜덤 액세스를 피하면서도 대량의 조인에 효과적이다.
물론 이러한 방식들이 항상 모든 경우에 유리한 것은 아니다. 조인의 성능을 결정짓는 연결고리 상태는 상황에 따라 매우 달라질 수 있기 때문에, 항상 일관된 방법만 고수하는 것은 위험하다. 인덱스가 존재하지 않더라도, 조인 조건이 특정 부분에만 걸려 있는 경우엔 Nested Loops 조인도 여전히 유효할 수 있다. 하지만 특정 상황에서 불필요하게 많은 작업이 수행된다면, 적절히 해시 조인이나 정렬 병합 조인을 사용해야 한다.
궁극적으로 조인을 잘 수행하기 위해서는 연결고리 조건의 상태, 인덱스 유무, 데이터 분포와 크기 등 다양한 요소들을 고려해 최적의 조인 방식을 선택해야 한다. 하나의 조인 방식을 모든 상황에 일괄적으로 적용하는 것은 적절하지 않으며, 상황별로 최적화된 방식으로 조인의 효율을 극대화하는 것이 중요하다.
2.2.1. 연결고리 정상
조인 조건의 연결고리가 '정상'이라는 말은, 조인에 사용되는 모든 컬럼이 인덱스를 보유하고 있으며, SQL의 조건절에서 연결을 시도할 때 성능상 문제가 없음을 의미한다. 즉, 인덱스가 양쪽 테이블에 존재하는 상태를 의미하며, 이 상태에서는 연결 대상의 상수값을 통해 인덱스를 효율적으로 탐색할 수 있다.
예를 들어 TAB1.KEY1 = TAB2.KEY2라는 조건으로 조인이 이루어지는 경우, KEY1, KEY2 컬럼 양쪽 모두에 인덱스가 존재하면 어느 방향에서든 정상적인 조인이 가능하다. 이 상태를 우리는 '연결고리 정상' 상태라고 부른다.
연결 순서에 따라 TAB1 → TAB2 또는 그 반대가 될 수 있는데, 중요한 것은 양쪽에 인덱스가 존재하면 방향이 바뀌어도 성능에 문제는 없다는 것이다. 이론적으로는 M:1 또는 1:M 관계에서도 상관없이 동일한 성능을 낼 수 있지만, 실제로는 먼저 액세스되는 테이블의 범위가 작고 조인되는 건수가 적을수록 전체 수행 속도에 유리하다.
예제 SQL에서는 다음과 같은 구문을 통해 조인이 이루어진다.
SELECT a.FLD1, b.COL1, ...
FROM TAB2 b, TAB1 a
WHERE a.KEY1 = b.KEY2
AND b.COL2 LIKE 'AB%'
AND a.FLD1 = '10';이 SQL에서는 TAB2의 조건 b.COL2 LIKE 'AB%'를 먼저 만족하는 데이터가 많지 않기 때문에 TAB2를 먼저 액세스하는 것이 유리하며, 이후 a.KEY1 인덱스를 활용해 TAB1과 연결하는 방식이 더 빠를 수 있다.
또한, 수행 속도에 영향을 주는 또 다른 요인은 조인 조건을 평가하기 전 먼저 걸러지는 데이터의 양이다. 조인을 시도하기 전에 먼저 부합하지 않는 데이터가 필터링될 수 있다면 전체 수행량이 줄어들고 성능도 개선된다.
결론적으로 연결고리가 '정상'이라는 것은 단순히 인덱스 존재 여부만을 말하는 것이 아니라, 어느 방향에서든 연결을 시도해도 효율적인 조인이 가능함을 의미한다. 하지만 실행 계획 상의 액세스 방향이나 필터링 순서에 따라 실제 성능에는 차이가 발생할 수 있으며, 이 차이는 접근하는 데이터의 양과 필터 조건의 효율성에 의해 결정된다.
2.2.2. 한쪽 연결고리 이상(異常)
한쪽 연결고리에 인덱스가 존재하지 않는 상태, 즉 연결고리 이상 상태에서는 조인 작업의 효율이 크게 저하될 수 있다. 예를 들어 TAB2의 연결고리(KEY2)에는 인덱스가 있고 TAB1의 연결고리(KEY1)에는 인덱스가 없다고 가정할 때, 조인이 TAB1을 먼저 액세스한 후 TAB2를 처리하는 방향으로 수행된다면 문제가 발생하지 않는다. 이 방향에서는 KEY1이 단순히 상수값을 제공하는 역할만 하므로 인덱스가 없어도 하등의 영향을 주지 않기 때문이다.
하지만 반대로 TAB2를 먼저 액세스한 후 TAB1을 조인 대상으로 삼는다면 매우 심각한 문제가 발생한다. 이 경우 TAB2의 KEY2 값을 기준으로 조인 대상인 TAB1을 검색해야 하는데, TAB1에는 인덱스가 없기 때문에 전체 테이블을 매번 스캔해야 한다. TAB2의 각 로우마다 TAB1의 전체 데이터를 탐색하게 되어 비효율적인 처리가 반복되며, 이는 조인 성능 저하의 주요 원인이 된다.
따라서 이런 문제를 피하기 위해서는 연결고리에 이상이 있는 쪽의 테이블을 먼저 액세스해서는 안 되며, 가능한 경우 양쪽 모두 인덱스를 갖추는 것이 바람직하다. 이를 위해 옵티마이저는 실행계획을 수립할 때 연결고리 상태를 고려하여 인덱스가 없는 쪽을 뒤에 배치하도록 유도하거나, 특정 방향의 실행계획을 강제로 제어하기도 한다.
실제 실행계획에서 TAB2의 연결고리(KEY2)는 인덱스가 있지만 TAB1의 KEY1은 인덱스가 없는 상황에서 TAB2가 먼저 액세스되도록 계획이 수립되면, TAB1의 전체 테이블을 조인 범위 내내 반복해서 읽는 비효율이 발생한다. 이러한 잘못된 계획은 일부 DBMS에서 옵티마이저가 부적절한 판단을 하거나 인덱스 통계가 잘못되어 발생하는 경우가 있다.
이와 같은 문제를 방지하기 위한 방법 중 하나는, 조인 조건의 방향성을 바꿔 인덱스가 있는 테이블을 먼저 액세스하도록 SQL을 작성하거나, 실행 힌트를 통해 처리 방향을 명시적으로 지정하는 것이다.
또한 실무에서는 조인되는 컬럼이 1:1 관계로 명확히 대응되지 않을 경우, 컬럼이 포함된 구조를 변경하거나 조인을 위한 외부키 구성을 변경하는 등의 모델링상의 개선이 필요하다. 예를 들어, 'A', 'B', 'C' 컬럼이 있던 테이블을 'D'라는 통합 외부키로 단순화하면, 조인 조건이 정확하게 1:1 대응되지 않아 옵티마이저가 부정확한 방향을 선택할 수 있다. 이 경우에는 조인되는 컬럼을 쪼개어 조건을 보다 명확히 하거나, 테이블의 분리 및 설계를 다시 점검하는 것이 바람직하다.
마지막으로, 데이터 타입의 불일치도 연결고리 이상을 유발하는 주요 원인 중 하나다. 예를 들어, 판매일자 컬럼을 어떤 사용자는 CHAR 타입으로, 또 다른 사용자는 NUMBER 타입으로 정의했다면, 이 둘을 조인할 경우 내부적으로 형 변환이 발생하면서 인덱스를 활용하지 못하고 결국 연결고리 이상이 발생한다. 특히 DATE 타입을 CHAR나 VARCHAR2 타입으로 변형하여 사용하는 경우에도 동일한 문제가 자주 발생한다.
이러한 문제를 방지하기 위해서는 설계 단계에서부터 도메인 관리의 표준화와 데이터 타입의 일관성을 유지하려는 노력이 선행되어야 하며, 체계적인 통제를 통해 연결고리 이상이 발생하지 않도록 해야 한다.
2.3. 조인 종류별 특징 및 활용방안
조인은 데이터를 연결하는 가장 기본적인 방법으로, 우리가 원하는 정보를 얻기 위해서는 정규화된 정보를 필요에 따라 다양한 형태로 다시 결합해야 한다. 그러나 이러한 조인 방식은 너무나도 다양하고 복잡하여 상황에 따라 최적의 처리 경로를 찾는 것이 쉽지 않으며, 단순히 두 가지 방식으로 모든 경우를 만족시키기 어렵다.
이러한 이유로 더 많은 조인 방식들이 생겨났고, 앞으로도 새로운 방식이 등장할 가능성이 높다. 조인의 방식은 크게 두 가지로 분류할 수 있는데, 첫째는 어떤 집합을 먼저 액세스하여 상수값을 만들고 이를 이용해 대응되는 집합을 찾는 방식이고, 둘째는 서로 연결하기 좋도록 미리 소정의 선행작업을 수행한 후 서로 대응시키는 방식이다. 이 두 가지 범주에 대부분의 조인이 속한다.
조인의 종류가 많아지면서 각각의 조인은 독특한 장단점을 가지며, 상황에 따라 유리한 방안이 달라진다. 따라서 조인의 특성을 잘 이해하고 상황에 맞는 적절한 방법을 선택해야 한다는 점이 중요하다. 옵티마이저의 판단만으로는 완벽할 수 없기 때문에, 조인을 정확하게 이해하고 필요한 경우 옵티마이저의 오판을 조정할 수 있는 능력도 중요하다.
조인의 튜닝은 단순히 조인 방식만 바꾸는 것이 아니라, 주어진 SQL에 최적화를 적용하는 과정으로, SQL에 문제가 있는 부분까지 최적화해주는 것은 아니다. 사용자가 각 조인 유형을 잘 이해하고 적절한 SQL을 작성하면 옵티마이저가 더 높은 확률로 최적화를 할 수 있다. 관계형 데이터베이스를 효율적으로 활용하려면 조인의 개념을 확실히 이해하는 것이 기본이며, 이를 통해 다양한 응용도 가능해진다.
우선 사용자는 Nested Loops 조인과 Sort Merge 조인을 완벽히 이해해야 하며, 이와 함께 부분범위 처리 개념을 포함하여 이해할 필요가 있다. SQL 최적화에서 중요한 개념 중 하나는 결과를 빠르게 추출하는 ‘FIRST_ROWS’와 전체 작업을 빠르게 끝내는 ‘ALL_ROWS’이며, 이 개념의 차이에 따라 SQL의 최적화 방향이 달라질 수 있다.
2.3.1. Nested Loops 조인
Nested Loops 조인은 가장 전통적인 조인 방식이자, 동시에 가장 널리 사용되는 방법이다. 조인의 방식을 결정할 때 이 방식으로 수행해도 문제가 없다면, 다른 대안을 고민할 필요가 없을 정도로 기본이 되는 중요한 조인 방식이다.
이 조인 방식이 널리 쓰이는 가장 근본적인 이유는, 먼저 액세스한 결과를 다음 액세스에서 상수값으로 제공할 수 있다는 점에 있다. 이는 매우 중요한 특징인데, 앞서 처리한 결과가 범위를 좁게 제한해 줄 수 있고, 그 결과 다음 처리 대상 집합의 접근을 최적화할 수 있기 때문이다. 만약 이러한 조건을 활용할 수 없다면, 어떤 방식도 효율적일 수 없다.
실제 현장에서는 대부분 사용자가 원하는 특정 범위를 처리하고자 하는 경우가 많으며, 일반적으로 이는 보편적 처리를 의미하는 것이 아니라 특정 집합만을 처리하고자 하는 것을 뜻한다. 이처럼 범위가 정해진 처리에서는 Nested Loops 조인이 가장 적합하며, 실제 많은 경우에 이 방식이 사용된다.
따라서 다양한 조인 방식이 존재하더라도, 가장 먼저 이 Nested Loops 조인을 완벽하게 이해하는 것이 필수적이다.
2.3.1.1. Nested Loops 조인의 기본 개념
Nested Loops 조인은 가장 전통적이고 보편적으로 사용되는 조인 방식이며, 조건이 적절한 상황에서 매우 효과적일 수 있다. 이 방식은 내부적으로 반복적인 작업을 수행하므로 ‘Nested’라는 이름이 붙었다. 이 조인 방식의 핵심은 먼저 액세스한 결과를 상수값으로 제공하여 다음 테이블의 액세스를 가능하게 한다는 점에 있다.
예시 쿼리를 기준으로 설명하면, TAB1과 TAB2가 M:1 관계이며 각 조인 조건 컬럼에 인덱스가 있다고 가정했을 때 다음과 같은 순서로 처리된다.
- 먼저 TAB1의 FLD1 인덱스를 통해 ‘111’ 값에 해당하는 첫 번째 로우를 액세스한다.
- 해당 로우의 모든 컬럼값은 상수값이 되며, 조건 FLD2 LIKE 'AB%'를 검사한 뒤, 실패 시 다음 로우로 진행하고 성공 시 다음 단계로 넘어간다.
- 이어서 TAB1의 KEY1 값을 기준으로 TAB2의 KEY2 인덱스를 통해 대응되는 로우를 찾는다.
- TAB2 로우 중 COL1 = '10' 조건을 만족하는지 검사한다. 조건이 일치하지 않으면 다음으로 넘어간다.
- 최종적으로 조건이 모두 충족되면 원하는 결과를 반환한다.
이러한 과정을 모든 대상 로우에 대해 반복하며, TAB1의 FLD1 범위가 끝날 때까지 루프가 수행된다.
이 처리 방식의 특징 중 하나는 모든 컬럼이 인덱스를 가지고 있더라도 실행 계획에 따라 일부 인덱스만 실제로 사용될 수 있다는 점이다. 예를 들어 FLD1과 KEY2는 인덱스로 활용되었지만, 단순 조건으로 쓰인 다른 컬럼 인덱스는 사용되지 않았다.
가) Nested Loops 조인의 특징
- Nested Loops 조인은 순차적으로 처리된다. 선행 테이블(Driving Table)의 처리범위에 있는 각 로우들이 순서대로 실행되며, 연결 과정도 순차적으로 이루어진다.
- 처리 범위는 먼저 액세스되는 테이블의 범위에 따라 결정되며, 이 범위가 전체 처리 양을 좌우한다.
- 후행 테이블은 자신에게 주어진 상수값만으로 액세스된다. 즉, 선행 테이블에서 제공하는 상수값과 기존 상수값을 조합해 가장 효율적인 연결 방식을 결정하게 된다.
- 연결작업은 랜덤 액세스로 처리된다. 선행 테이블의 인덱스를 사용해 첫 번째 로우만 랜덤하게 액세스하고 이후는 스캔 형태로 이어지기 때문이다.
- 조인 조건에 포함된 모든 컬럼이 인덱스를 가지고 있다고 해도, 실제로 사용하는 인덱스는 조인의 방향과 구조에 따라 달라진다.
- 연결고리가 되는 인덱스는 조인 작업의 수행 속도에 매우 큰 영향을 준다. 연결 인덱스의 유무, 방향성에 따라 성능 차이가 커질 수 있다.
- 부분범위 처리가 가능하다. 선행 테이블에서 만족하는 조건의 로우가 일정 수 채워지면 조인을 멈출 수 있으므로 빠른 결과 도출이 가능하다.
- 마지막에 조건을 체크하는 방식이라면 전체 조인을 수행한 후 조건을 걸러내야 하므로, 조건의 위치에 따라 성능이 크게 달라진다. 2차 가공이 많은 경우 성능 저하가 발생할 수 있다.
나) Nested Loops 조인의 적용 기준
- 부분범위 처리가 필요한 경우에 유리하다. 대부분의 조인 방식은 부분범위 처리가 어렵지만, Nested Loops는 이를 지원하기 때문에 유리한 선택이 될 수 있다.
- 조인되는 한쪽 테이블의 결과만으로 처리범위를 줄일 수 있는 상황이라면 Nested Loops 방식이 유리하다. 이 조건은 이 조인 방식에서 가장 중요한 선택 기준이다.
- 주로 처리량이 적은 경우, 즉 빠르게 결과를 가져올 수 있는 상황에서는 Nested Loops가 가장 유리하다. 다만 처리해야 할 양이 많다면 부담이 될 수 있다.
- 이 방식은 인덱스를 사용하기 때문에 연결고리 인덱스 유무와 상태에 따라 적합성이 달라질 수 있다.
- 먼저 수행된 집합의 크기가 처리 범위 축소 및 수행속도에 큰 영향을 미친다. 따라서 조인 순서를 잘 정하는 것이 중요하다.
- 부분범위 처리 시에는 운반단위 크기가 중요한 영향을 미친다. 운반단위가 작으면 빠른 결과가 가능하나, 큰 경우에는 패치(fetch) 횟수가 많아져 성능 저하가 발생할 수 있다.
- 선행 테이블의 처리 범위가 작거나, 연결 테이블의 랜덤 액세스가 많다면 Nested Loops를 고려해야 한다. 특히 메모리 부담이 적은 환경에서는 Nested Loops가 유리할 수 있다.
2.3.1.2 Nested Loops 조인의 순서결정
Nested Loops 조인은 선행 테이블의 처리 결과가 후행 테이블의 액세스 범위에 직접적인 영향을 주기 때문에, 조인의 순서를 어떻게 결정하느냐에 따라 전체 조인 효율이 크게 달라진다. 테이블 중 어떤 것을 먼저 액세스하느냐에 따라 상수값으로 활용할 수 있는 컬럼이 달라지며, 이는 결국 액세스 조건의 효율성과 처리 범위에 영향을 준다.
처리 순서에서 가장 큰 영향을 미치는 요소는 처음 액세스되는 테이블, 즉 선행 집합의 처리 범위가 얼마나 좁아질 수 있느냐에 달려 있다. 연결 조건이 동일하더라도 인덱스 구성에 따라 어떤 테이블을 먼저 처리하느냐가 중요하며, 가능한 초기에 많은 범위를 줄일 수 있는 테이블을 우선 선택해야 한다.
예를 들어, 다음과 같은 SQL이 있다고 가정해 보자.
SELECT ...
FROM TAB1 x, TAB2 y, TAB3 z
WHERE x.A1 = y.B1
AND z.C1 = y.B2
AND x.A2 = '10'
AND y.B2 LIKE 'AB%'이 SQL은 x.A2 = '10'과 같은 상수 조건을 포함하고 있으며, x.A1 = y.B1, z.C1 = y.B2 같은 조인 조건이 혼합된 형태이다. 이때 어떤 테이블을 먼저 액세스하느냐에 따라, 후행 테이블에서 사용할 수 있는 조건의 범위와 형태가 달라지며, 그 결과 인덱스 활용 여부와 전체 성능에도 큰 차이를 만든다.
실제로 아래와 같이 동일한 SQL을 서로 다른 조인 순서로 수행한 경우, 각 테이블에 적용되는 액세스 조건도 달라진다.
| No | 조인 순서 | 적용된 ACCESS PATH |
| 1 | TAB1 → TAB2 → TAB3 | TAB1: A2 = '10'TAB2: B1 = A1 AND B2 LIKE 'AB%'TAB3: C1 = B2 |
| 2 | TAB2 → TAB3 → TAB1 | TAB2: B2 LIKE 'AB%'TAB3: C1 = B2TAB1: A1 = B1 AND A2 = '10' |
| 3 | TAB3 → TAB2 → TAB1 | TAB3: FULL TABLE SCAN TAB2: B2 = C1 AND B2 LIKE 'AB%' TAB1: A1 = B1 AND A2 = '10' |
조인 순서 ①번처럼 TAB1을 먼저 액세스하는 경우, x.A2 = '10'이 상수값이므로 이를 조건으로 첫 번째 로우를 읽고, 이후 y.B1 = x.A1로 연결되는 TAB2의 B1이 상수값이 된다. 결과적으로 TAB2는 y.B2 LIKE 'AB%' 조건으로 좁혀질 수 있고, TAB3는 y.B2가 이미 상수값이므로 z.C1 = y.B2도 액세스 조건이 된다. 이처럼 각 컬럼이 상수값이 되는 시점이 조인의 흐름을 결정짓는다.
조인 순서 ②번에서는 TAB2가 먼저 액세스되고, y.B2 LIKE 'AB%' 조건만 상수값으로 활용되며, 이후 TAB3에서 y.B2 = z.C1로 인해 z.C1이 상수값이 되고, TAB1에 이르러서야 x.A1 = y.B1과 x.A2 = '10' 조건이 상수값이 된다.
③번에서는 TAB3를 가장 먼저 액세스하게 되는데, 이때는 z.C1 = y.B2만 존재하지만 y.B2는 아직 상수값이 아니므로 액세스 조건이 없다. 결국 TAB3는 풀 테이블 스캔이 되고, 이후 TAB2에서 y.B2 LIKE 'AB%'를 통해 조건이 걸리고, 마지막 TAB1에서야 A1 = B1 AND A2 = '10' 조건이 성립한다.
이러한 비교를 통해 알 수 있는 핵심은, 가능한 빨리 상수값을 확보하고, 그것을 후속 테이블의 액세스 조건으로 활용하는 것이 가장 효율적인 조인 순서를 만드는 길이라는 것이다.
2.3.2. Sort Merge 조인
Sort Merge 조인은 Nested Loops 조인과는 다르게, 각 연결 대상에 대해 랜덤 액세스를 하지 않기 위해 미리 정렬(Sort)된 데이터를 병합(Merge)하여 연결하는 방식이다. 조인 집합에 대응되는 대상은 서로 알 수 없는 임의의 장에 위치하고 있기 때문에, 이들을 연결 가능하게 하기 위해서는 사전 정렬 작업을 통해 연결될 수 있도록 만들어야 한다. 이 방식은 정렬이라는 오버헤드가 있지만, 랜덤 액세스를 하지 않기 때문에 연결 자체는 매우 빠르게 수행된다.
정렬의 부담을 고려하지 않는다면, Sort Merge 조인은 연결 속도 면에서 매우 빠르다는 장점을 가진다. 그러나 결국 이 조인의 성능은 정렬 작업에 대한 부담이 얼마나 큰지에 따라 결정된다. 실제로는 정렬된 집합을 스캔하며 머지를 수행하므로 연결 작업 자체는 효율적이다.
이를 설명하기 위한 예시 SQL과 함께 아래 그림에서는 Nested Loops 조인과 동일한 SQL을 Sort Merge 방식으로 수행하는 구조를 보여준다.
┌────────────────────────────┐ ┌────────────────────────────┐
│ INDEX (FLD1) │ │ INDEX (KEY2) │
│ ┌────────────────────┐ │ │ ┌────────────────────┐ │
│ │ FLD1 = '111' │ │ │ │ COL1 = '10' │ │
│ └────────┬───────────┘ │ │ └───────┬────────────┘ │
└────────────┼───────────────┘ └───────────┼────────────────┘
│ │
▼ ▼
┌──────────────┐ ┌──────────────┐
│ TAB1 │ │ TAB2 │
└────┬─────────┘ └──────┬───────┘
│ │
│ FLD2 LIKE 'AB%' │
▼ ▼
┌──────────────┐ ┌──────────────┐
│ SORT │ │ SORT │
└────┬─────────┘ └────┬─────────┘
│ │
└────────────┬──────────────┬──────┘
▼ ▼
┌──────────────────────┐
│ MERGE JOIN │ ← KEY1 = KEY2
└────────┬─────────────┘
▼
┌───────────┐
│ 결과 집합 │
└───────────┘실행 절차는 아래와 같다.
- TAB1은 FLD1 인덱스를 통해 '111' 값을 가진 로우를 차례로 액세스하고, 이후 조건 FLD2 LIKE 'AB%'를 만족하는 로우들에 대해 연결 조건인 KEY1 값을 기준으로 정렬된다.
- TAB2는 COL1 = '10' 조건에 따라 인덱스를 통해 액세스되고, 이 역시 연결 조건인 KEY2 값을 기준으로 정렬된다.
- 두 테이블 모두 정렬된 상태에서 병합을 수행하며, KEY1 = KEY2인 로우들을 찾아서 결과를 반환한다.
이 방식의 장점은 정렬된 결과를 순차적으로 스캔하므로 랜덤 액세스가 없다는 점이다. 정렬이 완료된 후에는 메모리 내에서 병합 연산만 수행되기 때문에 전체적으로 연결 비용이 적고 안정적이다. 특히 연결할 때 각 테이블의 조건이 인덱스를 통해 필터링되었기 때문에 액세스 대상이 이미 줄어든 상태에서 병합이 수행되어, 불필요한 로우에 대한 비교가 최소화된다.
가) Sort Merge 조인의 특징
Sort Merge 조인은 조인 대상 테이블의 각 집합이 독립적으로 정렬된 상태에서 병렬적으로 조인을 수행한다는 점이 가장 큰 특징이다. 즉, 양쪽 집합이 모두 정렬이 완료되어야만 병합(Merge)을 시작할 수 있으므로, 순차적인 처리만 가능한 Nested Loops 조인과는 다른 성격을 가진다.
첫 번째로, 이 조인은 동시적으로 처리되며 각 집합의 처리 결과나 상태에 영향을 받지 않고 자신이 가진 조건만으로 액세스를 수행한 뒤 정렬한다. 이처럼 양 집합이 미리 준비되어야만 조인이 가능하므로, 순차처리는 어렵고 선행 집합 없이도 실행이 가능한 방식이다.
두 번째로, 각 집합이 자체적인 조건으로 필터링 범위를 줄일 수 있으며, 상대 집합의 상태와 무관하게 조인 준비를 수행한다. 이러한 독립성은 집합 간 의존도가 높은 Nested Loops 조인과 가장 대조되는 부분이다.
세 번째로는 정렬이 반드시 선행되어야 조인이 가능하다는 점에서, 조인 대상이 정렬 준비를 완료하지 않으면 병합을 시작할 수 없다. 이로 인해 원칙적으로는 전체범위 처리와 비슷한 비용이 발생할 수 있다.
네 번째는 스캔 방식이 주로 순차적이며, 정렬 후 조인이 이루어지므로 무작위 액세스(Random Access)가 거의 발생하지 않는다. 대신, 인덱스를 사용하더라도 정렬을 피할 수 없는 구조다.
다섯 번째로, 주어진 조건이 모두 인덱스를 사용하고 있다고 하더라도 연결 조건 컬럼의 인덱스가 반드시 활용되는 것은 아니다. 불필요하다고 판단되면 무시될 수 있으며, 이는 옵티마이저의 판단에 따른 것이다.
여섯 번째로, 이 조인은 조인 방향에 거의 영향을 받지 않는다. 어느 집합이 먼저 수행되더라도 전체 집합이 정렬되어야만 병합이 가능하기 때문이다.
일곱 번째로, 인덱스를 활용하여 자신의 처리 범위를 줄이기 위해서는 가장 유리한 조건 하나만 사용되며, 그 외의 조건들은 인덱스를 사용하지 못하고 정렬 대상에 포함되어야 한다. 이 때문에 Nested Loops 조인에 비해 인덱스 사용의 이점이 제한적이다.
Sort Merge 조인은 대용량 데이터를 병합할 때 적합하지만, 정렬 비용과 인덱스 제약으로 인해 무조건적인 우위를 갖는 것은 아니다. 집합이 정렬되기 전까지 병합을 시작할 수 없기 때문에 처리 순서가 유연하지 않으며, 정렬 자체가 비용이 큰 작업이 될 수 있다는 점도 고려해야 한다. 따라서 옵티마이저는 Sort Merge 조인을 선택할 때 이러한 제약들을 종합적으로 평가해 판단해야 하며, 개발자 또한 조인 전략의 구조적 특성을 이해하고 있어야 한다.
나) Sort Merge 조인의 적용 기준
Sort Merge 조인은 전체 범위를 처리해야 하거나, 특정 조건하에서 효율적인 방식으로 선택될 수 있다. 다음과 같은 기준에서 적용 여부를 판단할 수 있다.
먼저, 아직까지 전체 범위를 처리할 수밖에 없는 프로세싱에서 이 조인을 고려할 수 있다. 즉, 랜덤 액세스가 어렵고 전체 집합을 다뤄야 하는 상황에 적합하다.
또한, 상대방 테이블에서 상수값을 받지 않더라도 자체적으로 처리 범위를 줄일 수 있다면, 상대적으로 유리하게 작용한다. 예컨대, 상수값을 받아도 범위를 줄이지 못하는 테이블이라면 Sort Merge 조인이 유리할 수 있다. 그러나 부분범위 처리가 가능하다면 Nested Loops가 더 효율적일 수 있으므로 주의가 필요하다.
주로 처리량이 많거나 항상 전체 범위 처리를 요하는 경우에는 유리하다. 이는 Sort Merge 조인이 주로 스캔 방식이기 때문에, 많은 양의 랜덤 액세스를 피할 수 있기 때문이다.
인덱스를 생성할 필요가 없는 상황, 즉 연결 조건이 단순하거나 이미 정렬이 되어 있는 경우에는 인덱스 없이도 효율적으로 사용할 수 있다.
그리고 이 방식은 자신의 처리 범위를 명확히 줄일 수 있는 경우, 전체 수행 속도에 큰 영향을 줄 수 있으므로, 적절한 인덱스 구성이 중요하게 작용한다.
다만, 전체 범위 처리를 하므로 일단 로우 수(Fetch)가 많은 작업에서 성능에 영향을 줄 수 있다. 물론, 한 번에 대량의 로우를 가져오는 경우 그 영향은 적다.
또한, 대량의 처리가 일어나는 OLAP, 데이터 마이닝, 온라인 애플리케이션에서는 Nested Loops 조인보다 유리한 경우가 많기 때문에 주로 이 방식이 사용된다.
옵티마이저의 목표가 ALL_ROWS인 경우, 정렬 후 병합하여 결과를 얻는 Sort Merge 조인이 자주 선택될 수 있다. 특히 부분범위 처리를 하려고 할 경우 옵티마이저의 목표가 어떻게 설정되었는지를 반드시 확인해야 한다.
끝으로, 충분한 메모리가 사용 가능하고 병렬 처리를 통해 정렬 작업을 빠르게 수행할 수 있는 시스템이라면, Sort Merge 조인은 대량 데이터 조인에 매우 효과적으로 적용될 수 있다.
2.3.3. Nested Loops 조인과 Sort Merge 조인의 비교
SELECT a.FLD1, ..., b.COL1
FROM TAB1 a, TAB2 b
WHERE a.KEY1 = b.KEY2
AND a.FLD1 = '111'
AND a.FLD2 LIKE 'AB%'
-- AND b.COL1 = '10' -- ← 이 조건은 삭제TAB2에 적용되던 COL1 = '10' 조건을 제거하여 조인 방식별 처리 차이를 관찰한다.
Nested Loops 조인
TAB1에서 FLD1 조건으로 인덱스를 이용해 데이터를 찾고, FLD2 조건도 적용한다. 그 결과로 얻은 KEY1 값을 이용해 TAB2의 KEY2 인덱스를 통해 ROWID로 접근하는 구조는 아래와 같다.
┌────────────┐ ┌────────────┐
│ INDEX │ │ INDEX │
│ (FLD1) │ │ (KEY2) │
└────┬───────┘ └─────┬──────┘
│ │
┌────▼────┐ ┌────▼────┐
│ TAB1 │ │ TAB2 │
└────┬────┘ └────┬────┘
│ │
조건: FLD1='111' 조건: (삭제됨)
FLD2 LIKE 'AB%' ▶ 전체 접근
▼ ▼
결과 결과
- COL1 조건이 삭제되면서 TAB2는 ROWID 기반 조건 필터링 없이 전체 접근이 일어남.
- 조건이 없더라도 Nested Loops 방식은 이미 선행 테이블에서 범위를 좁힌 상태이므로 영향이 적음.
- 처리량은 약간 늘었지만 전체 조인 성능엔 큰 차이가 없을 수 있음.
Sort Merge 조인
동일한 조건으로 Sort Merge 방식으로 수행한 경우는 다르다.
TAB1과 TAB2는 각각 정렬되어 병합(merge)된다.
┌────────────┐ ┌────────────┐
│ INDEX │ │ FULL TABLE │
│ (FLD1) │ │ SCAN │
└────┬───────┘ └──────┬─────┘
│ │
┌────▼────┐ ┌─────▼───┐
│ TAB1 │ │ TAB2 │
└────┬────┘ └────┬────┘
│ │
SORT SORT
▼ ▼
┌──────────────┐
│ MERGE JOIN │
└────┬─────────┘
▼
결과
- TAB2의 조건이 사라졌기 때문에 정렬 대상 데이터가 늘어나 병합 시 비용 증가.
- 특히 TAB2는 전체 테이블 스캔 후 정렬되므로 처리량이 급증.
- 정렬 대상이 많으면 메모리 부족, 정렬 실패 등으로 Nested Loops보다 느려질 수도 있음.
조건이 모두 없는 경우
SELECT a.FLD1, ..., b.COL1
FROM TAB1 a, TAB2 b
WHERE a.KEY1 = b.KEY2
ORDER BY a.FLD5, b.COL5;모든 조건이 제거된 후 실행되며, 두 테이블 모두 전체 스캔하게 된다.
아래는 Nested Loops 조인을 했을 때 상황이다.
┌────────────┐ ┌────────────┐
│ FULL SCAN │ │ INDEX │
│ TAB1 │ │ (KEY2) │
└────┬───────┘ └─────┬──────┘
│ │
┌────▼────┐ ┌────▼────┐
│ TAB1 │ │ TAB2 │
└────┬────┘ └────┬────┘
│ │
랜덤 액세스 정렬
▼ ▼
▶▶▶▶▶▶ 결과- TAB1은 전체 스캔하고, 그 결과로 TAB2를 인덱스로 반복 접근.
- 랜덤 액세스가 많이 발생하여 비효율적.
- 최악의 조합으로 성능 저하 우려.
그리고 그 다음은 Sort Merge 조인을 했을 때 상황이다.
┌────────────┐ ┌────────────┐
│ FULL SCAN │ │ FULL SCAN │
│ TAB1 │ │ TAB2 │
└────┬───────┘ └─────┬──────┘
│ │
┌────▼────┐ ┌────▼────┐
│ SORT │ │ SORT │
└────┬────┘ └────┬────┘
┌──────────────┐
│ MERGE JOIN │
└────┬─────────┘
▼
결과- 두 테이블 모두 정렬 후 병합.
- 전체 범위 처리이지만 랜덤 액세스를 피할 수 있어 Nested Loops보다 유리.
2.3.4 해시(Hash)조인
전통적인 조인 방식인 Nested Loops 조인과 Sort Merge 조인은 서로의 장·단점을 보완해주는 대표적인 방식으로 오랫동안 사용되어 왔다. 그러나 데이터의 양이 많아지거나 처리 범위가 넓어질 경우, 이 두 방식 모두 한계에 봉착하게 되며, 이를 보완하고자 등장한 것이 바로 해시 조인(Hash Join) 방식이다.
해시 조인은 특히 소량의 데이터보다는 대량의 데이터를 처리할 때 유리하다. Nested Loops 조인은 소량의 특정 범위에 대해 빠르고 효율적인 반면, 대량 범위에는 그 효과가 떨어진다. Sort Merge 조인은 정렬이라는 부하가 존재하고, 대량 범위의 조인에서는 이 정렬 작업이 전체 성능에 큰 영향을 미친다. 이러한 문제를 해결하기 위해 등장한 해시 조인은, 랜덤 엑세스를 줄이고 정렬 비용을 피할 수 있는 방식이다.
이 방식에서 가장 핵심이 되는 것은 바로 정렬과 랜덤 엑세스의 부담으로부터 벗어나는 원리다. 랜덤 엑세스는 블록 I/O와 디스크 탐색을 반복하므로 비용이 큰데, 해시 조인은 일괄적이고 균일한 작업 방식으로 이 부담을 줄인다. 특히 정렬은 메모리 내 정렬 버퍼 크기를 초과하면 디스크를 사용하는 비효율적인 상황이 생기며, 이로 인해 성능 저하가 발생한다.
또한, 대용량 데이터의 정렬은 버퍼를 아무리 늘려도 일정 배수 이상으로는 성능 개선이 쉽지 않고, 오히려 낭비가 되는 경우도 많다. 따라서 랜덤 엑세스를 줄이고, 정렬을 피하는 해시 조인은 대용량 데이터 처리에 매우 적합한 방식이다.
해시 조인의 가장 기본적인 원리는 **해시 함수(Hash Function)**를 사용하는 것이다. 이는 어떤 값을 입력으로 받아 그 값을 기준으로 일정한 위치값을 반환하는 방식으로, 데이터베이스 내에서 원하는 레코드를 빠르게 찾아내는 데 사용된다. 이 해시 함수는 문자열이나 숫자값을 일정한 길이의 해시 키로 변환하는 과정이며, 이 키를 통해 원하는 데이터를 바로 참조하거나 일치 여부를 빠르게 판단할 수 있다.
해시 조인은 이러한 해싱 기법을 기반으로 조인 연산을 수행하며, 일반적으로는 조인의 대상이 되는 테이블 중 하나를 해시 테이블로 만들고, 다른 테이블과 조인 조건을 만족하는지 비교한다. 이 방식은 인덱스를 사용하지 않고도 빠르게 연결이 가능하며, 또한 파티션 단위로 조작되기 때문에 처리 성능이 예측 가능하고 일관된다는 장점이 있다.
해시 영역(Hash Area)
해시 조인을 수행하기 위한 메모리 내의 공간으로, 비트맵 벡터, 해시 테이블, 파티션 테이블로 구성된다. 이 공간은 디스크 사용을 줄이기 위해 크기를 적절히 지정하는 것이 중요하다.
파티션(Partition)
파티션(Partition) 동일한 해시 값을 가진 데이터를 묶는 단위로, 빌드 입력이 메모리 내 처리 불가능할 경우 나눠지는 단위이다.
너무 많거나 적을 경우 I/O 비효율을 유발한다.
클러스터(Cluster)
파티션은 다시 클러스터 단위로 나뉘며, 연속된 블록으로 구성되어 I/O 단위로 활용된다. 해시 클러스터링 시 매우 유사한 구조로 활용된다.
빌드입력(Build Input)과 검색입력(Prove Input)
조인에서 먼저 준비되는 입력은 빌드입력, 이후 확인을 위한 입력은 검색입력이다. 파티션 내 같은 해시값 묶음은 동일한 연결 단위가 된다.
인-메모리 해시조인과 유예 해시조인
해시 조인 시 해시 영역이 충분하면 인-메모리 방식으로 수행되며, 부족하면 유예 방식으로 임시 세그먼트에 파티션이 저장된다.
비트맵 벡터(Bitmap Vector)
검색입력의 파티션 생성을 위한 필터링 값으로 사용되며, 해시 영역을 초과 시 임시 세그먼트에 저장되어 조인 종료 시까지 유지된다.
해시 테이블(Hash Table)
조인 수행을 위한 메모리 내 테이블로, 일반 인덱스와 달리 해시값과 클러스터 주소를 가진다. 클러스터링 개념으로 저장되며 해시값으로 연결된다.
파티션 테이블(Partition Table)
메모리 초과로 생성된 파티션의 위치정보를 담으며, 다시 메모리로 올려 사용할 수 있다. 파티션 간 Pair도 관리하며 효율성에 영향 준다.
2.3.4.1 인-메모리 해쉬조인
인-메모리 해쉬조인은 조인 대상 중 빌드 입력(Build Input) 집합을 모두 메모리에 적재한 후, 해시 함수를 적용하여 해시 테이블(Hash Table) 을 생성하고, 검색 입력(Probe Input) 을 스캔하면서 해시 테이블을 참조해 조인을 수행하는 방식이다.
이 방식은 랜덤 액세스를 거의 발생시키지 않으므로, 디스크 정렬을 필요로 하는 Sort Merge 조인보다 훨씬 효율적이다. 뿐만 아니라, Nested Loops 조인처럼 작은 부분 범위 조인까지도 유연하게 처리할 수 있어 다양한 상황에 효과적이다.
빌드 입력 집합 검색 입력 집합
(Build Input) (Probe Input)
│ │
┌─────────┴─────────┐ ┌────────┴───────┐
▼ ▼ ▼ ▼
해시함수1 적용 통계정보 기반 해시함수2 적용 필터링용
(파티션 결정) 팬아웃 결정 (해시값 생성) 비트맵 벡터 생성
│ │ │ │
└──────┬────────────┘ └──────┬─────────┘
▼ ▼
┌─────────────────┐ ┌────────────────────┐
│ Partition Table │ │ Partition Table │
└─────────────────┘ └────────────────────┘
│ │
┌───────┴────────┐ ┌───────┴───────┐
▼ ▼ ▼ ▼
해시값으로 비트맵 벡터 검사 비트맵 벡터 통해 조건 불일치시 skip
슬롯 저장 유효성 체크 유효 파티션만 접근
│ │
▼ ▼
┌──────────────────────┐ ┌─────────────────────────┐
│ 해시 테이블 생성 │ <──────┐ │ 해시 테이블 검색 │
└──────────────────────┘ │ └─────────────────────────┘
│ │
▼ ▼
SQL SELECT 컬럼만 매칭된 결과 운반단위에 담음
저장 (fetch된 결과 반환)
│ │
└────────┐ ┌───────────┘
▼ ▼
┌─────────────────────────────┐
│ 운반단위(Return Block) │
└─────────────────────────────┘
▲
│ (반복 처리)
└───────────────────────┐
▼
검색입력 종료 시까지 반복
- 먼저 통계정보를 활용해 카디널리티가 낮은 쪽을 빌드 입력으로 선택한다.
- 그 후 해시함수1을 적용하여 파티션 분할(Fan-out) 수를 결정한다.
- 파티션된 결과는 메모리 슬롯(Slot)에 저장되며, 필요한 컬럼만 선택적으로 저장한다.
- 유효한 해시값을 기반으로 비트맵 벡터를 생성하여 검색 입력 필터링에 사용한다.
- 검색 입력 집합에 해시함수2를 적용하고, 비트맵 벡터로 유효성 검사를 수행한 후 해시 테이블에서 매칭을 시도한다.
- 매칭된 결과는 운반단위(Return Block)에 담겨 반환된다.
- 검색 입력이 끝날 때까지 이 과정을 반복 수행한다.
인-메모리 해쉬조인은 여러 측면에서 성능상 큰 장점을 제공한다. 먼저 디스크에 대한 접근을 최소화하고 랜덤 액세스를 유발하지 않기 때문에 정렬을 필요로 하지 않으며 매우 빠르게 조인이 수행될 수 있다.
둘째로 기존에는 Nested Loops 조인에서 주로 담당하던 일부 범위에 대한 조인 처리도 인-메모리 해쉬조인을 통해 효율적으로 수행할 수 있다.
셋째로 빌드 입력 테이블이 메모리에 적재 가능할 경우 유리하게 적용할 수 있어, 상황에 따라 유연한 선택이 가능하다.
넷째로 인-메모리 해쉬조인은 Group By 이후의 집합 처리와 같은 고급 SQL 처리에도 적합하여 활용도가 높다.
마지막으로 비트맵 벡터를 이용한 사전 필터링 기법을 통해 조인 대상을 최소화할 수 있어 전체적인 성능 최적화에도 기여한다.
하지만 인-메모리 해쉬조인을 사용할 때는 몇 가지 유의해야 할 점이 있다. 먼저 빌드 입력 집합의 크기가 너무 클 경우에는 메모리 한계를 초과하여 디스크에 임시 세그먼트를 생성해야 하므로 성능에 영향을 줄 수 있다.
둘째로 메모리는 시스템 내 여러 프로세스가 공유하는 자원이기 때문에, 모든 조인을 인-메모리 방식으로 처리하는 것은 사실상 어렵고 적절한 범위에서만 적용하는 것이 중요하다.
셋째로 무분별하게 해쉬조인을 적용하면 전체 시스템에 과도한 부하를 유발할 수 있으며, 특히 통계 정보가 부족하거나 히스토그램 정보가 부정확한 경우에는 오히려 비효율적인 실행 계획으로 이어질 수 있으므로 적절한 선택 기준에 따라 적용하는 것이 바람직하다.
2.3.4.2. 유예 해쉬조인 부분
유예 해쉬조인(Deferred Hash Join)은 빌드 입력의 해쉬 영역이 메모리를 초과할 때 발생하며, 이 경우 해쉬조인은 디스크를 활용하여 복잡한 과정을 거친다. 이는 빌드 입력이 클 경우 메모리에 전부 적재할 수 없어 디스크의 임시 세그먼트에 데이터를 저장하고, 이후 다시 메모리로 불러와 조인을 수행해야 하기 때문이다.
이 방식은 디스크 I/O가 포함되므로 속도가 느려질 수밖에 없으며, 정렬 기반의 Sort Merge 조인과 유사한 효과를 기대할 수 있는 구조로 발전한다. 즉, 정렬 없이도 조인을 위한 연결이 가능하도록 설계된 방식이다. 해쉬함수를 이용해 같은 해쉬 값을 갖는 로우들을 찾아 연결하고, 해쉬 테이블을 통해 조인을 수행한다.
아래는 유예 해쉬조인의 처리 흐름을 그림으로 ASCII 기반으로 표현한 것이다.
빌드 입력 집합 검색 입력 집합
(Build Input) (Probe Input)
│ │
┌─────────┴────────┐ ┌────────┴─────────┐
▼ ▼ ▼ ▼
통계정보 활용 해시함수1 적용 해시함수2 적용 비트맵 벡터 생성
(카디널리티 기반) (파티션 생성) (해시값 계산) (필터링용)
│ │
└──────┬─────────────┐ ┌────────┴───────┐
▼ ▼
┌─────────────────┐ ┌────────────────────┐
│ Partition Table │ │ Partition Table │
└─────────────────┘ └────────────────────┘
│ │
┌─────────┴────────┐ ┌──────────┴────────┐
▼ ▼ ▼ ▼
해시값 기반 슬롯 저장 검색 대상 비트맵 검사 조건 불일치시 skip
│ │
▼ ▼
┌────────────────────┐ ┌─────────────────────────┐
│ 해시 테이블 생성 │ <───┐│ 해시 테이블 검색 │
└────────────────────┘ │└─────────────────────────┘
│ │
▼ ▼
SELECT 컬럼 저장 조인된 결과 운반단위로 반환
│ │
┌────────┴────────┐ │
▼ ▼ ▼
┌──────────────────────────────┐
│ 운반단위(Return Block) │
└──────────────────────────────┘
▲
│ (반복 수행)
└────────────────────┐
▼
검색입력 전체 범위 종료 시까지 반복해쉬조인의 유예 처리 방식은 다음과 같은 특징과 장점을 가진다.
유예 해쉬조인은 해쉬값을 기준으로 조인을 수행하기 때문에 별도의 정렬 과정 없이도 연결이 가능하다. 이러한 방식은 정렬 비용을 회피하면서도 Sort Merge 조인이 가지는 장점을 일부 흡수할 수 있다는 점에서 효율적이다. 또한 검색 입력에 대해 비트맵 벡터를 활용한 사전 필터링이 가능해, 불필요한 디스크 접근을 줄이고 조인 처리의 효율성을 높일 수 있다. 더불어 메모리에 적재할 수 없는 대용량의 빌드 입력이 존재하더라도, 이를 디스크에 유예 저장해두었다가 필요한 시점에 다시 불러와 처리할 수 있기 때문에 메모리 제약 없이 유연하게 대용량 처리를 수행할 수 있다는 장점이 있다.
하지만 유예 해쉬조인을 사용할 때에는 몇 가지 주의할 점이 있다. 메모리 용량을 초과하는 데이터를 처리해야 하므로 디스크 임시 세그먼트를 활용하게 되고, 그에 따라 디스크 I/O가 증가하면서 처리 속도가 저하될 수 있다. 또한 일부 집합만을 처리할 의도로 설계한 조인이 통계정보 부족이나 실행계획 상의 오류로 인해 전체 범위 조인으로 확장될 가능성이 있으며, 이 경우 오히려 성능이 떨어질 수 있다. 이와 함께 메모리뿐 아니라 디스크 자원 사용량도 증가하게 되어, 시스템 전체의 리소스에 부담을 줄 수 있으며, 특히 복수의 조인이 동시에 수행될 경우 성능 저하가 두드러질 수 있다. 마지막으로 해쉬 영역의 크기를 적절히 설정하지 않으면 문제를 일으킬 수 있다. 영역이 너무 작으면 유예 발생률이 높아져 불필요한 디스크 작업이 많아지고, 반대로 너무 크면 메모리 자원을 과도하게 점유하여 다른 작업의 성능을 해칠 수 있다. 따라서 시스템 환경과 작업 특성을 고려해 해쉬 영역 크기를 적절히 조절하는 것이 중요하다.
2.3.5. 세미(Semi)조인
세미(Semi) 조인은 조인이지만 결과적으로는 매우 유사한 형태의 데이터 연결 방식을 의미한다. 특히 메인 쿼리와 서브쿼리 간 연결을 처리하는 상황에서 자주 등장하며, 서브쿼리를 사용하는 경우에 조인과 동일하게 집합 간 연결이 필요하다는 점에서는 일반 조인과 유사하지만, 처리 방식이나 목적에서는 차이를 가진다.
많은 사용자들은 "조인과 서브쿼리는 어떤 차이가 있고, 어떤 경우에 각각을 선택해야 하는가?"에 대해 명확한 기준 없이 사용하고 있으며, 조인과 다르지 않다는 이유로 오히려 불필요하다고 여기거나 세미조인의 필요성을 인식하지 못하는 경우도 많다. 이는 서브쿼리의 개념이나 동작 방식에 대한 명확한 이해 없이 적용하고 있다는 방증이다.
서브쿼리는 자체적으로 매우 고유한 특징을 가지며, 그로 인해 일반 조인과는 다른 장점과 단점을 동시에 가진다. 따라서 세미조인이라는 개념은 단순히 조인의 하위 개념이 아니라, 서브쿼리 특성을 기반으로 정리된 독립적 개념으로 이해될 필요가 있다.
이 개념은 바둑이나 체스에서 정석을 먼저 파악하고 그에 따라 전략을 세우듯이, 실제 SQL 쿼리 작성에 앞서 어떤 방식의 연결이 유리한지를 판단하는 기준을 마련하는 데 도움을 준다. 따라서 세미조인을 이해하려면 서브쿼리의 구조와 역할을 먼저 명확히 파악하고, 특히 많이 사용되는 IN과 EXISTS의 동작 방식과 그 차이를 조인과 비교하여 분석하는 것이 중요하다. 이후에는 서브쿼리의 다양한 활용 유형을 예시와 함께 소개하고, 실무에서 사용할 때 주의해야 할 사항들까지 체계적으로 정리하는 것이 바람직하다.
2.3.5.1. 세미 조인의 개념 및 특징
세미 조인은 본래 분산 질의를 효율적으로 수행하기 위한 개념에서 도입되었다. 두 테이블 간 조인을 할 때 한 테이블을 다른 사이트로 전송하기 전, 조인에 필요한 속성만을 먼저 추출하여 전송하고, 조인이 성공한 경우에만 결과값을 다시 전송함으로써 네트워크를 통해 오가는 데이터 양을 줄이는 데 목적이 있다.
‘세미(Semi)’는 ‘반(半)’ 또는 ‘약간’이라는 의미를 갖는다. 세미파이널(Semifinal)이라는 단어처럼, 결승전에 거의 가까운 상태를 뜻하는 것처럼 세미 조인도 정식 조인과 매우 유사하지만, 완전히 동일하지 않은, 거의 비슷한 형태의 연산을 수행한다는 점에서 이 명칭이 붙여졌다.
세미 조인은 조인과 매우 유사한 처리 방식을 가지지만, 결과적으로는 조인과 다르게 집합 전체를 결합하지 않는다. 일반적인 조인은 양쪽 집합의 결과를 합친 테이블을 반환하지만, 세미 조인은 메인쿼리의 조건을 만족하는지만을 판단하고, 해당 결과만을 반환한다.
이 방식은 주로 서브쿼리에서 메인쿼리와 연결될 때 사용되며, IN이나 EXISTS 절과 함께 자주 등장한다. 이때 서브쿼리는 하위(Sub) 쿼리로서, 메인(Main) 쿼리에 종속된 형태를 띠며, 그 자체로 모든 속성을 공유하거나 사용할 수 없다.
세미 조인은 Nested Loops, Sort Merge, Hash 조인 등 일반적인 조인 방식으로도 구현될 수 있으나, 이 경우에도 결과와 의미가 동일하지 않기 때문에 단순히 구현 방식이 같다고 해서 동일한 조인이라 보기 어렵다.
조인과 세미 조인의 본질적인 차이는 집합 간의 종속 관계에 있다. 조인은 집합 간에 동등한 위치에서 서로 데이터를 연결하고 교환할 수 있는 관계라면, 세미 조인은 메인쿼리가 중심이 되고 서브쿼리는 종속된 관계를 가진다. 이러한 점에서 조인 결과의 교환성이나 연산 방식이 다르게 나타난다.
SQL 예시를 보면, 일반 조인은 두 테이블 간 모든 컬럼을 가져와 결합하지만, 세미 조인은 EXISTS 또는 IN 절을 통해 조건에 부합하는지 여부만 판단한다. 이로 인해 세미 조인의 결과는 항상 메인쿼리의 결과 집합에 속한 데이터만을 포함하게 되며, 서브쿼리의 데이터를 출력하거나 조인 결과로 함께 포함하지 않는다.
또한 세미 조인은 메인쿼리의 컬럼만을 결과로 출력할 수 있다는 제한이 있다. 일반 조인의 경우 조인된 테이블의 모든 컬럼을 자유롭게 활용할 수 있으나, 세미 조인의 경우 서브쿼리의 컬럼은 메인쿼리에서 사용할 수 없다. 이는 서브쿼리 간에도 컬럼 공유가 불가능하다는 제약으로 이어지며, 향후 실행 계획이나 쿼리 재작성 시 고려되어야 할 중요한 사항이다.
2.3.5.2. 세미조인의 실행계획
세미조인의 실행계획은 일반적인 조인의 범주에서 벗어나지 않으며, 대부분의 경우 가장 보편적인 Nested Loops 조인과 매우 유사하게 처리된다.
가) Nested Loops형 세미조인
ested Loops형 세미조인은 어떤 집합이 먼저 수행되어 상수값을 만든 뒤, 이 상수값을 기준으로 상대 집합을 필터링하는 방식으로 작동한다.
서브쿼리가 먼저 수행되면 SELECT 리스트의 연결고리 값을 상수값으로 만들고, 이를 메인쿼리의 연결고리에 대입하는 방식이 사용된다. 반대로 메인쿼리가 먼저 수행된 후 상수값이 서브쿼리의 조건절에 제공되는 방식도 존재한다. 이와 같이 서브쿼리는 '제공자' 또는 '확인자'의 역할을 수행할 수 있다.
이러한 방식은 메인쿼리의 집합을 보호하는 것이 철칙이며, 이를 위해 SORT(UNIQUE)와 같은 추가 단계가 삽입되어 제공되는 상수값을 유일한 값으로 만든다. 그 결과, 일반 조인과 동일한 결과 집합을 유지할 수 있다.
예를 들어, 다음과 같은 SQL을 살펴보면,
SELECT COL1, COL2, ...
FROM TAB1 x
WHERE KEY1 IN (SELECT KEY2
FROM TAB2 y
WHERE y.COL1 ...
AND y.COL2 ...);여기서 TAB1이 메인쿼리이고 TAB2가 서브쿼리로 작동한다. 실행계획은 TAB2를 먼저 수행하여 결과를 도출하고, 이를 기반으로 메인쿼리와 연결된다. 이 과정에서 KEY2는 유일한 값으로 변환되어 메인쿼리의 집합이 보존된다.
만약 옵티마이저의 오류로 인해 SORT(UNIQUE)가 누락되거나 적절히 제공자 역할이 수행되지 않으면, 메인쿼리 집합의 보호가 깨지고, 전반적인 실행 효율이 심각하게 저하될 수 있다.
이런 점을 명확히 이해하기 위해 다음과 같은 실 사례 SQL을 보자.
UPDATE 청구 x
SET 입금액 = NVL(입금액, 0) + :in_amt
WHERE 청구년월 = '201503'
AND 고객번호 IN (SELECT 고객번호
FROM 고객 y
WHERE 납입자 = :in_cust);이 SQL은 문제 없어 보이지만, 서브쿼리가 나중에 수행되도록 실행계획이 수립되었기 때문에, 메인쿼리에서 조건에 맞는 데이터가 약 500만 건이라면 서브쿼리가 500만 번 수행된다. 이는 큰 성능 저하로 이어진다.
반면 다음과 같이 서브쿼리에서 메인쿼리의 연결고리를 제거하고 미리 조인 조건을 지정하면,
UPDATE 청구 x
SET 입금액 = NVL(입금액, 0) + :in_amt
WHERE 청구년월 = '201503'
AND 고객번호 IN (SELECT 고객번호
FROM 고객 y
WHERE 납입자 = :in_cust);실행 시간은 0.1초 이내로 줄어든다. 이는 서브쿼리가 먼저 수행되어 메인쿼리에 값을 제공할 수 있도록 실행계획이 수립되었기 때문이다.
따라서 세미조인을 사용할 때는 단순한 기대감만으로 작성하지 말고, 반드시 실행계획과 연산 순서를 검토해 성능 저하를 방지해야 한다.
나) Sort Merge형 세미조인
세미조인은 항상 메인쿼리의 집합을 보존해야 한다는 원칙을 따르며, 일반 조인과 거의 동일한 개념이다. Sort Merge형 세미조인은 이러한 원칙을 따르되 Nested Loops형과는 다른, 대량 데이터를 다루기 위한 효율적인 방식이다.
이 방식은 메인쿼리 집합 보존을 위해 특별한 정렬 과정이 추가로 삽입된다. 즉, 메인쿼리와 서브쿼리 모두 정렬(SORT)을 통해 ‘1’ 집합을 만든 후 병합(MERGE)하는 방식으로 연결된다.
SQL 예시는 다음과 같다.
SELECT *
FROM 사원
WHERE 부서코드 IN (
SELECT 부서코드
FROM 근태
WHERE 입자 BETWEEN '20150601' AND '20150612'
AND 근태유형 = '무단결근'
)
AND 직책 >= '과장';위 SQL에서 ‘사원’과 ‘근태’는 M:M 관계이며, 조인의 특성상 메인쿼리 집합은 항상 보존된다. 이처럼 M:M 관계라 하더라도 결과적으로는 M:1 조인처럼 동일하게 작동한다.
서브쿼리는 항상 ‘1’ 집합이 되어야 하며, 이를 위해 정렬(SORT UNIQUE)을 통해 유일한 집합을 생성하고, 이후 사원 테이블과 병합 조인한다.
실행계획의 주요 항목은 다음과 같다.
MERGE JOIN
SORT (JOIN)
TABLE ACCESS (FULL) OF '사원'
SORT (JOIN)
VIEW
SORT (UNIQUE)
TABLE ACCESS (BY ROWID) OF '근태'
INDEX (RANGE SCAN) OF '유형_입자_IDX'이러한 방식은 서브쿼리 내에 GROUP BY 등이 있을 때도 사용되며, 인덱스 없이도 유효하게 작동한다. 단, GROUP BY에 의해 조인 수가 줄어들면 더욱 효율적이다.
연결 조건이 NOT일 경우에도 유사한 형태의 조인이 발생하며, 이는 “부정형 세미조인(Anti Semi Join)”에서 다루게 된다.
다) 필터(Filter)형 세미조인
‘필터’는 문자 그대로 조건을 ‘걸러내는’ 방식으로 동작한다. 이 방식은 서브쿼리가 메인쿼리의 결과와 비교하여 조건을 만족하는지 ‘확인’하는 역할을 한다.
서브쿼리는 먼저 실행되며, 결과를 버퍼에 저장한 후 메인쿼리의 각 로우가 조건에 맞는지를 하나씩 확인한다. 이때 저장된 값과 비교하는 과정을 통해 액세스를 최소화한다.
일반적으로 EXISTS 또는 IN 조건을 사용하는 쿼리에서 나타나며, 예시는 다음과 같다.
SELECT ...
FROM ORDER x
WHERE ORDDATE LIKE '201506%'
AND EXISTS (
SELECT 'X'
FROM DEPT y
WHERE y.DEPTNO = x.SALDEPT
AND y.TYPE1 = '1'
);이 방식은 Nested Loops와 유사하지만, 연결조건이 메인쿼리 WHERE절에 직접 보이지 않는다는 점에서 다르다.
실행계획의 주요 항목은 다음과 같다.
FILTER
TABLE ACCESS (BY ROWID) OF 'ORDER'
INDEX (RANGE SCAN) OF 'ORDDATE_INDEX'
TABLE ACCESS (BY ROWID) OF 'DEPT'
INDEX (UNIQUE SCAN) OF 'DEPT_PK'ORDDATE_INDEX가 3,201개의 범위로 걸려 있다면, 메인쿼리의 3,200개의 로우에 대해 DEPT 테이블을 확인하는 작업이 반복된다. 하지만 버퍼를 활용하면 동일한 비교값은 재사용하여 액세스를 줄일 수 있다.
이 방식은 EXISTS 서브쿼리에 자주 사용되며, 불필요한 액세스를 줄이는 데 유리하다. 또한 인라인뷰를 사용하는 조인과도 유사한 특성을 가진다.
라) 해쉬(Hash)형 세미조인
해쉬형 세미조인은 필터 형식의 세미조인이 랜덤 액세스를 유발하여 대량의 연결 시 성능 저하가 우려될 때 이를 해결하기 위한 방식으로 사용된다. Sort Merge 방식도 고려할 수 있으나, 일반적으로는 해쉬 조인이 더 효율적이기 때문에 주로 활용된다.
해쉬 조인을 유도하려면 HASH_SJ 힌트를 SQL 서브쿼리에 명시한다. 이는 서브쿼리가 해쉬 방식으로 연결될 수 있도록 유도하며, 대량의 연결이 있고 메모리에 충분한 해쉬 영역이 확보된 상황에서 매우 유리한 수행속도를 보장할 수 있다.
하지만 몇 가지 제약이 존재한다. 서브쿼리에는 단일 테이블만 존재해야 하며, 서브쿼리 내부에 또 다른 서브쿼리가 있다면 적용이 어렵다. 또한, 조인의 연결 조건은 반드시 등호(=) 연산자를 사용해야 하고, GROUP BY, CONNECT BY, ROWNUM 등의 절은 사용할 수 없다.
버전과 환경에 따라 힌트는 HASH_SJ, HASH_AJ 등으로 다양하게 적용될 수 있으며, 실행 계획은 일반 해쉬 조인(USE_HASH)과 동일한 형태로 나타난다.
해쉬형 세미조인 SQL의 예시는 아래와 같다.
SELECT ...
FROM ORDER x
WHERE ORDDATE LIKE '201506%'
AND EXISTS (
SELECT /*+ hash_sj(x, y) */ 'x'
FROM DEPT y
WHERE y.DEPTNO = x.SALDEPT
AND y.TYPE1 = '1'
);마) 부정형(Anti) 세미조인
부정형 세미조인은 조인의 연결 조건에 NOT이 포함된 경우 사용되며, 즉 ‘존재하지 않는 것’을 찾는 논리에 기반한다. 예컨대 특정 값이 존재하지 않음을 확인해야 할 때, 일반적인 세미조인 방식으로는 성능 문제가 발생하거나 논리적으로 연결 자체가 어렵다.
예를 들어 111이 아닌 값을 찾는 것은 매우 어렵고, 어떤 값이 존재하지 않는지를 판별하려면 모든 경우를 다 비교해야 하므로 처리 비용이 커진다. 이로 인해 부정형 세미조인은 필터 형태의 서브쿼리로 나타나며, 항상 확인자(Checker)의 역할만 수행하게 된다.
주로 사용되는 연산자는 NOT IN, NOT EXISTS이며, 이들은 IN, EXISTS와 반대되는 역할을 수행한다. 부정형 조인에서도 Nested Loops, Sort Merge, Hash Join 형식이 사용될 수 있지만, 실행 계획에서는 대부분 FILTER 또는 MERGE JOIN (ANTI), HASH JOIN (ANTI)로 나타난다.
NOT IN을 사용할 경우에는 반드시 NULL 값을 제외해야 하기 때문에 메인 쿼리와 서브쿼리 모두 IS NOT NULL 조건을 명시해야 한다. 그렇지 않으면 예기치 않은 결과를 초래할 수 있다. 이처럼 부정형 세미조인은 NULL 처리에 매우 민감하다.
그리고 실행 계획에서 MERGE_AJ 또는 HASH_AJ 힌트를 사용하면 각각 Sort Merge 기반, Hash 기반의 부정형 조인을 유도할 수 있으며, 이는 필터 방식보다 더 효율적인 방식으로 조인이 수행되도록 도와준다.
MERGE_AJ 힌트 사용하는 부정형 세미조인 SQL의 예시는 아래와 같다.
SELECT COUNT(*)
FROM TAB1
WHERE COL1 LIKE 'ABC%'
AND COL2 IS NOT NULL
AND COL2 NOT IN (
SELECT /*+ MERGE_AJ */ FLD2
FROM TAB2
WHERE FLD3 BETWEEN '20150101' AND '20150131'
AND FLD2 IS NOT NULL
);HASH_AJ 힌트 사용한 부정형 세미조인 SQL의 예시는 아래와 같다.
SELECT COUNT(*)
FROM TAB1
WHERE COL1 LIKE 'ABC%'
AND COL2 IS NOT NULL
AND COL2 NOT IN (
SELECT /*+ HASH_AJ */ FLD2
FROM TAB2
WHERE FLD3 BETWEEN '20151201' AND '20151231'
AND FLD2 IS NOT NULL
);2.3.6 스타(Star) 조인
스타 조인은 기존의 조인 방식 자체를 바꾸기보다는, 여러 차원 테이블과 하나의 팩트 테이블이 결합되는 상황에서 실행 계획을 최적화하는 전략이라고 볼 수 있다. ‘스타’라는 이름은, 중심이 되는 팩트 테이블을 기준으로 여러 코드(차원) 테이블이 마치 별 모양처럼 방사형으로 연결된 구조에서 유래하였다. 주로 대용량 트랜잭션 데이터가 쌓이는 테이블과 여러 차원 테이블 간의 조인에서 사용된다.
본문에서는 다음과 같은 스타 조인 예시 SQL이 제시된다.
SELECT d.dept_name, c.country_name, p.product_name, t.type_name,
SUM(s.sales_amount) sales_amount
FROM SALES s, COUNTRY c, PRODUCTS p, DEPT d, TYPES t
WHERE c.country_cd = s.country_cd
AND p.product_cd = s.product_cd
AND d.dept_no = s.sales_dept
AND t.type_cd = s.sales_type
AND s.sales_date BETWEEN '20151001' AND '20151231'
AND p.product_name IN ('PA001', 'DQ101')
AND t.type_name = 'DOMESTIC'
AND d.dept_name = 'SEOUL'
GROUP BY d.dept_name, c.country_name, p.product_name, t.type_name;이 쿼리는 SALES 테이블을 중심으로 하여 4개의 차원 테이블(COUNTRY, PRODUCTS, DEPT, TYPES)과 조인한다. SALES 테이블은 각 차원 테이블과 외래키로 연결되어 있으며, 각 차원 테이블에 존재하는 조건들을 먼저 걸러낸 후, 해당 조건에 맞는 판매 데이터를 집계한다. 이처럼 스타 조인은 팩트 테이블의 접근을 늦추고, 가능한 한 먼저 필터링된 차원 테이블 집합을 만들어서 처리 범위를 줄이는 데 목적이 있다.
본문에서는 이런 문제를 해결하기 위한 다른 형태의 SQL도 제시된다. 아래 쿼리는 인라인 뷰와 힌트를 통해 스타 조인을 유도한 방식이다.
SELECT /*+ USE_HASH(d, f) */
country_name, product_name, sales_type,
SUM(sales_amount) sales_amount
FROM (
SELECT ROWNUM, product_cd, product_name, country_cd, country_name
FROM PRODUCTS, COUNTRY
WHERE product_cd IN ('PA001','DQ101')
) d, SALES f
WHERE f.product_cd = d.product_cd
AND f.country_cd = d.country_cd
AND f.sales_date BETWEEN '20151001' AND '20151231'
GROUP BY d.country_name, d.product_name, f.sales_type;여기서는 PRODUCTS와 COUNTRY를 먼저 인라인 뷰로 조인하고, ROWNUM을 활용하여 선행 조인을 유도하며, USE_HASH 힌트를 사용해 해시 조인 방식을 명시적으로 유도하고 있다.
SELECT STATEMENT Optimizer=ALL_ROWS
SORT (AGGREGATE)
HASH JOIN
COUNT
MERGE JOIN (CARTESIAN)
TABLE ACCESS (FULL) OF 'PRODUCTS'
BUFFER (SORT)
TABLE ACCESS (FULL) OF 'COUNTRY'
TABLE ACCESS (BY INDEX ROWID) OF 'SALES'
INDEX (RANGE SCAN) OF 'SALES_X1' (NON-UNIQUE)이 계획에서 눈여겨볼 부분은 ROWNUM을 이용한 집합 제어다. 옵티마이저는 ROWNUM을 통해 결합 순서를 제어하고 불필요한 병합(merge) 을 방지하는 효과를 얻는다. 또한 SALES_X1이라는 인덱스의 사용 여부도 중요한데, 이 인덱스의 구성에 따라 실행 계획이 완전히 달라질 수 있다.
예를 들어 SALES_X1이 DEPT_NO + PRODUCT_CD 순서로 구성되어 있으면 문제가 없지만, 중간에 SALES_TYPE이 끼어 있으면 옵티마이저는 효율을 확보하지 못하고 비효율적인 Nested Loops 조인을 유도할 수 있다.
2.3.7 스타변형(Star Transformation) 조인
기본적으로 스타 조인이 갖는 ‘차원 × 차원 카티션 곱’과 과도한 MERGE JOIN 부담을 줄이기 위해 옵티마이저가 수행하는 내부 변환이다. 핵심 아이디어는 차원 테이블을 한데 묶어 상수 집합을 만든 뒤 팩트 테이블을 조인하는 대신, 각 차원 테이블을 서브쿼리로 분리하여 먼저 읽고, 그 서브쿼리 결과를 비트맵 인덱스로 “ROWID 비트맵”으로 바꾼 뒤 이 비트맵들을 AND 연산으로 합성해 팩트 테이블의 액세스 범위를 한 번에 좁히는 것이다. 이렇게 하면 차원 수가 늘어나더라도 곱셈 폭발이 일어나지 않고, 팩트 테이블 I/O가 극단적으로 감소한다.
본문의 첫 번째 예시는 조건이 상수 두 개뿐인 가장 단순한 형태다.
SELECT *
FROM sales
WHERE product_cd LIKE 'PA%'
AND sales_dept BETWEEN '2110' AND '2310';product_cd_BX와 sales_dept_BX 두 비트맵 인덱스를 각각 RANGE SCAN 한 뒤 BITMAP AND로 묶어 ROWID 집합을 만들고, 그 ROWID로 SALES를 읽는다. 여기까지는 “상수를 직접 준 것”과 사실상 같다.
두 번째 단계에서는 차원 테이블을 서브쿼리로 바꾼다. 옵티마이저는 이 서브쿼리를 먼저 스캔해 나온 값으로 팩트 테이블 비트맵 인덱스를 다시 키 이터레이션 방식으로 접근한다.
SELECT *
FROM sales
WHERE product_cd IN (SELECT product_cd
FROM products
WHERE product_name LIKE 'P%')
AND sales_dept IN (SELECT dept_no
FROM dept
WHERE dept_name LIKE 'S%');실행 계획은 PRODUCTS, DEPT를 먼저 Full Scan 하여 값 목록을 얻고, 그 목록으로 각각 SALES_PRODUCT_BX, SALES_DEPT_BX를 BITMAP KEY ITERATION 방식으로 읽어 두 비트맵을 AND한 뒤 ROWID로 변환한다. 그 후 좁아진 범위만큼의 SALES를 액세스하고 필요하다면 다른 차원과 해시 조인이나 Sort Merge Join을 수행한다. 차원 수가 늘어도 곱셈이 아닌 비트맵 AND만 늘어나므로 부하는 선형에 가깝다.
본문에 실린 전체 예시는 다섯 개 테이블을 조인·집계하는 원래 스타 SQL을 옵티마이저가 자동으로 변형한 모습이다.
SELECT d.dept_name, c.cust_city, p.product_name,
SUM(s.amount) AS sales_amount
FROM sales s,
products p,
customers c,
dept d
WHERE s.product_cd = p.product_cd
AND s.cust_id = c.cust_id
AND s.sales_dept = d.dept_no
AND c.cust_grade BETWEEN '10' AND '15'
AND d.location = 'SEOUL'
AND p.product_name IN ('PA001','DR210')
GROUP BY d.dept_name, c.cust_city, p.product_name;옵티마이저는 위 문장을 내부적으로 다음과 같은 형태로 변형해 실행한다. 각 차원 서브쿼리가 먼저 결과를 얻고, 그 결과 값을 이용해 SALES 비트맵 인덱스를 키 이터레이션 방식으로 읽어 비트맵 AND를 수행한 뒤 ROWID 집합을 얻는다.
SELECT d.dept_name, c.cust_city, p.product_name,
SUM(s.amount) AS sales_amount
FROM sales s
WHERE s.product_cd IN (SELECT product_cd
FROM products
WHERE product_name IN ('PA001','DR210'))
AND s.cust_id IN (SELECT cust_id
FROM customers
WHERE cust_grade BETWEEN '10' AND '15')
AND s.sales_dept IN (SELECT dept_cd
FROM dept
WHERE location = 'SEOUL')
GROUP BY d.dept_name, c.cust_city, p.product_name;실행 계획에는 BITMAP KEY ITERATION → BITMAP MERGE → BITMAP AND → ROWID conversion → 팩트 테이블 액세스가 차례로 나타난다. 그 뒤에는 해시 조인으로 DEPT·PRODUCTS·CUSTOMERS와 연결·집계가 이루어진다.
스타변형 조인이 작동하려면 몇 가지 전제 조건이 있다. 팩트와 최소 두 개 이상의 차원 테이블이 있어야 하고, 차원 칼럼에는 비트맵 인덱스가 존재해야 하며, 팩트 테이블 통계 정보가 분석되어 있어야 한다. 데이터베이스 파라미터 star_transformation_enabled 가 TRUE 이거나 힌트 STAR_TRANSFORMATION을 명시해야 옵티마이저가 변환을 시도한다. 바인드 변수를 WHERE 절에 사용하거나 FULL·ROWID·STAR 등의 상충 힌트를 함께 사용하면 변환이 일어나지 않는다. 또한 팩트 테이블이 너무 작아 자체 조건으로도 충분히 범위가 줄어들 경우나, 차원 테이블이 원격(REMOTE)에 있으면 스타변형을 포기하기도 한다.
끝으로, 본문은 대형 차원 테이블 비중이 높을 때에는 변환 효과가 작아질 수 있으나, 대부분의 데이터 웨어하우스 환경에서는 팩트 테이블보다 차원 테이블이 상대적으로 작고 필터 조건이 풍부하므로 스타변형 조인이 스타 조인보다 훨씬 자주, 그리고 효율적으로 선택된다고 강조한다.
START: 25. 07. 20
END: 25. 08. 02
'프로그래밍(Web) > 공부일기' 카테고리의 다른 글
| [바미] 새로 쓴, 대용량 데이터 베이스 솔루션 Vol.1 - 2부 - 1장 부분범위처리 (0) | 2025.06.04 |
|---|---|
| [바미] 새로 쓴, 대용량 데이터 베이스 솔루션 Vol.1 - 4장 인덱스 수립 전략 (0) | 2025.04.21 |
| [바미] 새로 쓴, 대용량 데이터 베이스 솔루션 Vol.1 - 3장 SQL의 실행계획 - 2 (0) | 2025.04.04 |
| [바미] 새로 쓴, 대용량 데이터 베이스 솔루션 Vol.1 - 3장 SQL의 실행계획 - 1 (0) | 2025.04.03 |
| [바미] 옵티마이저(Optimizer)란 무엇인가? (0) | 2025.02.02 |
