새롭게 안 사실들
실행 계획의 유형들..
실행 계획에 대한 내용이 이렇게 다양하게 많이 있을 줄 몰랐다.
엑세스, 스캔에 대한 내용들
처음 들어 보는 조인
세미 조인(Semi Join), 카티젼 조인Cartesian Join), 내포 조인(Nested loop Join), 정렬 병합 조인(Sort Merge Join), 해쉬 조인(Hash Join), 등등
처음 접하는 쿼리문법
MERGE, CREATE OR, 등등
힌트에 대한 내용들
공부한 내용들
3.2 실행계획의 유형
실행계획은 데이터베이스 테이블에서 데이터를 추출하여 원하는 결과를 생성하기 위한 처리 경로를 의미한다. 가장 기본적인 실행계획은 실제 데이터가 저장된 물리적인 데이터를 액세스하는 것으로, 이를 스캔(Scan)이라고 부른다. 또한, 여러 테이블에서 스캔을 수행한 후 데이터를 서로 연결시키는 처리를 조인(Join)이라고 하며, 이러한 과정에서는 다양한 연산이 수행된다. 각각의 연산은 다시 여러 가지 방법으로 처리될 수 있고, 이것들이 실행계획을 구성하는 개별 단위가 된다.
실행계획을 구성하는 각 단위를 이해하는 것은 마치 여러 가지 부속품을 조합하여 최종적인 제품을 만들어가는 과정과 유사하다. 즉, 여러 가지 기본적인 실행계획의 단위가 모여 전체적인 실행계획이 완성된다. 따라서 실행계획을 정확하게 이해하려면 각각의 유형에 대한 개념과 내부 처리 과정을 분명히 구분하여 학습하는 것이 필요하다.
책에서는 실행계획을 크게 다음의 네 가지 유형으로 나누어 설명하고 있다.
- 스캔을 위한 실행계획
- 데이터 연결을 위한 실행계획
- 각종 연산을 위한 실행계획
- 기타 특수 목적을 처리하는 실행계획
이러한 분류를 바탕으로 각 실행계획 유형별 특징과 처리 방법을 이해하는 것이 중요하며, 이후 보다 고급 개념과 실행계획의 전체적인 구성을 파악하는 데 필수적인 과정이다.
3.2.1 스캔(Scan)의 기본 유형 부분
물리적으로 저장된 테이블에서 로우를 찾아 액세스하는 방법을 스캔(Scan)이라고 하며, 이는 거의 모든 실행계획의 시작점이자 가장 하위 단계이다. 스캔은 전체 실행계획에서 수행 속도에 결정적인 영향을 미치는 부분이기도 하다. 이때, 데이터를 액세스하는 방식은 테이블의 저장된 구조나 처리 방법에 따라 영향을 받으며, 같은 결과를 얻기 위한 다양한 처리 방법이 존재한다.
스캔 방법 중에서 어떤 것이 가장 유리한지는 절대적으로 정해진 것이 아니라, 데이터의 양이나 클러스터링된 정도, 처리하고자 하는 작업의 목적 등 여러 조건에 따라 최적의 방법이 달라진다. 이는 마치 트럼프 카드 게임을 할 때 카드가 우연히 분포되듯이, 최적의 카드 사용법이 항상 고정적이지 않고 상황에 따라 달라지는 것과 유사하다.
스캔의 기본 유형에는 다음과 같은 것들이 있다.
- 전체 테이블 스캔(Full Table Scans)
- 로우ID를 이용한 로우 스캔(Rowid Scans)
- 인덱스 스캔(Index Scans)
- 클러스터 액세스(Cluster Access)
- 해쉬 액세스(Hash Access)
- 표본 테이블 스캔(Sample Table Scans)
각각의 유형은 세부적인 특징과 데이터 처리 목적에 따라 선택되며, 이후 실행계획의 성능과 효율성에 직접적인 영향을 미친다.
3.2.1.1. 전체 테이블 스캔
전체 테이블 스캔(Full Table Scan)은 테이블에 존재하는 모든 로우를 순차적으로 읽는 방법이다. 이 방법은 WHERE 절에서 정의된 조건을 평가하고, 조건을 만족하는 로우를 선택하는 과정이다. 테이블의 최고 수위선(High Water Mark)까지 모든 블록을 스캔하므로, 데이터가 삭제되어 블록이 비었더라도 블록이 스캔 대상이 된다. 이렇게 읽힌 블록들은 메모리에 올라가고, I/O 작업이 다중 블록 단위로 일어나기 때문에 효율적인 읽기가 가능하다.
전체 테이블 스캔은 인덱스 스캔보다 더 많은 데이터를 처리할 때 상대적으로 유리할 수 있다. 작은 테이블은 인덱스를 통해 빠르게 접근할 수 있지만, 매우 큰 테이블에서는 인덱스 접근이 오히려 비용이 많이 들 수 있으며, 랜덤 접근 방식의 오버헤드가 발생할 수 있다. 전체 테이블 스캔은 디스크 캐시에 블록이 이미 위치해 있을 가능성이 높아 메모리 접근 효율도 높다.
전체 테이블 스캔이 선택되는 주요 이유는 다음과 같다.
- 적용 가능한 인덱스 부재 - 쿼리 조건에 맞는 인덱스가 없거나 적절한 인덱스가 없으면 전체 테이블 스캔이 선택된다.
- 넓은 범위 데이터 액세스 - 쿼리 범위가 넓으면 인덱스 스캔보다 전체 테이블 스캔이 비용적으로 유리할 수 있다.
- 소량의 테이블 액세스 - 테이블 크기가 작으면 전체 테이블 스캔이 효율적이다.
- 병렬 처리 액세스 - 병렬 처리를 활용하면 전체 테이블 스캔을 효과적으로 수행할 수 있다.
- 'FULL' 힌트 사용 - 사용자가 명시적으로 'FULL' 힌트를 주어 전체 테이블 스캔을 강제할 수 있다.
전체 테이블 스캔의 수행 결과는 액세스된 총 로우 수를 통해 확인할 수 있다. 과거 버전에서는 중간 결과를 나타냈지만, 최근 버전에서는 최종 결과만 표시하여 혼란을 줄였다. 실행계획 분석 시 전체 테이블 스캔을 명확히 이해하기 위해 TRACE를 사용하여 내부적으로 처리된 로우 수를 확인할 수 있다.
최종적으로 전체 테이블 스캔은 테이블 크기와 쿼리 특성, 데이터 분포 등을 고려하여 적절한 상황에서 사용될 때 높은 효율성을 제공한다.
3.2.1.2 ROWID 스캔
ROWID는 특정 로우가 저장된 데이터파일, 데이터 블록, 그리고 블록 내 위치를 나타내는 정보이다. 따라서 ROWID 스캔은 하나의 로우를 테이블에서 추출하는 가장 빠른 방법이다. ROWID 스캔을 수행하기 위해서는 반드시 로우의 ROWID 값을 미리 확보하고 있어야 한다. 이 ROWID 값을 확보하는 방법으로는, 조건절에서 명시적으로 기술하거나 데이터 액세스 시 FETCH하여 저장해둔 후 사용하는 방식이 있다. 후자의 방법은 데이터가 갱신되거나 삭제되는 경우에도 적용할 수 있다.
하지만 ROWID 값을 직접 리터럴로 사용하기보다는 바인드 변수로 처리하는 것이 바람직하다. 그 이유는 한 번 생성된 로우가 항상 동일한 ROWID 값을 유지하지는 않기 때문이다. 로우의 물리적인 위치는 시간이 지나며 이주(Migration)하거나, 체인(Chain)이 발생하거나, 데이터가 임포트(Import) 또는 익스포트(Export)되는 과정에서도 변경될 수 있다.
ROWID 스캔은 배치처리 애플리케이션에서 특히 유용하게 활용된다. 예를 들어, DECLARE CURSOR 문으로 로우를 전부 읽은 뒤 FETCH한 결과를 갱신 처리할 때, 저장해 둔 ROWID를 활용하면 대상 로우를 정확히 액세스하여 효율적인 처리를 할 수 있다.
대부분의 ROWID 스캔은 테이블에 존재하는 인덱스를 통해 ROWID를 얻고, 이를 기반으로 테이블을 액세스하는 방식으로 이루어진다. 물론 모든 인덱스 스캔에서 ROWID 스캔이 발생하는 것은 아니며, 쿼리의 컬럼이 전부 인덱스에 존재하는 경우는 테이블 액세스 자체가 필요하지 않기 때문에 ROWID 스캔이 발생하지 않는다.
책에서 예시로 든 실행계획은 인덱스를 이용해 얻은 ROWID를 사용하여 테이블에 접근하는 전형적인 ROWID 스캔의 모습을 보여준다.
3.2.1.3. 인덱스 스캔
인덱스 스캔은 가장 흔하게 사용되는 데이터 액세스 방법으로, 로우를 찾는 것이 목적이지만 실제 내부적으로는 블록 단위의 액세스가 이루어진다. 옵티마이저는 블록을 기준으로 비용을 산정하며, 로우의 위치 분포는 I/O 성능에 중요한 영향을 미친다. 이를 클러스터링 팩터라고 하며, 데이터의 클러스터링이 잘 되어있을수록 효율적이다.
인덱스 스캔 유형들은 다음과 같다.
가) 인덱스 유일 스캔(Index Unique Scan)
유일 인덱스를 통해 하나의 ROWID만을 추출하는 방식으로, 조건에 '=' 비교가 필수적이다. 대부분 별도의 힌트 없이도 옵티마이저가 자동 선택하며, 필요한 경우 명시적으로 INDEX 힌트를 적용할 수 있다.
나) 인덱스 범위 스캔(Index Range Scan)
가장 일반적인 방식으로 하나 이상의 로우를 연속된 리프 블록에서 범위로 추출한다. 인덱스 구성 컬럼과 쿼리의 조건이 잘 맞아야 하며, LIKE 연산자의 앞 부분에 와일드카드가 있으면 적용되지 않는다. 필요하면 INDEX 힌트를 사용할 수 있다.
다) 인덱스 역순 범위 스캔(Index Range Scan Descending)
기본 인덱스 범위 스캔과 유사하지만 역순으로 데이터를 액세스한다. 최신 데이터부터 검색이 필요한 경우 유용하며, ORDER BY 절과 함께 효과적으로 사용할 수 있다. INDEX_DESC 힌트로 명시적으로 유도 가능하다.
라) 인덱스 스킵 스캔(Index Skip Scan)
인덱스의 선행 컬럼이 없거나 일부 컬럼이 조건절에 빠졌을 때에도 부분적인 스캔이 가능하게 하는 방식이다. 브랜치 블록에서 연속적이지 않은 영역을 스킵하며 필요한 영역만 선택적으로 액세스한다. 그러나 무조건적으로 좋은 성능을 보장하지 않으며, 경우에 따라 오히려 성능 저하가 발생할 수도 있으므로 유의하여 사용해야 한다.
마) 인덱스 전체 스캔(Index Full Scan)
인덱스의 모든 컬럼이 포함되고 최소 하나의 NOT NULL 컬럼이 있을 때 가능하다. 테이블 대신 빠르게 데이터를 액세스하는 용도로 활용 가능하다. 다만, ORDER BY를 요구하는 경우는 적용되지 않는다.
바) 인덱스 고속 전체 스캔(Index Fast Full Scan)
모든 컬럼이 인덱스에 포함되어 있으며 NOT NULL 제약조건이 하나 이상 있는 경우에 적용 가능하다. 이는 멀티 블록 I/O를 통해 빠르게 수행되며, 테이블 액세스를 하지 않고 인덱스만으로 데이터를 읽는다. INDEX_FFS 힌트를 이용해 유도할 수 있다.
3.2.1.4. B-Tree 클러스터 액세스 요약
B-Tree 클러스터 액세스는 데이터를 효율적으로 관리하고 검색하기 위한 두 가지 방법이 있다. 첫 번째는 대규모 데이터의 범위를 효율적으로 처리하는 클러스터링 방식이고, 두 번째는 조인의 효율성을 높이기 위해 여러 개의 테이블을 하나의 클러스터에 저장하는 방식이다.
클러스터링에서는 1:M 관계를 가지는 두 테이블의 데이터가 동일한 클러스터 키를 기준으로 묶이게 된다. 이 구조에서는 '1쪽'의 테이블에서 하나의 로우만 찾을 수 있지만, 'M쪽'에서는 여러 개의 로우가 검색된다.
예를 들어, ‘I_OBJ#’ 인덱스를 통해 145건을 액세스한 경우와 ‘COL$’ 테이블을 통해 1593건을 액세스한 경우를 보면, 클러스터 키를 통해 두 테이블이 함께 클러스터링되어 있다는 점을 알 수 있다. 즉, 한 클러스터 키에 여러 개의 로우가 저장된 구조다.
단일 테이블 클러스터 액세스에서는 ‘BPM500T’ 테이블이 클러스터 액세스를 통해 3207건이 조회되었다. 이는 클러스터 키당 320건이 한 곳에 모여 있어 10번의 랜덤 액세스로 전체 데이터를 찾는 효율적인 구조다.
이러한 방식은 대규모 데이터 처리나 조인에서 강점을 가지지만, 모든 경우에 클러스터링이 유리한 것은 아니다. 데이터의 특성과 처리 범위를 고려해 최적의 방식을 선택하는 것이 중요하다.
3.2.1.4. B-Tree 클러스터 액세스
B-Tree 클러스터 액세스는 데이터를 효율적으로 관리하고 검색하기 위한 두 가지 방법이 있다. 첫 번째는 대규모 데이터의 범위를 효율적으로 처리하는 클러스터링 방식이고, 두 번째는 조인의 효율성을 높이기 위해 여러 개의 테이블을 하나의 클러스터에 저장하는 방식이다.
클러스터링에서는 1:M 관계를 가지는 두 테이블의 데이터가 동일한 클러스터 키를 기준으로 묶이게 된다. 이 구조에서는 '1쪽'의 테이블에서 하나의 로우만 찾을 수 있지만, 'M쪽'에서는 여러 개의 로우가 검색된다.
예를 들어, ‘I_OBJ#’ 인덱스를 통해 145건을 액세스한 경우와 ‘COL$’ 테이블을 통해 1593건을 액세스한 경우를 보면, 클러스터 키를 통해 두 테이블이 함께 클러스터링되어 있다는 점을 알 수 있다. 즉, 한 클러스터 키에 여러 개의 로우가 저장된 구조다.
단일 테이블 클러스터 액세스에서는 ‘BPM500T’ 테이블이 클러스터 액세스를 통해 3207건이 조회되었다. 이는 클러스터 키당 320건이 한 곳에 모여 있어 10번의 랜덤 액세스로 전체 데이터를 찾는 효율적인 구조다.
이러한 방식은 대규모 데이터 처리나 조인에서 강점을 가지지만, 모든 경우에 클러스터링이 유리한 것은 아니다. 데이터의 특성과 처리 범위를 고려해 최적의 방식을 선택하는 것이 중요하다.
해시 클러스터 엑세스(Hash Cluster Access)는 물리적인 I/O 효율을 높이기 위해 사용되는 방법이다. 이 방식은 해시함수를 이용하여 데이터의 클러스터링 패턴을 최적화하는 방식으로, 동일한 해시값을 가진 데이터는 동일한 블록 내에 저장된다. 이러한 방식은 I/O를 줄일 수 있어 시스템 성능 향상에 긍정적인 영향을 준다.
3.2.1.5. 해쉬 클러스터 엑세스
해시 클러스터 엑세스는 일반적인 인덱스 스캔과는 다르다. 인덱스를 통한 데이터 액세스는 인덱스 I/O와 테이블 I/O가 필수적이지만, 해시 클러스터는 해시 함수로 구성된 경로만을 통해 데이터를 찾을 수 있으므로 I/O 횟수를 최소화할 수 있다. 이 방식은 특히 넓게 분포된 데이터를 액세스할 경우 더 큰 성능 향상을 기대할 수 있다.
해시 클러스터의 생성 시 'HASHKEYS * SIZE'라는 설정을 통해 초기 저장 공간의 크기(Extent)가 결정된다. 이는 디스크 공간을 미리 확보하는 개념으로, 데이터가 급격하게 증가할 경우 추가 공간 확보가 어려운 단점이 있다. 따라서 지속적인 데이터 증가가 예상되는 경우 해시 클러스터 방식은 적절하지 않을 수 있다.
해시값의 개수는 해시 생성 시 지정하며, 가장 작은 소수(Prime Number) 값이 해시값의 개수로 사용되는 것이 바람직하다. 클러스터 생성 시 최초로 할당된 해시 블록을 루트 블록(Root Block)이라고 하며, 동일한 해시값을 가진 데이터가 추가될 경우 초과 블록(Overflow Block)이 발생한다. 초과 블록이 많아지면 클러스터링 패턴이 무너지고 성능이 저하될 위험이 있다.
해시 엑세스를 사용할 때는 반드시 비교 연산자로 'LIKE', '<', '>', 'BETWEEN', 'IN' 등이 사용될 수 없다. 이는 해시 엑세스 방식이 다양한 액세스 형태를 지원하지 않는 구조이기 때문이다. 대신 키 값에 대한 정확한 조건이 주어졌을 때 해시 엑세스는 매우 효율적인 성능을 발휘할 수 있다.
이 방식을 사용할 경우, 해시 키로 지정된 컬럼이 자주 수정되지 않아야 하며, 데이터의 대량 증가가 예상되지 않는 경우에 적합하다. 일반적으로 로우의 길이 변동이 적고 크기가 예상 가능한 테이블에 적용하는 것이 이상적이다.
해시 클러스터링은 인덱스와 달리 별도의 인덱스를 필요로 하지 않는다는 점에서 효율적인 방법으로, 특히 특정 키 값에 대한 액세스가 빈번한 경우 큰 성능 향상을 기대할 수 있다.
3.2.1.6. 표본 테이블 엑세스 (Sample Table Scan)
표본 테이블 스캔은 전체 데이터를 모두 읽지 않고 일정 비율의 데이터를 표본으로 선택해 엑세스하는 방식이다. 이는 모든 로우를 스캔하는 전체 테이블 스캔과 달리, 특정 비율만큼의 데이터를 임의로 읽고 해당 조건을 만족하는 로우를 반환하는 것이 특징이다.
표본 테이블 엑세스의 동작 방식
표본 테이블 스캔에서는 SAMPLE 키워드를 사용하여 SQL에서 특정 비율을 지정할 수 있다.
SELECT ...
FROM table_name SAMPLE (BLOCK option) (Sample Percent)
WHERE ...
GROUP BY ...
HAVING ...
ORDER BY ...SAMPLE BLOCK(sample percent) 옵션은 전체 대상 블록 중 일부만 엑세스하도록 한다. 이때 지정한 비율은 환률값으로, 고정된 블록이 아닌 매 실행 시 다른 블록이 선택될 수 있다.
특징
- SAMPLE(sample percent)는 모든 블록을 엑세스한 후 각 블록에서 지정한 비율만큼 로우를 선택한 후 조건을 만족하는 결과를 반환한다.
- 비율 값으로 0.000001부터 99.999999까지 지정할 수 있으며, 0이나 100은 지원하지 않는다.
- 과거에는 단일 테이블에만 적용이 가능했으나 최신 버전에서는 조인이나 원격 테이블 엑세스에서도 사용 가능하도록 개선되었다.
활용 사례
- 데이터 마이닝 (Data Mining)
- 방대한 데이터에서 숨겨진 패턴을 발견하거나, 유의미한 상관관계를 찾는 데 유용하다.
- 데이터 정제 (Data Cleansing)
- 데이터 정제 작업에서는 일부 데이터만 표본으로 선택하여 데이터의 품질과 구조를 확인하는 데 활용된다.
- 테스트 환경
- 시스템 개발이나 유지보수를 위한 테스트에서 전체 데이터를 대상으로 하지 않고 일부 표본 데이터를 사용함으로써 비용과 시간을 절감할 수 있다.
이 방식의 장점으로는 비용 절감과 성능 향상이 기대된다는 점이 있으며, 특히 대규모 데이터를 빠르게 분석하거나 테스트하는 상황에서 뛰어난 효율성을 보인다. SAMPLE BLOCK 옵션을 사용하면 전체 블록을 엑세스하지 않고 일정 비율만 선택해 더 효과적으로 작업을 수행할 수 있다.
다만 몇 가지 유의점도 있다. SAMPLE 옵션은 매 실행마다 선택되는 데이터가 달라질 수 있어 항상 동일한 결과를 보장하지 않는다. 또한 작은 표본만으로는 신뢰성 있는 결과를 도출하기 어려울 수 있으므로 적절한 표본 크기를 설정하는 것이 중요하다.
3.2.2. 데이터 연결을 위한 실행계획
데이터 연결을 위한 실행계획에서는 테이블, 뷰, 인라인뷰 등 다양한 중간 집합들을 주로 조인을 통해 연결한다. 여기서 말하는 조인은 우리가 흔히 아는 일반적인 조합뿐만 아니라, 세미 조인(Semi Join)이나 카테시안 조인(Cartesian Join) 같은 특수한 형태까지 포함한다. 조인은 조건을 통해 데이터를 연결하는 논리적 연결고리의 역할을 하며, 두 개 이상의 집합을 조인하는 문장에서는 특정 시점에서 반드시 두 개의 집합이 연결된다.
현실에서는 대부분의 데이터가 서로 연결되어 있기 때문에 데이터베이스에서 조인을 활용하는 경우가 매우 많다. 단순히 하나의 테이블만 액세스하는 경우는 드물며, 대개 여러 테이블을 함께 처리해야 하는 경우가 대부분이다. SQL 문에서는 이러한 조인을 효과적으로 활용하는 것이 매우 중요하며, SQL을 고급스럽게 다루는 사람일수록 조인에 대한 이해와 활용 능력이 뛰어나다.
조인은 동일한 결과를 얻기 위해 다양한 방법을 선택할 수 있으며, 어떤 방법을 선택하느냐에 따라 성능 차이가 크게 발생할 수 있다. 따라서 조인의 처리절차를 정확히 이해하고 이를 실행계획을 통해 분석하는 것이 중요하다.
조인의 주요 유형은 다음과 같다.
- 내포 조인(Nested Loops Join)
- 정렬병합(Sort Merge Join)
- 해시 조인(Hash Join)
- 세미 조인(Semi Join)
- 카테시안 조인(Cartesian Join)
- 아우터 조인(Outer Join)
- 인덱스 조인(Index Join)
3.2.2.1. 내포 조인 (Nested Loops Join)
내포 조인은 가장 고전적이면서 현실적으로 가장 많이 사용되는 조인 방식이다. 이는 기본적인 조인 방식으로, 한 집합(Outer)의 각 로우에 대해 반복적으로 다른 집합(Inner)의 로우를 탐색하는 방식을 의미한다.
Nested Loops 조인은 가장 기본적인 조인 방식으로, 한 집합의 각 로우에 대해 반복적으로 다른 집합의 로우를 탐색하는 구조를 가진다. 이 조인 방식에서 먼저 수행되는 집합의 처리 범위가 전체 작업의 일량을 결정하게 된다. 이후 연결 작업이 필요한 로우가 발견될 경우 해당 로우에서 테이블 액세스가 발생한다. 작은 범위의 로우를 연결할 경우 매우 효율적이지만, 대량의 데이터를 연결할 경우 성능 저하가 발생할 수 있다. '중첩 루프 조인'으로 번역되기도 하지만, 대부분 'Nested Loops 조인'이라는 용어가 널리 사용된다.
이 조인의 처리 단계는 크게 세 가지로 나뉜다. 첫 번째는 드라이빙 집합(Driving Set) 선택이다.
이 단계에서는 옵티마이저가 먼저 수행될 집합을 선택하고, 해당 집합에 조건을 부여해 대상 로우를 찾는다.
두 번째는 연결 작업 수행 단계이다. 이 단계에서는 찾은 로우 각각에 대해 Inner 집합의 데이터를 탐색하고 연결 작업을 수행한다.
이때 인덱스 스캔이나 기타 다른 스캔 방식이 함께 활용된다.
세 번째는 조건 검증 단계이다. Inner 집합에서 찾은 로우들이 조건을 만족하는지 확인한 후 최종적으로 결과 집합을 반환한다.
Nested Loops 조인은 실전에서 여러 단계 중첩된 형태로 나타날 수 있다. 각 루프는 이전 루프의 결과에 대해 반복적으로 Inner 루프가 실행되는 구조를 갖는다. 여러 개의 테이블이 모두 Nested Loops 조인으로 실행될 경우, 가장 안쪽에 위치한 조인의 결과는 모든 단계의 조인이 성공했을 때 최종 결과로 반환된다.
효율적인 활용을 위해서는 USE_NL(table1, table2) 힌트를 사용하여 특정 테이블을 Nested Loops 방식으로 유도할 수 있다.
작은 데이터 범위에서는 매우 효율적이지만, 데이터 양이 많아질 경우 성능 저하가 클 수 있어 상황에 맞게 신중하게 사용해야 한다.
Nested Loops 조인은 작은 데이터 범위에서는 매우 효과적이지만, 대규모 데이터에서는 과도한 반복 탐색으로 인해 성능 저하가 발생할 수 있으므로 주의가 필요하다.
진보된 내포 조인(Advanced Nested Loops Join)
기본적인 Nested Loops 조인의 확장된 형태로, 단순한 Nested Loops 구조에서 약간의 변형이 가미된 방식이다. 여기서 중요한 차이점은 내측 루프의 테이블 액세스가 가장 위로 이동하고, 그 자리를 인덱스 액세스가 차지한다는 점이다.
이 방식은 다음과 같은 단계로 진행된다.
먼저 DEPT_LOC_IDX 인덱스를 사용해 LOC 값이 'SEOUL'인 DEPT 테이블의 첫 번째 로우를 액세스한다. 이후 액세스한 DEPT 테이블의 DEPTNO 값을 이용해 EMP 테이블을 인덱스 범위 스캔하여 DEPT 테이블의 DNAME과 연결된 결과 집합을 생성한다. 이 과정에서 EMP 테이블의 블록을 액세스한 후 해당 ROWID를 PGA 메모리에 저장하게 된다.
이후 PGA 버퍼에 저장된 ROWID를 이용해 EMP 테이블의 블록을 다시 찾고, JOB이 'CLERK'인 경우에만 최종적으로 데이터를 반환한다. EMP 테이블에서 새로운 블록이 등장할 경우에는 동일한 과정을 반복 수행하게 된다. 그런 다음 다시 DEPT_LOC_IDX 인덱스를 통해 두 번째 로우를 액세스하고, 위에서 언급한 절차를 반복한다. 최종적으로 DEPT_LOC_IDX 인덱스의 처리 범위가 종료되면 쿼리도 종료된다.
진보된 Nested Loops 조인은 클러스터링 패턴이 양호할 경우 일반 Nested Loops 조인보다 더 많은 부분을 한 번의 블록 액세스에서 연결할 수 있어 성능이 크게 향상된다.
이 방식은 DBMS 버전에 따라 지원 여부가 달라질 수 있으며, 실행 계획을 분석할 때 반드시 'M' 쪽 집합이 내측 루프에서 수행되는지 확인하는 것이 중요하다. 'M' 쪽 집합에서 내측 루프가 수행되는 이유는 동일한 블록에 여러 개의 조인 대상이 존재할 가능성이 높기 때문이다. 반대로 '1' 쪽 집합이 내측 루프에서 수행될 경우, 이미 액세스된 결과가 ROWID로 모여 있을 가능성이 높아 성능이 저하될 수 있다.
진보된 Nested Loops 조인은 적절한 조건에서 매우 뛰어난 성능을 보이지만, 잘못된 구성에서는 성능이 저하될 수 있어 신중하게 사용해야 한다.
3.2.2.2. 정렬 병합 조인(Sort Merge Join)
고전적인 조인 방식에 속하며, 주로 Nested Loops 조인이 가진 문제를 해결하기 위한 대안으로 사용된다. 이 조인 방식은 조인 대상의 범위가 넓고 랜덤 액세스를 줄이기 위해 고안된 방식으로, 연결고리에 적절한 인덱스가 없을 경우 유리하게 작용한다.
이 방식의 가장 큰 특징은 랜덤 액세스를 하지 않고 스캔을 통해 조인을 수행한다는 점이다. 이를 위해서는 반드시 두 개의 집합이 조인 가능한 구조로 미리 정렬되어 있어야 한다. 이 때문에 연결 작업이 훨씬 효율적으로 수행될 수 있으며, 정렬 자체가 하나의 추가적인 부담으로 작용할 수 있다.
정렬은 메모리에서 수행되기 때문에 메모리 크기인 Sort Area Size에 따라 성능 차이가 크게 발생한다. 대부분의 정렬이 메모리에서 이루어질 경우 효율적이지만, 메모리를 초과할 경우 디스크 I/O가 추가로 발생해 성능이 저하될 수 있다.
정렬 병합 조인은 비교 연산자가 '=' 이외의 경우에도 유리하게 작용하는 경우가 많다. 특히 'LIKE', 'BETWEEN', '<', '>' 등의 연산자를 사용할 경우 효율적이다. 이는 해시 조인처럼 특정 조건에만 한정되지 않기 때문에 다양한 상황에서 활용될 수 있다.
정렬 병합 조인의 또 다른 특징은 선행 집합이라는 개념이 없다는 점이다. Nested Loops 조인과 달리 정렬을 먼저 수행한 후 병합(Merge)을 통해 데이터를 조인하기 때문에, 정렬된 집합과 무관하게 독립적으로 실행된다.
또한, 정렬이 완료된 후 양쪽 집합은 스캔 방식을 통해 연결되므로, 인덱스가 존재하지 않아도 영향을 받지 않는다. 인덱스가 있어도 별도의 정렬 작업이 필요 없을 경우 더욱 효율적으로 수행될 수 있다.
실제 실행 계획에서는 한쪽 집합이 인덱스를 통해 이미 정렬된 경우, 별도의 정렬 없이 빠르게 병합할 수 있다. 반대로 양쪽 집합이 모두 정렬되지 않았다면 Sort 작업이 추가로 수행된다. 일부러 이 방식을 유도하고 싶다면 USE_MERGE(table1, table2) 힌트를 통해 제어할 수 있다.
정렬 병합 조인은 조인 대상이 광범위하거나 인덱스가 부재한 경우, 또는 복잡한 비교 연산자를 사용하는 경우에 강력한 성능을 발휘하지만, 정렬 자체가 부담이 될 수 있어 상황에 맞는 선택이 중요하다.
3.2.2.3. 해쉬 조인
해시 함수를 이용해 조인을 수행하는 방식이다. 해시 함수는 특정 값에 대해 상수값을 반환하거나, 해당 값의 위치 정보를 반환하는 기능을 한다. 이를 통해 해시 조인은 넓은 범위의 랜덤 액세스를 방지하고, 연결 대상을 특정 영역에 모아두는 방식으로 성능을 향상시킨다.
해시 조인은 일반적으로 데이터의 양이 많고 정렬 부담이 클 경우 대안으로 사용된다. 메모리 내에 해시 테이블을 만들어 조인을 수행하며, 해시 테이블은 조인 대상 데이터의 파티션 단위로 생성된다. 이 과정에서 로우들은 파티션별로 묶이고, 연결 대상인 페어 데이터끼리 연결 작업이 진행된다.
조인 과정에서는 두 개의 주요 단계가 진행된다. 첫째는 파티션 단계로, 데이터를 파티션별로 구분해 모으는 작업이다. 둘째는 조인 단계로, 파티션된 데이터를 이용해 연결 작업을 수행한다. 이 방식은 Nested Loops 조인과 달리, 한 번의 블록 액세스에서 다수의 데이터를 찾을 수 있는 장점이 있다.
해시 조인은 조인의 조건이 '=' (동치 조건)일 때만 적용 가능하며, 'LIKE', 'BETWEEN', '<', '>' 등의 조건에는 사용할 수 없다. 또한 대량 데이터를 처리할 때 특히 유리하며, 많은 단계에 걸쳐 조인이 이루어질 경우에도 높은 성능을 유지할 수 있다.
인덱스를 사용하는 경우와 달리 해시 조인은 별도의 정렬 과정 없이 효율적인 조인이 가능하다. 실행 계획에서 해시 조인을 강제로 유도하기 위해서는 USE_HASH(table1, table2) 힌트를 사용할 수 있다.
해시 조인은 메모리 내에서 데이터를 저장하고 액세스하는 구조이기 때문에 대규모 데이터 환경에서 매우 유리하다. 최근 옵티마이저는 이러한 장점을 살려 해시 조인을 선호하는 경향이 강해지고 있다. 하지만 모든 상황에서 해시 조인이 최고의 방법은 아니며, 각 상황에 맞는 적절한 조인 방식을 선택하는 것이 중요하다.
3.2.2.4. 세미 조인 (Semi Join)
세미 조인은 다양한 비교 연산자를 통해 사용된 서브쿼리가 메인쿼리와 연결되는 광범위한 조인 방식을 의미한다. 일반적인 조인과는 달리, 세미 조인은 서브쿼리의 집합과 메인쿼리의 집합을 특정 방식으로 연결하는 구조다. 특히, M:1 관계에서 메인쿼리가 M집합, 서브쿼리가 1집합인 경우 효율적으로 연결하도록 설계된 방식이다.
세미 조인의 특징은 서브쿼리가 메인쿼리와 수직적(종속적) 관계에 있으며, 서브쿼리의 집합은 메인쿼리와 완전히 동일한 구조를 유지하지 않아도 된다는 점이다. 일반적으로 서브쿼리의 집합을 1집합으로 강제하는 방법이 많이 사용되며, 이를 통해 조인의 정확성을 확보할 수 있다.
세미 조인의 처리 방식에는 몇 가지 유형이 있다.
첫 번째로 서브쿼리가 먼저 수행되는 경우는 서브쿼리가 먼저 실행되어 메인쿼리에 결과를 제공하고, 이후 'SORT (UNIQUE)' 처리를 통해 1집합으로 강제한 후 Nested Loops나 Sort Merge 조인으로 결합이 이루어진다.
두 번째로 서브쿼리가 나중에 수행되는 경우는 메인쿼리가 외측 루프, 서브쿼리가 내측 루프가 된다. 이 경우 내측 루프에서는 M집합이 자동으로 1집합으로 변환되며, 첫 번째 연결이 성공하면 이후 연결은 생략하는 방식으로 성능을 최적화한다. 이러한 방식을 필터(Filter) 처리라고 한다.
세 번째로 EXISTS 구문을 사용할 경우 서브쿼리는 항상 먼저 실행되며, Nested Loops 방식으로 처리된다.
실행 계획에서는 FILTER 키워드가 등장하면 Nested Loops와 유사한 방식으로 처리되며, 조건을 만족한 경우 추가 탐색 없이 종료된다. 과거에는 MERGE_SJ, MERGE_AJ, HASH_SJ, HASH_AJ 등의 힌트를 통해 강제로 특정 조인 방식을 유도할 수 있었지만, 최신 버전에서는 이러한 힌트 없이도 서브쿼리는 효율적인 방식으로 자동 처리된다.
필요한 경우 USE_MERGE(table1, table2)나 USE_HASH(table1, table2) 힌트를 사용하여 특정 조인 방식을 강제하는 방법도 가능하다.
3.2.2.5. 카티젼 조인 (Cartesian Join)
두 개의 집합 간에 연결 조건이 없이 수행되는 조인을 의미한다. 일반적인 M:1 관계 조인과는 달리, 카티젼 조인은 연결 조건 없이 모든 데이터를 결합하기 때문에 비효율적일 수 있다. 실행계획에서 CARTESIAN이라는 단어가 표시될 경우, 이는 예상치 못한 조인 형태가 발생했음을 의미하며, 이는 대부분 SQL 작성 시 연결 조건이 누락된 경우에 나타난다.
카티젼 조인은 일부 경우 의도적으로 활용될 수 있다. 예를 들어, 비정형 데이터 분석이나 복잡한 연산을 수행해야 할 경우 고의적으로 사용할 수 있다. 그러나 대부분의 경우는 실수로 인해 발생하며, SQL에서 연결 조건이 빠졌거나 조인 순서가 잘못 지정될 경우 의도치 않게 발생할 수 있다.
MERGE JOIN (CARTESIAN)이 실행 계획에서 나타나면, 해당 조인이 불필요한 중간 단계인지 최종 결과인지를 확인하는 것이 중요하다. 일반적으로 Nested Loops 조인에서는 CARTESIAN이라는 명시가 나타나지 않으므로, 예상치 못한 CARTESIAN이 등장했다면 SQL을 검토하고 누락된 조건이 있는지 확인해야 한다.
카티젼 조인은 대규모 데이터 처리에서 성능 저하를 유발할 수 있으므로, 불필요한 경우 반드시 피해야 한다. 이를 방지하려면 SQL 작성 시 조건을 꼼꼼히 확인하고, 실행 계획에서 CARTESIAN이 나타날 경우 누락된 연결 조건을 추가하는 것이 중요하다. 특히 조인 순서가 잘못된 경우에 발생할 가능성이 크므로, 옵티마이저 힌트를 통해 적절한 조인 순서를 명시하는 것도 좋은 방법이다.
3.2.2.6. 아우터 조인
아우터 조인은 특정 기준 집합을 기준으로 해당 집합에 대응되는 조인 대상 집합이 없을 경우에도 기준 집합의 모든 로우를 반환하는 조인 방식이다. 일반적인 조인은 양쪽 집합에 대응되는 데이터만 반환하지만, 아우터 조인은 기준 집합의 모든 데이터를 보존하면서 대응하는 데이터가 없는 경우에도 결과에 포함된다. 이는 데이터가 없다는 사실 자체가 중요한 정보일 수 있기 때문이다.
아우터 조인은 논리적으로 한쪽 집합의 데이터 존재 여부를 확인하는 과정이라고 볼 수 있다. 즉, 특정 대상 집합에 대응되는 데이터가 없는 경우에도 기준 집합의 데이터는 그대로 반환하는 방식이다.
아우터 조인은 일반적으로 외측 루프는 항상 먼저 수행되고, 내측 루프는 후행하는 형태로 실행된다. 즉, 아우터 루프가 기준이 되고 내측 루프가 선택적으로 대응되는 구조이다.
또한, 아우터 조인은 한쪽 방향만 수행할 수도 있고, 양쪽 모두 아우터 조인이 수행될 경우도 있다. 양쪽 아우터 조인은 모든 기준 집합의 데이터와 모든 대응 집합의 데이터를 포함하는 결과를 반환하기 때문에 상대적으로 복잡한 조인 방식이다.
특정 조건에서 아우터 조인이 발생하는 경우는 다음과 같다.
- 기준 집합의 모든 로우가 결과에 포함되어야 할 경우.
- 대응 집합에 값이 없는 경우에도 기준 집합의 데이터가 필요한 경우.
- 누락된 데이터를 포함한 결과를 생성해야 하는 경우.
아우터 조인은 다양한 실행 방식에 따라 실행 계획에서 특수한 형태로 나타나며, 일반적으로 Nested Loops 방식에서 아우터 조인이 수행된다.
Nested Loops 아우터 조인
특정 기준 집합을 외측 루프로 설정하고 반드시 먼저 수행해야 하는 조인 방식이다. 이때 외측 루프에서 데이터를 찾은 후, 내측 루프에서 대응되는 데이터를 찾지 못해 연결이 실패하더라도 외측 루프의 데이터는 그대로 보존된다. 이러한 방식은 아우터 조인의 필수 조건이므로 조인 방향이 고정된다는 점을 반드시 유의해야 한다.
이 조인의 특징은 필수적으로 외측 루프가 우선 실행되며, 이후 내측 루프가 실행되는 구조를 따른다. 내측 루프에서 대응 데이터를 찾지 못했을 경우에도 외측 루프의 데이터는 결과에서 제외되지 않고 그대로 유지된다. 이러한 특성 때문에 기준 집합(보존 집합)과 내측 루프의 순서가 매우 중요하며, 순서가 잘못될 경우 예상치 못한 결과가 발생할 수 있다.
Nested Loops 아우터 조인을 설정할 때 실무에서는 실수로 잘못 설정하는 경우가 종종 발생한다. 특히 아우터 조인으로 처리될 필요가 없는 경우에도, 테스트 환경에서는 아우터 조인으로 인해 의도치 않은 결과가 나타날 수 있다. 이러한 문제가 발견되어도 개발자가 이를 수정하지 않고 그대로 운영 환경으로 넘기는 경우가 많아 문제가 지속될 수 있다.
이러한 문제는 특히 데이터 이행(Migration) 프로젝트에서 자주 발생한다. 프로젝트의 막바지나 마감이 임박했을 때 개발자들이 급하게 작업을 마무리하면서 실수로 아우터 조인을 설정하거나 수정하지 않고 넘어가는 사례가 많다.
효율적인 사용을 위해서는 옵티마이저가 외측 테이블을 루프 처리할 때 과도한 방법을 피하고, 데이터 양이 많을 경우 더 효율적인 방식으로 전환하는 기능이 활용된다. 기존의 'USE_NL(table1, table2)' 힌트를 통해 강제로 이 방식을 유도할 수 있으며, 실행 계획에서는 'NESTED LOOPS (OUTER)'로 표시되어 일반 Nested Loops 조인과 구분된다.
해쉬 아우터 조인
해쉬 아우터 조인은 아우터 조인을 수행할 때 해시 조인의 방식을 통해 실행되는 조인 방식이다. 일반적으로 옵티마이저는 Nested Loops 조인을 사용하지만, 대규모 데이터 처리 시 성능 문제가 발생하거나 인덱스가 적절하지 않을 경우 해쉬 아우터 조인이 선택된다.
이 방식에서는 기준 집합이 무조건 빌드 입력(Build Input)으로 설정되며, 내측 조인 집합이 해시 테이블을 생성한 후 연결 작업이 진행된다. 해쉬 아우터 조인은 아우터 조인의 특성상 기준 집합의 모든 데이터를 보존하는 형태로 수행되며, 내측 집합에서 대응되는 데이터가 없더라도 기준 집합의 데이터는 그대로 결과에 포함된다.
해쉬 아우터 조인은 Nested Loops 조인에 비해 조인의 방향에 대한 제약이 크지 않다. 이는 해쉬 조인의 특성상 두 집합이 연결되기 전에 해시 테이블이 생성되기 때문이다. 따라서 해쉬 아우터 조인은 대규모 데이터를 처리할 때 상대적으로 효율적인 방법으로 사용된다.
예를 들어 SQL 쿼리에서 SELECT last_name, NVL(SUM(ord_amt), 0) FROM customers c, orders o WHERE c.cust_id = o.cust_id(+)와 같이 작성되었을 경우, 해쉬 아우터 조인을 사용하면 모든 customers 테이블의 데이터를 기준으로 orders 테이블의 데이터가 매칭되지 않는 경우에도 NULL 값으로 보존된다.
해쉬 아우터 조인의 주요 특징은 다음과 같다.
- 기준 집합의 모든 데이터는 반드시 결과에 포함된다.
- 내측 집합에서 대응되는 데이터가 없더라도 기준 집합의 데이터는 NULL로 채워져 반환된다.
- 일반적인 조인과 달리, 인라인 뷰(Inline View)와 함께 사용할 경우 복잡한 조건절이 포함될 경우 문제가 발생할 수 있으므로 주의가 필요하다.
특히, 조건절이 내측 집합에만 적용될 경우, 불필요하게 많은 데이터가 처리될 위험이 있으므로 적절한 조건절 설정이 중요하다. 이러한 문제를 방지하기 위해 ANSI 표준 SQL에서는 LEFT OUTER JOIN 구문을 활용하는 것이 보다 직관적이고 안전한 방법이 될 수 있다.
결론적으로, 해쉬 아우터 조인은 대규모 데이터를 처리할 때 성능을 최적화하는 강력한 방법이지만, 불필요한 데이터 처리나 조건 누락으로 인해 예상치 못한 결과가 나타날 수 있으므로 주의가 필요하다.
전체 아우터 조인
양쪽 집합이 모두 기준 집합이 되면서 동시에 대응 집합이 되는 형태의 아우터 조인을 의미한다. 일반적인 아우터 조인은 한쪽 집합만을 기준으로 대응 집합을 찾는 방식이지만, 전체 아우터 조인은 양쪽 집합이 모두 기준이 되어야 하기 때문에 논리적으로 한 단계만으로는 처리가 어렵다. 이를 해결하는 방식으로는 먼저 한쪽을 기준으로 아우터 조인을 수행한 후, 그 결과에 대해 다른 집합을 기준으로 부정형 조인을 결합하는 방법이 사용된다.
이 방식을 이해하기 위해서는 전체 아우터 조인이 일종의 ‘최소 공배수’ 집합이라고 생각하면 된다. 즉, 한쪽 집합에만 존재하는 데이터와 상대 집합에만 존재하는 데이터를 모두 포함하면서, 두 집합에 모두 있는 데이터 역시 결과에 포함된다. 이러한 형태의 전체 아우터 조인은 실행 계획에서 UNION-ALL과 함께 HASH JOIN (OUTER)과 HASH JOIN (ANTI) 형태로 나타날 수 있다.
전체 아우터 조인은 설계 상의 문제나 데이터의 일관성 문제로 인해 발생하는 경우가 많다. 특히 논리적으로는 하나의 엔티티였던 데이터를 물리적으로 분리하여 관리하는 경우, 이로 인해 각각의 집합이 서로 일치하지 않는 문제에서 많이 발생하게 된다. 이러한 문제는 데이터 구조 개선이나 품질 향상을 통해 해결하는 것이 이상적이지만, 현실적으로는 애플리케이션 차원에서 특정 처리를 통해 우회적으로 해결하는 방법이 자주 사용된다.
전체 아우터 조인은 대규모 데이터 처리 시 성능 문제가 심각하게 나타날 수 있으므로 신중하게 사용해야 한다. 이를 방지하기 위한 대안으로는 ‘UNION ALL’을 통해 두 집합의 최소 공배수 집합을 만들거나, 인라인뷰 및 GROUP BY를 이용한 방법 등이 활용될 수 있다. 또한, 특정 저장 함수 등을 사용하여 아우터 조인의 부담을 줄이는 방법도 효과적이다.
3.2.2.7. 인덱스 조인
인덱스 조인은 특정 테이블에서 사용된 하나 이상의 인덱스를 이용하여 데이터를 효율적으로 조회하는 방식이다. 일반적으로 인덱스 병합을 통해 복수의 인덱스를 결합하여 조건에 맞는 데이터를 찾고, 최종적으로 해당 ROWID를 이용해 데이터를 조회한다. 이는 테이블을 직접 스캔하지 않고 인덱스를 통해 필요한 데이터를 선별하는 효율적인 방법이다.
인덱스 조인은 일반적으로 크기가 작고 조건 필터링에 특화된 인덱스를 활용하기 때문에 빠른 성능을 기대할 수 있다. Nested Loops 형태로 수행되며, 해시 조인에서는 사용할 수 없다. ROWID를 통해 최종적으로 테이블 데이터를 조회하는 방식이 일반적이다. 여러 개의 인덱스를 결합하는 인덱스 병합 기법이 사용된다.
SELECT /*+ INDEX_JOIN(t) */
A2, B3, C1
FROM table1 t
WHERE A1 = '10'
AND B1 LIKE 'AB%'
AND C2 > 100;VIEW OF 'index$_join$_001'
HASH JOIN
HASH JOIN
INDEX (RANGE SCAN) OF 'IDX1' (NON-UNIQUE)
INDEX (RANGE SCAN) OF 'IDX2' (NON-UNIQUE)
INDEX (FAST FULL SCAN) OF 'IDX3' (NON-UNIQUE)INDEX_JOIN 힌트는 인덱스 조인을 강제하는 힌트로, 여러 인덱스를 활용하여 데이터를 검색하도록 한다. 해당 쿼리에서는 다음과 같은 인덱스들이 사용된다.
IDX1: A1 + A2 + A3, IDX2: B1 + B3, IDX3: C1 + C2 + C3
인덱스 조인은 단일 테이블뿐만 아니라 여러 테이블 간의 조인에서도 사용될 수 있다. 다음은 복잡한 조인에서 인덱스 조인을 활용한 예제이다.
SELECT /*+ INDEX_JOIN(x) */
x.A2, x.B3, y.D2
FROM table1 x, table2 y
WHERE x.D1 = y.D1
AND x.A1 = '10'
AND x.B1 LIKE 'AB%';TABLE ACCESS (BY INDEX ROWID) OF 'TABLE2'
NESTED LOOPS
VIEW OF 'index$_join$_001'
HASH JOIN
HASH JOIN
INDEX (RANGE SCAN) OF 'IDX1' (NON-UNIQUE)
INDEX (RANGE SCAN) OF 'IDX2' (NON-UNIQUE)
INDEX (FAST FULL SCAN) OF 'IDX4' (NON-UNIQUE)INDEX_JOIN 힌트를 통해 인덱스 조인을 강제한다. VIEW OF 'index$_join$_001'은 인덱스 병합을 통한 처리 과정을 나타낸다. TABLE ACCESS (BY INDEX ROWID)는 최종적으로 ROWID를 이용해 데이터를 조회하는 단계이다.
인덱스 조인은 조건이 복잡하고 여러 인덱스를 병합해야 할 경우 매우 유리하다. 그러나 인덱스 병합을 잘못 사용하거나, 적절하지 않은 인덱스를 선택하면 오히려 성능이 저하될 수 있다. 특히 ROWID를 기반으로 테이블을 조회하기 때문에, 잘못 설계된 경우 랜덤 I/O가 급증해 비효율이 발생할 수 있다. 따라서 인덱스 구조와 조건 필터링을 잘 고려한 후 인덱스 조인을 적용하는 것이 중요하다.
3.2.3. 연산방식에 따른 실행계획
실행계획은 사용한 연산 방식에 따라 다양한 형태로 나타날 수 있다. 해당 연산이 가지는 특성은 결과를 얻도록 하기 위해 나름대로 독특한 처리를 요구한다. 때로는 더 나은 실행계획을 얻기 위해 어떤 연산을 다른 형태로 변형하기도 한다
이 장에서는 다음과 같은 연산들이 가지는 실행계획의 형태와 특성을 이해하게 된다.
- IN-List 탐침 실행계획
- 연쇄(Concatenation) 실행계획
- 원격(Remote) 실행계획
- 정렬 처리(Sort Operation)
- 합집합(Union, Union-All) 실행계획
- 교집합(Intersection) 실행계획
- 차집합(Minus) 실행계획
- COUNT(STOPKEY) 실행계획
3.2.3.1. IN-List 탐침 (Iterator) 실행계획
IN-List 탐침 실행계획은 비교 연산자 중 'IN'을 사용할 경우 나타날 수 있는 실행 계획이다. 이 방식은 상황에 따라 실행 계획에 큰 영향을 미칠 수 있다. 'BETWEEN' 연산자는 연속된 범위를 의미하지만 'IN'은 여러 개의 '점'을 의미한다. 이 두 개념의 차이는 처리 방식에서 발생한다. 'BETWEEN'은 범위 처리 (Range Scan) 방식으로 이루어지며, 'IN'은 여러 개의 '점'을 기준으로 처리되기 때문에 경우에 따라 매우 큰 차이가 나타날 수 있다.
예를 들어, 점 '1'과 '2' 사이에 수많은 데이터 포인트가 존재할 수 있지만, 점 '1', '2'는 단순히 두 개의 점만을 의미한다. 즉, 범위 처리인 BETWEEN과 비교했을 때 IN은 불규칙하고 특정한 값들만 선택하는 형태다.
아래는 IN-List 탐침이 실행계획에서 어떻게 나타나는지 보여주는 SQL 예제와 실행 계획이다.
SELECT order_id, order_type, order_amount
FROM orders
WHERE order_type IN (1, 2, 3);SELECT STATEMENT
INLIST ITERATOR
TABLE ACCESS (BY INDEX ROWID) OF ORDER_ITEM
INDEX (RANGE SCAN) OF 'ORDER_ITEM_IDX1' (NON-UNIQUE)이 실행 계획에서 'INLIST ITERATOR'는 IN 조건에 나열된 값만큼 반복 수행되는 것을 의미한다. 즉, IN 조건에 따라 여러 개의 값이 반복적으로 비교되며, 각 값마다 동일한 방식의 액세스를 반복하는 형태다.
만약 IN 조건이 OR 조건으로 변환되었다면 동일한 형태의 실행 계획이 나타난다. 이는 옵티마이저가 'IN'을 'OR'로 변형시킨 후 실행계획을 수립하기 때문이다. 이 과정은 'OR'과 'IN'이 비교 연산자로서 처리 방식이 동일하기 때문이다.
아래는 IN-List 탐침이 조인에서 나타나는 경우의 예제다.
SELECT o.order_id, i.item_id, i.item_type, i.item_qty
FROM orders o, order_item i
WHERE i.order_id = o.order_id
AND i.item_type IN (1, 2, 3)
AND i.act_dept = '12300'
AND o.order_date = :b1;SELECT STATEMENT
NESTED LOOPS
TABLE ACCESS (BY INDEX ROWID) OF 'ORDERS'
INDEX (RANGE SCAN) OF 'ORDERS_IDX3' (NON-UNIQUE)
INLIST ITERATOR
TABLE ACCESS (BY INDEX ROWID) OF 'ORDER_ITEM'
INDEX (RANGE SCAN) OF 'ITEM_IDX2' (NON-UNIQUE)이 실행 계획에서는 Nested Loops 조인의 내측 루프에서 'INLIST ITERATOR'가 수행되고 있음을 보여준다. INLIST ITERATOR가 나타날 경우, 각 값이 독립적으로 스캔되기 때문에 불필요한 범위를 스캔하게 되면 오히려 처리량이 증가할 수 있다.
이러한 실행 계획을 유도하는 별도의 힌트는 존재하지 않는다. 필요하다면 기존의 'INDEX(table_alias index_name)' 힌트를 사용하여 IN 조건에 사용된 컬럼이 인덱스를 사용할 수 있도록 유도하는 방법이 있다. 다만, INLIST ITERATOR의 적용 여부는 옵티마이저가 최종적으로 결정한다.
3.2.3.2. 연쇄 실행계획
연쇄 실행계획(Concatenation Execution Plan)은 OR 조건을 사용하는 경우 발생하는 실행계획을 의미한다. 이 실행계획은 서로 다른 컬럼을 사용한 조건을 별도의 실행 단위로 분리하여 각자의 최적의 액세스 경로를 수행하도록 설계된다. 하지만 OR 조건이 있다고 해서 무조건 연쇄 실행계획이 나타나는 것은 아니다. 연쇄 실행계획은 OR 조건이 인덱스를 적절히 활용해야 할 경우에만 발생한다.
예를 들어 아래 두 쿼리를 비교하면, 같은 결과를 얻지만 실행 방식이 달라질 수 있다.
SELECT *
FROM table1
WHERE A = '10' AND B = '123';SELECT *
FROM table1
WHERE A = '10' OR B = '123';첫 번째 쿼리는 'A'와 'B' 조건을 모두 만족하는 로우만 반환하기 때문에 상대적으로 빠른 실행이 가능하다. 반면 두 번째 쿼리는 'A' 조건이나 'B' 조건 중 하나만 만족해도 결과에 포함되므로 상대적으로 더 많은 로우를 반환할 수 있다. 이러한 차이로 인해, OR 조건이 들어가면 불필요한 스캔이 발생할 가능성이 커지므로, 옵티마이저는 이를 방지하기 위해 연쇄 실행계획을 사용한다.
아래는 연쇄 실행계획의 형태를 보여주는 예제다.
SELECT o.header_id, i.line_id, i.revenue_amount
FROM order_item i, orders o
WHERE i.item_group = :b1
AND (o.s_order_id = i.order_id
OR o.m_order_id = i.order_id);이 쿼리의 실행 계획은 다음과 같이 나타날 수 있다.
Execution Plan
------------------------------------
SELECT STATEMENT
CONCATENATION
NESTED LOOPS
TABLE ACCESS (BY INDEX ROWID) OF 'ORDER_ITEM'
INDEX (RANGE SCAN) OF 'ORDER_ITEM_IDX1' (NON-UNIQUE)
TABLE ACCESS (BY INDEX ROWID) OF 'ORDERS'
INDEX (UNIQUE SCAN) OF 'ORDER_PK' (UNIQUE)
NESTED LOOPS
TABLE ACCESS (BY INDEX ROWID) OF 'ORDER_ITEM'
INDEX (RANGE SCAN) OF 'ORDER_ITEM_IDX1' (NON-UNIQUE)
TABLE ACCESS (BY INDEX ROWID) OF 'ORDERS'
INDEX (UNIQUE SCAN) OF 'ORDER_U1' (UNIQUE)이 실행계획에서는 CONCATENATION 연산자가 나타나며, 두 개의 NESTED LOOPS가 각기 다른 조건을 수행하도록 분리된다. 이는 옵티마이저가 OR 조건을 각각의 최적의 조건으로 수행하도록 변환했기 때문이다.
연쇄 실행계획이 발생하는 것을 방지하고 싶다면 NO_EXPAND 힌트를 사용하면 된다. 반대로 연쇄 실행계획을 강제로 유도하려면 USE_CONCAT 힌트를 적용할 수 있다.
SELECT /*+ USE_CONCAT */ *
FROM orders o, order_item i
WHERE o.s_order_id = i.order_id
OR o.m_order_id = i.order_id;OR 조건은 상황에 따라 연쇄 실행계획이 유리할 수도, 불리할 수도 있다. 따라서 실행계획을 확인하는 습관이 중요하며, 무조건 연쇄 실행계획을 유도하는 것은 위험할 수 있다.
3.2.3.3. 원격 실행계획
원격 실행계획이란 다른 데이터베이스의 테이블을 '데이터베이스 링크(Database Link)'를 통해 액세스하는 형태를 의미한다. 이는 분산 데이터베이스 환경에서 사용되며, 논리적으로는 같은 데이터베이스인 것처럼 사용할 수 있으나 물리적으로는 분리된 데이터베이스이므로 처리 비용이 상대적으로 높다. 이러한 특성은 옵티마이저의 결정에 중요한 영향을 미친다.
예를 들어 다음과 같은 쿼리를 보자.
SELECT c.cust_name, count(*)
FROM customers c, orders@crm_db o
WHERE c.cust_id = o.cust_id
AND o.order_number = :b1
GROUP BY c.cust_name;SELECT STATEMENT
SORT (GROUP BY)
NESTED LOOPS
REMOTE
TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMERS'
INDEX (UNIQUE SCAN) OF 'CUSTOMERS_PK' (UNIQUE)이 실행계획은 원격 테이블이 선행 집합으로 사용되어 외측 루프를 수행하고, 로컬 테이블이 내측 루프로 수행되는 구조다. 그러나 이 실행계획은 원격 테이블의 세부 액세스 계획을 표시하지 않으며, 해당 내용은 '라이브러리 캐시(Library Cache)'에서 확인할 수 있다.
아래는 해당 내용의 예시다.
SELECT "ORDER_NUMBER", "CUST_ID"
FROM "ORDERS"
WHERE "CUST_ID" IS NOT NULL
ORDER BY "CUST_ID";원격 테이블의 조인을 수행하는 과정에서는 예상치 못한 비용이 증가할 수 있으므로 옵티마이저의 결정이 중요하다. 비용 기반 최적화에서는 통제 불가능한 원격 테이블을 효율적으로 제어하는 것이 관건이며, 이를 위해 USE_NL 힌트를 통해 원격 액세스 방식이 지정될 수 있다.
SELECT c.cust_name, o.order_number, i.revenue_amount
FROM customers c, orders@crm_db o, orders_item@crm_db i
WHERE c.cust_id = o.cust_id
AND o.order_id = i.order_id
AND o.order_date = :b1;SELECT STATEMENT
NESTED LOOPS
REMOTE
TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMERS'
INDEX (UNIQUE SCAN) OF 'CUSTOMERS_PK' (UNIQUE)이 실행계획은 두 개의 원격 테이블이 조인되는 복잡한 형태로, 실행 속도 저하의 원인이 될 수 있다. 따라서 원격 액세스를 사용할 경우 실행계획을 면밀히 검토하는 것이 중요하다.
3.2.3.4. 정렬처리 실행계획
정렬 처리 실행계획은 데이터 액세스 후 사용자 요구를 충족시키기 위해 정렬을 수행하는 과정을 의미한다. 정렬 작업은 다양한 상황에서 발생하며, 실행 계획에 다음과 같은 형태로 나타날 수 있다: SORT (UNIQUE), SORT (AGGREGATE), SORT (GROUP BY), SORT (JOIN), SORT (ORDER BY).
1. SORT (UNIQUE) 이 정렬은 SELECT 절에 기재된 컬럼들로 구성된 로우에 대해 유일한 집합을 생성할 때 나타난다. 대표적으로 DISTINCT 키워드를 사용할 때 이 정렬이 수행된다.
SELECT DISTINCT deptno, ename
FROM emp
WHERE job = :b1;실행계획
SELECT STATEMENT
SORT (UNIQUE)
TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
INDEX (RANGE SCAN) OF 'EMP_IDX2' (NON-UNIQUE)또한, 서브쿼리에서 제공자 역할을 할 경우에도 SORT (UNIQUE)가 발생한다. 서브쿼리의 집합이 1 집합이 되어야 할 때 SORT (UNIQUE)가 수행된다.
서브쿼리에서의 SORT (UNIQUE) 예제
SELECT order_id, order_date, revenue_amount
FROM orders
WHERE order_id IN (
SELECT order_id
FROM order_item
WHERE item_id = :b1
AND order_qty > 100
);실행계획
SELECT STATEMENT
NESTED LOOPS
VIEW 'VW_NSO_1'
SORT (UNIQUE)
TABLE ACCESS (BY INDEX ROWID) OF 'ORDER_ITEM'
INDEX (RANGE SCAN) OF 'ORDER_ITEM_IDX2' (NON-UNIQUE)
TABLE ACCESS (BY INDEX ROWID) OF 'ORDERS'
INDEX (UNIQUE SCAN) OF 'ORDERS_PK' (UNIQUE)2. SORT (AGGREGATE) SORT (AGGREGATE)는 GROUP BY 없이 전체 집합에 대해 집계 함수(SUM, COUNT, MIN, MAX, AVG)를 사용할 때 나타난다.
SELECT SUM(revenue_amount)
FROM order_item
WHERE order_date = :b1;실행 계획
SELECT STATEMENT
SORT (AGGREGATE)
INDEX (FULL SCAN (MIN/MAX)) OF 'ORDERS_PK' (UNIQUE)이 실행 계획에서는 인덱스 전체 스캔으로 보여지지만 실제로는 첫 번째 블록만 액세스하며 추가 스캔은 하지 않는다. MAX 역시 마지막 블록만 액세스하고 종료된다.
정렬 처리 실행계획은 데이터 정렬이 중요한 경우 효율적인 방식으로 설계되어야 하며, 불필요한 정렬이 발생하지 않도록 조건과 구조를 명확하게 구성하는 것이 중요하다.
3.2.3.5. 집합 처리(Set Operations) 실행계획
집합 처리는 SQL에서 서로 다른 쿼리에서 처리한 결과를 다시 모으는 형태를 의미한다. 이를 통해 복잡한 처리도 여러 개의 분리된 단위로 나눠 단순하게 수행할 수 있다. 집합 처리의 종류는 다음과 같이 크게 세 가지가 있다.
- 합집합 (Union, Union-All)
- 교집합 (Intersection)
- 차집합 (Minus)
합집합 (Union, Union-All) 실행계획
합집합은 서로 다른 쿼리의 결과를 하나로 합치는 연산이다. 복잡한 처리도 여러 단위로 분리하면 단순하게 정리할 수 있어 효율적이다. 합집합의 본질은 액세스의 복잡성보다는 데이터를 모으는 형태로 나타난다.
SELECT order_id, order_date, status
FROM orders
WHERE order_date > :b1
OR order_id IN (
SELECT order_id
FROM order_item
WHERE item_group = 'A001'
);실행계획
SELECT STATEMENT
FILTER
TABLE ACCESS (FULL) OF 'ORDERS'
TABLE ACCESS (BY INDEX ROWID) OF 'ORDER_ITEM'
INDEX (RANGE SCAN) OF 'ORDER_ITEM_IDX2' (NON-UNIQUE)차집합 (Minus) 실행계획
차집합은 한쪽 집합을 기준으로 다른 집합의 요소들을 제거하는 연산이다. 차집합은 서브쿼리의 NOT EXISTS 형태와 다르며, SQL의 DISTINCT와 비슷한 결과를 만들어내지만 더 단순한 구조로 처리된다. 차집합에서는 양쪽 집합의 SELECT-List에 나타난 모든 컬럼들이 동일해야 한다.
SELECT regist_no
FROM employees
WHERE dept_no = :b1
MINUS
SELECT social_no
FROM billboard
WHERE cre_date = :b2;실행계획
SELECT STATEMENT
MINUS
SORT (UNIQUE)
TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEES'
INDEX (RANGE SCAN) OF 'EMPLOYEES_IDX2' (NON-UNIQUE)
SORT (UNIQUE)
TABLE ACCESS (BY INDEX ROWID) OF 'BILLBOARD'
INDEX (RANGE SCAN) OF 'BILLBOARD_IDX1' (NON-UNIQUE)이 실행계획에서는 합집합과 유사하게 SORT(UNIQUE)가 나타나며, 이는 유일성 보장을 위해 추가된다. 그러나 WHERE 절에 적절한 조건이 부여될 경우 SORT(UNIQUE)는 제거될 수 있다.
3.2.3.6. COUNT(STOPKEY) 실행계획
COUNT(STOPKEY) 실행계획은 쿼리의 조건절에 ROWNUM을 사용할 경우 나타나는 실행계획이다. ROWNUM은 쿼리 실행 중에 정해지는 가상(Pseudo) 컬럼으로, 자동으로 부여되는 행(Row) 순서를 의미한다. ROWNUM은 테이블에 물리적으로 존재하는 컬럼이 아니지만, SQL에서는 특정 조건에 따라 활용할 수 있도록 제공된다. 대표적인 가상 컬럼으로는 SYSDATE, USER, ROWID, LEVEL 등이 있으며, ROWNUM은 이들과 함께 자주 사용된다.
ROWNUM은 내부적인 실행 단계에서 값이 생성되므로, 이를 효율적으로 사용하기 위해서는 내부적인 실행 원리를 이해하는 것이 중요하다.
다음은 ROWNUM을 사용한 기본적인 예제이다.
SELECT *
FROM orders
WHERE order_date = :b1
AND act_deptno = :b2
AND ROWNUM <= 20;실행 계획
SELECT STATEMENT
COUNT (STOPKEY)
TABLE ACCESS (BY INDEX ROWID) OF 'ORDERS'
INDEX (RANGE SCAN) OF 'ORDER_IDX2' (NON-UNIQUE)이 실행계획에서는 인덱스 범위 스캔을 통해 데이터를 액세스한 후, 조건을 만족하는 로우에 차례로 ROWNUM을 부여하면서 20개에 도달하면 처리를 종료하는 형태이다. 이는 조건을 만족하는 로우를 빠르게 찾은 순간 처리를 종료하기 때문에 매우 효율적이다.
다음은 ROWNUM이 복잡하게 사용된 예제이다.
SELECT MIN(DECODE(ROWNUM, 1, rnum2), plan_time)
FROM (
SELECT ROWNUM rnum1, MIN(plan_time) plan_time
FROM (
SELECT ROWNUM rnum2, plan_time
FROM subjects
WHERE role_cd = '1007'
GROUP BY SUBSTR(plan_time, 3, 4), ROWNUM
)
WHERE ROWNUM <= 10
)실행 계획
SELECT STATEMENT
COUNT (STOPKEY)
VIEW
SORT (GROUP BY STOPKEY)
VIEW
SORT (GROUP BY)
TABLE ACCESS (BY INDEX ROWID) OF 'SUBJECTS'
INDEX (RANGE SCAN) OF 'SUBJECT_IDX3' (NON-UNIQUE)이 실행계획에서는 ROWNUM이 여러 단계에 걸쳐 사용되고 있다.
- 첫 번째 ROWNUM은 실행계획 상에서 COUNT로 나타난다. 이 값은 사용자가 지정한 값 rnum1으로 제한되어 있다.
- 두 번째 ROWNUM은 SORT (GROUP BY STOPKEY)라는 형태로 나타나며, 그룹화된 결과에서 상위 몇 개의 행만 처리하도록 제한한다.
- 마지막 단계에서는 ROWNUM이 최종적으로 COUNT(STOPKEY) 형태로 나타나며, 전체 프로세스를 제한하는 역할을 한다.
이처럼 ROWNUM을 사용할 경우 실행계획을 면밀히 살펴보고, 불필요한 단계가 추가되지 않도록 주의가 필요하다.
3.2.4. 비트맵(Bitmap) 실행계획
비트맵 실행계획은 하나의 테이블에 대해서만 수행된다. 비트맵 인덱스가 사용된 테이블은 다른 테이블과 다양한 방식으로 조인될 수 있지만, 이로 인해 실행계획이 특별한 형태로 나타나는 경우는 드물다. 비트맵 실행계획의 특징은 비트맵 인덱스를 이용하여 처리하는 방식이며, 대부분의 연산자는 비트 연산 방식으로 처리된다. 이러한 비트맵 연산은 데이터웨어하우스에서 자주 활용된다.
3.2.4.1. 조건 연산자별 비트맵 실행계획
비트맵 인덱스는 거의 모든 형태의 조건 연산자에 대해 비트 연산 처리가 가능하다. 따라서 조건 연산자의 형태에 따라 다양한 비트맵 실행계획이 수립된다.
가) 동치(Equal) 비교 실행계획
비트맵 실행계획 중 가장 단순한 형태로 하나의 컬럼을 '='로 비교할 때 나타난다. 단일 값에 대한 비교는 'SINGLE VALUE'로 표시되며, 여러 개의 '=' 연산자를 동시에 사용할 경우 'INLIST ITERATOR'가 나타난다.
나) 범위(Range) 비교 실행계획
범위를 나타내는 BETWEEN, LIKE, >, <, >=, <= 연산자에 대해서는 'RANGE SCAN'이 나타난다. NUMBER 타입 컬럼에 LIKE 연산자를 사용할 경우 B-Tree 인덱스와 달리 비트맵 인덱스는 전체 스캔(FULL SCAN)을 수행한다.
다) AND 조건 실행계획
각각의 비트맵을 가진 컬럼들을 AND 조건으로 사용할 경우 비트맵 간의 'AND 연산'이 실행된다. 범위 연산이 포함된 경우 'BITMAP MERGE' 작업도 함께 나타날 수 있다.
라) OR 조건 실행계획
OR 조건을 이용한 경우 각 컬럼이 개별적으로 비트맵을 생성한 뒤 이를 'BITMAP OR' 연산으로 처리한다. OR 조건 내에 복합 조건이 포함될 경우에도 중첩된 비트맵 연산 형태로 나타난다. 복잡한 OR 조건에서도 정상적인 비트맵 연산이 수행되지만, 부정형 OR 조건은 하나의 비트맵 인덱스만 사용하여 전체 스캔(FULL SCAN)을 수행할 수 있다.
마) 부등식(Not Equal) 비교 실행계획
부등식(<>) 조건이 사용되면 비트맵 인덱스는 먼저 전체 비트맵을 스캔한 후, 해당 값을 비트맵에서 제거하는 'BITMAP MINUS' 연산을 수행한다. 이때 부등식을 사용하는 컬럼이 NOT NULL 조건을 가진 경우와 그렇지 않은 경우의 처리 방식이 약간 다르다.
바) NULL 비교 실행계획
비트맵 인덱스에서는 'IS NULL' 또는 'IS NOT NULL' 조건도 처리 가능하다. NULL을 허용하는 컬럼이 'IS NULL' 조건을 사용하면 단순한 SINGLE VALUE 비트맵 연산을 수행한다. 반대로 'IS NOT NULL' 조건은 NULL 값에 해당하는 비트맵을 찾아 제거하는 BITMAP MINUS 연산을 수행하게 된다.
3.2.4.2 서브쿼리 실행계획
비트맵 인덱스라고 해서 서브쿼리를 포함한 조인에서 B-Tree 인덱스와 크게 다른 원칙이 적용되는 것은 아니다. 비트맵 액세스 이전에 B-Tree 인덱스를 사용한 서브쿼리가 수행되어 상수 값을 제공할 수 있다. 그러나 서브쿼리가 두 개 이상 사용되면 하나는 제공자 역할을 하고 나머지는 확인자 역할만 수행하는 비효율적 처리도 가능하다. 이를 최적화하려면 서브쿼리를 먼저 수행하여 조건을 좁힌 다음 팩트 테이블을 액세스하는 스타변형 조인(Star Transformation)을 사용하면 성능이 개선된다.
SELECT *
FROM SALES_SUM
WHERE ITEM_CD IN (SELECT ITEM_CD FROM ITEM_T WHERE CATEGORY_CD = 'ABC');SELECT /*+ STAR_TRANSFORMATION */
I.ITEM_CD, SUM(S.AMOUNT) SAL_AMOUNTS
FROM SALES_SUM S, ITEMS I, COUNTRYS C
WHERE S.ITEM_CD = I.ITEM_CD
AND S.COUNTRY = C.COUNTRY_CD
AND I.CATEGORY_TYPE = 'Clothes'
AND C.AREA = 'EUROPE'
GROUP BY I.ITEM_CD;3.2.4.3 B-Tree 인덱스와의 연합 실행계획
B-Tree 인덱스를 비트맵 인덱스로 변환하거나 그 반대도 가능하다. 이를 통해 비트맵 연산을 수행하여 처리범위를 좁힐 수 있으며, B-Tree 인덱스가 없을 때도 힌트를 통해 비트맵 액세스를 강제로 사용할 수 있다. 그러나 결합된 컬럼들이 선행하지 않거나 인덱스의 처리 범위가 넓다면 오히려 성능에 악영향을 줄 수 있어 주의해야 한다.
SELECT /*+ INDEX_COMBINE(SALES) */
FROM SALES
WHERE ITEM_CD = 'PA100'
AND PRICE >= 100000;3.2.5 기타 특수한 목적을 처리하는 실행계획
앞서 설명한 실행계획 외에도 데이터베이스는 특정한 목적을 위한 여러 형태의 실행계획을 생성할 수 있다. 여기서는 다음과 같은 형태를 살펴본다.
- 순환(Recursive) 전개 실행계획
- UPDATE SET절 서브쿼리 실행계획
- 특이한 형태의 실행계획
- 종합 실행계획 사례 연구
비트맵 실행계획, 병렬 처리, 파티션 처리 등은 다른 단원에서 자세히 다룬다.
3.2.5.1. 순환(Recursive) 전개 실행계획
순환 전개 실행계획은 순환구조를 가진 테이블에서 어떤 점을 시작으로 하위 구조를 전개하는 순전개(Implosion) 또는 역전개(Explosion)를 할 때 나타나는 실행계획이다. 이 실행계획은 CONNECT BY…START WITH 구문을 사용할 때 나타난다. 책의 이 부분에서는 실행계획의 형태와 처리 절차에 대한 설명만을 다루고 있다.
기본적인 순전개가 발생했을 때 실행계획은 다음과 같다. 먼저 START WITH 조건을 만족하는 모든 로우를 액세스하여 버퍼(1-1)에 저장하고, 이 로우들은 각 구조의 루트(Root)가 된다. 다음으로 (1-1)에 의해 저장된 로우들을 선발해 (1-2)를 수행하여 테이블을 액세스하며, 루트 테이블의 액세스를 완료한다. 그 후 하위 구조로의 전개가 본격적으로 시작된다.
하위 구조로의 전개 처리는 일반적인 Nested Loops 조인처럼 외측루프와 내측루프를 가진다. 외측루프(2-1)는 버퍼에 저장된 로우(prior 로우)를 대상으로 하고, 내측루프(2-2)는 EMPNO의 컬럼값(PRIOR EMPNO)을 상수 값으로 하여 CONNECT BY 절에 연결된 MGR을 인덱스 범위 스캔으로 액세스한다. 내측루프의 결과 로우들은 다시 버퍼의 해당 위치에 저장한다.
버퍼에 저장된 로우들을 대상으로 이 과정을 반복하며 더 이상 연결이 없을 때까지 수행한 후 최종 결과 집합은 실행계획에서 FILTER 작업으로 처리된다. 이 과정에서 WHERE 조건절이 처리를 끝내는 기능을 수행한다.
실제에서는 구조적 모습으로 결과가 출력되는데, ORDER BY를 사용할 경우 이 구조가 깨질 수 있다. 과거에는 CONNECT BY에서 연결을 주관하는 인덱스를 이용하여 자연스런 정렬을 하였으나, 사용자가 원하는 임의의 정렬을 할 수 없었기 때문에 ORDER SIBLINGS BY 구문이 추가되었다.
또한 해시 조인을 이용한 내측 루프의 실행계획도 나타날 수 있다. 이 실행계획에서는 CONNECT BY의 연결에 사용할 인덱스가 준비되지 않았거나 테이블 크기가 충분히 작다면 옵티마이저가 해시 조인을 통해 내측루프를 수행한다. 이 때, 해시조인은 빌드입력(Build Input)을 먼저 액세스하여 해시키를 만들고, 이를 통해 연결을 수행하는 검색입력(Probe Input)으로 나뉜다. 해시 영역에서 빌드입력을 담당하는 버퍼는 전개된 구조를 담고 있고, 내측 루프에 값을 제공(Pump)하는 역할을 수행한다.
서브쿼리가 여러 위치에서 존재하면 실행계획이 달라질 수 있다. 예를 들어, WHERE 절에 서브쿼리가 있으면 필터처리가 수행된 후 루트로 선택된다. START WITH 절에 서브쿼리가 있으면 루트를 찾기 위한 처리가 먼저 이루어지고 버퍼에 저장된 루트를 대상으로 전개가 수행된다.
순환전개 실행계획에서는 논리적으로 이해하기 어려운 경우가 발생할 수 있다. 예를 들어, 루트를 찾기 위한 서브쿼리 처리 뒤에 WHERE 절의 서브쿼리 처리가 실행계획의 하단부에 추가로 나타나는 경우가 있다. 이런 처리가 발생할 이유는 없지만, 옵티마이저의 판단으로 실행계획에 나타나게 된다.
특정 버전(10.2)에서는 루트를 찾는 부분이 이미 액세스가 되었음에도 다시 전체 테이블 액세스가 나타나는 이해하기 어려운 실행계획이 발생하기도 한다. 실제 TRACE를 분석해 보면 실행계획 단계와 실제 수행이 다르게 나타날 수 있다.
마지막으로, 조인 쿼리를 순환 전개시키는 경우에도 실행계획은 달라질 수 있다. START WITH 조건을 처리하지 않고 전체를 조인한 후 필터처리를 통해 루트를 선택하거나, 인라인뷰에서 먼저 순환전개를 처리하고 결과와 해쉬 조인을 수행하는 방법이 있다. 이 방법은 조건 컬럼이 서로 다른 테이블에 있는 경우 유용하며, 실제 환경에서도 자주 사용된다.
3.2.5.2. UPDATE 서브쿼리 실행계획
UPDATE 문에서는 SET 절과 WHERE 절에 서브쿼리를 사용할 수 있다. 이때 SET 절에 사용된 서브쿼리는 하나의 값을 반환하는 스칼라 서브쿼리로, 특정 컬럼 값을 계산하는 데 사용된다. SELECT 문에서의 SET 절은 특정 컬럼의 값을 지정하는 구문이므로, SELECT-List 항목과 유사한 의미를 가진다.
스칼라 서브쿼리의 결과가 존재하지 않으면 NULL을 반환하고, 하나 이상의 결과가 반환되면 에러가 발생한다. 이와 같은 스칼라 서브쿼리는 SELECT 문뿐 아니라 UPDATE 문에서도 동일하게 처리된다.
SET 절과 WHERE 절 모두에 서브쿼리를 사용한 경우
UPDATE employee e
SET sal = (SELECT AVG(sal) * 1.2
FROM bonus b
WHERE b.empno = e.empno
AND b.pay_date BETWEEN :b1 AND :b2)
WHERE deptno IN (SELECT deptno
FROM dept
WHERE loc = 'BOSTON');해당 SQL 실행계획에서는 서브쿼리가 먼저 수행되어 메인쿼리에 결과를 제공하며, 이후 본 테이블에 대한 업데이트가 수행된다. 이때 Nested Loops 조인이 사용되며, 서브쿼리는 주로 Sort(Aggregate) 연산과 함께 수행된다.
또한 WHERE 절에 서브쿼리가 있을 경우 해쉬 조인 방식으로 수행될 수 있으며, 이 역시 일반적인 SELECT 문과 동일한 방식이다.
스칼라 서브쿼리를 실행한 결과가 존재하지 않으면 해당 결과는 NULL로 처리된다. 이때 기존 컬럼 값은 보존되며 변경되지 않는다. 이를 "실패(Fail)"라고 하며, 이는 에러(Error)와는 구분되는 정상적인 실행결과이다.
ㄷ
이러한 실패를 방지하기 위해 NVL, NVL2, COALESCE 등을 사용하여 기본 값을 설정하는 방법이 있다. 예를 들어, AVG, SUM, COUNT 같은 그룹 함수는 항상 실패 없이 NULL을 반환하므로 NVL 함수와 함께 사용하면 안전하다.
하지만 스칼라 서브쿼리가 지나치게 많아질 경우 실행 효율이 떨어질 수 있다. 이 경우 WHERE 절에 같은 조건을 사용하는 서브쿼리를 별도로 수행하여 처리하거나, Modifiable Join View를 사용하여 불필요한 서브쿼리 수행을 방지할 수 있다.
다음 예제는 조인된 뷰(EMP_DEPT_VIEW)를 UPDATE 하는 SQL의 실행계획을 보여준다.
CREATE OR REPLACE VIEW EMP_DEPT_VIEW AS
SELECT x.EMPNO, x.ENAME, x.JOB, x.SAL, y.LOC, y.DNAME
FROM EMP x, DEPT y
WHERE x.DEPTNO = y.DEPTNO;UPDATE EMP_DEPT_VIEW e
SET SAL = DECODE(DNAME, 'SALES', 1.2, 1.1) * SAL
WHERE LOC LIKE 'AB%';해당 실행계획을 보면 SET 절에서 사용된 DNAME은 DEPT 테이블의 컬럼이며, WHERE 절의 LOC도 DEPT 테이블에 속한다. 실행계획 상에서는 HASH JOIN을 통해 한 번의 해쉬 조인만으로 처리되고 있어, 별도의 서브쿼리 실행 없이 효율적으로 수행됨을 알 수 있다.
3.2.5.3. 특이한 형태의 실행계획
이 절에서는 지금까지 언급되지 않았던 특이한 유형의 실행계획들을 간략히 소개한다. 이들 대부분은 비교적 최근에 등장한 기능들로, 현재까지는 사용 빈도가 낮지만 향후 유용하게 활용될 수 있는 기능들이다.
실행계획의 구체적인 내부 처리 구조와 분석은 이후 별도의 장에서 상세히 다룰 예정이며, 이곳에서는 실행계획의 흐름과 개념을 간단하게 이해할 수 있는 수준에서 설명하고 있다.
다음은 특이한 실행계획에 해당하는 항목들이다.
- 서브쿼리 팩토링(Factoring)
- 특이한 DELETE 문 서브쿼리
- 다중 테이블 입력(Multi-table Insert)
- HAVING 절 서브쿼리
- CUBE 처리
- GROUPING SETS 처리
- ROLLUP 처리
- MERGE 문
3.2.5.3. 특이한 형태의 실행계획 정리
가) 서브쿼리 팩토링 실행계획
서브쿼리 팩토링은 WITH 절을 사용하여 복잡한 쿼리를 임시 테이블처럼 생성해 재사용할 수 있도록 하는 기능이다. WITH 절 내부의 쿼리는 실행계획상 먼저 수행되며, 그 결과는 TEMP TABLE TRANSFORMATION 단계에서 임시 테이블로 변환된다. 이후 이 테이블은 일반 인라인뷰처럼 조인되어 사용된다.
실행계획에는 RECURSIVE EXECUTION이라는 단계도 나타나며, 이는 WITH 절 내부 쿼리가 반복적으로 실행됨을 의미한다. 이 방식은 인라인뷰와 거의 동일하게 작동하지만, 인덱스를 사용할 수 없다는 단점이 있어 대량 데이터에는 성능상 불리할 수 있다.
나) 특이한 DELETE 문 서브쿼리
DELETE 문에서도 SELECT 문처럼 서브쿼리를 활용할 수 있다. DELETE 대상 레코드를 찾기 위해 내부적으로 NESTED LOOPS 조인이 수행되며, 인덱스 스캔 등을 통해 조건에 맞는 데이터를 탐색한다.
실행계획에서는 DELETE STATEMENT 아래에 NESTED LOOPS가 나타나며, 조건이 복잡하더라도 일반 SELECT 쿼리와 동일한 방식으로 처리되는 것이 특징이다.
다) 다중 테이블 입력(Multi-table Insert) 서브쿼리
다중 테이블 입력은 하나의 SELECT 결과로 여러 개의 테이블에 데이터를 동시에 삽입할 수 있는 기능이다. INSERT ALL 또는 INSERT FIRST 구문을 사용하여 조건에 따라 데이터를 다른 테이블에 나눠 삽입한다. 실행계획에서는 INSERT STATEMENT 하위에 MULTI-TABLE INSERT가 나타나며, 그 아래에는 삽입 대상 테이블들이 나열된다.
소스 테이블에서는 데이터를 읽고 조건에 맞게 나눠 여러 테이블에 삽입되므로, 데이터 마이그레이션과 같은 작업에 매우 유용하게 사용된다.
라) HAVING 절 서브쿼리 실행계획
HAVING 절은 GROUP BY로 집계된 결과에 대해 추가 조건을 부여할 때 사용된다. 이 절에서 서브쿼리를 사용하는 경우, 실행계획에는 먼저 GROUP BY가 수행된 후 FILTER 단계에서 서브쿼리가 실행되는 구조가 나타난다. 실행계획에는 SORT (GROUP BY), FILTER 단계가 있으며, 서브쿼리는 NESTED LOOPS 또는 AND-EQUAL 방식으로 실행된다. 이를 통해 집계된 결과에 대해 조건을 세부적으로 적용할 수 있다.
마) ROLLUP, CUBE, GROUPING SETS 처리 실행계획
ROLLUP과 CUBE는 GROUP BY 결과에 대해 각 단계의 부분합(Subtotal)을 자동으로 생성하는 기능이다. ROLLUP은 특정 컬럼의 계층 구조에 따라 누적합을 계산하고, CUBE는 가능한 모든 조합의 집계를 계산한다. GROUPING SETS는 여러 개의 GROUP BY 집합을 한 쿼리에서 동시에 수행할 수 있도록 한다.
실행계획에서 ROLLUP은 SORT (GROUP BY ROLLUP) 단계로 표현되며, 일반 조인 이후 정렬 작업이 수행된다. CUBE는 SORT (GROUP BY) 이후 GENERATE (CUBE)라는 단계가 추가되어 구별된다.
GROUPING SETS는 각각의 집합을 따로 처리하기 위해 RECURSIVE EXECUTION 단계가 여러 번 반복되며, 각 단계에서 TEMP TABLE TRANSFORMATION을 통해 임시 테이블이 생성된다.
바) MERGE 문 실행계획
MERGE 문은 SELECT, INSERT, UPDATE, DELETE 작업을 하나의 SQL로 통합 처리할 수 있도록 설계된 문장이다. 기존에는 DML 문을 각각 따로 실행해야 했지만, MERGE 문을 사용하면 조건에 따라 UPDATE하거나, 조건이 맞지 않으면 INSERT할 수 있다.
MERGE 문은 USING 절을 통해 정보 제공 테이블을 지정하고, ON 절에서 대상 테이블과의 연결 조건을 명시한다. WHEN MATCHED 절에는 UPDATE 또는 DELETE 조건을, WHEN NOT MATCHED 절에는 INSERT 조건을 지정한다. 실행계획에서는 MERGE STATEMENT 하위에 MERGE OF 테이블명이 나타나며, 내부적으로 VIEW, NESTED LOOPS, TABLE ACCESS, INDEX SCAN 등의 단계가 포함된다. 실제 실행계획은 단순하게 보일 수 있으나, 내부적으로는 JOIN 및 조건 분기 처리가 수행된다.
특히 주의할 점은 MERGE가 기본적으로 OUTER JOIN을 수행한다는 점이며, 조건에 따라 UPDATE, DELETE, INSERT 중 하나가 선택되어 실행된다. 스칼라 서브쿼리나 복잡한 정보 제공 집합이 포함된 경우 실행계획은 더 복잡해질 수 있으며, 최적화를 고려해야 한다.
MERGE 문은 데이터 마이그레이션이나 대량의 UPSERT 작업에서 매우 유용하며, 기존 시스템의 배치 작업을 효과적으로 통합할 수 있는 수단으로 활용된다.
3.3. 실행계획의 제어
실행계획을 이해하고 있다는 것은 단순히 SQL 문장을 아는 것을 넘어서, 데이터베이스가 내부적으로 어떻게 처리 절차를 수행하는지 이해하고 있다는 것을 의미한다. 실행계획의 구조를 숙지하면, 옵티마이저가 선택한 접근 방식이 왜 그렇게 결정되었는지를 예측하고 제어할 수 있는 능력을 갖추게 된다.
옵티마이저는 매우 발전된 도구이지만, 항상 최적의 결정을 내리지는 않는다. 실무에서는 옵티마이저가 자동으로 선택한 실행계획이 오히려 비효율적일 때가 종종 있으며, 이럴 경우 사용자의 의도에 따라 힌트를 제공하거나 SQL 문 자체를 조정해 실행계획을 유도해야 할 필요가 있다.
이를 위해선 옵티마이저가 어떠한 기준으로 실행계획을 수립하는지를 정확히 파악해야 하며, 내부의 처리 과정을 충분히 이해하고 있어야 한다. 즉, 단순히 쿼리를 작성하는 수준이 아닌, 내부 동작 원리를 기반으로 의도한 실행계획을 이끌어낼 수 있어야 한다.
이러한 통찰력은 곧 높은 품질의 SQL 작성으로 이어지며, 데이터 모델링이나 데이터 패턴 설계, 인덱스 구성 등에 대한 깊은 이해를 바탕으로 한 고차원의 튜닝과 활용이 가능해진다.
특히 복잡한 실무 환경에서는 단일 쿼리의 성능만이 아니라 전체 시스템의 효율까지도 고려해야 하므로, 옵티마이저의 행동 원리와 제어 방법은 실무에 있어 중요한 역량 중 하나로 꼽힌다.
마지막으로, 이 장에서는 옵티마이저의 동작을 제어하기 위한 구체적인 방법으로 힌트를 사용하거나, SQL 구문 자체에 변화를 주는 방식 등 다양한 제어 기법들이 소개될 예정이다. 이로써 사용자는 옵티마이저에 전적으로 의존하지 않고, 능동적으로 실행계획을 컨트롤할 수 있는 기반을 마련할 수 있게 된다.
3.3.1. 힌트(Hint)의 활용 기준
힌트는 옵티마이저에게 우리의 의도를 전달할 수 있는 중요한 수단이다. 초기에는 옵티마이저의 실수를 보완하기 위해 사용되었으며, 마치 선수의 실수를 바로잡아주는 감독의 역할과도 유사하다. 힌트를 사용하는 사람과 사용하지 않는 사람의 실력 차이는 크고, 이것이 곧 실무에서의 차별점이 된다.
과거에는 옵티마이저가 초보적이었기 때문에 힌트 사용이 잦았지만, 기술의 발전으로 이제는 대부분의 경우 옵티마이저가 적절한 실행계획을 스스로 선택할 수 있게 되었다. 그럼에도 불구하고 특별한 상황에서는 여전히 힌트가 필요하며, 이는 옵티마이저가 우리의 의도를 정확히 이해하지 못할 때 유용하다.
힌트는 일종의 ‘주변 사람의 의견’과 같아서 반드시 따라야 하는 규칙은 아니며, 옵티마이저가 판단하여 무시할 수도 있다. 즉, 힌트는 선택 사항이며 옵티마이저에게 하나의 참고 정보로서 제공되는 것이다. 이러한 특성 때문에 힌트를 사용할 때는 항상 적절성과 필요성을 고려해야 한다.
최근에는 힌트를 무분별하게 사용하는 경우도 많아졌는데, 이는 오히려 실행계획의 품질을 떨어뜨릴 수 있다. 너무 많은 힌트를 사용하는 것은 비정상적인 상태일 수 있으며, 보통 SQL의 10% 이상에 힌트가 붙는다면 그 원인을 점검해야 한다. 특히 힌트가 없으면 실행이 되지 않거나 성능이 급격히 떨어지는 경우는 설계 자체에 문제가 있을 가능성이 높다.
또한, 힌트는 버전이 바뀌면 무효화되거나 다른 힌트로 대체될 수 있으므로 항상 최신 정보를 기준으로 사용해야 한다. 과거에 유효했던 힌트가 지금은 오히려 실행계획을 악화시키는 원인이 되기도 하므로, 힌트 사용 시에는 반드시 해당 DBMS 버전의 특성을 고려해야 한다.
실제 현장에서는 모든 쿼리에 일괄적으로 힌트를 삽입하는 사례도 존재하는데, 이는 비효율적인 방법이다. 예를 들어 FIRST_ROWS 힌트를 무조건 삽입하는 방식은 옵티마이저가 본래 선택했을 최적의 경로를 방해할 수 있으며, 이는 ‘자동차에 무조건 브레이크를 밟고 운전하는 것’과 같다.
결론적으로, 힌트는 옵티마이저를 정확하게 제어할 수 있는 유용한 도구이지만, 남용은 피해야 하며, 상황에 따라 전략적으로 사용하는 것이 바람직하다. 이 책에서 소개하는 힌트들은 특정 버전에 따라 다르게 적용될 수 있으므로, 적용 시 항상 버전 특성과 변화를 인식해야 한다.
3.3.2. 최적화 목표(Goal) 제어 힌트
쿼리를 수행할 때 옵티마이저가 생성하는 실행계획의 방향을 제어하기 위해, SQL 내부에서 힌트를 통해 옵티마이저 모드를 명시적으로 지정할 수 있다. 이러한 힌트들은 옵티마이저가 어떤 기준을 중심으로 실행계획을 수립할지를 결정하게 하며, 일반적으로 세 가지 목표 지향 힌트와 하나의 규칙 기반 힌트로 구분된다.
ALL_ROWS
ALL_ROWS 힌트는 쿼리의 전체 결과를 모두 처리하는 데 있어 최적의 처리량(Best throughput)을 확보하는 것을 목표로 한다. 이 힌트는 리소스를 최소로 사용하면서 전체 결과를 반환하는 실행계획을 수립하도록 유도한다. 이는 배치 처리나 대량의 데이터를 한꺼번에 조회할 때 유리하다.
예: SELECT /*+ ALL_ROWS */ ...
CHOOSE
CHOOSE 힌트는 액세스 대상 테이블들의 통계 정보를 활용해 자동으로 최적의 실행계획을 유도하는 방식이다. 테이블에 통계 정보가 존재할 경우에는 비용 기반 옵티마이저(CBO)가 적용되고, 없을 경우에는 규칙 기반 옵티마이저(RBO)가 사용된다. 사실상 현대의 데이터베이스에서는 대부분 CBO 환경이기 때문에 CHOOSE 힌트는 거의 ALL_ROWS와 유사하게 동작한다.
예: SELECT /*+ CHOOSE */ ...
FIRST_ROWS
FIRST_ROWS 힌트는 전체 결과보다 빠른 응답 속도(Best response time)를 중시하는 경우 사용되며, 가장 처음 또는 앞의 몇 개의 결과를 빠르게 반환하기 위한 실행계획을 유도한다. 웹 애플리케이션 등 사용자 상호작용 환경에서 유용하다. 인자로 반환 받을 행의 수를 명시할 수도 있다.
예: SELECT /*+ FIRST_ROWS */ ..., SELECT /*+ FIRST_ROWS(10) */ ...
RULE
RULE 힌트는 규칙 기반 옵티마이저(RBO)를 강제하는 힌트로, 조건절에 포함된 컬럼들의 인덱스 여부나 연산자 형태, 결합 순서 등에 따라 우선순위 규칙에 의해 실행계획을 수립하게 만든다. 현대에는 거의 사용되지 않으며, 주로 레거시 환경에서의 호환성을 위해 남아 있는 기능이다.
예: SELECT /*+ RULE */ ...
3.3.3. 조인 순서를 위한 힌트
조인 순서를 위한 힌트는 SQL 실행 시 조인의 순서를 조정하여 성능에 영향을 주기 위해 사용된다. 조인의 방식 자체도 중요하지만, 실제 실행 순서에 따라 성능 차이가 크기 때문에 적절한 순서 제어가 필요하다.
예를 들어, Nested Loops 조인에서 특정 인덱스를 활용하려면 조인 순서가 중요하게 작용하며, 이를 위해 조인 순서를 지정하는 힌트를 부여할 수 있다. 하지만 이러한 힌트는 반드시 인덱스 사용과 함께 조합되어야 효과를 볼 수 있다.
조인 순서 힌트는 특히 여러 테이블을 조인하는 경우에 유용하며, 테이블 간 조인 순서에 혼선이 생기지 않도록 제어하는 데 사용된다. 일반적으로는 Sort Merge 조인이나 Hash Join보다 작은 테이블을 먼저 처리하는 방식이 성능상 유리하다.
ORDERED
ORDERED 힌트는 FROM 절에 나열된 테이블의 순서대로 조인이 수행되도록 유도한다. 만약 LEADING 힌트와 함께 사용되면 LEADING 힌트는 무시된다. 하지만 ORDERED 힌트는 단순히 조인 순서만 제시할 뿐, 조인 방식까지는 지정하지 않기 때문에, 보통 USE_NL, USE_MERGE 같은 조인 방식 힌트와 함께 사용하는 것이 일반적이다.
LEADING
LEADING 힌트는 FROM 절의 순서와 상관없이 조인 순서를 제어하는 힌트이다. ORDERED 힌트와 달리 FROM 절의 순서를 변경하지 않아도 되므로 유연성이 높다. 예를 들어 LEADING(b c)라고 명시하면, 옵티마이저는 먼저 테이블 b를 액세스한 후 c를 조인하는 순서로 실행계획을 수립하게 된다. 단, ORDERED 힌트와 함께 쓰이면 LEADING 힌트는 무시된다.
3.3.4. 조인 방법 선택용 힌트
조인 방식을 선택하기 위한 힌트를 적용하기 전에, 각각의 조인 방식에 대한 장단점을 명확히 이해하고 실험해보는 과정이 우선되어야 한다. 이는 단순히 특정 힌트를 무조건적으로 사용하는 것이 아니라, 실제 상황에 따라 어떤 조인 방식이 최적인지를 파악하고 적용해야 한다는 의미다.
조인 방식의 선택은 인덱스의 유무, 인덱스 구성 상태, 처리 범위, 사용 빈도, 메모리 사용량, 작업 영역 크기(hash_area_size, sort_area_size) 등 다양한 요인에 영향을 받는다. 또한 실행 시점의 시스템 환경이나 OLTP, OLAP 등 시스템 유형에 따라 최적 방식이 달라질 수 있다.
USE_NL
USE_NL 힌트는 옵티마이저가 Nested Loops 방식으로 조인을 수행하도록 유도하는 역할을 한다. 이는 특정 테이블 간의 조인 방식만 지정하며, 조인 순서에는 영향을 주지 않는다.
NO_USE_NL
NO_USE_NL 힌트는 옵티마이저에게 지정된 테이블 간에는 Nested Loops 조인을 사용하지 말 것을 요청하는 힌트이다. 옵티마이저가 USE_NL을 적극적으로 무시할 수도 있기 때문에 이 힌트는 보다 강한 개입 수단으로 사용된다. Nested Loops 방식이 적합하지 않다고 판단될 때 사용한다.
USE_NL_WITH_INDEX
이 힌트는 Nested Loops 조인에서 외측 루프에 사용될 인덱스까지 명시하여 옵티마이저가 정확한 조인 계획을 수립하도록 유도한다. 과거에는 USE_NL과 INDEX 힌트를 따로 썼지만, 이제는 통합되어 사용된다. 인덱스를 명시하지 않으면 옵티마이저는 내부적으로 최적의 인덱스를 선택하거나 무시할 수도 있다.
USE_HASH
USE_HASH 힌트는 해시 조인 방식으로 조인이 수행되도록 유도한다. 해시 조인은 한쪽 테이블이 작고 메모리에 올릴 수 있을 때 유리하며, 빌드 입력과 프로브 입력의 선택이 중요하다. 옵티마이저는 통계 정보를 바탕으로 판단하며, ordered 힌트와 함께 사용하면 빌드 입력을 직접 지정할 수 있다.
NO_USE_HASH
NO_USE_HASH 힌트는 옵티마이저가 지정한 테이블들에 대해 해시 조인을 사용하지 말고 다른 방식의 조인을 고려하도록 유도한다. 해시 조인이 효율적이지 않거나 잘못 적용되어 성능 저하가 우려될 때 사용된다.
USE_MERGE
USE_MERGE 힌트는 옵티마이저가 Sort Merge 조인 방식을 사용하도록 유도하는 힌트이다. 일반적으로 정렬 가능한 컬럼들이 존재하며 대량 데이터 집합이 있을 때 유리하며, ordered 힌트와 함께 사용하면 더욱 명확한 조인 방향을 지정할 수 있다.
NO_USE_MERGE
NO_USE_MERGE 힌트는 옵티마이저가 특정 테이블들 간에 Sort Merge 조인을 하지 않도록 제한하는 힌트이다. 성능상 부적합한 경우에 다른 방식의 조인을 유도하기 위해 사용된다.
3.3.5. 병렬처리 관련 힌트
병렬처리 관련 힌트는 SQL 실행 시 시스템 자원을 다수 사용하는 경우, 그 사용 방식을 사용자 의도에 맞게 제어하기 위해 사용된다. 일반적으로 병렬처리는 시스템 자원을 최대한 활용하여 처리 시간을 단축하고자 하는 목적에서 활용되며, 옵티마이저는 가능한 모든 수단을 총동원하여 단위 SQL의 최적화를 시도하게 된다. 그러나 사용자의 요구 상황에 따라 자원의 사용 여부는 달라질 수 있으므로 힌트를 통해 제어할 필요가 있다.
병렬처리는 특히 대용량 데이터 처리, 대량의 DML 작업, 복잡한 집계 처리 등에 매우 유용하며, 기본값에만 의존하지 말고 명시적으로 병렬도나 방식 등을 힌트로 지정하는 것이 바람직하다. 병렬처리 힌트는 병렬 프로세스의 수나 병렬 처리 방식을 결정할 수 있으며, 해당 프로세스의 수는 시스템 부하에 직접적인 영향을 줄 수 있기 때문에 중요하다.
PARALLEL
PARALLEL 힌트는 테이블 또는 SQL 문이 병렬로 실행되도록 유도하는 힌트이다. 병렬로 수행할 수 있는 데이터량에 따라 병렬도(Degree)를 지정할 수 있으며, 해당 값이 없으면 시스템의 PARALLEL_THREADS_PER_CPU 설정값을 기반으로 자동 결정된다. PARALLEL(table, degree) 형태로 명시하며, DML 문장뿐만 아니라 SELECT, GROUP BY 절 등에도 적용 가능하다.
NOPARALLEL
NOPARALLEL 힌트는 기본적으로 병렬 수행이 가능한 테이블에 대해 병렬처리를 하지 않도록 옵티마이저에게 명시하는 힌트이다. 테이블에 병렬 설정이 되어 있더라도 이 힌트를 사용하면 옵티마이저는 직렬 실행 계획을 수립한다. 버전에 따라 NO_PARALLEL 힌트로 대체되기도 한다.
PQ_DISTRIBUTE
PQ_DISTRIBUTE 힌트는 병렬 조인의 성능을 최적화하기 위해 Producer와 Consumer 간의 데이터 분배 방식(Distribution)을 명시할 수 있도록 하는 힌트이다. 주로 슬레이브 프로세스 간에 조인할 데이터를 어떻게 나눌지를 설정하며, HASH, BROADCAST, PARTITION, NONE 등으로 지정할 수 있다.
예를 들어 PQ_DISTRIBUTE(table, HASH, BROADCAST)는 외측 테이블은 해시 방식으로, 내측 테이블은 브로드캐스트 방식으로 데이터를 분산시킨다는 의미다.
PARALLEL_INDEX
PARALLEL_INDEX 힌트는 파티션 인덱스에 대한 인덱스 범위 스캔을 병렬로 수행하도록 유도하는 힌트이다. 옵티마이저는 인덱스 생성 시 지정된 병렬 설정을 따르나, 이 힌트를 명시하면 해당 인덱스에 대한 병렬 범위 스캔이 강제된다.
NOPARALLEL_INDEX
NOPARALLEL_INDEX는 특정 인덱스에 대해 병렬 스캔을 하지 않도록 설정하는 힌트이다. 인덱스에 병렬 파라미터가 설정되어 있어도 이 힌트를 사용하면 옵티마이저는 직렬로 인덱스를 스캔한다. 버전에 따라 NO_PARALLEL_INDEX로 사용되기도 한다.
3.3.6. 엑세스 수단 선택을 위한 힌트
엑세스 수단 선택을 위한 힌트는 SQL 실행 시 옵티마이저가 어떤 방식으로 테이블에 접근할지를 명시적으로 유도하기 위해 사용된다. 대부분의 힌트는 앞서 소개된 액세스 방법 설명에 포함되어 있었기 때문에, 이 절에서는 간단하게 정리된 형태로 소개된다.
FULL
FULL 힌트는 특정 테이블에 대해 전체 테이블 스캔(Full Table Scan)을 수행하도록 유도하는 힌트다. 이 힌트를 사용하면 옵티마이저는 인덱스를 무시하고 테이블 전체를 읽게 된다.
HASH
HASH 힌트는 해시 클러스터 방식으로 저장된 테이블에 대해 해시 스캔 방식의 접근을 유도한다. 해시 조인 등에 활용되며, 옵티마이저가 해시 기반 접근 방식을 선택하도록 한다.
CLUSTER
CLUSTER 힌트는 클러스터링된 테이블을 클러스터 인덱스를 통해 액세스하도록 유도하는 힌트다. 해당 테이블이 클러스터에 정의되어 있어야 의미가 있다.
INDEX
INDEX 힌트는 특정 테이블 또는 컬럼에 대해 인덱스를 활용한 액세스를 유도하는 가장 일반적인 힌트이다. 뷰에서 테이블의 인덱스를 지정하는 것도 가능하다.
NO_INDEX
NO_INDEX 힌트는 옵티마이저가 특정 인덱스를 사용하지 않도록 제한하며, 다른 엑세스 방식을 선택하도록 유도한다. 인덱스를 제외할 때 자주 사용된다.
INDEX_ASC
INDEX_ASC 힌트는 인덱스 범위 스캔 시 오름차순(ascending)으로 스캔하도록 유도하는 힌트이다. 기본 정렬 기준이 필요한 경우에 사용된다.
INDEX_DESC
INDEX_DESC 힌트는 인덱스 범위 스캔 시 내림차순(descending)으로 스캔하도록 유도하는 힌트이다. 정렬 조건과 맞춰 성능을 향상시킬 수 있다.
INDEX_COMBINE
INDEX_COMBINE 힌트는 2개 이상의 인덱스를 비트맵 인덱스로 결합하여 액세스하도록 유도하는 힌트로, 일반 인덱스와 비트맵 인덱스 모두에 사용할 수 있다.
INDEX_FFS
INDEX_FFS 힌트는 전체 인덱스를 범위 스캔하여 마치 전체 테이블을 스캔하듯 데이터를 추출하는 Fast Full Scan 방식으로 유도하는 힌트다. 주로 다중 블록 I/O가 유리할 때 사용된다.
NO_INDEX_FFS
NO_INDEX_FFS 힌트는 INDEX_FFS와 반대로 인덱스의 Fast Full Scan 방식을 피하도록 유도하는 힌트이다.
INDEX_JOIN
INDEX_JOIN 힌트는 둘 이상의 인덱스를 활용하여 필요한 모든 컬럼을 가져오는 조인을 수행하게 하는 힌트이다. 전체 테이블 액세스 없이 인덱스만으로 쿼리를 처리할 수 있을 때 유용하다.
INDEX_SS
INDEX_SS 힌트는 인덱스 스킵 스캔 방식으로 접근하도록 유도하는 힌트로, 일반적으로 다중 컬럼 인덱스의 선두 컬럼이 빠졌을 때 적용 가능하다.
INDEX_SS_ASC
INDEX_SS_ASC는 인덱스 스킵 스캔 방식으로 오름차순 방향으로 인덱스를 읽도록 유도하는 힌트다.
INDEX_SS_DESC
INDEX_SS_DESC는 인덱스 스킵 스캔 방식으로 내림차순 방향으로 인덱스를 읽도록 유도하는 힌트다.
3.3.7. 쿼리형태 변형(Query Transformation)을 위한 힌트
쿼리형태 변형 힌트는 옵티마이저의 최적화 단계에서 수행되는 ‘질의 변환기’의 동작을 제어하는 데 사용된다. 이 변환기의 목적은 동일한 SQL을 보다 효율적인 형태로 바꾸어 더 나은 실행계획을 생성하는 것이다. 대부분의 경우 옵티마이저가 자동으로 변환을 수행하지만, 힌트를 통해 더 나은 실행계획을 유도할 수 있다.
USE_CONCAT
OR 조건 또는 IN 조건을 분리하여 각각 독립적인 실행 단위로 처리한 후, 그 결과를 연결(Concatenation)하는 실행계획을 유도하는 힌트이다. 효율적인 인덱스 활용이 가능한 경우에 유용하지만, 잘못 사용하면 비효율이 발생할 수 있다.
NO_EXPAND
OR 또는 IN 조건을 분리하지 않고 원래의 조건 그대로 실행계획을 수립하도록 유도한다. 이는 USE_CONCAT의 반대 개념이며, 조건 분리에 따른 비효율을 방지하고자 할 때 사용된다.
REWRITE
미리 생성된 실체뷰(Materialized View)를 활용하도록 쿼리를 재작성(Query Rewrite)하여 실행계획을 수립하도록 유도하는 힌트이다. 실체뷰가 명시된 경우 해당 실체뷰를 참조하며, 명시되지 않은 경우에도 옵티마이저가 적절한 실체뷰를 선택할 수 있다.
NOREWRITE
QUERY_REWRITE_ENABLED 파라미터가 TRUE로 설정되어 있더라도, 쿼리 재작성(Rewrite)을 수행하지 않도록 방지하는 힌트이다. 실체뷰의 적용을 원치 않는 경우 사용한다.
MERGE
뷰나 인라인뷰의 원본 테이블에 대해 문장을 병합(Merge)하여 직접 액세스하도록 변환을 유도하는 힌트이다. 병합이 가능할 때 효율적인 실행계획이 나올 수 있으며, 특히 복잡한 인라인뷰 처리에서 유용하다.
NO_MERGE
뷰 병합을 방지하여 뷰의 결과를 먼저 생성한 후, 이후의 연산을 수행하도록 한다. 복잡한 조건 처리 또는 실행계획 제어가 필요한 경우 사용된다.
STAR_TRANSFORMATION
스타 조인을 수행하도록 유도하는 힌트로, 비트맵 인덱스를 활용하여 다차원 데이터에 대한 조인 성능을 향상시키는 데 목적이 있다. 이 힌트는 옵티마이저가 내부적으로 스타 변형 전략을 사용하여 실행계획을 수립하게 만든다.
NO_STAR_TRANSFORMATION
스타 변형 조인을 하지 않도록 옵티마이저에 지시하는 힌트이다. 스타 변형으로 인한 실행계획의 부적절한 적용을 방지하고자 할 때 사용한다.
FACT
스타 변형 조인에서 어떤 테이블을 '팩트 테이블'로 인식시키기 위한 힌트이다. 기본적으로 옵티마이저가 자동 판단하지만, 명시적으로 지정하고자 할 때 사용된다.
NO_FACT
옵티마이저가 지정한 테이블을 팩트 테이블로 인식하지 않도록 제어하는 힌트이다. 특정 테이블이 부적절하게 팩트 테이블로 간주되는 것을 방지할 수 있다.
UNNEST
서브쿼리를 메인 쿼리와 조인 구조로 변환하여 실행계획을 수립하도록 유도하는 힌트이다. 서브쿼리의 분리 실행을 피하고 조인 방식으로 효율을 높일 수 있다.
NO_UNNEST
서브쿼리를 그대로 유지하고, 조인 구조로 변형하지 않도록 한다. 옵티마이저가 서브쿼리를 강제로 변형하지 않도록 방지할 수 있다.
3.3.8 기타 힌트
APPEND
APPEND 힌트는 INSERT 문에서 사용되며, 데이터를 입력할 때 ‘DIRECT-PATH’ 방식으로 처리되도록 유도한다. 이는 SGA를 거치지 않고 직접 저장 공간에 데이터를 기록하게 하며, 빠른 입력 처리 성능을 확보할 수 있다. 단, 입력된 데이터는 항상 High Water Mark 이후의 영역에 저장된다.
NOAPPEND
NOAPPEND 힌트는 일반적인 INSERT 방식인 ‘CONVENTIONAL-PATH’로 수행되도록 유도한다. 이는 기본적으로 직렬 모드에서 수행되며, 병렬모드에서는 사용되지 않는다.
CACHE
CACHE 힌트는 테이블을 스캔할 때 읽어온 블록을 LRU 리스트의 가장 최근 위치에 등록하여, 계속해서 메모리 내에 유지되도록 하는 캐시 관련 힌트다. 주로 크기가 작은 테이블에 사용되며, 이미 정의된 Default Caching을 무시하고 명시적으로 메모리 유지 전략을 지정할 수 있다.
NOCACHE
NOCACHE 힌트는 반대로, 읽어온 블록을 LRU 리스트의 마지막으로 보내 메모리에서 우선 제거되도록 유도하는 힌트다. 이 힌트는 캐시 메모리를 효율적으로 운영하기 위한 수단이다.
CARDINALITY
CARDINALITY 힌트는 옵티마이저에게 해당 쿼리 전체 또는 일부분에 대한 카디널리티(예상 결과 건수)를 제공하여, 실행계획 수립 시 참고할 수 있도록 한다. 특정 테이블을 지정하지 않을 경우 전체 쿼리를 수행한 결과로 간주된다.
CURSOR_SHARING_EXACT
CURSOR_SHARING_EXACT 힌트는 옵티마이저가 바인드 변수 값을 그대로 인식하여 공유 실행계획을 사용하지 않도록 한다. SQL 조건절에 리터럴 값이 그대로 표현되어야 할 경우에 사용되며, 파라미터 CURSOR_SHARING을 무시하고 EXTACT 모드로 실행되도록 강제한다.
DRIVING_SITE
DRIVING_SITE 힌트는 원격 테이블과의 조인 시, 분산 쿼리의 수행 위치를 지정하여 쿼리 성능을 최적화하는 데 사용된다. 특히 조인 대상이 네트워크 지연이나 성능에 민감할 경우 유용하다.
DYNAMIC_SAMPLING
DYNAMIC_SAMPLING 힌트는 통계 정보가 부재하거나 오래되어 신뢰도가 낮을 때, 옵티마이저가 SQL 수행 시점에 실시간으로 통계 값을 수집할 수 있도록 유도한다. 레벨 값은 0~10까지 줄 수 있으며, 값이 클수록 더 많은 데이터를 샘플링한다.
PUSH_PRED
PUSH_PRED 힌트는 뷰 인라인뷰의 외부에 있는 조인 조건을 뷰 내부로 밀어 넣어 조건을 먼저 적용하도록 유도한다. 이렇게 하면 조건을 빨리 걸어 성능을 높일 수 있다.
NO_PUSH_PRED
NO_PUSH_PRED 힌트는 위와 반대로 뷰 인라인뷰 외부의 조건을 뷰 내부로 밀어 넣지 않고, 뷰 결과를 먼저 계산한 후 조인을 수행하도록 유도한다. 조건이 나중에 적용되어야 유리할 때 사용된다.
PUSH_SUBQ
PUSH_SUBQ 힌트는 가능하다면 서브쿼리를 먼저 수행하도록 유도하여 조기 필터링을 가능하게 하고, 결과를 메인 쿼리에 공급함으로써 효율적인 실행계획을 생성할 수 있게 한다. 단, 서브쿼리가 메인 쿼리 조건절 내에서 직접 결합되지 않으면 무의미할 수 있다.
NO_PUSH_SUBQ
NO_PUSH_SUBQ 힌트는 서브쿼리를 가장 마지막에 수행하도록 유도하여, 부하를 분산하고 다른 조건들이 먼저 처리되도록 한다. 조인의 순서에 따른 최적화를 목적으로 사용된다.
QB_NAME
QB_NAME 힌트는 쿼리 블록에 이름을 부여하여 다른 힌트에서 해당 쿼리 블록을 참조할 수 있도록 한다. 동일한 이름의 쿼리 블록이 여러 개 존재하지 않도록 주의해야 하며, PLAN_TABLE에서 해당 이름을 확인할 수 있다.
REWRITE_OR_ERROR
REWRITE_OR_ERROR 힌트는 재작성 가능한 실체 뷰가 없을 경우 실행을 중단시키고, ORA-30393 오류를 발생시키도록 유도한다. 이를 통해 실체 뷰의 적용 여부를 사전에 강제하고 예외 상황을 제어할 수 있다.
Start: 25.02.25
End: 25.04.09
'프로그래밍(Web) > 공부일기' 카테고리의 다른 글
| [바미] 새로 쓴, 대용량 데이터 베이스 솔루션 Vol.1 - 2부 - 1장 부분범위처리 (0) | 2025.06.04 |
|---|---|
| [바미] 새로 쓴, 대용량 데이터 베이스 솔루션 Vol.1 - 4장 인덱스 수립 전략 (0) | 2025.04.21 |
| [바미] 새로 쓴, 대용량 데이터 베이스 솔루션 Vol.1 - 3장 SQL의 실행계획 - 1 (0) | 2025.04.03 |
| [바미] 옵티마이저(Optimizer)란 무엇인가? (0) | 2025.02.02 |
| [바미] 새로 쓴, 대용량 데이터 베이스 솔루션 Vol.1 - 2장 인덱스 유형과 특징 (0) | 2024.12.30 |
