4장. 옵티마이저 연산자
학습 목표
이번 장을 완료하면 다음과 같은 항목을 수행 할 수 있다.
- 대부분의 SQL 연산자를 설명 할 수 있다.
- 사용 가능한 액세스 경로를 나열 할 수 있다.
- 조인 작업의 수행 방법을 설명 할 수 있다.
행 원본(Row Source) 작업
행 원본은 반복하여 행들의 집합을 처리하고 행 집합을 생성하는 반복적인 제어 구조이다. 행 원본은
다음과 같이 분류 할 수 있다.
① 단항(unary) 연산 : 액세스 경로와 같이 오직 하나의 입력에 대해서만 작업하는 연산
② 이항(Binary) 연산 : 조인과 같이 두 개의 입력에 대해서 작업하는 연산
③ N항(N-ary) 연산 : 관계형 연산자와 같이 여러 개의 입력에 대하여 작업하는 연산
액세스 경로는 데이터베이스의 데이터에 접근하는 방법이다. 일반적으로 테이블의 행 집합에서 작은 부분
집합을 읽어 오는 문장들은 인덱스 액세스 경로를 사용하여야 한다. 반면, 테이블의 많은 부분을
액세스하는 경우에는 전체 스캔이 좀더 효율적이다. 높은 선택도를 가지며 짧게 실행 되는 SQL 문장으로
구성된 OLTP 애플리케이션은 인덱스 액세스 경로의 사용에 따라 애플리케이션의 성격이 결정된다.
한편, DSS는 파티션 테이블을 사용하며, 해당 파티션을 전체 스캔(full scan) 하는 경향이 많다.
주요 저장 구조와 액세스 경로
그림 4.1
모든 행들은 위 그림에서 언급한 방법 중 하나의 방법으로 검색되고 읽혀진다.
일반적으로, 테이블의 많은 부분을 액세스하는 경우에는 전체 스캔(full scan)이 좀 더 효율적인 반면에
테이블의 작은 부분 집합을 잇는 문장들은 인덱스 액세스 경로(index access path)를 사용하여야 한다.
실행 계획을 결정하려면 옵티마이저는 각각의 후보 실행 계획에 대하여 비용을 계산 하고 가장 낮은
비용을 갖는 실행 계획을 선택하여야 한다. 그림에서는 아직 언급되지 않은 클러스터, IOT(Index-
Organized Table), 파티션에 대한 특별한 형태의 테이블 액세스 경로들이 있다.
클러스터는 테이블의 데이터를 저장하는 또 다른 방법이다. 클러스터는 공통 컬럼을 공유하면서 함께
자주 사용되는 테이블들을 동일한 데이터 블록에 저장한 테이블 집단이다. 예를 들어, EMP 와 DEPT
테이블은 DEPTNO 컬럼을 공유한다. EMP와 DEPT 테이블을 클러스터링하면, 오라클은 EMP와 DEPT
테이블에서 DEPTNO 컬럼의 값이 같은 행들을 물리적으로 동일한 블록에 저장한다.
해시 클러스터는 같은 해시 키 값을 갖는 행들을 함께 저장한 단일 테이블 클러스터이다. 오라클은
수학적 해시 함수를 사용하여 클러스터 내부의 행 위치를 선택한다. 동일한 키 값을 갖는 모든 행들이
디스크 상에서 같은 블록에 함께 저장된다. 특별한 저장 구조 형태에서의 액세스 경로는 뒷 부분에서 논의한다.
전체 테이블 스캔(Full Table Scan)
그림 4.2
전체 테이블 스캔은 테이블의 모든 행을 순차적으로 읽고 조건절을 만족하지 못하는 행들을 필터링한다.
전체 테이블 스캔을 수행하면, 테이블의 모든 행이 삭제되었더라도 테이블의 HWM(High Water Mark)
이하, 모든 포맷된 블록들을 스캔한다. 각 블록들은 오직 한 번만 읽혀지며, HWM은 사용된 저장공간의
양 또는 데이터를 저장하기 위해 포맷된 저장 공간을 나타낸다. 이 후, 적용 가능한 필터를 이용하여,
읽혀진 행들이 WHERE 절을 만족하고 있는지 확인한다.
Explain Plan 결과의 Predicate Information 영역에서 필터 조건을 확인 할 수 있으며, 여기서는
EMP.ENAME=’King’을 만족하는 행들만 리턴하는 필터가 적용되었다.
전체 테이블 스캔은 테이블 내의 모든 포맷된 블록을 읽기 때문에 물리적으로 인접한 블록들을 읽어온다.
이 작업은 여러 개의 블록들을 동시에 읽는 입출력(I/O) 호출을 이용하기 때문에 성능 상의 장점을 얻을
수 있다. 읽기 요청의 크기는 단일 블록에서부터 초기화 파라메터인
DB_FILE_MULTIBLOCK_READ_COUNT에 설정된 블록 개수 사이의 값을 가질 수 있다.
참고 : Oracle V6에서는 전체 테이블 스캔과 인덱스에 의한 읽기 간의 블록 처리 방식이 별다른 차이가
없었기 때문에 전체 테이블 스캔은 읽어온 블록들을 버퍼 캐시에 가득 채웠다. 하지만, Oracle V7부터
전체 테이블 스캔에 의해서 읽혀지는 블록들은 버퍼 캐시 내에서 제한된 공간만을 채우도록
제한되었다. 현재, 해시 조인과 병렬 쿼리는 효율성을 위해 전체 테이블 스캔만 사용한다.
전체 테이블 스캔(Full Table Scan) : 사용 환경
옵티마이저는 다음과 같은 상황에서 전체 테이블 스캔을 선택한다.
① 인덱스가 없는 경우 : 만약, 해당 쿼리가 기존 인덱스를 사용 할 수 없고, ROWID 필터 또는 클러스터 액세스
경로의 사용이 불가능하다면, 전체 테이블 스캔을 선택한다. 예를 들어, 해당 쿼리에서 인덱스가 정의된 컬럼에
함수가 존재한다면, 옵티마이저는 해당 인덱스를 사용 할 수 없고, 대신 전체 테이블 스캔을 사용한다. 만약,
대소문자를 구분하지 않고 인덱스를 사용해야 한다면, 대소문자를 혼합하여 사용하지 말거나 UPPER(last_name)와
같은 함수 기반 인덱스를 생성해야 한다.
② 대량의 데이터(낮은 선택도) : 만약, 옵티마이저가 테이블 내의 수 많은 블록에 액세스해 야 된다고 판단한다면,
옵티마이저는 인덱스를 사용 할 수 있더라도 전체 테이블 스캔을 선택 할 수도 있다.
③ 소용량 테이블 : DB_FILE_MULTIBLOCK_READ_COUNT 파라메터에 지정된 블록 개수보다 테이블의 HWM 아래
블록 개수가 적다면, 전체 테이블 스캔의 비용이 인덱스 범위 스캔 의 비용보다 낮을 것이라고 판단하게 된다. 이는
쿼리가 테이블에서 읽어 오는 행의 개 수나 인덱스의 존재 여부와 관련이 없다.
④ 높은 병렬 처리 정도 : 테이블에 대한 높은 병렬 처리 정도는 옵티마이저가 범위 스캔보 다 전체 테이블 스캔을
선호하도록 왜곡 할 수 있다. 병렬 처리 정도를 결정하려면 ALL_TABLES에서 해당 테이블의 DEGREE 컬럼을 조사하여야 한다.
⑤ 전체 테이블 스캔 힌트 : FULL(테이블 별칭) 힌트를 사용하여 옵티마이저가 전체 테이블 스캔을 수행하도록 지시 할 수 있다.
ROWID 스캔
그림 4.3
행의 rowid는 해당 행을 포함하고 있는 데이터 파일과 데이터 블록을 나타내며, 해당 블록에서 행의 위치를 가리킨다.
해당 행의 rowid를 지정하여 행을 검색하는 것은 단일 행을 읽기 위한 가장 빠른 방법이다. 그 이유는 해당 행의 정확한
저장 위치를 지정하였기 때문이다.
테이블을 rowid로 액세스하려면, 먼저 선택된 행들의 rowid를 알아야 하는데, 이는 문장의 WHERE
구문에 지정하거나 테이블에 정의된 하나 이상의 인덱스를 스캔하여 얻어 올 수 있다. 그런 다음,
시스템은 해당 rowid를 기반으로 테이블에서 선택된 행들을 찾을 수 있다.
대부분의 경우에 옵티마이저는 인덱스로부터 이러한 rowid를 읽어와 사용한다. 문장 내의 모든 컬럼들이
인덱스에 존재하지 않으면 테이블 액세스가 발생 할 수도 있다. 인덱스 스캔이 수행 될 때마다 rowid에
의한 테이블 액세스가 반드시 필요하지는 않다. 만약, 인덱스가 문장에서 필요로 하는 모든 컬럼을
포함하고 있다면, rowid에 의한 테이블 액세스는 발생하지 않을 수도 있다.
rowid는 데이터가 저장되어 있는 위치를 표현하는 시스템의 내부 표현이다. 위치를 기반으로 직접
데이터에 액세스하는 것은 행 이주 현상과 행 연결 현상 또는 Export 및 Import 작업으로 행들이 이동
할 수 있으므로 추천하지는 않는다.
참고 : 행 이주 현상으로 인하여, rowid는 때때로 실제 행의 위치가 아닌 다른 주소를 가리킬 수도
있는데, 그로 인하여 행을 찾기 위해 하나 이상의 블록을 액세스하게 된다. 예를 들어, 행을 업데이트하게
되면 해당 행은 원본 블록에서 다른 블록으로 위치가 바뀔 수도 있다. 그러나, rowid는 여전히 원본
블록의 주소를 가지고 있게 된다.
샘플 테이블 스캔
그림 4.4
샘플 테이블 스캔은 간단한 테이블 또는 조인과 뷰가 포함된 문장과 같이 복잡한 SELECT 문장으로부터
데이터를 랜덤하게 샘플링하여 읽어온다. 이러한 액세스 경로는 문장의 FROM 절에 SAMPLE 구문 또는
SAMPLE BLOCK 구문을 포함하면 선택 된다. 샘플 테이블 스캔을 수행하기 위해 SAMPLE 구문으로
행들을 샘플링하면, 시스템은 테이블 내의 행들을 지정된 퍼센트만큼 읽어 온다. 샘플 테이블 스캔을
수행하기 위해 SAMPLE BLOCK 구문으로 블록을 샘플링하면, 시스템은 테이블 블록들을 지정된
퍼센트만큼 읽어온다.
① SAMPLE 옵션 : 샘플 테이블을 수행하기 위해 행들을 샘플링하면 테이블 내의 행들을 지정된 퍼센트만큼
읽어온 다음, 이 행들이 문장의 WHERE 절을 만족하는지 조사한다.
② SAMPLE BLOCK 옵션 : 샘플 테이블을 수행하기 위해 블록들을 샘플링하면 테이블 내의 블록들을 지정된
퍼센트만큼 읽어온 다음, 읽어온 블록들 내의 행들이 문장의 WHERE 절을 만족하는지 조사한다. 샘플 퍼센트는
숫자 값으로서 전체 행 또는 블록들 중에 샘플에 포함될 퍼센트를 지정한다. 샘플 값은 반드시 [0.000001, 99.999999] 범위
내에 있어야 한다. 이 퍼센트는 블록 샘플링의 경우, 각 행 또는 행들의 각 클러스터가 샘플로 선택될 확률이다.
이것은 데이터베이스가 정확히 테이블의 행들 중에 sample_percent 만큼을 읽어오지는 않는다는 것을 의미한다.
③ SEED seed_value : 이 구문은 매번 실행 할 때마다 동일한 샘플을 읽도록 데이터베이스에게
지시한다. seed_value는 반드시 0 이상 4294967295 이하이어야만 한다. 이 구문을 생략하면, 샘플
결과는 매번 실행 할 때마다 달라진다. 행 샘플링에서 주어진 샘플 크기만큼 행들을 얻기 위해 다수의
블록이 액세스되지만, 그 결과의 정확도는 매우 높다. 블록 샘플은 비용이 매우 낮지만 부정확해서
샘플이 많거나 적을 수 있다.
참고 : 블록 샘플링은 전체 테이블 스캔(full table scan) 또는 인덱스 고속 전체 스캔(index fast full scan)
을 수행할 때만 가능하다. 만약, 더욱 효율적인 실행 경로가 존재한다면, 오라클 데이터베이스는 블록
샘플링을 수행하지 않는다. 만약, 특정 테이블 또는 인덱스에 대하여 블록 샘플링이 수행되도록
보장하려면 FULL 또는 INDEX_FFS, 힌트를 사용하도록 한다.
인덱스 : 개요
인덱스는 테이블의 데이터와 논리적 및 물리적으로 분리된 별도의 데이터베이스 객체이다. 인덱스가
테이블과 독립적인 구조를 가지려면 저장 공간이 필요하다. 책의 인덱스가 정보를 빠르게 검색하는데
도움을 주는 것처럼 오라클 데이터베이스의 인덱스는 테이블의 데이터에 대하여 빠른 액세스 경로를
제공한다. 오라클 데이터베이스는 SQL 문장이 요구하는 데이터에 빠르게 액세스하기 위해 인덱스를 사용
할 수도 있으며, 무결성 제약조건을 강화하기 위해서도 인덱스를 사용 할 수 있다. 시스템은 관련
데이터가 변경되면 인덱스를 자동으로 유지 관리한다. 아무 때나 인덱스를 생성 및 삭제 할 수 있으며,
인덱스를 삭제해도 모든 애플리케이션은 제대로 동작한다. 그러나, 인덱스에 의한 데이터 액세스는
이전보다 느려지게 된다. 인덱스는 고유 또는 비고유 인덱스로 생성 될 수 있다.
결합 인덱스라고 부르기도 하는 복합 인덱스는 테이블 내의 여러 컬럼(최대 32개)들을 묶어서 정의한
인덱스이다. 복합 인덱스 내의 컬럼은 특별한 순서가 없으며, 반드시 테이블 내에서 인접된 컬럼이
되어야 할 필요도 없다.
표준 Index의 경우, Database는 액세스 시간이 동등하게 균형을 이룬 B*-트리 Index를 사용한다.
B*-트리 Index는 일반, 리버스 키, 내림차순, 함수 기반 Index로 정의 될 수 있다.
① B*-트리 Index : 현재까지 가장 일반적인 형태의 Index이다. 이진 트리 구조와 흡사하다. B*-트리 Index는
키에 의한 고속 액세스를 제공하여 특정 행 또는 행의 범위에 빠르게 접근하며, 정확한 행을 검색하기 위해서
단지 몇 번의 읽기 횟수만을 필요로 한다. 여기서, B*-트리의 B는 binary가 아닌 balanced를 의미한다.
② 내림차순 Index : Index 구조 내에서 데이터가 오름차순이 아닌 내림차순으로 정렬된 Index이다.
③ 리버스 키 Index : 키의 바이트를 역순으로 배열한 B*-트리 Index이다. 리버스 키 Index는 키 값이 계속
증가하는 경우에 Index 엔트리를 균등하게 분포하기 위해 사용된다. 예를 들어, 기본 키에 사용 할 값을 만들어
내기 위해 시퀀스를 사용한다면, 이 시퀀스는 987500, 987501, 987502 등의 순서로 값을 만들어 낸다. 리버스 키
Index의 경우, Database는 987500, 987501, 987502 대신 005789,105789, 205789의 값으로 Index를 구성한다.
그 결과, 리버스 키는 서로 다른 위치에 저장되므로 특정블록에 발생하는 경합을 감소시킬 수 있다. 그러나, 오직
등가 조건(=)을 사용하는 경우에만 리버스 키 Index를 사용 할 수 있다.
④ Index 키 압축 : 압축 키 Index의 기본 개념은 모든 Index 엔트리를 접두사와 접미사로 분리할 수 있다는 것이다.
접두사는 결합 Index의 선두 컬럼들로 구성되며 많은 반복이 발생한다. 접미사는 Index 키의 마지막 컬럼들로 구성되며,
동일한 접두사 내에서는 고유한 값을 갖는다. 이러한 방식의 압축은 ZIP 파일과 동일한 방식의 압축이 아니며, 연결 Index로부터
반복되는 컬럼들을 제거하는 선택적 압축이다.
⑤ 함수 기반 Index : 행의 컬럼 값이 아닌 컬럼 또는 컬럼들에 대하여 함수의 결과 값을 저장하는 B*-트리 또는
Bitmap Index이다. 이러한 컬럼들은 가상(파생 또는 숨어 있는) 컬럼으로 볼 수 있으며, 테이블에 물리적으로
저장되지 않는 컬럼이다. 이러한 가상 컬럼에 대해서도 통계 자료를 수집 할 수 있다.
⑥ IOT(Index-organized tables) : B*-트리 구조에 저장된 테이블이다. 힙 기반 테이블에 저장 된 행들은 특별한
순서 없이 저장되지만(사용 가능한 공간에 데이터가 저장되는 방식), IOT 내부의 데이터는 기본 키에 의해
정렬되어 저장된다. IOT는 애플리케이션의 관점에서 봤을 때, 테이블처럼 동작한다.
⑦ Bitmap Index : 일반 B*-트리 Index에서는 Index 엔트리와 행이 1:1의 관계를 갖는다. 즉, 하나의 Index 엔트리는
하나의 행을 가리킨다. Bitmap Index에서는 하나의 Index 엔트리가 Bitmap을 사용하여 동시에 많은 행들을 가리키게 된다.
Bitmap Index는 인기 전용의 반복적인 데이터(테이블의 전체 행에서 고유한 값이 많지 않은)에 적합하다. 비트 맵 Index는
OLTP Database에서는 절대 사용되지 않는데, 동시성 관련 문제가 존재 하기 때문이다.
⑧ Bitmap 조인 Index : Bitmap 조인 Index는 두 개 이상의 테이블을 조인한 결과에 대하여 정의된 Bitmap Index이다.
Bitmap 조인 Index는 테이블들의 실제 조인을 방지하기 위해 사용되거나 조인해야 할 데이터의 양을 급격히 감소시켜야
할 때 사용하지만 몇 가지 제한 사항이 있다. Bitmap 조인 Index를 사용하는 쿼리는 비트별 연산을 이용하여 빠른 속도를 보여준다.
⑨ 애플리케이션 도메인 Index : 이 Index는 패키지를 이용하여 Database 자체 또는 Database 외부에
만들어지는 Index이다. 사용자가 Optimizer에게 Index의 선택도 및 실행 비용을 알려주면, Optimizer는
해당 정보를 기반으로 Index의 사용 여부를 결정한다.
일반 B*-트리 Index
그림 4.5
각 B*-트리 Index는 시작 지점으로 루트 블록을 갖는다. 엔트리의 개수에 따라 여러 개의 리프 블록을 가질 수 있는 여러 개의
브랜치 블록들이 존재 할 수 있다. 리프 블록들은 Index의 모든 컬럼들과 관련 데이터 세그먼트 내에서 행의 위치를 나타내는 ROWID를 포함하고 있다.
직전 및 직후의 블록을 나타내는 포인터로 리프 블록이 연결되어 왼쪽에서 오른쪽 또는 그 반대 방향으로 이동 할 수 있다.
Index는 항상 균형을 이루며, 위에서 아래로 성장한다. 특별한 경우에서는 균형 알고리즘이 불 필요하게 B*-트리의 높이를
증가시킬 수도 있다. Index를 재구성하는 것도 가능하며, ALTER INDEX … REBUILD | COALESCE 명령으로 수행 할 수 있다.
B*-트리 Index의 내부 구조는 Index된 값을 빠르게 액세스 할 수 있도록 해준다. 시스템은 인덱스의 리프 블록에서 ROWID를
읽고 직접 행에 접근 할 수 있다.
참고 : 하나의 Index 엔트리가 가질 수 있는 최대 크기는 데이터 블록 크기의 약 1/2이다.
Index 스캔(Index Scan)
그림 4.6
Index 스캔은 다음과 같은 유형의 하나가 될 수 있다.
행은 문장의 WHERE 절에 지정된 Index 컬럼 값을 이용하여 Index를 통해 읽혀진다. Index 스캔은
Index에 정의된 하나 이상의 컬럼들에 저장된 값을 기반으로 하여 데이터를 읽는다. 인덱스 스캔을
수행하면, 시스템은 Index에서 문장에 의해 액세스 되는 Index 컬럼 값을 검색한다. 만약, 문장이
Index에 저장 되어 있는 컬럼들만 액세스한다면, 시스템은 테이블이 아닌 Index에 서만 컬럼 값을 읽게 된다.
Index는 Index 값 뿐만 아니라 테이블 내에서 그 값을 포함하는 행들의 rowid도 가지고 있다. 그러므로,
해당 문장이 Index 컬럼 뿐만 아니라 다른 컬럼도 액세스 한다면, 시스템은 rowid에 의한 테이블 액세스
또는 클러스터 스캔을 이용하여 테이블에서 해당 행을 찾을 수 있다.
참고 : 위 그림은 Index 스캔에 의해 읽혀진 rowid를 이용하여 테이블로부터 4건의 행을 읽어오는 경우이다.
Index 고유 스캔(Index Unique Scan)
그림 4.7
Index 고유 스캔은 하나의 ROWID를 읽는다. 문장이 UNIQUE 또는 PRIMARY KEY 제약조건을 포함하고
있어서 오직 하나의 행만이 액세스 됨을 보장 할 수 있다면, 시스템은 고유 스캔을 수행 한다. 이 액세스
경로는 고유 Index의 모든 컬럼들이 등가(=) 조건으로 지정되었을 경우에 사용 된다. 키 값과 ROWID는
Index로부터 읽혀지고 테이블의 행들은 ROWID를 이용하여 리턴된다.
실행 계획의 Predicate Information 영역에서 액세스 조건을 확인 할 수 있다. 여기서는 시스템이
EMPNO=9999를 만족하는 행들만 액세스하였다.
참고 : 필터 조건은 인출 작업 이후 행들을 필터링한 다음, 필터링된 행을 출력한다.
Index 범위 스캔(Index Range Scan)
그림 4.8
Index 범위 스캔은 선택적인 데이터 액세스를 위해 가장 많이 사용되는 작업이다. 이 작업은 범위의 상한
값과 하한 값이 모두 존재(bounded)하는 경우 또는 어느 한 쪽만 있는 경우(unbounded) 에 모두 사용
할 수 있다. 데이터는 Index 컬럼의 오름차 순으로 리턴된다. 동일한 값을 갖는 여러 행들은 ROWID의
오름차순으로 정렬된다.
Optimizer는 Index 내에서 하나 이상의 선두 컬럼들이 col1 = : b1, col1 < :b1, col1 > :b1 또는 이러한
조합들로 지정되었을 때, 컬럼 값을 찾기 위해 범위 스캔을 사용한다.
와일드카드 검색(col1 like ‘%ASD’)은 선두 컬럼이 없으므로 범위 스캔을 수행 할 수 없다.
범위 스캔은 고유 또는 비 고유 Index를 사용 할 수 있다. 범위 스캔은 Index 컬럼이 ORDER BY/GROUP
BY 컬럼에 포함되었고, 컬럼 값이 NOT NULL을 보장한다면 정렬 작업을 회피 할 수도 있다. 그렇지 않은
경우에는 정렬을 수행하여야 한다. 내림차순 Index 범위 스캔은 데이터가 내림차순으로 리턴된다는 점을
제외하면 Index 범위 스캔과 동일하다. Optimizer는 내림차순 정렬 구문이 Index를 사용 할 수 있다면
내림차순 인덱스 범위 스캔을 수행한다.
위 예제는 I_DEPTNO Index를 이용하여, EMP.DEPTNO=10인 조건을 만족하는 행들을 액세스한다.
시스템은 ROWID를 읽고, EMP 테이블에서 다른 컬럼들을 인출하며, 인출된 행들에 대하여
EMP.SAL > 1000 조건으로 필터링한 다음, 출력한다.
Index 범위 스캔(Index Range Scan) : 내림차
그림 4.9
오름차순에 의한 Index 범위 스캔에 추가하여 시스템은 그림에서 보인 것처럼 역순으로 Index를 스캔
할 수도 있다. 예제는 DEPTNO 컬럼을 내림차순으로 EMP 테이블을 인고 행들을 읽어온다. 실행 계획의
ID 2에서 DESCENDING 작업을 확인 할 수 있다.
참고 : 오름차순에 의한 Index 범위 스캔이 디폴트이다.
내림차순 Index 범위 스캔(Index Range Scan)
그림 4.10
내림차순 Index 범위 스캔은 데이터가 내림차순으로 리턴된다는 점을 제외하고 Index 범위 스캔과
동일하다. 내림차순 Index는 Index 구조 내에서 값들이 작은 값에서 큰 값이 아닌 큰 값에서 작은 값
순서로 데이터가 정렬되어 있는 것이다. 일반적으로 이러한 스캔은 가장 최근의 데이터가 가장 먼저
리턴되도록 하거나, 그림과 같이 지정된 값보다 작은 값을 검색하는 경우에 사용된다.
Optimizer는 내림차순 정렬이 내림차순 Index를 사용 할 수 있다면 내림차순 Index 범위 스캔을 사용한다.
이러한 액세스 경로를 강제로 사용하려면 INDEX_DESC(테이블 별칭 Index 이름) 힌트를 사용하면 된다.
참고 : 시스템은 내림차순 Index를 함수 기반 Index로 처리한다. DESC로 표시된 컬럼들은 인덱스
구조 내에서 특별한 내림차순으로 저장된다. 이러한 Index 구조는 SYS_OP_UNDESCEND 함수를
사용하여 다시 역순으로 배열된다.
Index 범위 스캔(Index Range Scan) : 함수 기반
그림 4.11
함수 기반 Index는 B*-트리 또는 Bitmap 구조로 저장 될 수 있다. 이러한 Index는 UPPER와 같은
함수에 의해 변화된 컬럼들 또는 col1 + col2와 같이 연산식을 포함한다. 함수 기반 Index를 사용하여
Index 내에 연산 작업이 과도한 연산식을 저장 할 수 있다. 변환된 컬럼 또는 연산식에 함수 기반
Index를 정의하면 해당 함수 또는 연산식이 WHERE 절 또는 ORDER BY 절에 사용된 경우, Index를
이용하여 해당 결과 값을 리턴 받을 수 있다. 그 결과, 시스템은 SELECT 및 DELETE 문장을 처리 할 때,
연산식을 계산 할 필요가 없어진다. 그러므로, 함수 기반 Index는 자주 실행되는 SQL 문장의 WHERE
또는 ORDER BY 구문에 연산된 컬럼 또는 연산식을 포함하는 경우에 많은 장점이 있다.
예를 들어, UPPER(컬럼명) 또는 LOWER(컬럼명)으로 정의된 함수 기반 Index는 대소문자 구분 없는 검색을 가능하게 해준다.
Index 전체 스캔(Index Full Scan)
그림 4.12
조건 절에서 Index 내의 컬럼들 중 하나를 참조하면 전체 스캔이 가능하다. 조건 절은 Index 드라이버
(선두 컬럼)를 필요로 하지 않는다. 또한, 다음과 같은 두 조건이 만족한다면 조건 절이 없을 때도 전체
스캔이 가능하다.
① 쿼리 내에서 참조되는 모든 컬럼들이 Index에 포함되어야 한다.
② Index 컬럼들 중 최소 하나의 컬럼이 NOT NULL이어야 한다.
전체 스캔은 데이터들이 Index 키에 의해서 정렬되어 있기 때문에 정렬 작업을 회피해야 할 경 우에도 사용 될 수 있다.
참고 : Index 전체 스캔은 고속 전체 Index 스캔과 달리 단일 블록 입출력(I/O)을 사용하여 인덱스를 읽는다.
Index 고속 전체 스캔(Index Fast Full Scan)
그림 4.13
쿼리에서 요구하는 모든 컬럼들을 Index가 포함하고, Index 내의 컬럼들 중에 최소 하나의 컬럼이 NOT
NULL을 보장한다면 전체 테이블 스캔 대신 Index 고속 전체 스캔이 사용 될 수 있다. 고속 전체 스캔은
테이블을 액세스하지 않고 Index 자체의 데이터에만 액세스한다. 이 방식은 정렬 작업을 회피 하는 데는
사용될 수 없는데 데이터들이 Index 키에 의해 정렬되어 있지 않기 때문이다. 이 방식은 min/avg/sum
집계 함수를 위해 사용 될 수도 있다. 이 경우에 옵티마이저는 테이블의 모든 행들이 Index에 저장되어
있음을 확인해야 하기 때문에 최소한 NOT NULL 컬럼이 하나 이상 포함되어야 한다.
이 작업은 전체 Index 스캔과 달리 다중 블록 인기를 사용하여 전체 Index를 읽는다. Bitmap Index의
경우에는 고속 전체 Index 스캔을 수행 할 수 없다. 고속 전체 스캔은 일반 전체 인덱스 스캔보다 빠른데,
테이블 스캔처럼 다중 블록 I/O를 사용하기 때문이다.
OPTIMIZER_FEATURES_ENABLE 초기화 파라메터 또는 INDEX_FFS 힌트를 사용하여 고속 전체 스캔을
지정 할 수 있다.
참고 : Index 고속 전체 스캔은 Index가 오프라인 상태에서 재구성될 때도 사용 가능하다.
Index 스킵 스캔(Index Skip Scan)
첫 번째 브랜치 블록 [B1]을 거슬러 올라가서, 서버는 다음 서브 트리(F16)는 스캔 할 필요가 없음을
인식하는데, [B1]의 다음 엔트리가 F20이기 때문이다. 서버는 F16과 F20 사이에 25를 찾는 것이 불가능
하다는 것을 알고, 리프 블록 [L2]의 스캔을 생략한다.
[B1]으로 돌아와서 서버는 다음 두 엔트리가 동일한 접두사 F2를 가지고 있음을 발견하고, 스캔 해야 할
서브 트리를 식별한다. 시스템은 이 서브 트리가 나이에 의해 정렬되어 있음을 알게 된다.
세 번째와 네 번째 리프 블록[L3-L4]이 스캔 되고 일부 값이 리턴된다. 첫 번째 브랜치 블록[B1]의 네
번째 엔트리를 확인하고, 시스템은 더 이상 F2x 엔트리를 검색하는 것은 불가능하다고 결정한다. 그 결과
[L5] 서브 트리를 스캔 할 필요가 없다. 동일한 과정이 이 Index의 오른쪽 부분에서 계속 진행된다. 전체
10개 블록에서 오직 5개의 블록만 스캔하였음을 주의하여야 한다.
Index 스킵 스캔(Index Skip Scan) : 예
그림 4.15
예제에서는 Index 스킵 스캔을 이용하여 급여가 1500 미만인 사원들을 검색한다. DEPTNO와 SAL 컬럼에
결합 Index가 존재한다고 가정한다. 여기서 볼 수 있는 것처럼 쿼리는 조건 절에 DEPTNO 선행 컬럼을
포함하고 있지 않다. 이 선두 컬럼은 10, 20, 30의 이산 값만을 갖는다.
스킵 스캔은 복합 Index를 작은 서브 Index로 논리적으로 분할한다. 논리적 서브 Index의 개수는 선두
컬럼 내의 이산 값의 개수로 결정된다.
시스템은 큰 Index 내부에 3개의 작은 Index 구조가 존재하는 것처럼 동작한다. 예를 들어, 3개의 Index 구조는 다음과 같다.
① where deptno = 10
② where deptno = 20
③ where deptno = 30
출력은 DEPTNO 컬럼으로 정렬된다.
참고 : 스킵 스캔은 복합 Index의 선두 컬럼에 값의 종류가 다양하지 않고, 후행 컬럼은 값의 종류가 다양한 경우에 장점이 있다.
Index 조인 스캔(Index Join Scan)
그림 4.16
Index 조인은 쿼리에서 참조되는 모든 테이블 컬럼들을 포함하고 있는 여러 Index들을 해시 조인한
것이다. Index 조인이 사용되면, 모든 컬럼 값들이 Index들로부터 읽혀질 수 있기 때문에 테이블을
액세스 할 필요가 없다. Index 조인은 정렬 작업을 회피하는데 사용 할 수는 없다.
Index 조인은 실제 조인 작업이 아니지만, rowid에 대한 조인 작업이 수행된 다음에 Index를 액세스하여
만들어진다. 위 그림의 예제에서는 EMP 테이블의 ENAME과 SAL 컬럼에 각각 Index가 정의되어 있다.
참고 : INDEX_JOIN 힌트를 사용하여 Index 조인을 지정 할 수 있다.
역자 주 : Index 조인 스캔은 WHERE 절의 모든 컬럼들이 NOT NULL을 보장하여야 한다.
AND-EQUAL 작업
그림 4.17
AND-EQUAL 작업은 시스템이 테이블에 액세스하기 전에 여러 개의 단일 컬럼 Index를 스캔 한 후 그 결과를 병합한다.
이 작업은 그림에서 자세하게 설명하고 있다.
기본적으로 이 작업은 WHERE 절에 두 개(또는 그 이상, 이론적으로 최대 5개)의 등가(=) 조건이 있으며,
각 컬럼이 단일 컬럼 Index로 정의되어 있는 경우에 동작한다. 기본 개념은 ROWID가 일치하는 Index를
탐색하는 것이다. 만약, 모든 Index에서 같은 ROWID가 발생하면, 조건이 만족되어 테이블로부터 일치되는 행들이 리턴된다.
참고 : AND-EQUAL 작업은 AND_EQUAL 힌트를 사용한다.
역자 주 : AND-EQUAL은 각 Index가 모두 비고유 Index이어야 한다. 고유 Index가 존재하면 해당 고유 Index를 이용하여 Index 스캔한다.
B*-트리 Index와 Null
그림 4.18
B*-트리 Index를 다룰 때, Null을 고려하지 않으면 실수를 하기 쉽다. 단일 컬럼 B*-트리 Index는
Null 값을 저장하지 않으므로, 쿼리에서 Null 값을 제거하는 무엇인가가 존재하지 않는 한, 쿼리는 Null이
허용된 컬럼들에 대하여 정의된 Index를 사용 할 수 없다.
위 그림의 예제에서 COL1은 Null 값을 허용하고 COL2는 Null 값을 입력 할 수 없다. 각 컬럼에 대하여 Index를 정의하였다.
첫 번째 쿼리는 모든 COL1 값을 읽어온다. COL1은 Null을 허용하므로, Index는 조건 절 없이 사용 될 수
없다. Index를 이용하기 위해 COL1(nullind1)의 정의된 Index를 사용하도록 힌트를 부여해도 COL1이
Null을 허용하기 때문에 실행 계획에는 변화가 발생하지 않는다. COL1 값만을 검색하기 때문에
테이블을 읽을 필요는 없다.
그러나, 두 번째 쿼리의 경우, COL1에 대하여 조건 절을 사용하여 해당 컬럼으로부터 리턴되는
데이터에서 Null 값을 제거하였다. 이런 경우는 Index를 사용 할 수 있다.
세 번째 쿼리는 테이블 생성 시에 해당 컬럼에 NOT NULL 제약조건을 정의하였기 때문에 해당 Index를
직접 사용 할 수 있다.
참고 : COL1 IS NOT NULL 조건을 사용하여 NOT NULL인 값만을 리턴하도록 지정하면 Index를 사용 할 수 있다.
Index 사용 : Null 허용 컬럼의 고려
그림 4.19
일부 쿼리들은 Index를 이용하여 행의 개수를 계산할 수 있는지 확인한다. 이 경우는 테이블을 스캔 하는
것보다 더욱 효율적이다. 그러나, 사용된 Index가 Null 값을 가질 수 있는 컬럼에 정의 되었다면 해당
Index는 절대 사용되지 못한다. 단일 컬럼 B*-트리 Index는 Null 값을 절대 저장하지 않으므로, 이러한
행들은 Index 내에서 절대 표현 될 수 없다. 그 결과, 이러한 컬럼은 COUNT를 수행하는데 사용 될 수
없다. 위 예제에서는 PERSON 테이블의 SSN 컬럼에 고유 Index가 정의되어 있다. SSN 컬럼은 Null
값을 허용하도록 정의되었다. 고유 Index를 생성하는 것은 Null 값을 제한하지 않는다. 이 Index 는
그림과 같이 카운트 쿼리를 실행 할 때 절대 사용될 수 없다. SSN이 Null인 모든 행들은 Index 내에
존재하지 않으며, Index를 대상으로 하는 카운트 결과는 정확도가 떨어진다.
이러한 결과로부터 고유 Index 보다 기본 키를 생성하는 것이 더 낫다는 사실을 확인 할 수 있다. 기본
키 컬럼은 Null 값을 가질 수 없다. 위 그림에서 고유 Index를 제거한 후, 기본 키를 정의 하면 행의
개수를 계산 할 때 해당 Index가 사용된다.
참고 : PRIMARY KEY 제약조건은 한번의 선언으로 NOT NULL 제약조건과 고유 제약조건을 함께 정의한 것과 같다.
IOT(Index-Organized Tables)
그림 4.20
IOT는 결합 Index 구조 내부에 테이블을 물리적으로 저장한 것이다. 테이블과 B*-트리 Index에 대한
키 값은 같은 세그먼트에 저장된다. IOT는 다음과 같은 값들을 포함한다.
① 기본 키 컬럼
② 해당 행의 기본 키를 제외한 컬럼들
테이블의 기본 키를 기반으로 B*-트리 구조가 Index와 동일한 방식으로 구성된다. 이 구조의 리프 블록은
ROWID 대신에 행들을 저장한다. 이것은 IOT 내부의 행들은 언제나 기본키의 순서대로 유지 관리됨을
의미한다. IOT에 추가 Index를 생성 할 수 있다. 기본 키는 복합 키가 될 수도 있다. IOT의 대부분의
행들은 B*-트리 구조의 밀도와 효율적인 저장구조를 파괴 할 수 있기 때문에 행의 일부를 다른
세그먼트에 저장 할 수 있으며, 이 세그먼트를 오버 플로우 영역이라고 부른다.
IOT는 쿼리가 정확한 키 값으로 검색하거나 범위 검색을 수행 할 때 고속의 키 기반 액세스를 제공한다.
테이블 데이터의 변경은 오직 Index 구조의 변경만을 유발한다. 또한, 테이블과 Index에서 키 컬럼이
중복되어 저장되지 않으므로 저장 공간도 절약된다. 키가 아닌 컬럼들은 Index 구조에 저장된다. IOT는
기본 키를 기반으로 데이터를 검색하거나 키가 아닌 컬럼들의 길이가 상대적으로 짧은 애플리케이션을
사용하는 경우에 특별히 유용하다.
참고 : 오버플로우 세그먼트가 존재하지 않으면 여기서 언급한 내용이 모두 올바르다. 오버플로우
세그먼트는 행의 길이가 긴 경우에 사용하여야 한다.
IOT 스캔
그림 4.21
위 그림에서 IOTEMP는 다음과 같은 문장에 의해 IOT 테이블로 정의 되었다고 가정한다.
create table iotemp
(empno number(4) primary key, ename varchar2(10) not null,
job varchar2(9), mgr number(4), hiredate date,
sal number(7,2) not null, comm, number(7,2), deptno number(2))
organization index;
IOT는 Index와 유사하다. 일반 Index에서 보는 것처럼 동일한 액세스 경로를 사용한다.
힙 구조 테이블과의 주요 차이점은 Index 된 데이터를 읽기 위해 Index와 테이블을 모두 읽을 필요가 없다는 것이다.
참고 : SYS_IOT_TOP_75664는 IOT 구조를 저장하는데 사용되는 세그먼트로 시스템이 생성한 이름이다.
USER_INDEXES의 INDEX_NAME, INDEX_TYPE, TABLE_NAME 컬럼으로부터 테이블 이름과 세그먼트
간의 연결 정보를 검색 할 수 있다.
Bitmap Index
그림 4.22
B*-트리에서는 Index 엔트리와 행이 1:1의 관계이므로 하나의 Index 엔트리는 하나의 행을 가리키고
있다. Bitmap Index는 B*-트리 Index와 같이 구성되어 있지만, Bitmap Index는 하나의 Index 엔트리가
Bitmap을 사용하여 동시에 여러 개의 행을 가리킬 수 있다. 만약, Bitmap Index가 하나 이상의 컬럼들을
포함하고 있다면, 각각의 가능한 조합으로 Bitmap이 존재하게 된다. 각 Bitmap 헤더는 시작 ROWID와
종료 ROWID를 저장하고 있다. 이 값들을 기반으로 시스템은 내부 알고리즘을 사용하여 Bitmap을
ROWID로 맵핑시킨다. 이 작업이 가능한 이유는 시스템이 블록에 저장 될 수 있는 행들의 최대 개수를
알고 있기 때문이다. Bitmap의 각 위치는 행들이 실제로 존재하지 않아도 테이블 내의 잠재적인 행으로
맵핑된다. 특정 컬럼 값에 대한 Bitmap에서 해당 위치의 내용은 그 행이 Bitmap 컬럼에 해당 값을
가지고 있는지 여부를 나타낸다. 해당 행의 값이 Bitmap 조건과 일치되면, Bitmap의 해당 값은 1이 되며,
그렇지 않은 경우는 0이다. Bitmap 인덱스는 데이터웨어 하우스에서 널리 사용된다. 이러한 환경은
일반적으로 대량의 데이터와 임의 쿼리를 가지고 있지만, 동시에 수행되는 DML 트랜잭션은 존재하지
않는다. 그 이유는 Bitmap에 잠금을 설정하면, 동시에 테이블의 모든 행들에 잠금이 설정되기 때문이다.
그러한 애플리케이션에서 Bitmap Index는 임의 쿼리에 대해 응답시간을 감소시켜 주며, 다른 Index
기법과 비교할 때, 저장 공간도 절약된다. 또한, 적은 개수의 CPU와 적은 메모리를 가진 하드웨어에서도
드라마틱한 성능 향상을 가져 올 수 있으며, 병렬 DML 및 로드 작업도 효율적으로 유지 관리 할 수 있다.
참고 : 대부분의 다른 Index 유형과 다르게 Bitmap Index는 NULL 값을 갖는 행들을 포함한다. Null 값을
포함하는 Index는 COUNT와 같은 집계 함수를 사용하는 쿼리에 대하여 매우 유용하 게 사용된다. 또한,
Bitmap Index에서는 IS NOT NULL 조건도 유용하다. Bitmap은 내부적으로 압축되더라도 행의 개수가
증가하면 여러 개의 리프 블록으로 분할된다.
Bitmap Index 액세스 : 예
그림 4.23
위 그림은 Bitmap Index에서 사용 가능한 두 가지 경로를 설명한다. 쿼리에서 사용된 조건절의 유형에
따라 BITMAP INDEX SINGLE VALUE 또는 BITMAP INDEX RANGE SCAN이 발생한다.
첫 번째 쿼리는 COUNTRY가 ‘FR’인 Bitmap에서 1의 값을 스캔한다. Bitmap에서 1의 위치는 ROWID로
변환되고 해당 행들이 리턴된다. COUNTRY가 ‘FR’인 행들의 개수를 카운트하는 쿼리의 경우에는 단순히
Bitmap을 이용하여 1의 개수를 카운트한다. 여기서 실제 행에 액세스 할 필요는 없다. 이러한 경우는 다음과 같다.
복합 Bitmap Index : 예
그림 4.24
Bitmap Index는 WHERE 절에 여러 개의 조건이 포함되어 있는 쿼리에 매우 효율적이다. 전체 조건이
아닌 일부 조건을 만족하는 행들은 테이블에 액세스하기 전에 필터링 된다. 그 결과, 응답 시간은 급격히
향상 된다. Bitmap Index로부터 Bitmap들이 빠르게 결합되기 때문에 단일 컬럼 Bitmap Index를
사용하는 것이 가장 좋다.
비트-AND, 비트-MINUS, 비트-OR 연산은 매우 빠르기 때문에 다음과 같은 상황에서는 Bitmap 인덱스가 효율적이다.
① IN (값 목록) 의 사용
② AND 또는 OR로 결합된 조건
Bitmap Index 액세스 경로 조합
그림 4.25
Bitmap Index는 쿼리가 컬럼 값들 중에서 몇 개를 조합하거나 두 개의 개별 Index 컬럼을 사용 하는
경우에 매우 효율적으로 사용된다. 일부 경우에서 WHERE 절은 그림에서 보는 바와 같이 별개의 Index
컬럼을 참조 할 수도 있다. 만약, COUNTRY와 GENDER 컬럼이 모두 Bitmap Index를 가지고 있다면,
두 Bitmap에 대하여 비트-AND 연산이 빠르게 수행되어 찾고자 하는 행의 위치를 얻을 수 있다.
WHERE 절이 더욱 복잡해질수록 Bitmap Index의 효과는 점점 더 커진다.
Bitmap 연산
다음은 수행 가능한 모든 Bitmap 연산들이다.
① BITMAP CONVERSION FROM ROWID : 효율적인 Bitmap 비교 연산이 가능하다면 옵티마이저는
B*-트리 Index를 Bitmap으로 변환한다. Bitmap 비교 연산이 수행된 후, 결과 비 트맵은 데이터 검색을
수행하기 위해 ROWID로 다시 변환된다(BITMAP CONVERSION TO ROWIDS).
② BITMAP MERGE는 범위 스캔에 의해 만들어진 여러 개의 Bitmap을 하나의 Bitmap으로 병합한다.
③ BITMAP MINUS는 두 번째 Bitmap에 대하여 0 비트를 1 비트로, 1 비트는 0 비트로 변환 하고,
이 Bitmap을 이용하여 BITMAP AND 작업을 수행하는 연산자이다. C1=2 and C2<>6과 같이 조건이 조합된 경우에 사용된다.
④ BITMAP KEY ITERATION은 테이블에서 각각의 행들을 인고 Bitmap Index로부터 해당 되는
Bitmap을 검색한다. 이 후, BITMAP MERGE 작업에서 Bitmap들을 하나의 Bitmap으로 병합한다.
Bitmap 조인 Index
그림 4.26
단일 테이블에 대한 Bitmap Index에 추가하여, Bitmap 조인 Index를 생성 할 수 있다. Bitmap
조인 Index는 두 개 이상의 테이블에 정의하는 Bitmap Index이다. Bitmap 조인 Index는 반드시
조인하여야 할 데이터들을 미리 조인하여 저장함으로써 데이터의 저장공간을 효율적으로 사용 할 수 있도록 해준다.
여기서, SALES 테이블에 cust_sales_jbi라는 새로운 Bitmap 조인 Index를 생성하였다. 이 Index의 키는
CUSTOMERS 테이블의 CUST_CITY 컬럼이다. 이 예제에서는 CUSTOMERS 테이블에 기본 키 제약조건을
지정하여 Bitmap에 저장되는 값들이 해당 테이블 내의 실제 데이터를 반영 할 수 있도록 보장하였다.
CUST_ID 컬럼은 CUSTOMERS의 기본 키이지만 SALES 내에서는 외래 키이다.
CREATE 문장 내의 FROM과 WHERE 절은 시스템이 두 테이블을 상호 연결 할 수 있도록 해준다. 즉,
두 테이블 간의 자연스러운 조인 조건을 나타낸다.
위 그림의 중간 부분에는 Bitmap 조인 인덱스의 이론적 구현을 보여준다. Index 내의 각 엔트리 또는
키는 CUSTOMERS 테이블에서 발견 될 수 있는 도시를 나타낸다. 그 다음, Bitmap은 하나의 특정 키와
연관된다. Bitmap의 각 비트는 SALES 테이블의 각 행에 해당된다. 그림의 첫 번째 키(Rognes)로부터
SALES 테이블의 첫 번째 행은 Rognes 고객에게 제품이 판매 되었음을 확인 할 수 있다. 조인 결과를
저장하면, Bitmap 조인 Index를 이용하여 조인을 수행하지 않고 SQL 문장을 완료 할 수 있다.
참고 : Bitmap 조인 Index는 구체화된 조인 뷰에 비해서 저장공간을 매우 효율적으로 사용한다.
복합 Index
복합 Index는 컬럼 값들을 함께 결합하여 Index 키 값을 만들기 때문에 결합 Index라고 하기도 한다.
그림에서 MAKE 및 MODEL 컬럼이 결합되어 Index를 구성하였다. Index 내부의 컬럼들이 반드시
테이블 내에서 이웃한 컬럼이어야 할 필요는 없다. 또한, Bitmap 복합 Index가 아니 라면 Index에
32개의 컬럼을 포함시킬 수 있으며, Bitmap 복합 Index의 경우는 30개의 컬럼을 포함시킬 수 있다.
복합 Index는 단일 컬럼 Index와 비교 할 때, 다음과 같은 추가적인 장점을 갖는다.
① 향상된 선택도 : 선택도가 좋지 않는 두 개 이상의 컬럼들로 높은 선택도를 갖는 복합 Index를 조합 할 수도 있다.
② I/O 감소 : 쿼리 내의 모든 컬럼들이 복합 Index에 포함되어 있다면, 테이블을 액세스하지 않고 Index로부터 모든 컬럼들을 인을 수 있다.
복합 Index에 정의된 컬럼들 중에 선두 컬럼들을 WHERE 절에서 참조하는 경우에는 복합 인덱스가 유리하다.
만약, 일부 키들이 WHERE 절에서 자주 사용된다면 복합 Index의 생성을 고려하 여야 하며, 여기서 자주 사용되는
키들을 복합 Index의 선두에 위치시킴으로써 이러한 키들만 사용하는 문장들도 해당 Index를 사용 할 수 있도록 보장해주어야 한다.
참고 : 쿼리가 Index의 선두 부분을 참조하지 않을지라도 Optimizer는 결합 Index를 사용하도록 할 수 있다.
이 작업은 Index 스킵 스캔과 고속 전체 스캔이 구현된 후부터 가능하게 되었다.
투명 Index(Invisible Index) : 개요
그림 4.28
투명 Index는 세션 또는 시스템 수준에서 OPTIMIZER_USE_INVISIBLE_INDEXES 초기화 파라메터를
TRUE로 설정하지 않는 한, Optimizer에 의해 무시된다. 이 파라메터의 디폴트 값은 FALSE이다.
투명 Index를 만드는 것은 해당 Index를 사용 불가능한 상태로 만들거나 삭제하는 대신 사용 할 수 있는 방법이다.
투명 Index를 사용하여 다음과 같은 작업을 수행 할 수 있다.
① Index를 삭제하기 전에 Index 제거 효과를 테스트한다.
② 전체 애플리케이션에 영향을 주지 않고, 애플리케이션의 특정 작업 또는 모듈에 대해 임시 Index 구조를 사용한다.
사용 불가능 Index(unusable)와는 달리 투명 Index는 DML 문장이 수행되면 자동으로 유지 관리 된다.
투명 Index : 예제
그림 4.29
Index가 투명 Index로 설정되면, Optimizer는 해당 Index를 사용하지 않는 실행 계획을 선택 한다. 만약,
성능이 눈에 띄게 저하 될 정도가 아니라면, 해당 Index를 제거 할 수 있다. 또한, 인덱스를 최초에는
투명 Index로 생성하고, 테스트를 수행한 다음, 투명 Index를 일반 Index로 변경할지를 결정한다.
어떤 Index가 VISIBLE 또는 INVISIBLE인지 확인하려면 *_INDEXES 데이터 딕셔너리의 VISIBILITY 컬럼을
검색하면 된다.
참고 : 위 그림의 모든 문장들은 OPTIMIZER_USE_INVISIBLE_INDEXES를 FALSE로 설정한 것으로 가정한다.
Index 관리 지침
① 테이블에 데이터를 입력한 후, Index를 정의 : 데이터는 SQL*Loader 또는 임포트 유틸리 티를 사용하여 자주
입력되거나 로드된다. 데이터를 입력하거나 로드한 후 테이블에 인덱스를 생성하는 것이 효율적이다.
② 올바른 테이블 및 컬럼에 Index를 정의 : Index의 생성 시점을 결정하는 방법은 다음 과 같다.
1. 대용량 테이블에서 15% 이내의 행을 자주 검색하는 경우에만 Index를 생성한다.
2. 다중 테이블의 조인 성능을 향상시키려면 조인에 사용되는 컬럼에 Index를 생성한다.
3. 소용량의 테이블에는 Index가 불필요하다.
③ Index를 정의하기에 적합한 컬럼들 : Index를 정의하기에 바람직한 컬럼들은 다음과 같다.
1. 컬럼 내의 값들이 고유한 경우
2. 값의 종류가 많은 경우(일반 Index에 적합)
3. 값의 종류가 적은 경우(Bitmap Index에 적합)
4. Null이 많이 포함된 컬럼. 값을 가진 모든 행들을 자주 검색하는 경우
④ Index를 정의하기에 적합하지 않는 컬럼들 :
A. 컬럼에 많은 Null을 가지고 있고 Not Null 값을 검색하지 않는 경우
B. LONG 및 LONG RAW 컬럼에는 Index를 정의하지 않는다.
C. 가상 컬럼 : 가상 컬럼에 고유 또는 비고유 Index를 정의 할 수도 있다.
⑤ 성능을 고려하여 Index 컬럼의 순서를 결정 : CREATE INDEX 문장 내에서 컬럼들의 순 서는 쿼리 성능에 영향을
미칠 수 있다. 일반적으로, 가장 자주 사용되는 컬럼들을 선두 에 둔다.
⑥ 테이블 당 Index 개수 제한 : 테이블에 정의 가능한 Index 개수에는 제한이 없다. 그러 나, Index가 많을수록
테이블에 발생하는 변경 작업에 오버헤드가 증가하게 된다. 즉, 테이블에서 데이터를 읽는 속도와 테이블을 변경하는 속도는 서로 대치된다.
⑦ 더 이상 사용하지 않는 Index 제거
⑧ Index가 저장될 테이블스페이스 지정 : 테이블과 Index를 같은 테이블스페이스에 저장 하여 사용하면, 테이블스페이스
백업과 같은 Database 유지 관리작업을 편리하게 수 행 할 수 있다.
⑨ Index 생성 작업을 병렬로 수행 : 병렬로 테이블을 생성하는 것처럼 Index도 병렬로 생성 할 수 있다. 이로 인하여
Index 생성 작업을 빠르게 완료 할 수 있다. 그러나, INITIAL을 5M, 병렬 처리 정도를 12로 설정하여 Index를 생성하면
Index가 생성되는 과정에서 최소 60MB의 저장 공간을 소비하게 된다.
⑩ NOLOGGING으로 Index 생성 고려 : CREATE INDEX 문장에 NOLOGGING을 지정하면 인 덱스를 생성하면서 리두
로그 레코드를 최소한으로 생성되게 할 수 있다. NOLOGGING을 사용하여 생성된 Index는 아카이브 되지 않기 때문에
Index가 생성된 후, 백업을 수행 해야 한다. NOARCHIVELOG Database에서는 NOLOGGING이 디폴트이다.
⑪ Index의 병합(coalesce)과 재구성 간의 비용과 장점 고려 : Index의 적절치 않은 용량 또는 성장은 Index 단편화를
발생시킨다. 단편화를 제거하거나 감소시키려면 Index를 재구성하거나 병합한다. 각각의 작업을 수행하기 전에 각
옵션의 비용과 장점을 비교하고 상황에 맞는 최상의 작업을 선택하도록 한다.
⑫ 제약조건의 비활성화와 삭제 간의 비용 비교 : 고유 키와 기본 키는 연관된 Index를 갖 기 때문에 UNIQUE 또는
PRIMARY KEY 제약조건을 비활성화하거나 삭제 할 때는 Index 의 삭제 및 생성 비용을 고려해야 한다. 만약, UNIQUE 또는
PRIMARY KEY 제약조건과 연관된 Index의 용량이 매우 크다면 Index를 삭제 및 재 생성하는 대신, 제약조건을 활 성화 된
상태로 두어 시간을 절약 할 수 있다. 또한, UNIQUE 또는 PRIMARY KEY 제약조건을 삭제 또는 비활성화는 경우, 유지하거나
제거할 Index를 명시적으로 지정 할 수도 있다.
Index 사용 여부 조사
특정 Index가 사용 될 것이라는 기대를 가지고 SQL 문장을 실행하지만, 그렇지 않는 경우가 발생한다.
이것은 Optimizer가 일부 정보를 인식하지 못하거나, Optimizer가 Index를 사용하지 못하도록 하기 때문이다.
함수
WHERE절에 Index가 정의된 컬럼에 대하여 함수를 적용하면 Index(컬럼 값을 기반으로 만들어진 Index)는 사용될 수 없다.
예를 들어, 다음 문장은 salary 컬럼의 Index를 사용하지 못한다.
SELECT * FROM employees WHERE 1.10*salary > 10000;
만약, 이러한 경우에 Index를 사용하려면 함수 기반 Index를 생성해야 한다. 함수 기반 Index는 앞서 이미 설명되었다.
데이터 타입 불일치
Index 컬럼과 비교 할 값 사이에 데이터 타입 불일치가 발생하면, 해당 Index는 사용되지 못한다.
이것은 묵시적 데이터 타입 변환이 수행되기 때문이다.
예를 들어, SSN 컬럼이 VARCHAR2 타 입이라면, 다음 문장은 SSN에 정의된 Index를 사용하지 못한다.
SELECT * FROM person WHERE SSN = 12345678;
오래된 통계 자료
통계 자료는 Optimizer가 Index의 사용 여부를 결정 할 때, 고려하는 정보이다. 만약, 통계 자료가
갱신되지 않았다면 Optimizer는 Index에 대하여 올바르지 못한 판단을 내릴 수 있다.
Null 컬럼
만약, 컬럼이 Null 값을 포함 할 수 있다면, 해당 컬럼의 Index 사용을 방해 할 수도 있다. 이런 현상의
원인은 이미 설명하였다.
느려진 Index
Index의 사용이 오히려 비효율적인 경우가 있다. 이것은 나중에 설명한다.
클러스터
그림 4.30
클러스터는 테이블의 데이터를 저장하기 위한 또 다른 저장 방식이다. 클러스터에서는 여러 테이블들이
자주 함께 사용되고 공통된 컬럼들을 사용하기 때문에 같은 데이터 블록들을 공유한다. 예를 들어,
ORDERS와 ORDER_ITEMS 테이블은 ORDER_ID 컬럼을 공유한다. ORDERS와 ORDER_ITEMS 테이블을
클러스터에 저장하면 시스템은 ORDERS와 ORDER_ITEMS 테이블에서 ORD_NO가 일치하는 모든 행들을
물리적으로 동일한 데이터 블록에 저장한다.
클러스터 Index : 클러스터 Index는 클러스터에 특별하게 정의된 Index이다. 이 Index는 각 각의
클러스터 키 값에 대하여 하나의 엔트리를 포함한다. 클러스터 내의 행들을 검색하려면, 클러스터 키
값을 찾기 위해 클러스터 Index가 사용되며, 클러스터 키 값은 해당 클러스터 키 값과 연관된 데이터
블록을 가리킨다. 그러므로, 시스템은 최소 두 번의 I/O를 수행하여 행에 접근한다.
해시 클러스터 : 해싱은 데이터 읽기 성능을 향상시키기 위한 테이블 데이터의 또 다른 저장 방법이다.
해싱을 사용하려면 해시 클러스터를 생성하고 클러스터에 테이블을 로드한다. 시스템은 테이블의 행들을
해시 클러스터에 물리적으로 저장하고, 해시 함수의 결과에 따라 행들을 읽어 온다. 해시 클러스터의 키
(Index 클러스터의 키와 같이)는 단일 컬럼 또는 복합 컬럼이 될 수 있다. 해시 클러스터에서 행을
찾거나 저장하려면, 시스템은 해시 함수를 해당 행의 클러스터 키 값에 적용한다. 연산 결과인 해시 값은
클러스터 내의 데이터 블록의 위치에 해당되기 때문에 시스템은 문장 대신 행을 읽거나 기록 할 수 있다.
참고 : 테이블이 등가(=) 조건을 가진 쿼리에 의해서 자주 검색되는 경우, Index가 정의된 테이블 또는
Index 클러스터를 사용하는 대신 해시 클러스터를 사용하는 것이 더 나은 선택이 될 수 있다.
클러스터가 유용한 경우
① Index 클러스터는 클러스터 키 값을 공유하는 하나 이상의 테이블로부터 행 데이터를 동일한 블록에
저장 할 수 있도록 해준다. 클러스터 Index를 이용하여 이러한 행들을 찾을 수 있으며, 클러스터 Index는
하나의 행이 아닌 하나의 클러스터 키 값에 대하여 하나의 엔트리를 갖는다. Index의 크기가 작기 때문에
여러 개의 행들을 찾아야 하는 경우, 액세스 비용은 감소하게 된다. 동일한 키를 갖는 행들은 소수의 블록들에
집중적으로 저장된다. 이것은 Index 클러스터의 경우, 클러스터링 팩터가 매우 우수하며 동일한 조인 키를
공유하는 여러 테이블들의 데이터들에 대해 클러스터링을 제공한다는 의미이다. Index의 크기가 작고 블록들의
개수가 적을 수록 버퍼 캐시에 대한 블록 방문 횟수를 감소시키므로 액세스 비용은 감소한다. Index 클러스터는
테이블의 크기를 사전에 알 수 없는 경우에 유용하다(예를 들어, 테이블의 크기가 거의 안정된 테이블들을
변환하는 것보다 새로운 테이블을 생성). 그 이유는 클러스터 키 값이 사용된 후에만 클러스터 버킷이 생성되기
때문이다. 또한, Index 클러스터는 필터 작업 또는 검색에 모두 유용하 다. 테이블 클러스터는 각 테이블을 힙
테이블로 생성할 때보다 더 많은 블록들을 갖기 때문에 클러스터에 포함된 여러 테이블 중에서 하나의 테이블을
전체 테이블 스캔으로 인지 말아야 한다.
② 해시 클러스터는 클러스터 키 값을 공유하는 하나 이상의 테이블들로부터 행 데이터를 동일한 블록에 저장 할 수
있도록 해준다. 이러한 행들은 시스템 제공 또는 사용자 제공해시 함수를 사용하거나, 이미 균등하게 분포되었다고
가정된 클러스터 키 값을 이용하 여 검색 할 수 있으며, Index 클러스터를 사용하는 것보다 더 빠르게 액세스 할 수
있도록 해준다. 동일한 클러스터 키 값을 갖는 테이블들의 행들은 동일한 클러스터 버킷에 저장되며, 동일한 블록
또는 적은 개수의 블록들에 집중적으로 저장된다. 이것은 해시 클 러스터의 경우, 클러스터링 팩터가 매우 우수하며
해당 키에 의해서 행들을 한번의 블록 방문 횟수로 액세스하고 Index가 불필요하다는 것을 의미한다. 해시 클러스터는
해당 클러스터가 생성될 때, 해시 버킷에 대한 저장 공간을 모두 할당하므로, 저장 공간을 낭 비 할 수도 있다. 또한,
등가(=) 검색 또는 비 등가 검색 이외에는 효율적으로 동작하지 못한다. Index 클러스터와 마찬가지로 전체 스캔을
수행하는 비용은 상당히 높은 편이다.
③ 단일 테이블 해시 클러스터는 해시 클러스터와 유사하지만, 단일 테이블 액세스에 대하여 블록 구조가 최적화 되어 있다.
그러므로, rowid 필터를 사용하는 대신 행에 대한 가장 빠른 경로를 제공한다. 단일 테이블 해시 클러스터는 오직 하나의
테이블을 갖기 때 문에 전체 스캔 비용은 힙 테이블의 전체 스캔 비용과 비슷하다.
④ 정렬된 해시 클러스터는 해시 키를 사용하여 정렬된 데이터에 액세스하는 경우, 비용을 감소 시키기 위해 고안되었다.
해시 키와 일치하는 첫 번째 행으로의 액세스는 대용량 테이블에 대하여 IOT를 사용하는 것보다 저비용이 될 수 있는데,
그 이유는 B*-트리 검색 비용을 절감 할 수 있기 때문이다. 특정 해시 키(예를 들어, 계정 번호)와 일치하는 모든 행들은
클러스터 내에서 정렬 키 또는 키들(예를 들어, 전화 번호)의 순서로 저장되므로, order by 구문을 처리하기 위해 정렬을
수행해야 할 필요가 없어 진다. 이러한 클러스터는 리포팅, 빌링 등의 일괄 처리 작업에 매우 유용하다.
클러스터 액세스 경로 : 예제
그림 4.31
위 그림은 두 개의 서로 다른 클러스터 액세스 경로를 보여준다.
첫 번째 예는 해시 값을 기반으로 해시 클러스터 내의 행들을 찾기 위해 해시 스캔이 사용된 것이다.
해시 클러스터에서 동일한 해시 값을 갖는 모든 행들은 같은 데이터 블록에 저장된다. 해시 스캔을
수행하기 위해, 시스템은 문장에 지정된 클러스터 키 값에 해시 함수를 적용하여 해시 값을 먼저 얻는다.
그 다음, 시스템은 해당 해시 값에 해당하는 행들을 포함하고 있는 데이터 블록들을 스캔한다.
두 번째 예는 EMP와 DEPT 테이블을 클러스터링 하기 위해 클러스터 Index가 사용되었다. 이 경우에
Index 클러스터에 저장된 테이블로부터 동일한 클러스터 키 값을 갖는 모든 행들을 인기 위해 클러스터
스캔이 사용되었다. Index 클러스터에서 동일한 클러스터 키 값을 갖는 모든 행들은 동일한 데이터
블록에 저장된다. 클러스터 스캔을 수행하기 위해 시스템은 클러스터 Index를 스캔하여 선택된 행들 중
하나의 ROWID를 먼저 얻는다. 그 다음은 이 ROWID를 기반으로 행들을 찾는다.
정렬 작업
사용자가 정렬을 필요로 하는 작업을 지정하면 정렬 작업이 수행된다. 가장 많이 접하게 되는 정렬 작업들은 다음과 같다.
① SORT AGGREGATE는 정렬 작업을 포함하지 않는다. 이 작업은 선택된 행 그룹에 그룹 함수를 적용하여 하나의 행을
돌려준다. COUNT와 MIN과 같은 작업이 SORT AGGREGATE로 표시된다.
② SORT UNIQUE는 중복된 행을 제거하기 위해 행을 정렬한다. 사용자가 DISTINCT 구문을 지정하거나 작업이 다음
단계를 위해 고유값을 필요로 하는 경우에 발생한다.
③ 소트 머지 조인이 수행되는 동안, 행들이 조인 키에 의해 정렬 될 필요가 있는 경우, SORT JOIN이 발생한다.
④ SORT GROUP BY는 데이터 내에서 서로 다른 그룹별로 집계 작업을 수행해야 하는 경우 에 사용된다. 행들을 서로
다른 그룹에 분리하기 위해 정렬이 필요하다.
⑤ SORT ORDER BY는 문장에 ORDER BY를 포함하였으나 Index들 중의 어느 하나도 사용 할 수 없는 경우에 수행된다.
⑥ HASH GROUP BY는 GROUP BY 절을 가진 쿼리에 대하여 행들을 그룹핑한다.
⑦ HASH UNIQUE는 중복된 행을 제거하기 위해 행을 정렬한다. 사용자가 DISTINCT 구문을 지정하거나 작업이 다음
단계를 위해 고유값을 필요로 하는 경우에 발생한다. 이 작업은 SORT UNIQUE와 유사하다.
참고 : 여러 SQL 연산자들 즉, DISTINCT, GROUP BY, UNION, MINUS, INTERSECT와 같은 연산자들 이 암묵적 정렬을 유발시킨다.
그러나, 정렬된 행들을 가져오기 위해 이러한 SQL 연산자를 사용 하면 안 된다. 만약, 정렬된 행을 원한다면, ORDER BY 구문을 사용하여야 한다.
버퍼 소트 연산자
그림 4.32
BUFFER SORT 연산자는 임시 데이터를 저장하기 위해 메모리 상의 임시 테이블 또는 정렬 영역
(sort area)을 사용한다. 그러나, 해당 데이터가 정렬되어 있을 필요는 없다.
이 작업은 해당 작업을 시작하기 전에 모든 입력 데이터를 필요로 하는 경우에 수행된다.
그래서, BUFFER SORT는 전통적인 정렬 작업의 버퍼링 메커니즘을 사용하지만, 이 작업은 실제 정렬을
수행하지는 않는다. 시스템은 UGA(User Global Area) 또는 PGA(Program Global Area) 내에서 단순하게
데이터를 버퍼링하는데, 실제 데이터 블록을 여러 번 테이블 스캔 하지 않도록 하기 위함이다.
완전한 정렬 메커니즘이 재사용되며, 여기에는 정렬 영역의 메모리가 충분하지 않은 경우, 디스크로의
스왑도 발생하지만 데이터를 정렬하지는 않는다.
임시 테이블과 버퍼 소트간의 차이점은 다음과 같다.
① 임시 테이블은 SGA를 사용한다.
② 버퍼 소트는 UGA를 사용한다.
이 두 작업들은 임시 파일로 스왑 될 수 있으며, 가장 큰 차이점은 임시 테이블은 버퍼 캐시를 경유하기
때문에 래치 획득으로 인한 성능 저하가 발생하는 반면에 버퍼 소트는 래치 효과가 발생하지 않는다는 사실이다.
Inlist Iterator
그림 4.33
쿼리에 IN 절이 포함되거나 같은 컬럼에 대하여 여러 개의 등가(=) 조건이 OR 연산자에 연결된 경우에
발생한다. INLIST ITERATOR 연산자는 값 목록을 열거하고 반복하여 모든 값들에 대해 각각의 작업을
독립적으로 수행한다. 실행 계획은 한가지 추가적인 단계를 제외하면 IN 대신에 등가 조건을 사용한
결과와 동일하다. INLIST ITERATOR가 목록으로부터 고유 값을 찾아야 하는 경우에 추가 단계가 발생한다.
이 연산자를 PL/SQL 내의 FOR LOOP 문장으로 볼 수도 있다. 예제에서는 두 개의 값, 1과 2에 대해서
Index를 반복 사용하였다. 또한, 이 연산자는 Index를 사용하기 때문에 목록 내의 각 값에 대하여
반복된다. 이 작업의 대 안으로 각 값에 대하여 UNION ALL을 수행하거나 모든 행들에 대하여 각 값에
대해 FILTER를 수행 할 수 있으며, 이 작업은 상당히 효율적이다.
Optimizer는 IN 절에 값이 지정되어 있으면 inlist iterator를 사용하며, 해당 컬럼에 사용 할 인덱스를
찾는다. 만약, 동일한 Index를 사용하는 다수의 OR 절이 존재한다면, Optimizer는 CONCATENATION
또는 UNION ALL 보다 이 작업을 선택하는데, 이러한 이 매우 효율적이기 때문이다.
뷰 연산자
그림 4.34
각 쿼리는 테이블 형태의 데이터 집합을 생성한다. 뷰는 이러한 데이터 집합에 간단히 이름을 부여한 것이다.
쿼리 내에서 뷰가 참조되면, 시스템은 두 가지 방식으로 뷰를 처리한다. 만약, 조건이 만족되면 메인
쿼리로 뷰를 머지 시킬 수 있다. 이것은 뷰 내부의 문장이 쿼리 내의 다른 테이블과 조인하는 문장으로
재 작성 된다는 것을 의미한다. 또한 뷰는 독립적인 뷰로 남아서 테이블과 같이 데이터를 직접 인을
수도 있다. 또한, 특정 조건이 만족된다면 조건 절을 뷰에 밀어 넣거나, 뷰 바깥으로 끌어올 수도 있다.
뷰가 머지되지 않으면, VIEW 연산자를 볼 수 있다. 뷰 연산은 개별적으로 수행되며, 뷰로부터 행들이
리턴된 다음에 다음 연산이 수행 될 수 있다.
때때로 뷰가 머지 될 수 없고, 별개의 쿼리 블록 내에서 독립적으로 실행되어야 할 때도 있다. 이
경우에도 실행 계획에서 VIEW 연산자를 확인 할 수 있다. VIEW 키워드는 해당 뷰가 별개의 쿼리
블록으로 실행되었음을 나타낸다. 예를 들어, GROUP BY 함수를 포함한 뷰들은 머지 될 수 없다.
그림의 두 번째 예제는 머지가 불가능한 inline 뷰를 보여준다. inline 뷰는 기본적으로 문장의 FROM 절
내부에 포함된 쿼리이다. 기본적으로 이 연산자는 쿼리 블록으로부터 모든 행들을 수집한 후에야 이
행들이 실행 계획의 상위 연산에 의해서 처리 될 수 있다.
카운트 스톱 키(Count Stopkey) 연산자
그림 4.35
COUNT STOPKEY는 리턴 될 행들의 개수를 제한한다. 이러한 제한은 WHERE 절에 ROWNUM
표현식으로 나타난다. 이 연산은 카운트 값이 도달하면 현재의 연산을 중지한다.
참고 : 이 연산자의 비용은 사용자가 인어 오고자 하는 값들의 발생 횟수에 의존한다. 만약, 이 값이
테이블 내에서 매우 자주 나타난다면, 이 카운트는 매우 빠르게 도달하게 된다. 만약, 이 값이 자주
나타나지 않고, Index가 존재하지 않으면, 시스템은 카운트 값에 도달 할 때까지 테이블의 모든 블록을
읽어야 할 것이다.
Min/Max와 First Row 연산자
그림 4.36
FIRST ROW는 쿼리에 의해 선택된 첫 번째 행만을 읽어온다. 이 연산자는 첫 번째 값이 리턴되면 데이터
액세스를 중지한다. 이것이 Oracle 8i에 소개된 최적화이며 Index 범위 스캔 및 Index 전체 스캔과 함께
동작한다.
위 예제는 ID 컬럼에 Index가 존재한다고 가정하였다.
조인 방법
그림 4.37
행 원본은 쿼리에 의해 액세스 될 수 있는 데이터 집합이다. 이것은 테이블, Index, 머지가 불가능한 뷰,
하다 못해 많은 다양한 객체들로 구성된 조인 트리의 결과 집합일 수도 있다. 조인 조건은 WHERE 절
내의 조건이며, 조인되는 두 테이블의 컬럼들을 결합한 것이다.
비 조인 조건은 오직 하나의 테이블을 참조하는 WHERE 절의 조건이다.
조인 연산은 두 개의 행 원본(테이블 또는 뷰와 같은)의 출력을 결합하여 하나의 행 원본(데이터집합)을
리턴한다. Optimizer는 다음과 같은 서로 다른 조인 방식을 지원한다.
① 중첩 루프 조인(Nested Loop join) : 데이터의 작은 부분 집합이 조인 중이고, 조인 조건 이 두 번째
테이블을 접근하는 효율적인 경로인 경우에 유용하다.
② 소트 머지 조인(Sort-merge join) : 두 개의 독립된 원본으로부터 행들을 조인해야 할 때 사용 할 수 있다.
만약, 하나 이상의 행 원본이 이미 정렬되어 있다면, 해시 조인이 일반 적으로 소트 머지 조인보다 성능이 우수하다.
③ 해시 조인(Hash join) : 대량의 데이터 집합을 조인 할 때 사용된다. Optimizer는 두 개 의 테이블 또는 데이터
원본 중에서 더 작은 것을 조인키로 해시하여 메모리에 해시 테이블을 구축한다. 이 방식은 작은 테이블이
가용 메모리에 충분히 적재 될 수 있는 경우 에 가장 많이 사용된다. 비용은 두 테이블의 데이터를 한번 읽는 비용으로 제한된다.
참고 : 위 그림은 ANSI와 비-ANSI 조인 구문을 사용한 동일한 쿼리를 보여준다.
중첩 루프 조인
그림 4.38
두 테이블 중 하나는 아우터(outer) 테이블 또는 드라이빙 테이블 또는 왼쪽 테이블이라고 정의하며,
다른 테이블은 이너(inner) 테이블 또는 오른쪽 테이블이라고 부른다. 단일 테이블 조건을 만족하는
아우터 테이블의 각 행들에 대하여, 조인 조건을 만족하는 이너 테이블의 모든 행들이 읽혀진다.
조인 조건과 비-조인 조건이 결합된 복합 Index가 사용되지 않는 한, 이너 테이블의 모든 비-조인 조건은
이너 테이블의 행이 읽혀진 후에 고려된다.
위 그림을 기반으로 중첩 루프를 모사한 코드는 다음과 같다.
for r1 in (EMP에서 단일 테이블 조건을 만족하는 행들을 선택) loop
for r2 in (EMP의 현재 행과 일치하는 DEPT의 행들을 선택) loop
EMP의 현재 행과 DEPT의 현재 행으로부터 값을 출력
end loop;
end loop
Optimizer는 두 개의 테이블 간에 우수한 드라이빙 조건을 가지고 있고, 행 중에서 일부 적은 수의 행들을
조인하는 경우에 중첩 루프 조인을 사용한다. 아우터 루프에서 이너 루프로 구동되므로 실행 계획에서 테이블의
순서는 중요하다. 두 개의 독립된 행 원본을 조인하는 경우에는 다른 조인 방식을 사용하여야만 한다.
중첩 루프 조인 : 프리패칭(Prefetching)
그림 4.39
Oracle 9i R2에서는 중첩 루프 프리패칭이라고 부르는 메커니즘을 등장시켰다. 이것은 rowid 룩업을
한 번에 모아서 일괄 처리하면 블록을 병렬로 인을 수 있기 때문에 테이블 룩업을 수행하는 Index
액세스의 I/O 이용률, 즉 응답 시간을 향상시킬 수 있을 것이라는 아이디어에서 착안한 것이다.
실행 계획 출력에서 이러한 변경 사항은 고려되지 않기 때문에 조인 순서, 조인 방식, 액세스 방법,
병렬화 방법에 영향을 미치지 않는다.
이 최적화는 이너 테이블의 액세스 경로가 Index 범위 스캔 일 때만 발생하며, 반면에 Index 고유
스캔일 때는 발생하지 않는다.
프리페칭 메커니즘은 테이블 룩업에서만 사용된다. Index 액세스 경로가 선택되고 Index 단독으로는
쿼리가 만족되지 않는다면, ROWID에 의해서 식별된 행 데이터들은 반드시 인출되어야 한다. 데이터 행을
ROWID로 액세스(테이블 룩업)하는 작업은 데이터 블록 프리페칭을 사용하면 향상되는데, 이것은
ROWID들의 배열이 가리키는 블록들의 배열을 읽어오는 것이다.
데이터 블록 프리페칭을 사용하지 않으면 클러스터가 되지 않은 B*-트리 Index를 사용하여 많은 행들을
액세스하는 것은 비용을 상승 시킬 수도 있다. Index에 의해 액세스 되는 각 행은 아마도 별개의 데이터
블록에 존재 할 것이며, 별도의 I/O를 요청 할 수 있다.
데이터 블록 프리페칭을 사용하면, 시스템은 Index에 의해 식별된 다수의 행들을 액세스 할 준비가
될 때까지 데이터 블록 인기를 지연시키며, 이 후, 한번에 하나의 데이터 블록을 인지 않고, 동시에
여러 개의 데이터 블록을 읽어오게 된다.
중첩 루프 조인 : 11g 구현
그림 4.40
Oracle Database 11g는 NESTED LOOPS 연산자를 이용하여 조인을 수행하는 새로운 방법을 소개 하였다.
이 NESTED LOOPS 구현을 사용하면, 시스템은 다른 테이블과 해당 Index 사이에 NESTED LOOPS 조인을
먼저 수행한다. 이 작업은 Index를 이용하여, 테이블에서 해당되는 행을 찾기 위해 사용 할 ROWID의
집합을 만들어 낸다. 첫 번째 NESTED LOOPS 조인에 의해 만들어진 각 ROWID를 가지고 테이블로
이동하는 대신에 시스템은 ROWID들을 일괄적으로 모아서 ROWID 들과 테이블 간에 두 번째 NESTED
LOOPS 조인을 수행한다. ROWID 일괄 처리 기법은 시스템이 테이블내의 각 블록들을 오직 한번만
읽으면 되므로 성능을 향상시킨다.
소트 머지 조인
그림 4.41
소트 머지 조인에서는 드라이빙 테이블의 개념이 존재하지 않는다. 소트 머지 조인은 다음과 같이 실행된다.
1. 모든 액세스 및 필터 조건을 이용하여 첫 번째 데이터 집합을 얻고 조인 컬럼으로 정렬 한다.
2. 모든 액세스 및 필터 조건을 이용하여 두 번째 데이터 집합을 얻고 조인 컬럼으로 정렬 한다.
3. 첫 번째 데이터 집합에서 각 행에 대해 두 번째 데이터 집합의 시작점을 찾은 다음 조인 되지 못하는 행들을 찾을 때까지 스캔을 진행한다.
머지 작업은 조인 조건에 사용되는 컬럼들에서 일치되는 값을 포함하는 행들의 짝을 인어 오기 위하여 두 개의 정렬된 행 원본을 결합한다.
만약, 하나의 행 원본이 이전 작업(예를 들어, 조인 컬럼에 Index가 존재)에서 이미 정렬되었다면, 소트 머지 연산은 해당 행 원본에 대하여
정렬 작업을 생략한다. 머지 조인을 수행 할 때, 다음 연산으로 첫 번째 행을 리턴하기에 앞서 두 개의 행 원본으로부터 모든 행들을 반드시
인출하여 야 한다. 정렬이 메모리 내에서 수행되지 못하면, 이 조인 기법의 비용은 높아 질 수 있다. 옵티마이저는 다음 조건 중, 어느 하나라도
만족하게 되면, 대량의 데이터를 조인 할 때, 해시 조인 보다 소트 머지 조인을 선택 할 수 있다.
① 두 테이블 간에 조인 조건이 등가 조인이 아닌 경우
② 이전 연산에 의해 정렬이 이미 수행된 경우
참고 : 소트 머지 조인은 두 테이블 간의 조인 조건이 <, <=, >, >=과 같은 비등가 조건 일 때, 더욱 유용하다.
해시 조인
그림 4.42
두 개의 행 원본 간에 해시 조인을 수행하려면, 시스템은 첫 번째 데이터 집합을 읽고 메모리 내에 해시
버킷 배열을 구축한다. 해시 버킷은 빌드 테이블 내에 저장된 행들의 연결 리스트의 시작점처럼 동작하는
위치에 지나지 않는다. 행들은 해당 행의 조인 컬럼 또는 컬럼들에 내부 해시 함수를 적용하여 얻어온
결과와 버킷 번호가 일치하는 버킷에 소속된다.
시스템은 사용 가능한 모든 액세스 메커니즘을 사용하여 행들의 두 번째 집합을 읽기 시작하고, 해당
행과 연관된 해시 버킷의 번호를 계산하기 위해, 조인 컬럼 또는 컬럼들에 동일한 해시 함수를 사용한다.
그 다음, 해당 버킷에 행들이 존재하는지 확인하게 된다. 이 과정은 해시 테이블 탐침(probing)이라고 알려져 있다.
만약, 관련 버킷에 행들이 존재하면, 행들이 정확히 일치가 되는지 확인하기 위해 조인 컬럼 또는
컬럼들을 대상으로 정확한 체크 작업을 수행한다. 정확한 체크 작업으로 살아남은 행들은 즉시
리턴되거나 실행 계획의 다음 단계로 전달된다. 해시 조인을 수행하면, 다음 연산에 첫 번째 행을
리턴하기 위해 가장 작은 행 집합으로부터 모든 행들이 반드시 인출되어야 한다.
참고 : 해시 조인은 등가 조인에서만 동작하며 대량의 데이터를 조인하는데 가장 유용하다.
카르테시안 조인
그림 4.43
카르테시안 조인은 하나 이상의 테이블들이 조인 조건을 가지지 않고 다른 테이블을 조인하는 경우에
사용된다. Optimizer는 첫 번째 데이터 원본으로부터 각각의 행들을 다른 데이터 원본의 모든 행들과
조인하여, 두 집합의 카르테시안 프로덕트를 만들어낸다.
카르테시안 조인은 첫 번째 행 원본을 읽고 다른 행 원본의 모든 행들을 리턴하는 제한 없는 중첩 루프
조인처럼 보일 수 있다.
참고 : 카르테시안 조인은 일반적으로 선호되지 않는다. 그러나, 단일 행 집합(예를 들어 고유 인덱스에
의해 한건 보장)을 다른 테이블과 조인하는 경우에는 완벽히 허용 가능하다.
조인 유형
조인 연산의 유형은 다음과 같다.
① 조인(등가 조인 및 비 등가 조인) : 조인 조건에 일치하는 행들을 리턴
② 아우터 조인 : 조인 조건에 일치하는 행들과 일치되지 않는 행들을 리턴
③ 세미 조인 : EXISTS 서브쿼리에서 일치되는 행들을 리턴. 이너 테이블에서 일치되는 한 건을 찾으면 검색을 중지
④ 안티 조인 : NOT IN 서브쿼리에서 일치되지 않는 행들을 리턴, 일치되는 한 건이 발견되면 중지
⑤ 스타 조인 : 조인 유형은 아니지만 팩트(fact) 및 디멘전(dimension) 모델을 효율적으로 처리하기 위해 성능 최적화를 구현한 것
안티 조인과 세미 조인은 서브쿼리 형태이지만 조인 유형으로 다룰 수 있다. 안티 조인과 세미 조인은
서브 쿼리를 조인과 유사한 방식으로 풀이하는 내부 최적화 알고리즘이다.
등가 조인과 비 등가 조인
그림 4.44
조인 조건은 조인이 등가 조인 인지 비등가 조인 인지를 결정한다. 등가 조인은 조인 조건이 ‘=’ 연산자를
포함한 조인이며, 두 테이블이 ‘=’이 아닌 조인 조건으로 연관된 것을 비등가 조인이라고 한다.
등가 조인이 가장 일반적으로 사용된다. 그림에서는 등가 조인과 비등가 조인을 보여준다. 비등가조인은 자주 사용되지 않는다.
SQL의 효율을 향상시키려면 가능하면 등가 조인을 사용한다. 변환되지 않은 컬럼 값에 등가 조인을 수행하는 문장은 튜닝하기가 쉽다.
참고 : 비 등가 조인은 해시 조인이 불가능하다.
아우터 조인
그림 4.45
시스템 내에서는 단순 조인이 가장 일반적으로 사용된다. 아우터 조인은 추가 기능을 가지고 있는데,
좀더 특별하게 사용 할 수 있다. 아우터 조인 연산자는 쿼리의 부족한 부분에 위치시킨다. 다르게
표현하면, 누락된 조인 정보를 갖는 테이블에 연산자를 위치시킨다. EMP와 DEPT를 가정 해보자. 여기에
사원이 없는 부서가 있을 수 있다. 만약, EMP와 DEPT를 같이 조인하면 해당 부서는 조인 조건에
일치하는 행이 없기 때문에 그 부서는 출력에 나타나지 않는다. 아우터 조인을 사용하면 누락된 부서도 표시 할 수 있다.
① 중첩 루프에 의한 아우터 조인 : 좌측/드라이빙 테이블은 행이 보존되어야 할 테이블(예제에서는 DEPT)이다.
DEPT의 각 행에 대하여 EMP에서 일치되는 행들을 검색한다. 만약, 일치되는 행이 발견되지 않으면, DEPT 값은
Null 값을 갖는 EMP 컬럼과 같이 출력된다. 만약, 행이 발견되면 DEPT는 EMP값과 함께 출력된다.
② 해시 아우터 조인 : 행을 보존해야 하는 좌측/아우터 테이블이 해시 테이블을 구축하는데
사용되고 우측/이너 테이블이 해시 테이블을 탐침하는데 사용된다. 일치되는 행이 발견되면 해당 행은
출력되고, 해시 테이블 내의 해당 엔트리는 일치된 행으로 표시된다. 이너 테이블을 모두 소진한
다음에는 해시 테이블을 다시 한번 인고, 표시되어 있지 않은 모든 행들을 EMP 컬럼의 Null 값과 함께
출력한다. Oracle Database 10g에서는 HASH JOIN RIGHT OUTER가 사용 가능하다. 시스템은 보존 될
필요가 없는 행들을 가진 테이블을 해시한 다음, 보존되어야 할 행들을 가진 테이블을 읽은 다음, 조인
할 행들이 존재하는지 해시 테이블을 탐침한다.
참고 : full, left, right outer join과 같은 ANSI 구문을 사용 할 수도 있다.
세미 조인(Semijoin)
그림 4.46
세미 조인은 첫 번째 조인 레코드가 발견되면 결과를 리턴한다. 세미 조인은 EXISTS 서브쿼리를
조인으로 변환하는 내부 방식이다. 그러나, 이러한 변환을 아무 때나 볼 수는 없다.
세미 조인은 조건 절 우변에서 여러 행들이 서브쿼리의 조건을 만족 했을 때, 조건절의 좌변으로부터
중복되는 행들을 제외하고 EXISTS 서브쿼리에 일치하는 행들을 리턴한다.
위 그림에서 각 DEPT 레코드에 대하여 EMP 레코드에서 최초로 일치되는 행만이 조인 결과에 리턴된다.
이것은 조인 조건을 만족하는 행들의 개수는 관심이 없고, 행들의 존재 여부에만 관심이 있을 때,
테이블 내에서 중복된 많은 행들을 스캔 하지 않도록 해준다.
서브쿼리의 중첩이 해제되지 않으면, FILTER 연산을 사용하여 동일한 결과를 얻을 수 있고, 일치 된 행이
발견 될 때까지 행 원본이 스캔 되고, 해당 행을 리턴한다.
참고 : Optimizer는 세미 조인을 수행하기 위해 중첩 루프, 머지 조인, 해시 조인 알고리즘을 사용 할
수 있다. 또한, 새로운 HASH JOIN RIGHT SEMI 최적화도 가능하다.
안티 조인(Antijoin)
그림 4.47
안티 조인은 우변의 서브쿼리가 일치하지 않는(NOT IN) 행들을 리턴한다. 예를 들어, 안티 조인은 사원이
없는 부서를 선택 할 수 있다. Optimizer는 기본적으로 NOT IN 서브쿼리에 대하여 중첩 루프 알고리즘을
사용한다. 그러나, MERGE_AJ, HASH_AJ, NL_AJ 힌트를 사용한다면, 다양한 요구 조건을 만족시킬 수 있다.
상관 관계 서브쿼리가 아닌 NOT IN 서브쿼리는 소트 머지 또는 해시 안티 조인으로 변환 될 수 있다.
Oracle Database 11g에서는 또 다른 최적화 방법으로 HASH [RIGHT] ANTI NA(NA for Null Aware)
연산을 수행한다.
안티 조인이 사용자에게 가장 투명한 방식이라고 할지라도 이러한 조인 형식이 존재하는 것을 알고 있는
것은 매우 유용하며, Database 버전 간에 예기치 못한 성능 변화를 설명하는데 도움 을 줄 수도 있다.
그 외, N-배열 연산
① FILTER
② CONCATENATION
③ UNION ALL/UNION
④ INTERSECT
⑤ MINUS
Filter 연산
그림 4.48
FILTER 연산은 다른 단계에서 리턴된 행들을 버리는 연산이며, 이 연산에서 행들을 읽어오지는 않는다.
연산의 모든 정렬 연산은 서브쿼리와 단일 테이블 조건을 포함하는 필터가 될 수 있다.
좌측의 예제에서 FILTER는 GROUP BY에 의해 생성된 집합에 적용된다. 우측의 예제에서 FILTER는
NESTED LOOPS와 동일한 방식으로 사용된다. DEPT가 한번 액세스되고 DEPT로부터 읽혀진 각 행은
DEPTNO의 Index에 의해 EMP가 액세스 된다(3번 작업).
이 연산은 DEPT 테이블의 행 개수만큼 반복된다.
DEPT의 행들이 인출된 후, 각 행에 대하여 FILTER 연산이 적용된다. FILTER는 3번 작업에서 리턴된 행들
중에 최소 한 건이 TRUE가 되면 행을 버린다.
연결(Concatnation) 연산자
그림 4.49
CONCATENATION은 두 개 이상의 행 집합에 의해서 리턴된 행들을 연결한다. 이것은 UNION ALL가
유사하며 중복된 행들을 제거하지 않는다.
이 연산은 OR 확장에 사용된다. 그러나, OR은 중복된 행들을 리턴하지 않으므로 부정조건을 추가 한다(LNNVL).
CONCATENATION
① BRANCH 1 – SAL=2
② BRANCH 2 – DEPTNO=1 AND NOT row in Branch 1
LNNVL 함수는 부정 조건을 처리하기 위해 OR 구문에 의해 생성된다. LNNVL() 함수는 조건이 NULL
또는 FALSE이면 TRUE를 리턴한다. 그래서 필터(LNNVL(SAL=2))는 SAL != 2 또는 SAL is NULL인 모든 행을 리턴한다.
UNION [ALL], INTERSET, MINUS
그림 4.50
SQL은 언어의 다양한 위치에 ALL 또는 DISTINCT를 추가하여 중복된 행들을 처리한다. ALL은 중복된 행을
보존하고 DISTINCT는 제거한다. 사용 가능한 SQL 집합 연산자를 빠르게 살펴보면 다음과 같다.
① INTERSECTION : 행들의 두 집합을 연산하여 중복을 제거하고 교집합을 리턴한다. 각각의 문장은 별도로
실행되거나 개별적으로 최적화 된다. 소트 머지 조인과 매우 유사하며, 전 체 행이 정렬되고 일치된다.
② MINUS : 행들의 두 집합을 연산하여 첫 번째 집합에만 나타나는 행들을 리턴하며, 중복 된 행은 제거한다.
각각의 문장은 별도로 실행되거나 개별적으로 최적화 된다. INTERSECT 처리와 유사하다. 그러나, 행들이
일치되면 리턴하는 대신, 일치되면 배제하는 연산이다.
③ UNION : 행들의 두 집합을 연산하여 합집합을 리턴하며, 중복된 행은 제거한다. 각각의 문장은 별도로
실행되거나 개별적으로 최적화 된다. 읽혀진 행들은 결합되고 중복된 행을 제거하기 위해 정렬된다.
④ UNION ALL : 행들의 두 집합을 연산하여 합집합을 리턴하지만 중복된 행을 제거하지 않는다. 고비용의
정렬 연산은 필요하지 않다. 만약, 중복된 행을 다룰 필요가 없다면 UNION ALL을 사용하도록 한다.
결과 캐시(Result Cache) 연산자
그림 4.51
SQL 쿼리 결과 캐시는 쿼리 결과 집합과 쿼리 단편을 Database 메모리에 명시적으로 캐시 할 수 있도록
해준다. Shared Pool 내에 저장된 전용 메모리 버퍼가 캐시 결과를 저장하고 읽어오는데 사용 될 수 있다.
이 캐시에 저장된 쿼리 결과는 쿼리에 의해 액세스 되는 Database 객체 내의 데이터가 수정되면
무효화된다. SQL 쿼리 캐시는 모든 쿼리에 대하여 사용 될 수 있지만, 매우 많은 행들에 액세스하여 극히
일부를 리턴하는 문장들이 좋은 대상이 된다. 주로 데이터웨어 하우스 애플리케이션에서 사용된다.
만약, 쿼리 결과 캐시를 사용하기 위해 RESULT_CACHE_MODE 초기화 파라메터를 MANUAL로
설정하였다면, 쿼리에 RESULT_CACHE 힌트를 반드시 명시적으로 지정하여야 한다. 이것은 쿼리에 대한
실행 계획에 Result_Cache 연산자를 추가한다. 쿼리를 실행하면 Result_Cache 연산자는 쿼리 결과가
캐시에 이미 존재하는지를 확인하기 위해 결과 캐시 메모리를 검색한다. 만약, 쿼리 결과 가 존재하면,
해당 결과는 캐시 외부로 직접 읽혀진다. 만약, 결과가 캐시에 아직 존재하지 않는 다면, 쿼리를 실행한
후, 리턴된 결과를 결과 캐시 메모리에 저장한다. 만약, RESULT_CACHE_MODE 초기화 파라메터를
FORCE로 지정하였고, 결과 캐시에 쿼리의 결과를 저장하고 싶지 않다면, 쿼리에 NO_RESULT_CACHE
힌트를 반드시 사용하여야 한다.
'oracle11R2 > SQL Tuning 11g' 카테고리의 다른 글
| 06장. 케이스 스터디 : 스타 변환 (0) | 2011.06.23 |
|---|---|
| 05장. 실행 계획의 해석 (0) | 2011.06.23 |
| 03장. 옵티마이저 개요 (0) | 2011.06.15 |
| 02장. SQL 튜닝 개요 (0) | 2011.06.15 |
| 01장. 오라클 데이터베이스 아키텍처 (0) | 2011.06.13 |
