DB Research & Study Group - www.bysql.net (with durinuri.com)

2.3.4. 해쉬 (Hash) 조인

휘휘 — Sun, 13 Sep 2009 15:00:18 +0900

p 565 - 605

2.3.4. 해쉬 (Hash) 조인
데이터 처리범위가 초기보다 초대형으로 증가하면서 Sort Merge로 처리할수 없는 수준에 까지 도달
해쉬조인이 랜덤와 정렬의 부담을 벗어나게 할수 있는 원리를 알필요가 있음
- 인덱스를 경유하여 랜덤을 하지 않고 해쉬함수를 이용한 연결을 수행
- 파티션 단위로 처리하기 때문에 대량의 처리에 수행속도가 급격히 상승하지 않음

해쉬영역(Hash Area)
- 해쉬조인을 수행하기 위해 메모리 내에 만들어진 영역
- 비트맵 백터 ; 먼저 액세스 하는 빌드 입력의 유일한 값을 생성해 두었다가 나중에 검색하는 검색입력을 필터링
   해쉬테이블 ; 파티션들의 위치정보를 가지고 있으며 조인의 연결 작업을 수행하거나 디스크의 파티션 짝들을 찾는데 사용
   파티션테이블 ; 여러개의 파티션이 존재, 조인할 집합의 실제 로우들을 가짐

파티션 (Partition)
- 파티션을 결정하기 위해 수행하는 첫번째 해쉬함수가 리턴한 동일한 해쉬값을 갖는 묶음 (동일한 해쉬갑을 갖는 로우들의 버캣)
- Fan-out ; 인메모리에서 작업이 불가능하여 만들어진 파티션의 수
- 하나의 파티션은 여러개의 클러스터로 분리
- 2차 해싱을 하면 저장할 클러스터의 위치가 결정( I/O의 단위, 검색 단위 로활용)

클러스터(Cluster)
- 파티션의 분할
- 연속된 블록으로 이루어지며 디스크와 I/O를 하는 단위
- Hash_multiblock_io_count에 의해 동시 I/O 양 결정
- 해쉬테이블을 만들어 해당 클러스터를 찾고 클러스터를 스캔하여 원하는 로우와 연결

빌드입력(Build Input)과 검색입력(Probe Input)
- 빌드입력 ; 조인을 위해 먼저 액세스하여 필요한 준비를 처리
- 검색입력 ; 액세스하면서 조인을 수행하는 처리(검정 혹은 검색입력)

인-메모리(In-Memory) 해쉬조인과 유예 해쉬조인
- 인메모리 해쉬조인 ; 빌드입력이 해쉬영역에 모두 위치할수있는 경우 수행
- 유예 해쉬조인 ; 빌드입력이 해쉬영역에 모두 위치할수 없는 경우 수행

비트맵 백터(Bitmap Vector)
- 빌드입력에 대해 파티션을 구하는 작업중에 생성
- 빌드입력값들에 대한 유일한 값을 메모리 내의 해쉬영역에 정의하는것( 검색입력의 파티션 생성작업시 필터링에사용)
- 해쉬영역에 만들어지는 비트맵 벡터는 빌드 입력의 전체 집합에 대해서 생성
(처리대상이 커서 해쉬영역을 초과하여 임시 세그먼트에 저장하게 되더라도 처리할 빌드 입력의 모든 집합에 대해 조인 종료시까지 유지)

해쉬 테이블(Hash Table)
- 메모리 내에 생성
- 조인의 연결 작업에서 대응되는 로우를 찾기 위한 해쉬 인덱스로 사용 (조인수행시 임시적으로 생성)
- 해쉬값과 해쉬 클러스터의 주소를 보유
- 검색입력에 있는 연결고리 값으로 해쉬키 값을 찾고, 그곳에 있는 클러스터 주소를 이용해 해당 클러스터를 찾아 스캔
- 해당 처리 단위의 빌드 입력에 대해서만 정보를 가지고 있음 (연결작업을 위한 인덱스 개념)

파티션 테이블 (Partition Table)
- 빌드입력이 메모리 크기를 초과하여 파티션을 생성하게 되면 관련정보가 저장되는곳
- 디스크의 임시세그먼트로 이동하면 그 위치정보 또한 보유
- 같은 묵음으로 처리되는 단위마다 파티션의 크기의 합이 적은 집합을 빌드입력으로, 나머지는 검색입력의 역할
- 동적 역할 반전 (Dynamic Role Reverse) ; 처리 되는 단위마다 동적으로 역할이 변함

2.3.4.1. 인-메모리 해쉬조인

* 빌드입력; 빨간색(통계정보를 참조하여 효과적인 카디널리티를 갖는 집합선택)

- 조인의 연결을 위해서 기존의 인덱스를 전혀 사용하지 않음 (인라인뷰에서 가공한집합과 조인하는경우 유용)
- 부분처리가 가능 (빌드 입력이 처리범위를 줄여줄수있거나 소형인경우에는 유용)

2.3.4.2. 유예 해쉬조인

- 빌드 입력이 해쉬영역을 최과하게 되면 디스크에저장하여 처리
- 인-메모리의 처리과정에 임시 세그먼트를 생성하여 디스크상에 위치시켜가면서 처리시키는 과정이 추가
- 연결을 수행할수 없는 파티션들을 디스크로 이동

- 미리 생성되어 있는 인덱스를 사용하지 않음
- 부분범위 처리가 불가능하게됨
- 해쉬조인이 소트머지 조인보다 더많은 메모리 영역을 필요 (처리량에 따라 손익의 차이가 나는 시점이 존재)
- SORT MERGE조인과 해쉬조인의 큰차이가 나지 않는경우 해쉬조인은 추가적인 SORT과정이 필요할수 있음

2.3.5.세미(Semi) 조인
- 서브쿼리를 사용했을 대 메인쿼리와의 연결하는 처리

2.3.5.1. 세미조인의 개념 및 특징
- 서브쿼리를 사용했을 때 메인 쿼리와의 연결을 하기 위해 적용되는 광범위한 유사 조인을 의미
- 조인은 동일한 동급상에 위치 서브쿼리는 주종관계를 가짐
- 조인은 집합의 곱 1*1=1, 1*M=M, M*1=M,M*M=MM(카티젼곱) 과 동일한 결과집합을 가짐
- 세미조인은 메인쿼리의 결과집합과 동일
- 서브쿼리는 메인쿼리의 컬럼을 마음대로 사용할수있지만 메인쿼리는 서브쿼리의 집합에 잇는 컬럼들을 사용할수 없음

2.3.5.2. 세미조인의 실행계획
가) Nested Loops 형 세미조인

- 서브쿼리가 '제공자' 역할을 할수도 '확인자' 역할을 할수도 있음
- 메인쿼리 집합의 보호

제공자
SELECT COL1,COL2,...
FROM TAB1 x
WHERE KEY1 IN (SELECT KEY2 FROM TAB2 y
                       WHERE y.COL1 ... AND y.COL2 ... );

실행계획

NESTED LOOPS
    VIEW
        SORT (UNIQUE) ---> 메인 쿼리 집합을 보호
            TABLE ACCESS BY ROWID OF TAB2
                INDEX RANGE SCAN OF COL1_IDX
TABLE ACCESS BY ROWID OF TAB1
    INDEX RANGE SCAN OF KEY1_IDX

확인자

SELECT 사번,성명,직급,입사일,....
FROM 사원 X
WHERE 부서='경리과'
    AND 사번 IN (SELECT 사번 FROM 가족 y
                      WHERE y.사번=x.사번 <-- 논리적으로 종속시킴 (실행에는 필요없음)
                        AND y.생년월일 < '20050101');

실행계획

FILTER
    TABLE ACCESS (BY ROWID) OF '사원'
            INDEX (RANGE SCAN) OF '부서_INDEX'
    TABLE ACCESS (BY ROWID) OF '가족'
            INDEX (RANGE SCAN) OF 'PK_INDEX'

SUB쿼리에 메인쿼리 집합의 컬럼을 종속시키게 되면 서브쿼리가 우선 실행되게 되어
경우에 따라 실행계획의 비효율이 발생할수도 있다.

나) Sort Merge 형 세미조인
연결고리의 이상이 발생하거나 대량의 데이터를 연결해야 할 때는 세미조인역시 SORT MERGE형 조인이 적용될수 있음

실행계획의 예

SELECT STATEMENT
    MERGE JOIN
        SORT (JOIN)
            TABLE ACCESS (FULL) OF '사원'

                SORT (JOIN)
                VIEW
                    SORT (UNIQUE)
                        TABLE ACCESS (BY ROWID) OF '근태'
                            INDEX (RANGE SCAN) OF '유형_일자_IDX' (NON-UNIQUE)

다) 필터(Filter)형 세미조인

먼저 수행하여 액세스한 결과를 서브쿼리를 통해 체크하여 취할것인지 아니면 버려야 할것인지를 결정하는 역할
버퍼내에 이전의 값을 저장해 두었다가 대응되는 집합을 액세스하기 전에 먼저 저장된값과 비교함으로 액세스를 최소화
EXISTS를 사용한 서브쿼리에서 대부분 나타남(언제나 확인자 역할만 가능함)

SELECT ... FROM ORDER x
WHERE ORDDATE LIKE '200506%'
    and    EXISTS (SELECT 'X'
                       FROM DEPT y
                       WHERE y.DEPTNO = x.SALDEPT and y.TYPE1 = '1')

실행계획
FILTER
    TABLE ACCESS (BY ROWID) OF 'ORDER'
        INDEX (RANGE SCAN) OF 'ORDDATE_INDEX' (NON_UNIQUE)
    TABLE ACCESS (BY ROWID) OF 'DEPT'
        INDEX (UNIQUE SCAN) OF 'DEPT_PK' (UNIQUE)

필터 처리는 연결을 시도하다가 성공하는 로우를 만나면 연결작업을 멈춤
버퍼를 이용한 처리를 하여 랜덤 액세스 양을 최소화

라) 해쉬(Hash)형 세미조인

SELECT ... FROM ORDER x
WHERE ORDDATE LIKE '200506%'
    and    EXISTS (SELECT /*+ HASH_SJ(x,y) */ 'X'
                       FROM DEPT y
                       WHERE y.DEPTNO = x.SALDEPT and y.TYPE1 = '1')

실행계획
HASH JOIN SEMI
    TABLE ACCESS (BY ROWID) OF 'ORDER'
        INDEX (RANGE SCAN) OF 'ORDDATE_INDEX' (NON_UNIQUE)
    TABLE ACCESS (FULL) OF 'DEPT'

서브쿼리는 하나의 테이블만 존재
서브뭐리내에 또다시 서브쿼리를 사용했을 경우 사용불가
연결고리 연산자는 반드시 '=' 이어야 함
서브쿼리내에 GROUP BY,CONNECT BY, ROWNUM 을 사용할수 없음 (버전에 따라 다름)

마) 부정(Anti)형 세미조인
알지 못하는 값을 가지고 인덱스를 적용할 수 없기 때문에 연결 작업이 수행된다면 매우 심각한 부담이 발생

머지 부정형 조인으로 유도

SELECT COUNT(*) FROM TAB1
WHERE COL1 LIKE 'ABC%'
    and COL2 IS NOT NULL
    and COL2 NOT IN (SELECT /*+ MERGE_AJ */ FLD2
                           FROM TAB2
                           WHERE FLD3 between '20050101' and '20050131'
                                and FLD2 IS NOT NULL);

실행계획
SELECT STATEMENT Optimizer=FIRST_ROWS
    MERGE JOIN (ANTI)
        SORT (JOIN)
            TABLE ACCESS (BY ROWID) OF 'TAB1'
                INDEX (RANGE SCAN) OF 'COL1_INDEX' (NON-UNIQUE)
        SORT (UNIQUE)
            VIEW
                TABLE ACCESS (BY ROWID) OF 'TAB2'
                    INDEX (RANGE SCAN) OF 'FLD3_INDEX' (NON-UNIQUE)

- 반드시 NOT IN 을 사용, 비용기준 옵티마이져, NOT NULL 이 보장

해쉬 부정형 조인으로 유도

SELECT COUNT(*) FROM TAB1
WHERE COL1 LIKE 'ABC%'
    and COL2 IS NOT NULL
    and COL2 NOT IN (SELECT /*+ HASH_AJ */ FLD2
                           FROM TAB2
                           WHERE FLD3 between '20050101' and '20050131'
                                and FLD2 IS NOT NULL);

실행계획
SELECT STATEMENT Optimizer=FIRST_ROWS
    HASH JOIN (ANTI)
            TABLE ACCESS (BY ROWID) OF 'TAB1'
                INDEX (RANGE SCAN) OF 'COL1_INDEX' (NON-UNIQUE)
            VIEW
                TABLE ACCESS (BY ROWID) OF 'TAB2'
                    INDEX (RANGE SCAN) OF 'FLD3_INDEX' (NON-UNIQUE)

-'NOT IN' 만가능
- NOT NULL 이라는 전제가 있을때만 가능 (HASH조인과 같은방법으로 처리하다가 성공여부 결정만 반대로 수행)
- 긍정형연산에서는 NULL은 어차피 찾는 값이 아니므로 문제 될것이 없음
- 부정형에서는 NULL이 비교연산에서 무시되어 원하는값과는 달라질수 있음

2.3. 조인 종류별 특징및 활용 방안

휘휘 — Sat, 12 Sep 2009 17:16:20 +0900

534-564
2.3. 조인 종류별 특징및 활용 방안

조인은 크게 두가지 분류로 나눌수 있다.
- 어떤 집합을 먼저 액세스 하여 상수값을 만들고 그것을 이용해 대응되는 집합을 찾아가는것 (Nested Loops)
- 서로 연결하기 좋도록 미리 소정의 선행작업을 수행한 다음 서로를 대응 시켜 가는 방법 (Sort Merge)

2.3.1. Nested Loops 조인
- 가장 전통적인 조인방법이다.
- 먼저 액세스한 결과를 다음의 액세스에 상수값으로 제공해준다.

2.3.1.1. Nested Loops 조인의 기본개념
가) Nested Loops 조인의 특징
- 순차적
( Driving TABLE의 처리범위에 있는 각각의 로우와 테이블간의 연결모두 순차적)
- 선행적
( 초기 액세스 테이블의 처리범위에 의해 처리량이 결정)
- 종속적
( 나중에 처리되는 테이블은 앞서 처리된 값을 받아 액세스)
- 랜덤 액세스
( 선행테이블은 첫번째 경우만 랜덤, 연결테이블은 모두 랜덤 액세스로 수행)
- 선택적
( 연결되는 방향에 따라 사용되는 인덱스들이 전혀 달라짐)
- 방향성, 연결고리 상태
( 연결고리의 인덱스 유무에 따라 액세스 방향및 수행속도에 많은 차이 발생)
- 부분범위 처리 가능
( 부분범위 처리 조건을 만족하면 운반단위가 채워지는 순간에 우선 멈출수 있음
- 체크조건의 영향력
( 부분범위의 경우 조건이 넓으면 운반단위를 빨리채우므로 빨리 수행
   전체범위 처리일 경우 가공할 대상이 많아지므로 수행속도가 나빠짐)

나) Nested Loops 조인의 적용 기준
- 부분범위 처리
- 조인되는 한쪽이 상대방 테이블의 결과를 제공받아야만 처리범위를 줄일수 있는 상황일 경우
- 처리량이 적은 경우 (많다면 부분처리)
- 연결고리에 이상이 없는 경우
- 먼저 수행한 집합의 처리범위의 크기와 얼마나 많은 처리 범위를 미리 줄여 줄 수 있느냐가 수행속도에 영햘을 미침
- 운반단위의 크기가 수행속도에 상당한 영향을 미침
- 대용량 처리일시라도 메모리 사용이 부담될경우 유리 (Sort조인 이나 해쉬조인은 과도하게 메모리 사용을 요구)

2.3.1.2. Nested Loops 조인의 순서결정
- 어떤순서로 수행되느냐에 따라 조인의 효율이 달라짐

SELECT ......
FROM TAB x, TAB2 y, TAB3 z
WHERE x.A1 = y.B1
    AND z.C1 = y.B2
    AND x.A2 = y.B2
    AND x.A2 = '10'
    AND y.B2 LIKE 'AB%';

(1) TAB1 - TAB2 -TAB3

순서	ACCESS PATH	설명
TAB1 x TAB2 y TAB3 z	A2='10' B1=A1 and B2 LIKE 'AB%' C1=B2	x.A1=y.B1 은 y가 아직 액세스 되지않았음 z.C1=y.B2 의경우 z가 아직 액세스 되지 않았음 이미 x 와 y가 액스세 되어있으므로 선택

(2) TAB2 - TAB3 - TAB1

순서	ACCESS PATH	설명
TAB2 y TAB3 z TAB1 x	B2 LIKE 'AB%' C1=B2 A1=B1 and A2='10'	y 중 상수값을 가진 값만 선택됨 y 가 이미 액세스 되었으므로 선택 이미 y와 z가 액세스 되어있으므로 x와 조인된 모든것 선택

(3) TAB3 - TAB2 - TAB1

순서	ACCESS PATH	설명
TAB3 z TAB2 y TAB1 x	FULL TABLE SCAN B2=C1 and B2 LIKE 'AB%' A1=B1 and A2='10'	z 의 유일한 조건인 z.c=y.b2 처리 불가능 z를 전체다 읽음 z 가 이미 액세스 되므로 z와 조인과 y의 상수대입 조건 수행 x 와 관련된 조건 수행

각각의 조건은 주어진 환경에 따라 성능이 달라진다.
자신의 처리범위를 줄여줄 수있는 다른 집합들이 액세스 된후에나
자신이 먼저 처리범위를 줄여줄 수 있다면 그들보다 먼저 수행도도록 하는것이 가능하면 좋다.

2.3.2. Sort Merge 조인
조인할 집합들을 정렬한후 서로 연결하여 수행

가) Sort Merge 조인의 특징
1) 동시적
( 상대 집합의 처리결과와 상관없이 자신이 보유한 처리조건만 가지고 액세스하여 정렬을 완료한후 머지 수행)
2) 독립적
( 자신에게 주어진 상사값에 의해서만 범위를 줄임)
3) 전체범위 처리
( 정렬이 완료된 후에라야 조인을 시작할수 있음)
4) 스캔방식
( 스캔방식으로 처리)
5) 선택적
( 연결고리가 되는 컬럼은 인덱스를 전혀 사용하지 않음)
6) 무방향성
(조인의 방향과는 거의 무관)
7) 자신의 처리범위를 줄이기위해 인덱스는 한가지만 사용되고 나머지는 머지 작업대상을 줄여주기 때문에 중요함

나) Sort Merge 조인의 적용기준
1) 전체처리로만 가능한 형태일 경우 검토
2) 상대 테이블에서 값을 받지 않고도 처리범위를 줄일수 있다면 효과가 큼
3) 스캔방식의 처리로 많은 양의 랜덤 액세스를 줄일수 있다.
4) 연결고리 이상 상태에 영향을 받지 않음
5) 자신의 처리범위를 줄여줄수 있는 인덱스 구성이 중요
6) 운반단위의 크기가 수행속도에 영향을 미치지 않음
7) 온라인 상태에서는 NL이 유리
8) 옵티마이져가 'ALL_ROWS'일경우 'SORT MERGE 조인' 이나 '해쉬조인'의 실행계획이 자주 수립됨
9) 충분한 메모리 활용이 가능하고 병렬처리를 통해 빠르게 정렬작업이 가능한경우

2.3.3. Nested Loops 조인과 Sort Merge 조인의 비교

1) 조건처리

SELECT a.FLD1, ..., b.COL1 ...
FROM TAB1 a, TBA2 b
WHERE a.KEY1 = b.KEY2
    AND a.FLD1 = '111'
    AND a.FLD2 like 'AB%'
    AND b.COL1='10' ---> 삭제

Nested Loops
- 삭제된 조건은 최종적으로 체크하는 역할을 수행하므로 일의양에는 큰차이가 없음
부분처리의 경우 운반단위가 빨리 채워졌으므로 더 유리

Sort Merge
- TAB2의 범위를 줄여주는 역할이었지만 삭제되었으므로 TAB2를 FULL SCAN후 정렬
   머지할 양의 증가

2) 전체 범위 처리의경우

SELECT a.Fld1, ...,b.COL1, ...
FROM TAB1 a, TAB2 b
WHERE a.KEY1=b.KEY2
ORDER BY a.FLD5, b.COL5;

Nested Loops
- TAB1 (어느한쪽도 될수있음) 이 먼제 전체 테이블을 스캔
- KEY1에 따라 KEY2인덱스에서 찾아 TAB2의 해당 로우를 액세스
- 전체 테이블을 대상으로 랜덤 액세스 발생 (대량의 랜덤액세스)
- ORDER BY 가 없다면 운반단위처리가 가능해져 수행속도가 빨라 질수 있다

Sort Merge 조인
- 각각의 테이블마다 전체 테이블 스캔이 발생하여 연결고리가 되는 컬럼으로 정렬 후 머지
- 대량의 랜덤 액세스 발생이 없음 (NL 조인보다 유리)
- Sort area size의 크기가 적으면 정렬작업에 대한 부하로 인해 NL보다 늦어질수도 있음

2부 2.3.(2.3.6-2.3.8) 조인 종류별 특징 및 활용 방안(스타조인, 비트맵조인인덱스)

balto — Mon, 07 Sep 2009 23:21:33 +0900

2.3. 조인 종류별 특징 및 활용방안

2.3.6. 스타조인

- 조인되는 집합들 간의 관계가 별처럼 생긴 형태이다(텍스트 그림 참조)

(예)

SALES(salesno, product_cd, dept_no, country_cd, type_cd, ...)

PRODUCTS(product_cd, ...)

DEPT(dept_no, ...)

COUNTRY(country_cd, ...)

TYPES(type_cd, ...)

- 여러 개의 디멘전(dimension) 테이블이 있을 때 이들 각각을 팩트테이블(fact table)에 연결시켜서는 안된다는 개념이다. 마스터테이블을 중심으로 테이블을 조인하되 마스터 테이블의 투플 수를 얼마나 줄여 가느냐가 중요한 포인트이다. 마스터테이블에 결합인덱스를 적절하게 구성하는 것이 중요하다.

- 해결해야할 문제점

(1) 마스터테이블의 디멘전 테이블 간에는 릴레이션이 없기 때문에 특별한 방법을 동원하여 디멘전테이블 간의 연결을 먼저 시도할 수 없다.

(2) 최적화로 액세스할 수 있는 인덱스의 구성이 중요하다.

- 스타조인은 디멘전의 카티젼 곱이 지나치게 많지 않을 때 사용한다.(MERGE JOIN(CARTESIAN))

- (예) DEPT와 PRODUCTS를 먼저 조인한다는 SALES 테이블과 조인하는 예(* DEPT_NO+PRODUCT_CD 인덱스가 존재한다면 HASH JOIN 대신 NESTED LOOP 조인이 사용된다.)

SELECT STATEMENT Optimizer=ALL_ROWS

HASH JOIN

MERGE JOIN(CARTESIAN)

TABLE ACCESS(FULL) OF 'DEPT'

BUFFER(SORT)

TABLE ACCESS(FULL) OF 'PRODUCTS'

TABLE ACCESS(FULL) OF 'SALES'

2.3.7. 스타변형조인

- (많이 생략하였습니다....)

- 스타조인의 단점을 개선하기 위한 방법이다. 비트맵 인덱스의 특징을 살린다.

- 비트맵을 이용한 인덱스머지를 한다.

- 예제(교재참조) - 생략

- 스타변형조인이 일어나기 위한 전제 조건

(1) 하나의 팩트 테이블과 2개 이상의 디멘전테이블

(2) 팩트 테이블에 있는 디멘전 속성(디멘전 테이블의 외부키)에 비트맵 인덱스 존재

(B-tree 인덱스가 있어도 비트맵 컨버전이 일어나서 스타변형조인이 발생 가능)

(3) 팩트테이블에 통계정보가 생성되어 있어야 함.

(4) Star_transformation_enabled 인자가 True 혹은 Temp_disable로 설정

(아니면 직접 STAR_TRANSFORMATION 힌트를 준다)

- 스타변형 조인의 제약조건 7가지(교재 참조)

- 옵티마이저가 스타변형조인을 하지 않는 경우

(1) 팩트 테이블이 가진 조건만으로도 충분히 처리범위가 줄어들 경우

(2) 팩트 테이블 크기가 너무 작아 스타변형조인이 필요없다고 판단될 경우

2.3.8. 비트맵 조인 인덱스

- 비트맵 조인 인덱스 사용방법

- 생성 : 생성된 인덱스는 cust_job 속성이 SALES 팩트 테이블 컬럼처럼 인식된다.

CREATE BITMAP INDEX sales_cust_job_bix ON sales(customers.cust_job)

FROM sales, customer

WHERE sales.cust_id=customer.cust_id

LOCAL LOGGING COMPUTE STATISTICS;

- 조인시 인덱스 생성 집합이 보존되기 위한 조건

(1) 인덱스를 생성하는 테이블에 조인되는 테이블의 속성은 기본 키이거나 유일성 제약을 가져야 한다.

(2) 참조테이블의 조인키가 결합인덱스로 구성되어 있으면 모든 컬럼이 조인에 존재해야한다.

(3) 조인조건은 반드시 = 조건이어 야하며 아우터조인은 사용할 수 없다.

(4) 인덱스일체형 테이블이나 임시테이블과는 비트맵 조인인덱스를 생성할 수 없다.

(5) 인덱스를 생성하는 조인 문에 집합연산, 집계함수, GROUP, ORDER, CONNECTED 문을 사용할 수 없다.

(6) 인덱스 컬럼은 반드시 조인되는 테이블에 소속된 컬럼이어야 한다.

(7) 비트맵 조인인덱스는 비트맵 인덱스의 각종 규칙을 준수하여야 한다.

- 비트맵 조인인덱스의 제약사항

(1) 병렬 DML 처리는 비트맵 조이인덱스를 가지는 테이블에서만 지원된다.

(2) 비트맵 조인인덱스를 사용하면 어떤 트랜잭션에서 동시에 하나의 테이블만 처리해야한다. 조인된 테이블이 COMMIT되지 않은 상태에서 동시에 변경되면 일관성을 보장할 수 없다.

(3) 조인 문장에서 동일한 테이블이 두 번 등장할 수 없다.

-- 정말 미리 읽어야할 규칙들이 많네요!! 어설프게 알아서 사용하는 것이 위험할 수도...

제2부 2장 조인의 최적화 방안

휘휘 — Sun, 06 Sep 2009 14:26:38 +0900

495~513 페이지 까지의 내용입니다. ^^

제2부 2장 조인의 최적화 방안
관계형 DB는 집합의 개념의로 자료를 처리하여 그 형태 및 연산자의 활용에의해 다양하게 사용되어질수 있으며
각 테이블간의 물리직 연결과는 산관없이도 조인에 의해 자료의 연결이 쉽게 이루어질수있다
하지만 조인의 무분별한 사용은 수행속도의 문제를 가져올수 있으므로 조인의 구체적인 처리 절차와 핵세스 효율등을 알아야한다.

2.1. 조인과 반복 연결(LOOP QURY) 비교

join은 다양한 처리방법을 가지고 있고 그 처리방법에 따라 수행시간의 차이가 많이 나타날수 있다.
loop의경우 비교형식으로 처리되기 때문에 예상과 비슷한 시간을 확보할수 있다. 하지만
정확한 join의 처리방법을 활용하여 처리하면 loop와는 비교할수 없는 속도 향상을 가져올수 있다.
그러므로 데이터 처리의 대부분을 조인을 통해 declare에서 처리하고, 아주 특수한 경우의 처리만 loop에서 처리하도록 하는것이
최선의 방법중 하나라 할수 있다.

2.1.1. 전체 범위 처리 방식에서의 비교

예1)

[조인처리]
SELECT A.FLD1, ... ,B.COL1,...
FROM TAB2 B, TAB1 A
WHERE A.KEY1=B.KEY2
    AND A.FLD1='10'
ORDER BY A.FLD2;

TAB1의 FLD1='10' 인것만 전체 스캔후 TAB2를 모두 연결한후
FLD2로 ORDER BY 한후 운반단위 만큼 추출
(FLD1='10' 이 1000건이면 이면 1000회의 연결이 일어난후 정렬)

[반복연결]
(1)
SELECT FLD1, .. ,FLDn
FROM TAB1
WHERE FLD='10'
ORDER BY FLD2;

(2)
SELECT COL1, ...,COLn
FROM TAB2
WHERE KEY2 = :a.KEY1;

TAB1을 1000건 읽어온후 ORDER BY 한후
TAB2의 필요한 운반단위만큼만 수행

[인라인뷰처리]
SELECT x.FLD1, ...., x.FLDn, y.COL1,....,y.COLn
FROM (SELECT FLD1, ...., FLDn
          FROM TAB1
          WHERE FLD ='10'
          ORDER BY FLD2) x, TAB2 y
WHERE y.KEY2=x.KEY1;

인라인뷰로 FLD='10'인것을 FLD2로 모두 정렬후 조인수행

예2)

[조인연결]
SELECT b.부서명, sum(a.매출액)
FROM TAB1 a, TAB2 b
WHERE a.부서코드 = b.부서코드
AND a.매출일 like '200503%'
GROUP BY B.부서명;

두테이블을 전체범위로 조인한후 GROUP BY를 하여 운반단위 만큼 추출

[반복연결]
(1) SELECT 부서코드, SUM(매출액)
   FROM TAB1
   WHERE 매출일 LIKE '200504'
   GROUP BY 부서코드;

(2) SELECT 부서명
      FROM TAB2
      WHERE 부서코드 = :a.부서코드;

tab1으로 group by한 결과를 tabw의 필요한 부분만 가져와서 처리
tab2의 액세스 양이 줄어듬

[인라인뷰처리]

SELECT x.부서크도, y.부서명, 매출액
FROM (SELECT 부서코드 , SUM(매출액) 매출액
       FROM TAB1
       WHERE 매출일 LIKE '200503%'
         GROUP BY 부서코드 ) x, TAB2 y
WHERE y.부서코드 = x.부서코드;

두 테이블(집합)의 관계가 1:M 일때
조인처리는 두테이블의 조인후 M집합으로 만든후 처리하며
반복연결은 M집합처리후 필요한 자료만 액세스하여 처리하여 조인이 비효율적일수있어나
인라인뷰등의 대체수단을 활용한다면 같은 형태의 액세스로 만들어 처리할수 있다.
하지만 관계형 DBMS의 집합개념은 경우에 따라 반복연결이 더 효율적일수있으므로
각 집합간의 관계를 잘 파악후 사용하여야 한다.

2.1.2. 부분범위 처리방식에서의 비교

부분처리로 처리하는경우 운반단위가 채워질 때까지만 연결작업이 일어나며
반복연결 역시 필요한 자료의 양만큼 수행하고 멈출수 있으므로 형태면에서 큰차이가 없을수 있다.

하지만 조인의 경우 한번의 SQL 처리가 수행되어지는 반면
반복연결은 연결시 마다 SQL이 수행된다.

이는 조인이 SQL 내에서 연결한 테이블의 컬럼을 마듬대로 가공하여 사용하는데 반해
반복연결은 별도의 언어를 통해 추가적인 가공을 해야하므로 사용면이나
속도면에서 조인이 유리 할수 있다.

제2부 1.4.7. 저장형 함수를 이용한 부분 범위 처리

휘휘 — Sun, 06 Sep 2009 11:44:29 +0900

" p454-494 입니다"

제 2부 1.4. 부분범위 처리로의 유도
1.4.7. 저장형 함수를 이용한 부분 범위 처리
- 비절차형인 SQL 에서 절차형 처리를 가능하게 해준다

EX)
CREATE OR REPLACE FUNCTION GET_AVG_STOCK (
    v_start_date in date,
    v_end_date    in date,
    v_product_cd in varchar2 )
    RETURN number IS
    RET_VAL number(14);
BEGIN
    SELECT SUM(stock_qty) / (v_start_date - v_end_date) ) into RET_VAL
    FROM PROD_STOCK
    WHERE product_cd=v_product_cd
        AND stock_date between v_start-date and v_end_date;
    RETURN RET_VAL;
END GET_AVG_STOCK;

SELECT product_cd, product_name,
    GET_AVG_STOCK(product_cd, :b1, :b2) avg_stock
FROM PRODUCT
WHERE category_cd='20';

전체처리가 없음

가) 확인자 역할의 M 집합 처리를 위한 부분범위처리

M 집합을 단지 확인자 역할로 조인을 했다면 원래 집합으로 돌아가기 위해 GROUP BY,DISTINCT 등의 부담이 많은 쿼리를 취애야함
서브쿼리를 활용하는 방법도 불필요한 자료를 처리해야하는 경우가 생길수도 잇음

저장형 함수를 작성후 WHERE절에서 호출하여 조건 처리를 하고 SELECT LIST 문에서 값을 출력하는 형태
-> 저장형 함수를 중복수행하는 문제가 있을수 있음

인라인뷰를 활용하여 수행하는 방법
EX)
    CUST_ARR_FEE_FUNC(고객번호, 시작일,종료일) -> 요금총액 RETURN

    SELECT cust_no,cust_name, bill_tot, ..
    FROM (SELECT ROWNUM, cust_no,cust_name,
        CUST_ARR_FEE_FUNC(cust_no,:b1,:b2) bill_tot,...
            FROM customer
            WHERE cust_status='ARR' )
    WHERE bill>=1000000
        AND ROWNUM <= 30;

Execution Plan
----------------------------------------------------------
SELECT    STATEMENT
    COUNT (STOPKEY)
        VIEW    ............................. (b)
            COUNT    ....................... (a)
                TABLE ACCESS (BY ROWID) OF 'CUSTOMER'
                    INDEX (RANGE SCAN) OF 'CUST_STATUS_IDX'

서브쿼리의 rownum (A) 의 역할; 서브쿼리에서 ROWNUM의 확보요청으로 옵티마이져가 서브쿼리를
VIEW로 (B) 생성한후 조건절에서 처리가능하게 됨 ( 저장형 함수를 한번만 수행)

나) 특정 부분만 부분범위처리로 유도

출력해야할 자료자체의 범위가 넓어 실행계획의 조정만으로 속도 향상을 기대하기 힘들때

EX)
    순수sql)
        SELECT sal_dept, agent_name, jan_tot,feb_tot ...,dec_tot
        FROM
            (SELECT sal_dept,
                        SUM(decode(substr(sal_date,1,6),:year||'01',sal_amt)) jan_tot,
                        SUM(decode(substr(sal_date,1,6),:year||'02',sal_amt)) feb_tot,
                            ........................................................
                        SUM(decode(substr(sal_date,1,6),:year||'03',sal_amt)) dec_tot
            FROM sales
            WHERE sal_date like :year||'%'
            GROUP BY sal_dept
            ORDER BY jan_tot DESC ) s, agent a
    WHERE a.agent_no = s.sal_dept
            AND a.loc= :b1;

    sales 테입을 조건의 년도에 맞게 전체 가공한후 agent 테이블과 조인하여 최종결과 출력
    (sales 자료의 양에 따라 엄청난 access를 가져올수 있음)

   저장형함수 활용)

    GET_AGENT_SALAMT ( 판매처, 년월) -> 판매총액 출력

SELECT sal_dept, agent_name, jan_tot,
           GET_AGENT_SALAMT (sal_dept, :year||'02') feb_tot,
            ........................................................
           GET_AGENT_SALAMT (sal_dept, :year||'12') dec_tot
FROM ( SELECT sal_dept, nvl (sum(sal_amt),0) JAN_TOT,
            max(agent_name) agent_name
            FROM sales s,agent a
            WHERE s.sal_dept=a.agent_no
                AND a.loc = :b1
                AND s.sal_date like :year || '01'
            GROUP BY sal_dept
            ORDER BY jna_tot desc);

첫달만 group by 로 계산하하고 메인쿼리의 SELECT 문에서 GET_AGENT_SALAMT를 호출하여 첫쿼리의 결과값을 활용하여
저장형 함수를 호출하여 각각 의 범위를 최대한 줄임

1.4.8. 쿼리의 분리를 이용한 부분범위처리

SQL)
    SELECT deptno, y.ename, y.empno, y.job, sal_tot, comm_tot
    FROM (SELECT empno,
                        SUM(sal_amt) sal_tot,
                        SUM(comm)   comm_tot
           FROM salary s
             WHERE s.deptno like '12%'
                AND s.sal_date between '20050101' and '20051231'
            GROUP BY empno) x, employee y
    WHERE y.empno=x.empno;

    부서번호가 12로 시작하교 2005년에 지급한 급여의 총액(salary)을 기본적은 사원정보(employee) 와 함께 출력
    salary 테일블을 조건에 따라 전체 group by하고 다시 employee로 조인하여 결과출력

1.4.9. 웹 게시판에서의 부분범위처리
가) 웹 게시판 부분범위처리 사례 1 (NON-UNIQUE INDEX)

   SELECT /*+ index (w cust_name_idx) */
            ROWIDTOCHAR(rowid) rid, cust_name, ....
    FROM cust_table w
    WHERE :v2='FIRST' AND cust_name like :v1 || '%'
        AND rownum <=25

    UNION ALL

    SELECT /*+ use_concat index (x cust_name_idx) */
            ROWIDTOCHAR(rowid) rid, cust_name, ...
    FROM cust_table x
    WHERE :v2='NEXT'
        AND (cust_name > :v3 OR
              (cust_name = :v3 AND rowid > CHARTOROWID(:v4)))
        AND cust_name like :v1||'%'
        AND rownum <=25

    UNION ALL

    SELECT /*+ use_concat index_desc(y cust_name_idx) */
            ROWIDTOCHAR(rowid) rid, cust_name, ...
    FROM cust_table y
    WHERE :v2='PREV'
        AND (cust_name < :v3 OR
              (cust_name = :v3 AND rowid < CHARTOROWID(:v4)))
        AND cust_name like :v1 || '%'
        AND rownum <=25
    ORDER BY cust_name, rid;

각 쿼리의 :v2 변수로 인해 호출조건이 특정지어지어 'FIRST','NEXT','PREV' 세쿼리중 하나만 수행되어짐
v3: 은 고객이름
v4: 는 rowid 값으로 'next'일경우 큰값중 25개 ,prev는 작은값으로 25개 씩 출력

나) 웹 게시판 부분범위처리 사례 2(UNIQUE INEX)

기본키: '게시판ID+작성일자+글번호'

SELECT BBS_ID, 작성일자, 글번호, RNUM
FROM ( SELECT /*+ USE_CONCAT INDEX_DESC (a billboard_uk) */
                        ROWNUM RNUM, BBS_ID, 작성일자, 글번호, 글내용
           FROM BILLBOARD a
           WHERE :SW = 'NEXT'
            AND BBS_ID=BID
            AND ( 작성일자 < :INIT_DT
              OR (작성일자 = :INIT_DT AND 글번호 < :V_NUM ) )
            AND RNUM <= 25

            UNION ALL

            SELECT /*+ USE_CONCAT INDEX_ASC (a billboard_uk) */
                    (26-ROWNUM) RNUM, BBS_ID,작성일자, 글번호, 글내용
            FROM BILLBOARD a
            WHERE :SW = 'PREV'
                AND BBS_ID = :BID
                AND (작성일자 > :INIT_DT
               OR (작성일자 =:INIT_DT AND 글번호 > :V_NUM) )
           AND ROWNUM <=25
    )
ORDER BY RNUM;

INDEX_DESC는 최근값부터 출력하기 위해사용
초기 호출시는 INIT_DT는 SYSDATE, V_NUM의 MAX값을 준후 호출
SW 변수로 버튼에 따른 쿼리 수행 조정

다) 웹 게시판 부분범위 처리 사례 3(처음-이전-다음-끝)
기본키: CID+CSEQ
결합인덱스: CDAY+CID+CSEQ

SELECT CDAY, CUST_NM, CID, CSEQ, CTEXT
FROM ( SELECT /*+ INDEX_ASC (a IDX 01) */
                    cday, cust_nm, cid, cseq, ctext
          FROM cstab a
          WHERE :SW = 'FIRST'
              AND cday between :b11 and :b12)
              AND rownum <= 25

        UNION ALL

        SELECT /*+ INDEX_DESC (a IDX01) */
                cday, cust_nm, cid, cseq, ctext
        FROM cstab a
        WHERE :SW ='LAST'
            AND cday between :b11 and :b12
            AND rownum <=25

        UNION ALL

        SELECT /*+ USE_CONCAT INDEX_ASC (a IDX01 ) */
                cday, cust_nm, cid, cseq, ctext
        FROM cstab a
        WHERE :SW = 'NEXT'
            AND ( (cday > :b100)
             OR (cday = :b100 and cid > :b20)
           OR (cday = :b100 and cid = :b20 and cseq > :b27) )
            AND cday between :b11 and :b12
            AND rownum <=25

        UNION ALL

        SELECT /*+ USE_CONCAT INDEX_DESC (a IDX01) */
                cday, cust_nm, cid, cseq, ctext
        FROM cstab a
        WHERE :SW='PREV'
            AND ( (cday < :b100)
           OR (cday = :b100 and cid <:b20)
           OR (cday = :b100 and cid = :b20 and cseq < :b27) )
            AND cday between :b11 and b:12
            AND rownum <=25
)

order by cday,cid,cseq

라) 웹 게시판 부분범위 처리 사례 4 (set 단위 처리)

두단계로 나누어서 처리 하단부분에 각 페이지 링크를 위한 쿼리

SELECT rnum, bbs_id, cre_dt,num
FROM ( SELECT /*+USE_CONCAT INDEX_DESC (a bbs_idx1) */
           ROWNUM rnum, bbs_id, cre_dt,num
        FROM BILLBOARD a
        WHERE :sw='NEXT'
            AND bbs_id = :v_bbs
            AND (cre_dt < :v_init_dt
              OR ( cre_dt = :init_dt AND num <= :v_num) )
            AND ROWNUM <=201

        UNION ALL

        SELECT /*+ USE_CONCAT INDEX_ASC (a bbs_idx1) */
                ((20*10)+2 - ROWNUM) rnum,bbs_id,cre_dt,num
        FROM BILLBOARD a
        WHERE :sw='PREV'
            AND bbs_id=:v_bbs
            AND (cre_dt > :v_init_dt
              OR (cre_dt = :v_init_dt AND num >=:v_num) )
            AND ROWNUM <=201
        )
WHERE rnum in (1,21,41,61,81,101,121,141,161,181,201)
ORDER BY rnum;

->게시판 목록 하단에 페이지 처리

이전 1,2,3,4,5,6,7,8,9,10 다음

일경우 메인쿼리의 rnum으로 각 페이지의 시작페이지들을 출력
각 페이지들이 클릭될경우 다음 쿼리를 호출하여 페이지별 목록 출력

SELECT /*+ USE_CONCAT INDEX_DESC(a bbs_idx1) */
        bbs_id, cre_dt, num, c_text
FROM BILLBOARD a
WHERE bbs_id = :bbs
    AND ( cre_dt < :v_init_dt
      OR ( cre_dt = :v_init_dt AND num <= :v_num) )
    AND ROWNUM <= 20;

마) 웹 게시판 부분범위처리 사례 5 (계층구조의 처리)

REPLY 에 REPLAY에 달리는 형태의 게시판

절차형 처리를 가미하고 쿼리를 분리하여 처리

DECLARE
    CURSTOR c1 (start_id NUMBER) IS
        SELECT /*+ index_asc (a idx1) */
                rownum as rnum, id , pid
        FROM BILLBOARD a
        WHERE sw=1 and id>=start_id
            and rownum <=11;

    COURSOR c2(root_id NUMBER) IS
        SELECT rownum as rnum2, id, pid,
                lpad (' ',2*level-1) || comments as comm,
                sys_connect_by_path(id, '/') as path
        FROM BILLBOARD
        CONNECT BY pid=PRIOR id
                and rownum <=11
        START WITH id= root_id;

    TYPE IdTAbTyp is TABLE of billboard.id%TYPE
                    index by binart_integer;
   TYPE CommTabTyp is TABLE of billboard.comments%TYPE
                    index by binary+integer;

    ret_id          IdTabTyp;
    ret_root       IdTabTyp;
    ret_comm   CommTabTyp;
    ret_num   IdTabTyp;
    root_id   number;
   first_seq       number := &seq;
    start_id        number := &start_id;
    i              binary_integer :=0;

BEGIN
    FOR c1_rec IN c1(start_id) LOOP    /* 본문글 액세스*/
        IF c1_rec_rnum > 11 OR i >10 THEN EXIT;
        END IF;
        root_id := c1_rec.id;
       FOR c2_rec IN c2(root_id) Loop   /* 계층구조 전개*/
            IF i> 10 THEN EXIT;
            END IF;
            IF (c1_rec.id = start_id and c2_rec.rnum2 >= first_seq) OR
              (c1_rec.id <> start_id ) THEN
                i := i+1;   /* 배열에 저장 */
                ret_id(i) :=c2_rec.id;
                ret_root(i) :=substr(c2_rec.path,2,instr(c2_rec.path, '/',2) -2);
                ret_comm(i) := c2_rec.comm;
                ret_num(i)    := c2_rec.rnum2;
            END IF;
        END LOOP;
    END LOOP;

    FOR i IN 1..11 LOOP
        DBMS_OUTPUT.PUT_LINE(ret_id(i)||' '||ret_root(i)||' ' ||   /*화면으로 출력*/
                                            ret_comm(i)||' '||ret_num(i));
    END LOOP;
END;

출력된 결과를 사용하는 애플리케이션에서 처리하여 결과 출력

이전페이지 이동시

DECLARE
    CURSOR c1(start_id NUMBER) IS
        SELECT /*+ index_desc(a idx1) */
               rownum as rnum, id, pid
        FROM BILLBOARD a
        WHERE sw=1 and id<=start_id
            and rownum <=11;

    CURSOR c2(root_id NUMBER) IS
        SELECT rownum as rnum2, id, pid,
                lpad(' ',2*level-1) || comments as comm,
                sys_connect_by_path(id,'/') as path
       FROM BILLBOARD
        CONNECT BY pid=PRIOR id
                and rownum <= 11
        START WITH id=root_id;

    TYPE IdTabTyp is TABLE of billboard.id%TYPE
            index by binary_integer;
    TYPE CommTabTyp is TABLE of billboard.comments%TYPE
            index by binary_integer;
    ret_id    IdTabTyp;
    ret_root     IdTabTyp;
    ret_comm    CommTabTyp;
    ret_num    IdTabTyp;
    root_id   number;
    last_rnum    number :=&seq;
    start_id    number :=&start_id;
    i            binary_integer := 0;
    j            binary_integer := 0;
    cnt        number :=0;

    BEGIN
        FOR c1_rec IN c1 (start_id) LOOP /* 계층구조전계 */
            IF c1_rec.rnum > 11 OR cnt > 21 THEN EXIT;
            END IF;
            root_id :=c1_rec.id;

            FOR c2_rec IN c2(root_id) LOOP
                IF c2_rec.rnum2 <= last_rnum THEN
                    i := c2_rec.rnum2;
                    ret_id(i) := c2_rec.id;
                    ret_root(i) :=substr(c2_rec.path,2,instr(c2_rec.path,'/',2)-2);
                    ret_comm(i) :=c2_rec.comm;
                    ret_num(i) :=c2_rec.rnum2;
                END IF;
            END LOOP;

            last_rnum := 999;

            FOR j IN reverse 1..i LOOP    /* 배열에 저장 */
                cnt :=cnt+1;
                ret_id(112-cnt) :=ret_id(j);
                ret_root(112-cnt) :=ret_root(j);
                ret_comm(112-cnt) :=ret_comm(j);
                ret_num(112-cnt) := ret_num(j);
            END LOOP;
        END LOOP;

        FOR j IN 101..111 LOOP   /* 화면에 출력 */
            BEGIN
                DBMS_OUTPUT.PUT_LINE (ret_id(j)||' '||ret_root(j)||' '||
                                                    ret_comm(j)||' '||ret_num(j)) ;
                EXCEPTION
                    WHEN NO_DATA_FOUND THEN i:=1;
           END;
        END LOOP;
END;

2.2. 연결고리 상태가 조인에 미치는 영향

balto — Sat, 05 Sep 2009 14:55:53 +0900

2.2. 연결고리 상태가 조인에 미치는 영향

- 조인에서 연결고리란 테이블을 연결하는 조인 조건을 말한다. 연결고리에 인덱스가 있는지 여부에 따라 정상, 이상으로 구분한다.

2.2.1. 연결고리 정상

- 조인조건이 되는 속성에 대한 인덱스가 모두 존재하는 경우.

- 두 테이블 중 하나가 드라이빙 테이블이 될 수 있다.

- 어느 테이블이 드라이빙(driving) 테이블이 되더라도 드리븐(driven) 테이블의 인덱스를 사용한다.

가능한 1:M의 1쪽의 테이블을 드라이빙 테이블로 한다.

- 어느 테이블이 드라이빙 테이블이 되는가에 따라 속도가 큰 차이는 없지만 차이가 있을 수 있다.

(SQL예)

SELECT *

FROM TAB2 b, TAB1 a

WHERE a.key1=b.key2 AND b.col2 LIKE 'AB%' AND a.col1='10';

(가정)

(TAB1 : a.col1='10'을 만족하는 범위는 5,000건)

(TAB2 : b.col2 LIKE 'AB%'를 만족하는 데이터는 100건)

(처리예 1)

TAB1에서 10인 것을 찾는다 -> KEY2 인덱스와 연결 -> 결과 생성

TAB2에 KEY2+COL2 인덱스가 있다면 더 효율적이다.

(처리예 2)

TAB2에서 AB%를 찾는다 -> KEY1 인덱스와 연결 -> 결과 생성

(참고) TAB2를 먼저 처리하려면 /*+ ORDERED */ 힌트를 사용한다.

2.2.2. 한쪽 연결고리 이상

- 조인조건이 되는 속성에 대한 인덱스가 한쪽 테이블에만 존재하는 경우

- 인덱스가 없는 테이블을 드라이빙 테이블로 하거나, 소트머지 혹은 해시조인을 사용한다.

- 인덱스가 없는 테이블을 드라이빙 테이블로 하지만 아래 예처럼 비효율적인 경우가 있음을 알아야 한다.

(SQL예)

SELECT *

FROM TAB2 b, TAB1 a

WHERE a.key1=b.key2 AND a.col1='20';

(처리예)

TAB2 처리 -> KEY1 인덱스와 연결 -> 결과 생성

(참고) TAB1의 데이터를 줄일 수 있는 a.col='20'을 인덱스를 활용하지 못한다.

2.2.3. 양쪽 연결고리 이상

- 조인조건이 되는 속성에 대한 인덱스가 양쪽 테이블 모두 존재하지 않는 경우

- 소트머지 혹은 해시조인을 사용한다.

2부 1.4.(1.4.1-1.4.7) 부분범위처리로의 유도 (중요하지 않은 부분 조금 미완성)

balto — Sat, 29 Aug 2009 10:47:39 +0900

2부-1.4-부분범위처리로의유도.hwp

1.4 부분범위처리로의 유도

- 이장에서는 어떤 SQL 문들을 부분범위 처리로 유도할 수 있는 지 각 방법들을 살펴본다.

1.4.1. 액세스 경로를 이용한 SORT의 대체

- ORDER BY에 의한 전체처리 범위

- 예 1 : 인덱스를 이용하여 정렬된 순서대로 추출하는 부분범위 처리 방법

(SQL 문) - 전체처리

SELECT *

FROM product

WHERE item_cd LIKE 'AB%' AND category LIKE 'C%'

ORDER BY item_cd DESC

(SQL 문) - item_index를 이용하여 부분범위처리.

SELECT /*+ INDEX_DESC(product item_index)*/ *

FROM product

WHERE item_cd LIKE 'AB%' AND category LIKE 'C%'

- 예 2 :

(SQL 문) - 전체처리 - ORDER BY 컬럼과 인덱스가 다를때

SELECT *

FROM order

WHERE ord_date like '2005%'

ORDER BY ord_dept DESC

(SQL 문) - 부분범위처리 - ord_dept를 액세스할 때 자동으로 정렬됨.

SELECT /*+ INDEX_DESC(a ord_dept_index)*/ *

FROM order a

WHERE ord_date like '2005%' AND ord_dept > ' ';

1.4.2. 인덱스만 액세스하는 부분범위 처리

- 인덱스만 사용하는 실행계획 수립 조건

질의에 사용된 모든 컬럼이 하나의 인덱스에 결합되어 있거나

인덱스 머지되는 실행계획의 경우 머지되는 두 개의 인덱스에 모든 컬럼이 사용되어야 한다.

질의에 사용된 전체 컬럼이 몇 개의 인덱스에 포함되면 인덱스 조인 성립

- 예 1 : ord_date 인덱스보다는 (ord_date+qty) 인덱스가 2배 이상 빠르다.

(SQL)

SELECT ord_date, SUM(qty)

FROM order

WHERE ord_date like '200510%'

GROUP BY ord_date

- 예 2 : 적절한 인덱스 구성

(SQL) (ord_date+agent_cd)를 사용한다면 약간 비효율적이다.

SELECT ord_date, count(*)

FROM order

WHERE ord_date like '200510%'

GROUP BY ord_dept

(SQL) (ord_date+agent_cd)를 사용한다면 매우 효율적이다.

SELECT ord_date, count(*)

FROM order

WHERE ord_date like '200510%'

GROUP BY agent_cd

1.4.3. MIN, MAX의 처리

- sequence를 사용하는 방법(생략)

1.4.4. FILTER 형 부분범위 처리

- 예 1 : 필터형 처리를 유도하는 것이 좋은 사례

(SQL) 전체범위 처리 예(COUNT()함수의 목적이 결과의 존재를 확인)

SELECT count(*) INTO :cnt

FROM ITEM_TAB

WHERE DEPT='101' AND seq > 100

...

IF cnt > 0 ...

(SQL) 부분범위 처리 예(EXIST로 변환한 예)

SELECT INTO :cnt FROM DUAL

WHERE EXISTS (SELECT 'X' FROM ITEM_TAB

WHERE DEPT='101' AND seq > 100)

...

IF cnt > 0 ...

(실행계획)

SELECT STATEMENT

FILTER

TABLE ACCESS (FULL) OF 'DUAL'

TABLE ACCESS (BY INDEX ROWID) OF 'ITEM_TAB'

INDEX (RANGE SCAN) OF item_dept_idx(NON-UNIQUE)

- 예 2 : 필터형 처리를 유도하는 것이 좋은 사례

(SQL) 전체범위 처리 예(MINUS)

SELECT ord_dept, ord_date, custno

FROM order

WHERE ord_date like '2005%'

MINUS

SELECT ord_dept, ord_date, '12541'

FROM sales

WHERE cust_no='12541'

(SQL) 부분범위 처리 예(EXIST로 변환한 예)

SELECT ord_dept, ord_date, custno

FROM order x

WHERE ord_date like '2005%' AND

NOT EXISTS ( SELECT * FROM sales y

WHERE y.ord_dept=x.ord-dept AND

t.ord_date=x.ord_date AND cust_no='12541')

1.4.5. ROWNUM의 활용

- ROWNUM - 가상의 컬럼으로 SQL 결과 튜플의 일련 번호이다.

- 예 : 10개의 결과 튜플이 생성되면 실행중지, 아니면 전체를 모두 수행.

SELECT *

FROM ITEM_TAB

WHERE DEPT='101' AND seq > 100 AND ROWNUM <= 10

- 실행계획의 예

SELECT STATEMENT

COUNT(STOPKEY)

TABLE ACCESS (FULL) OF table

- ROWNUM 사례 - ROWNUM 순서와 item_cd 정렬순과 일치(내부저장을 하지 않으므로)

SELECT ROWNUM, item_cd, category_cd

FROM product

WHERE deptno like '120%' and qty >0 and ROWNUM <=10

ORDER BY item_cd

- ROWNUM 사례 - ROWNUM 순서와 item_cd 정렬순과 일치(전체범위처리)

SELECT ROWNUM, item_cd, category_cd

FROM ( SELECT * FROM product

WHERE deptno like '120%' and qty >0 ORDER BY item_cd)

WHERE ROWNUM <=10

- ROWNUM 사례 - GROUP BY 결과를 원하는 수 만큼 출력하고 싶을 때

SELECT ROWNUM, dept, totqty

FROM ( SELECT deptno, sum(qty) totqty FROM product

WHERE deptno like '120%' GROUP BY deptno)

WHERE ROWNUM <=10

- ROWNUM 사례 - GROUP BY 결과중 5번째 결과만 출력하고 싶을 때

SELECT ROWNUM, dept, totqty

FROM ( SELECT ROWNUM as RNUM, deptno, totqty

FROM ( SELECT deptno, sum(qty) totqty FROM product

WHERE deptno like '120%' AND ROWNUM <= 100

GROUP BY deptno)

)

WHERE RNUM=5 AND ROWNUM =1 /* 불필요한 처리 없애기 */

- ROWNUM 사례 - 사용자의 무제한 액세스를 방지하고 싶을 때

SELECT *

FROM table

WHERE conditions AND ROWNUM <= 1000

1.4.6. 인라인뷰를 이용한 부분범위 처리

- 인라인뷰를 활용하여 부분범위처리를 유도하는 원리는 전체범위처리가 되는 부분을 인라인뷰로 묶어서 다른 부분들은 부분범위처리가 되도록 한다.

- 예 1 : 세 개의 테이블을 조인 - 처리대상이 적은 테이블을 먼저 처리하여 전체범위가 되도록하고 대량의 테이블은 부분범위처리로 유도

(SQL 1) 세 테이블 조인

SELECT a.dept_name, b.empno, b.emp_name, c.sal_ym, c.sal_tot

FROM department a, employee b, salary c

WHERE b.deptno=a.deptno AND c.empno=b.empno AND

a.location='SEOUL AND b.job='MANAGER' AND c.sal_ym='200512'

ORDER BY a.dept_name, b.hire_date, c.sal_ym

(SQL 2) 세 개의 테이블을 조인 - salary 테이블이 대용량인 경우 인라인뷰를 이용하여 dept, employee 조인한 후 (empno+sal_ym) 인덱스 사용하여 salary와 조인

SELECT /*+ ORDERED, USE_NL(x,y) */

a.dept_name, b.empno, b.emp_name, c.sal_ym, c.sal_tot

FROM (SELECT a.dept_name, b.hire_date, b.empno, b.emp_name

FROM dept a, employee b

WHERE b.deptno=a.deptno AND a.location='SEOUL

AND b.job='MANAGER'

ORDER BY a.dept_name, b.hire_date) x, salary y

WHERE y.empno=x.empno AND c.sal_ym='200512'

- 예 2 : GROUP BY를 통하여 가동된 중간집합을 생성하고 나머지는 참조되는 일종의 디멘전 테이블들을 부분범위처리로 유도하는 경우

(SQL 1)

SELECT a.product_cd, product_name, avg_stock

FROM product a,

(SELECT product_cd, SUM(stock_qty) / (:b2-:b1) avg_stock

FROM prod_stock

WHERE stock_date BETWEEN :b1 AND :b2

GROUP BY product_cd) b

WHERE b.product_cd=a.product_cd AND a.category_cd='20';

(최적이 수행절차)

product의 category_cd 값이 20인 product_cd 속성을 찾는다.

구해진 product_cd 값과 stock_date 값으로 prod_stock 테이블을

(product_cd+stock_date) 인덱스를 이용하여 액세스한다.

위 작업을 반복하다 운반단위가 채워지면 종료한다.

(해결방법) 사용자지정 저장함수 사용 혹은 스칼라 서브쿼리 사용

(SQL 2) 스칼라 서브쿼리 사용 예

SELECT a.product_cd, product_name,

(SELECT SUM(stock_qty) / (:b2-:b1) avg_stock

FROM prod_stock b

WHERE b.product_cd=a.product_cd AND

stock_date BETWEEN :b1 AND :b2) avg_stock

FROM product a,

WHERE category_cd='20';

1.4.7저장형 함수를 이용한 부분범위 처리

- (생략)

4.2. 클러스터링 형태의 결정 기준

balto — Wed, 26 Aug 2009 17:29:00 +0900

(편집에 문제 있을 수 있으니 첨부파일 참조 바랍니다.)

4.2. 클러스터링 형태의 결정 기준

- 예제 : 어느 테이블을 클러스터링하는 것이 효율적인지..

TAB1(COL1, otherkeys)

TAB2(COL1, COL2, oterkeys)

TAB3(COL1, COL2, COL3, otherkeys) : 대용량테이블, TAB1, TAB2와 자주조인

TAB4(COL1, COL2, COL4, otherkeys) : 대용량테이블

4.2.1. 포괄적인 클러스터링

- TAB3 + TAB4를 클러스터링라면 -> M:M의 관계이므로 좋지 않다.

- TAB1 + TAB2 + TAB3 을 클러스터링할 경우 -> 1개의 TAB1로우, 여러 개의 TAB2로우, 다수의 TAB3로우로 클러스터링 체인이 많이 발생하여 비효율적. TAB1과 TAB2는 독립적으로 액세스해도 문제 없음. 결속력은 강화되지만 각각의 테이블 독립성은 떨어져서 TAB1이나 TAB2를 독립적으로 액세스하는 효율 저하.

4.2.2. 부분적인 클러스터링

- TAB1 + TAB2가 결합도가 높고 자주 조인한다면 클러스터링 해볼 만하다.

4.2.3. 단일테이블 클러스터링

- 조인의 효율성 향상을 위한 다중 클러스터링은 특정한 경우를 제외하고는 피한다.

- 단일테이블 클러스터링은 넓은 범위의 처리를 스캔방식으로 유도하는 좋은 방법이다.

4.2.4. 단위 클러스터의 크기 결정

- 단위클러스터의 크기를 어느 정도로 하면 좋은가?

- 예 :

A형 : 클러스터 길이가 길고 로우의 길이가 짧다.

B형 : 클러스터 길이가 짧고 로우의 길이가 길다.

C형 : 클러스터 길이가 보통 로우의 길이가 짧다.

D형 : 클러스터 길이가 짧고 로우의 길이가 짧다.

- 클러스터를 하는 테이블은 로우가 길지 않을수록 좋고(길면 클러스터에 로우가 몇 개 못 들어간다.), 단위클러스터가 지나치게 크지 않는 것이 좋다(크면 기억장소 효율성이 떨어진다). 단위 클러스터에 20-100개가 좋다.

- 단위클러스터는 32K까지 허용한다. 32K보다 작은 클러스터는 작은 크기만 배정된다.

- 8K블록에 2K로 단위클러스터가 지장되어 있으면 오버헤드 장소를 빼고 약 3개정도의 클러스터가 저장된다. 같은 키를 가진 클러스터가 많으면 블록에 저장되는 클러스터는 더 줄어든다.

- 단위클러스터에 저장될 데이터가 많다면 체인블록에 저장된다. 체인블록은 빈 블록 혹은 다른 클러스터가 사용하다 남는 블록이 된다.

[단위클러스터 계산 방법]

- 입력 인자들

① 블록당 유휴 저장공간 = (블록크기-헤더크기)*(100-PCTFREE)/100

(예) 저장가능 공간 = (8,000Byte-100Byte)*(100-10)*100=7,110Byte

② 로우의 평균길이

(평균길이 아는방법)

ANALYZE TABLE COMPUTE STATISTICS

SELECT avg_row_len FROM user_tables WHERE table_name = ;

(예) SQL 실행결과 100Byte 가정하면 약 71 로우 저장 가능

③ 클러스터 키별 로우의 수

(평균 로우수 알아내는 방법)

SELECT AVG(CASE WHEN row_cnt > 71 THEN

ELSE row_cnt END) average_row_count

FROM (SELECT cluster_key_columns, count(*) row_cnt

FROM table_name

WHERE sampling _conditions...

GROUP BY cluster_key_columns);

④ 단위 클러스터 크기 산정 = AVG_ROW_LEN * average_row_count)

(예) 클러스터키별 평균로우의 수가 약 20이라면 클러스터 크기 = 20*100Byte = 2,000Byte, 7,110/2,000=3.5이므로 블록에 약 3개까지 단위클러스터 저장 가능

[클러스터 생성 절차의 예]

① 클러스터 생성

CREATE CLUSTER sales_cluster (sale_date varchar2(8))

STORAGE (storage_clause...)

PCTFREE 10 PCRUSED 60

SIZE 2000

② 클러스터 인덱스 생성

CREATE INDEX sales_cluster_index ON CLUSTER sales_cluster

PCTFREE 2 STORAGE (INITIAL 20K NEXR 10K)

③ 기존 테이블의 명칭을 다른 이름으로 바꾼다.

RENAME sales To sales_copy

④ 클러스터 내에 테이블을 생성한다.

CREATE TABLE sales

( salesno varchar2(6) not null,

sale_dept char(4),

sale_date varchar2(8) not null,

... )

CLUSTER sales_cluster (sale_date);

⑤ 생성된 테이블 내에 데이터를 저장시킨다.

INSERT INTO sales

SELECT * FROM sales_copy

WHERE sale_date <= '20010101';

⑥ 기존 테이블을 삭제시키고 새롭게 생성한 테이블에 다른 인덱스를 추가시킨다.

DROP TABLE sales_copy;

CREATE INDEX ...;

4.2.5. 클러스터 사용을 위한 조치

- 넓은 범위 처리의해결을 목적으로 생성하는 단일테이블 클러스터링에 주의할 사항

(1) 클러스터 키 컬럼을 첫 번째로 하는 인덱스를 생성하지 말 것.

클러스터 인덱스가 있음에도 클러스터 키 컬럼을 첫 번째로 하는 결합인덱스가 존재하면 클러스터를 사용하지 않고 결합인덱스를 사용하는 경우가 있을 수 있다.

예) (sale_date + sale_dept) 결합인덱스가 있다면 아래 질의는 결합인덱스를 사용한다.

SELECT SUM(count(*))

FROM sales

WHERE sale_date BETWEEN '20051201' AND '20051225'

AND sale_dept like '110%';

(2) 클러스터가 반드시 사용되기를 원한다면 액세스 경로를 고정시킨다.

예) 다음 SQL 문을 클러스터를 사용하는 것이 유리하다고 생각되면 힌트를 추가한다.

SELECT /*+CLUSTER(sales)*/ SUM(count(*))

FROM sales

WHERE sale_dept BETWEEN '20051201' AND '20051225'

AND sale_dept='11200';

4.1.6. 인덱스 선정 절차

휘휘 — Sun, 23 Aug 2009 21:58:27 +0900

4.1.6. 인덱스 선정 절차

인덱스 선정을 위한 전체적인 접근 절차
ㅁ관계형 DB를 사용하는 애플리케이션은 옵티마이져 액세스 경로 판단 기준으로 수행속도를 향상
(인덱스,클러스터의 조정, 통계정보 관리등으로 최적의 실행계획을 생성)
ㅁ인덱스는 특정 애플리케이션이나 특정 액세스를 위해서만 생성하는것이 아님
ㅁ현재나 향후의 예상되는 액세스 형태를 종합적으로 감안하는 전략이 필요

테이블의 액세스 형태를 최대한으로 수집
인덱스 대상 컬럼의 선정 및 분포도 조사
특수한 액세스 형태에 대한 인덱스 선정
클러스터링 검토
결합 인덱스 구성 및 순서의 결정
시험 생성 및 테스트
수정이 필요한 애플리케이션 조사 및 수정
일괄 적용

가) 테이블의 액세스 형태를 최대한으로 수집

개발단계의 액세스 형태수집

[그림 1-4-7] 표 참조

ㅁ 반복 수행되는 액세스 형태를 찾는다
- 자신의 수행속도에 반복 횟수를 곱한 만큼의 부하를 가져옴(속도에 많은영향이 있음)

ㅁ 분포도가 아주 양호한 컬럼드을 발췌하여 액세스 유형을 조사한다
- 하나의 컬럼만을 대상으로 하지말고 경우에 따려 여러개의 컬럼을 검토

ㅁ 자주 넓은 범위의 조건이 부여되는 경우를 찾는다
- 인덱스의 경우 넓은 범위를 처리할때 부담을 많이줌
- 처리범위의 최대크기와 평균 예상범위, 처리범위(order by,group by 등) 을 표시

ㅁ 조건에 자주 사용되는 주요컬럼들을 추출하여 액세스 유형을 조사한다

ㅁ 자주 결합되어 사용되는 컬럼들의 조합형태 및 정렬순서를 조사한다
- 각 컬럼간의 상호관계를 파악후 조사

ㅁ 역순으로 정렬하여 추출되는 경우를 찾는다
- 최근 자료를 필요로 하는 데이터등

ㅁ 통계자료 추출을 위한 액세스 유형을 조사한다
- 통계처리는 범위가 넓기 때문에 수행속도에 영향을 줄수 있음

운영단계의 액세스 형태수집
- 애플리 케이션이 작성되어 있는단계 (테스트시점, 가동중인시스템등)

ㅁ애플리케이션 소스코드에서 SQL을 추출하여 분석용 테이블에 보관한다
- 애플리케이션 명칭, SQL에 속한 서브루틴과 위치, SQL유형, SQL 원문, 사용 테이블, 사용컬럼, ORDER BY 컬럼, GROUP BY 컬럼, 반복사용여부등 저장
- SQL 추출 (소스크드에서 직접추출, SW 이용 - TOAD 의 경우 SQL MONITOR 제공)

ㅁSQL-Trace 파일을 파싱
- SQL문장뿐만 아니라 실행계획,현재 적용되고 있는 인덱스,실행횟수, 처리범위 등 매우 상세한 정보록 획득

ㅁ공유 SQL영역에서 직접 SQL을 찾음
- SQL수집을 보다 간편하게 할수 있도록 도와줌 , 언어의 영향을 받지 않으며, TRACE보다 장기간 수집이 가능

수집된SQL을 테이블 별로 출력하여 액세스 형태기록

예- [그림 1-4-8] 표
- 액세스 형태를 좀더 통합된 형태로 모을수 있다면 유형도 줄어들고 전략 수립도 단순해짐
- 조인의 연결고리부분은 무조건 추출하고 연결고리로 알수 있도록 표시 (EX) (J)등)

나)인덱스 대상 컬럼의 선정 및 분포도 조사

대상컬럼 선정기준
ㅁ 액세스 유형에 자주 등장하는 컬럼
ㅁ 인덱스의 앞부분에 지정해야할 컬럼
ㅁ 기타 수행 속도에 영향을 미칠것으로 예상되는 컬럼

조건절이라고 무조건 인덱스 구성에 참여할 필요는 없으며
예상되는 액세스 유형을 조사하여 인덱스 대상 컬럼 선정과 분포도록 파악하여 효율적인 구조를 연구
현행인덱스가 있다면 조사한 액세스 형태가 어떤 형태를 사용하는 등의 파악이 필요

다)특수한 액세스 형태에 대한 인덱스 선정
반복해서 액세스되는 형태(Critical Access Path) 찾기
- 자신의 속도 * 횟수 이므로 횟수나 속도가 늘어남에 따라 최종결과도 영향을 받음

혼자만으로 확실한 수행속도를 보장받을수 있다면 우선적으로 결정

라)클러스터링 검토
넓은 범위의 처리를 향상시킬수 있는 방법을 찾기
- 인덱스는 여러개를 동원해야 하지만 클러스터로 액세스 형태를 해결할수 있다면 적용가능 여부 판단 필요
- 투자대비 효과(ROI; Return On Investment) 를 확인해야함

마)결합 인덱스 구성 및 순서의 결정
윗단계에서 처리한 컬럼들을 제외한 나머지 컬럼들을 대상으로 액세스 유형들을 다시 한번 확인하여
두 컬럼이상의 결합으로 인덱스 생성의 부담보다 생성시 이점이 발견될 경우
새로운 결합 인덱스 생성

바)시험생성 및 테스트
인덱스의 변경은 기존 실행계획에 변화를 가져오므로 운영중인 시스템에 적용시키지 말고 테스트 과정을 거쳐야함
테스트 결과 예상했던 실행계획이 나타나지 않거나 한다면 액세스 형태에 필요한 힌트등을 기록

사)수정이 필요한 애플리케이션 조사 및 수정
특정한 액세스 경로로 유도하기 위한 힌트나 인덱스 사용제한, 해제등의 수정

아)일괄적용
애플리케이션이 실행된되는 순간 파싱이 수행(Runtime parsing)
하지만 액세스 형태를 수집할때 잘못된 SQL을 발견하면 SQL의 수정사항등을 같이 기록하여야하며
필요시 애플리케이션의 수정을 요구할때도 있다.

3.2.4. 비트맵(Bitmap) 실행계획

휘휘 — Sun, 23 Aug 2009 21:54:18 +0900

다양한 실행계획의 종류를 하나씩 살펴보고
각 상황에 맞는 쿼리 형태를 학습한후 상황에 맞게 사용할수 있도록 이해하여 필요시 적적하게
사용할수 있도록 하는게 중요

3.2.4. 비트맵(Bitmap) 실행계획
비트맵은 단일 테이블의 입장에서만 살펴봄

** 범위
조건 연산자별 비트맵 실행계획
- 동치(Equal) 비교 실행계획
- 범위(Range) 비교 실행계획
- AND 조건 실행계획
- OR 조건 실행계획
- 부등식(Not equal)실행계획
-NULL 비교 실행계획
서브 쿼리 실행계획
B-Tree 인덱스와의 연합(Comnbine) 실행계획

3.2.4.1. 조건 연산자별 비트맵 실행계획

가)동치(Equal) 비교 실행계획

'=' 이나 'IN' 연산 모두 SINGLE VALUE로 나타남

SELECT *

FROM SALES_SUM

WHERE TIME_CD='200512';

Execution Plan

---------------------------------------------

SELECT STATEMENT

TABLE ACCESS (BY INDEX ROWID) OF 'SALES_SUM'

BITMAP CONVERSION (TO ROWIDS)

BITMAP INDEX(SINGLE VALUE) OF 'TIME_BIX'

....

WHERE TIME_CD IN ('200505','200507','200510');

Execution Plan

-------------------------------------

INLIST ITERATOR

TABLE ACCESS (BY INDEX ROWID) OF 'SALES_SUM'

BITMAP CONVERSION (TO ROWIDS)

BITMAP INDEX(SINGLE VALUE) OF 'TIME_BIX'

나)범위(Range)비교 실행계획

'BETWEEN,LIKE,>,<,>=,<=' ; RANGE SCAN

SELECT *

FROM SALES_SUM

WHERE TIME_CD BETWEEN '200505' AND '200507'

Exectuion Plan

-------------------------------

SELECT STATEMENT

TABLE ACCESS (BY INDEX ROWID) OF 'SALES_SUM'

BITMAP CONVERSION (TO ROWIDS)

BITMAP INDEX (RANGE SCAN) OF 'TIME_BIX'

만약 NUMBER 형으로 저장된 컬럼에 LIKE 를 사용한다면

BTREE INDEX는 자료 변형으로 사용되지 않지만 BITMAP은 'FULL SCAN'으로 나타난다.

SELECT * FROM FROM SALES_SUM

WHERE SALE_DEPT LIKE '1250%'

Execution Plan

-------------------------------

SELECT STATEMENT

TABLE ACCESS (BY INDEX ROWID) OF 'SALES_SUM'

BITMAP CONVERSION (TO ROWIDS)

BITMAP INDEX (FRULL SCAN) OF 'SALE_DEPT_BIX'

다) AND 조건 실행계획

두 비트맵 인덱스 보유 컬럼의 'AND' 조건 사용시 'AND 연산' 실시

SELECT * FROM SALES_SUM

WHERE SALE_DEPT = '12500'

AND TIME_CD ='200510';

Execution Plan

-------------------------------

SELECT STATEMENT

TABLE ACCESS (BY INDEX ROWID) OF 'SALES_SUM'

BITMAP CONVERSION (TO ROWIDS)

BITMAP AND

BITMAP INDEX (SINGLE VALUE) OF 'TIME_BIX'

BITMAP INDEX (SINGLE VALUE) OF 'SALE_DEPT_BIX'

BETWEEN등으로 범위스캔하고 AND 조건 사용시

SELECT * FROM SALES_SUM

WHERE SALE_DEPT = '12500'

AND TIME_CD BETWEEN '200510' AND '200512';

Execution Plan

-------------------------------

SELECT STATEMENT

TABLE ACCESS (BY INDEX ROWID) OF 'SALES_SUM'

BITMAP CONVERSION (TO ROWIDS)

BITMAP AND

BITMAP INDEX (SINGLE VALUE) OF 'SALE_DEPT_BIX'

BITMAP MERGE

BITMAP INDEX (RANGE SCAN) OF 'TIME_BIX'

라)OR조건 실행계획

각컬럼별로 단위 액세스를 생성후 'OR' 연산 실시

SELECT * FROM SALES_SUM

WHERE TIME_CD = '200510'

OR ITEM_CD LIKE 'ABC%'

OR SALE_DEPT = 12500;

Execution Plan

---------------------------------------

SELECT STATEMENT

TABLE ACCESS (BY INDEX ROWID) OF 'SALES_SUM'

BITMAP CONVERSION (TO ROWIDS)

BITMAP OR

BITMAP INDEX (SINGLE VALUE) OF 'TIME_BIX'

BITMAP MERGE

BITMAP INDEX (RANGE SCAN) OF 'ITEM_BIX'

BITMAP INDEX (SINGLE VALUE) OF 'SALE_DEPT_BIX'

복잡한 OR 조건에도 동일한 원리로 수행

부정형 조건과 사용되면 부정형 조건이 적용된 컬럼은 사용되지 않음

마)부등식(Not equal)비교 실행계획

'BITMAP MINUS' 연산을 수행 ; 등식 조건으로 액스사한 후 먼저 수행한 비트맵에서 제거하는 처리

단일 컬럼만 존재할경우 ; FULL SCAN 수행

NOT NULL 제약 조건을 가진경우 AND 연산 ; BITMAP MINUS를 한번만 거침

SELECT * FROM SALES_SUM

WHERE TIME_CD = '200512'

AND SALE_DEPT <> 12500;

Execution Plan

-------------------------------

SELECT STATMENT

TABLE ACCESS (BY INDEX ROWID) OF 'SALES_SUM'

BITMAP CONVERSION (TO ROWIDS)

BITMAP MINUS

BITMAP INDEX (SINGLE VALUE) OF 'TIME_BIX'

BITMAP INDEX (SINGLE VALUE) OF 'SALE_DEPT_BIX'

NULL을 허용할 경우

BITMAP MINUS

BITMAP INDEX (SINGLE VALUE) OF 'TIME_BIX'

BITMAP INDEX (SINGLE VALUE) OF 'SALE_DEPT_BIX'

BITMAP INDEX (SINGLE VALUE) OF 'SALE_DEPT_BIX' <--- NULL인경우 제거

바)NULL비교 실행계획

NULL 연산 역시 일반 값들과 동일하게 비트맵 연산에 참여시켜 동작

SELECT * FROM SALES_SUM

WHERE TIME_CD='200512'

AND COUNTRY IS NULL;

Execution Plan

----------------------------------------

SELECT STATEMENT

TABLE ACCESS (BY INDEX ROWID) OF 'SALES_SUM'

BITMAP CONVERSION (TO ROWIDS)

BITMAP AND

BITMAP INDEX (SINGLE VALUE) OF 'TIME_BIX'

BITMAP INDEX (SINGLE VALUE) OF 'COUNTRY_BIX'

3.2.4.2. 서브쿼리 실행계획

SELECT * FROM SALES_SUM

WHERE ITEM_CD IN (SELECT ITEM_CD FROM ITEM_T

WHERE CATEGORY_CD = 'ABC' );

Execution Plan

------------------------------------

SELECT STATEMENT

TABLE ACCESS (BY INDEX ROWID) OF 'SALES_SUM'

NESTED LOOPS

SORT (UNIQUE) <----- 서브 쿼리

TABLE ACCESS (BY INDEX ROWID) OF 'ITEM_T'

INDEX (RANGE SCAN) OF 'ITEM_IDX2' (NON-UNIQUE)

BITMAP CONVERSION (TO ROWIDS)

BITMAP INDEX (SINGLE VALUE) OF 'ITEM_BIX' <-- 제공 받은 값으로 비트맵 인덱스

서버쿼리가 동시에 사용될경우는

첫 서브쿼리가 먼저 수행되어 비트맵 인덱스로 실행되어 제공자 역할을 한후 후 수행되는 서브쿼리가

제공자 역할을 하는 형태의 수행계획이 생성됨

스타 변형 조인 ; 모든 서브쿼리가 먼저 수행되어 처리 범위를 충분히 줄인 다음 팩트 테이블을 액세스 하는것

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;

Execution Plan

--------------------------------

SELECT STATMENT Optimizer=ALL_ROWS

SORT GROUP BY

HASH JOIN

HASH JOIN <-- 엑세스한 팩트 테이블과 디멘전 테이블 조인

TABLE ACCESS (BY INDEX ROWID) OF 'SALES_SUM' <-- 비트맵들을 결합하여 액세스

BITMAP CONVERSION (TO ROWIDS)

BITMAP AND

BITMAP MERGE <-- ITEMS 서브쿼리로 팩트 테입ㄹ의 비트맵 액세스

BITMAP KEY ITERATION

TABLE ACCESS (FULL) OF 'ITEMS'

BITMAP INDEX (RANGE SCAN) OF 'ITEM_BIX'

BITMAP MERGE <-- COUNTRY 서브쿼리로 팩트 테이블의 비트맵 액세스

BITMAP KEY ITERATION

TABLE ACCESS (FULL) OF 'COUNTRYS'

BITMAP INDEX (RANGE SCAN) OF 'COUNTRY_BIX'

3.2.4.3. B-tree 인덱스와의 연합(Combine) 실행계획

'ITEM_CD'; 비트맵 인덱스

'PRICE' ; B-Tree 인덱스

SELECT /*+ INDEX_COMBINE (SALES) */ *

FROM SALES

WHERE ITEM_CD = 'PA100'

AND PRICE >= 100000;

Execution Plan

----------------------------------------

SELECT STATEMENT

TABLE ACCESS (BY INDEX ROWID) OF 'SALES'

BITMAP CONVERSION (TO ROWIDS)

BITMAP AND

BITMAP INDEX (SINGLE VALUE) OF 'ITEM_BIX'

BITMAP CONVERSION (FROM ROWIDS) <- BTREE를 비트맵으로 전환

SORT (ORDER BY)

INDEX (RANGE SCAN) OF 'PRICE_IDX' (NON-UNIQUE)

B-TREE인덱스만 사용될경우도 'INDEX_COMBINE' 힌트를 적용하면 비트맵 액세스가능

테이블 액세스전 최대한 인덱스간의 머지를 통해 범위를 줄이는것이 가능하지만

인덱스 머지의 오버해드가 더 커다면 역효과가 나타날수도 있음

3.2.5. 기타 특수한 목적을 처리하는 실행계획

3.2.5.1. 순환(Recursive) 실행계획

'CONNECT BY ... START WITH' 문을 사용했을 때나타나는 실행계획

순전개

SELECT LPAD(' ',2* (LEVEL -1__ || ename, empno, sal,mgr,

SYS_CONNECT_BY_PATH(last_name,'/') "Path"

FROM emp

WHERE job='CLERK'

CONNECT BY mgr= PRIOR empno

START WITH empno= :b1;

Execution Plan

-------------------------------------

SELECT STATEMENT

FILETER --- 3 where절 job='clerk'

CONNECT BY (WITH FILTERNING)

NESTED LOOPS --- 1 start with 에서 정한 초기 조건

INDEX (UNIQUE SCAN) OF 'PK_EMP' (UNIQUE)

                TABLE ACCESS (BY USER ROWID) OF 'EMP
           NESTED LOOPS ---2   connect by 절의 prior 절뒤쪽을 가져와서 상대 컬럼을 조인비교
               BUFFER (SORT)

CONNECT BY PUMY

TABLE ACCESS (BY INDEX ROWID) OF 'EMP'

INDEX (RANGE SCAN) OF 'MGR_IDX' (UNIQUE)

* 1,2,3 의 순서대로 수행

각 단계(level)별 정렬이 필요할경우 ORDER SIBLINGS BY ... 을 사용

connect by 의 연결에 사용할 인덱스가 없거나 테이블 크기가 적다면 해쉬조인으로 내측 루프를 수행

조인쿼리를 순환 전개 시킬경우

where 절의 조인을 수행한후 루트들을 선별 (조인한 결과 집합이 필요하기 때문)

3.2.5.2. UPDATE 서브쿼리 실행계획

UPDATE 문은 SET절과 WHERE 절에서 서브쿼리 사용가능

SET 절의 서브쿼리를 스칼라 서브 쿼리 (하나의 로우에 하나의 컬럼값을 가짐)

UPDATE emp e

SET sal = (SELECT AVG(sal) * 1.2

FROM bouns b

WHERE b.empno=e.empno

AND b.pay_date between :b1 and :b2)

WHERE deptno IN (SELECT deptno FROM dept

WHERE loc='BOSTON');

Execution Plan

-------------------------

UPDATE STATEMENT

UPDATE OF 'EMP'

TABLE ACCESS (BY INDEX ROWID) OF 'EMP'

NESTED LOOPS

TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' --- 서브쿼리를 수행 하여 메인쿼리와 조인

INDEX (RANGE SCAN) OF 'LOC_IDX' (NON-UNIQUE)

INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (NON-UNIQUE)

SORT (AGGREGATE)

TABLE ACCESS (BY INDEX ROWID) OF 'BONUS' -- SET 절의 스칼라 쿼리 수행

INDEX (RANGE SCAN) OF 'BONUS_PK' (UNIQUE)

UPDATE 문이라고해도 해쉬조인으로도 수행

* 메인쿼리에서 제공받은 값으로 수행된 서브쿼리의 결과가 'No DATA FOUND' 라면 'NULL'로 인정되어 갱신

* 에러: 쿼리가 정상적으로 수행되지 않은것

* 실패: 결과가 존재하지 않은것 ; NVL 등이 듣지 않음

* SUM,MIN,MAX,COUNT,AVG등의 그룹함수는 '실패'가 발생하지 않음

뷰의 UPDATE

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%';

Execution Plan

-------------------------------------

UPDATE STATEMENT

UPDATE OF 'EMP'

HASH JOIN

TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'

INDEX (RANGE SCAN) OF 'LOC_IDX' (NON-UNIQUE)

TABLE ACCESS (FULL) OF 'EMP'

3.2.5.3. 특이한 형태의 실행계획

가) 서브쿼리 팩토링 실행계획

'WITH' : 쿼리를 임시 테이블에 저장한후 사용 (INLINE 뷰와 유사하게 사용)

WITH total_sal AS

(SELECT D.deptno, D.loc, E.job, sum(E.sal) tot_sal

FROM emp E, dept D

WHERE E.deptno=D.deptno

AND E.hiredate > :b1

GROUP BY D.deptno, D.loc, E.job)

SELECT e.empno, e.name, e.sal, e.sal/t.tot_sal sal_percent

FROM emp e,total_sal t

WHERE e.deptno = t.deptno

AND e.sal > (SELECT max(tot_sal)

FROM total_sal

WHERE job='CLERK');

Execution Plan

----------------------

SELECT STATEMENT

RECURSIVE EXECUTION OF 'SYS_LE_2_0' --- WITH 절의 쿼리 수행

TEMP TABLE TRANSFORMATION

TABLE ACCESS (BY INDEX ROWID) OF OF 'EMP'

NESTED LOOPS

VIEW

TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6616_165207A'

INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (NON-UNIQUE)

SORT (AGGREGATE)

VIEW

TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6616_165207A'

TEMP TABLE TRANSFORMATION ; 기존의 쿼리를 새로 생성된 임시 테이블로 변형

WITH절의 쿼리가 한번만 쓰임 인라인 뷰였다면 동일 쿼리를 두번수행

한번의 복잡한 가공을 여러번 재활용 할때 최적

WITH절의 하나이상을 사용가능

나) 특인한 DELETE 문 서브쿼리

DELETE FROM (SELECT * FROM EMP

WHERE JOB='CLERK'

AND comm > 10000

AND deptno IN (SELECT deptno FROM dept

WHERE loc='BOSTOM'));

Execution Plan

-----------------------------------------

DELETE STATEMENT

DELETE OF 'EMP'

NESTED LOOPS

TABLE ACCESS (BY INDEX ROWID) OF 'EMP'

INDEX (UNIQUE SCAN) OF 'PK_EMP' (UNIQUE)

TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'

INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)

다)다중테이블 입력(Multi-table Insert)서브쿼리

INSERT ALL

WHEN ORDER_TOTAL < 1000000

THEN INTO SMALL_ORDERS

WHEN ORDER_TOTAL > 1000000 AND ORDER_TOTAL < 2000000

THEN INTO MEDIUM_ORDERS

WHEN ORDER-TOTAL > 2000000

THEN INTO LARGE_ORDERS

SELECT ORDER_ID,ORDER_TOTAL,SALES_REP_ID,CUSTOMER_ID

FROM ORDERS;

------------------

INSERT STATEMENT

MULTI-TABLE INSERT

INTO OF 'SMALL_ORDERS'

INTO OF 'MEDIUM_ORDERS'

INTO OF 'LARGE_ORDERS'

TABLE ACCESS (FULL) OF 'ORDERS'

시스템 데이터를 관계형 데이터베이스 구조로 이행할때 유용

라)HAVING 절 서브쿼리 실행 계획

SELECT department_id,manager_id

FROM employees

GROUP BY department_id,manager_id

HAVING (department_id, manager_id) IN (SELECT e.deptno, e.mgr

FROM emp e,dept d

WHERE e.deptno = d.deptno

AND d.oc='BOSTON');

Execution Plan

-------------------------------

SELECT STATEMENT

FILTER

SORT (GROUP BY)

TABLE ACCESS (FULL) OF 'EMPLOYEES'

NESTED LOOPS

TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'

INDEX (RANGE SCAN) OF 'LOC_IDX' (NON-UNIQUE)

AND-EQUAL

INDEX (RANGE SCAN) OF 'EMP_MGR_IDX' (NON-UNIQUE)

INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (NON-UNIQUE)

마)ROLLUP,CUBE,GROUPING SETS 처리 실행계획

SELECT co.country_region,co.country_subregion,

SUM*s.amount_sold) "Revenue",

GROUP_ID() g

FROM sales s,customers c, countries co

WHERE s.cust_id=c.cust_id

AND c.country_id=co.country_id

AND s.time_id= :b1

AND co.country_region IN ('Americas','Europe')

GROUP BY ROLLUP (co.country_region, co.country_subregion);

Execution Plan

-----------------------------

SELECT STATEMENT

SORT (GROUP BY ROLLUP)

NESTED LOOPS

PARTITION RANGE (SINGLE)

TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'SALES'

BITMAP CONVERSION (TO ROWIDS)

BITMAP INDEX (SINGLE VALUE) OF 'SALES_TIME_BIX'

TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMERS'

INDEX (UNIQUE SCAN) OF 'CUSTOMERS_PK' (UNIQUE)

INDEX (UNIQUE SCAN) OF 'COUNTRY_C_ID_PK' (UNIQUE)

CUBE 역시 동일 형태의 실행계획 생성

GROUPING SETS; GROUPING SETS( (DEPTNO,JON),(ENAME,DEPTNO) ) 의 형태로 사용되며

정의된 그룹별로 결과가 나타남

바)MERGE문 실행계획

조인에 성관한것은 UPDATE, 실패한것은 INSERT

MERGE INTO bonuses D

USING ( SELECT employee_id, salary, department_id

FROM employees

WHERE department_id=80) S

ON (D.employee_id = S.employee_id)

WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary * .01

DELETE WHERE (S.salary > 8000)

WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)

VALUES (S.employee_id, S.salary * 0.1);

Execution Plan

-------------------------------------

MERGE STATEMENT

MERGE OF 'BONUSES'

VIEW

NESTED LOOPS (OUTER)

TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEES'

INDEX (RANGE SCAN) OF 'EMPLOYEE_X1' (NON-UNIQUE)

TABLE ACCESS (BY INDEX ROWID) OF 'BONUSES'

INDEX (RANGE SCAN) OF 'BONUSES_X1' (NON-UNIQUE)

3.3. 실행계획의 제어

휘휘 — Tue, 18 Aug 2009 00:50:36 +0900

3.3. 실행계획의 제어

3.3.1. 힌트의 활용 기준

- 잘못을 바로 잡아 주는 용도보다 옵티마이져가 가지고 있지 못하는 정보를 우리가 더 많이 알고 있을 때나 특별한 목적을 관철하고자 할때 사용

- 불필요한 힌트는 액세스 경로에 결정적 악영향을 미칠수도 있음

3.3.2. 최적화 목표(Goal) 제어 힌트

옵티마이져 모드를 바꾸어서 그 결과를 살펴보는 목적

ALL_ROWS ; 쿼리 전체를 최적화 할경우의 최저비용의 실행계획 수립

ex) select /*+ ALL_ROWS */ ..

CHOOSE ; 통계정보 유무에 따라 규칙기준이나 비용기준을 적용

ex) SELECT /*+ CHOOSE */ ..

FIRST_ROWS ; 최적 응답시간(Best response time)을 목표로 최저 비용의 실행계획을 수립하도록 유도
ex) SELECT /*+ FIRST_ROWS */ ...

SELECT /*+ FIRST_ROWS(10) */ ...

RULE ; 규칙 기준 옵티마이져를 이용한 최적화를 요구

ex) SELECT /*+ RULE */ ...

3.3.3. 조인 순서 조정을 위한 힌트

다수의 테이블을 조인하는 경우에 조인 순서에 혼선이 있을 때 적용

ORDERED ; FROM 절에 기술된 테이블 순서대로 조인을 수행하도록 유도
               조인 방법을 유도하기 위한 USE_NL, USE_MERGE 등의 힌트와 함계 사용

ex) SELECT /*+ ORDERED */ ...
    FROM    TAB1, TAB2, TAB3
    WHERE ....

    SELECT /*+ ORDERED USE_NL (A B C) */ ...
    FROM TAB1 a, TAB2 b, TAB3 c
    WHERE ....

LEADING ; FROM 절에 기술한 테이블의 순서와 상관없이 조인 순서를 제어

ex)     SELECT /*+ LEADING ( b c ) * / ...
    FROM CUST a, ORDER_DETAIL b, ITEM c
    WHERE a.cust_no = b.cust_no
    and    b.item_no = c.item_no

3.3.4. 조인 방법 선택용 힌트

조인 방식들에 대한 확실한 이해가 바탕이 되어야 함

USE_NL ; Nested Loops 방식을 사용하여 조인을 수행

ex) SELECT /*+ USE_NL(a b c) */ ...
    FROM TAB1 a, TAB2 b, TAB3 c
    WHERE ...

NO_USE_NL ; Nested Loops 방식을 제외한 다른 방식의 조인을 사용하도록 제시

ex) SELECT /*+ NO_USE_NL(l h) */ ...
    FROM    orders h, order)items l
    WHERE    l.order_id = h.order_id
        AND    l.order_date >= '20050101';

USE_NL_WITH_INDEX ; USE_NL과 INDEX 힌트를 하나로 통합한것 (NL 조인에서 외측루프의 주관 인덱스를 지정)

ex) SELECT /*+ USE_NL_WITH_INDEX(l item_prod_ix) */ ...
    FROM orders h, order_items l
    WHERE l.order_id = h.order_id
        AND l.order_item like 'ADEN%'
        AND h.order_dt between '20051001' and '20051015';

USE_HASH ; 해쉬 조인 방식으로 조인이 수행되도록 유도

ex) SELECT /*+ USE_HASH(a b) */ ...
    FROM sale a, pre+order b
    WHERE a.order_id=b.order_id
    AND a.sale_dt like '2005%';

NO_USE_HASH ; 해쉬 조인을 제외한 다른 방식의 조인을 고려토록 유도

ex) SELECT /*+ NO_USE_HASH(m d) */ ...
    FROM member m, dept d
    WHERE m.deptid = d.deptid;

USE_MERGE ; Sort Merge 방식으로 조인을 수행하도록 유도 , 필요시 'ordered' 힌트와 같이 사용

ex) SELECT /*+ USE_MERGE(a b) */ ...
    FROM sale a, pre_order b
    WHERE a.order_id = b.order_id
    AND a.sale_dt BETWEEN '20050701' AND '20050930'
    AND b.cust_no ='101';

NO_USE_MERGE; Sort Merge 방식을 제외한 다른 방식의 조이 고려토록 유도

3.3.5. 병렬처리 관련 힌트

시스템 자원을 최대한 사용하여 결과를 얻는 절대 시간을 줄이겠다는 목적

PARALLEL ; 대량의 DATA에 대한 TABLE을 액세스 할때와 DML을 처리할때 SQL의 병렬처리를 지시하는 힌트
PARALLEL THREADS를 나타내는 숫자(PARALLEL DEGREE)와 함께사용, Parallel Degree를 지정하지 않으면

                  PARALLEL_THREADS_PER_CPU 파라메터에 정의된 값을 이용
               TABLE 정의시 PARALLEL 을 지정하였다면 힌트없이 적용
                 DML 문에 적용시는 ALTER SESSION ENABLE PARALLEL DML' 을 지정해야함

ex) SELECT /*+ FULL (sales) PARALLEL (sales) */
       SUBSTR(sale_dt,1,6),SUM(qty)
    FROM sales
    WHERE sale_dt between '20030101' AND '20051231'
    GROUP BY SUBSTR(sale_dt,1,6);

    SELECT /*+ FULL (sales) PARALLEL (sales, 8 ) */
        SUBSTR(sale_dt,1,6), SUM(qty)
    FROM sales
    WHERE sale_dt    BETWEEN '20030101' AND '20051231'
    GROUP BY SUBSTR(sale_dt,1,6);

NOPARALLEL ; 해당 테이블의 PARALLEL 파라메터를 무시하고 병렬처리를 하지 않는 실행계획을 수립
                       (버전에 따라 NO_PARALLEL)
ex) SELECT /*+ NOPARALLEL (m) */ member_name
    FROM members m;

PQ_DISTRIBUTE ; 슬레이브 프로세스 ( 생산자(Producer)와 소비자(Consumer)프로세스) 사이에서 조인할 테이블 로우를 할당작업(Distribution)을 하는 방법을 정의하는 힌트

expression) /*+PQ_DISTRIBUTE (table, outer_distribution,inner_distribution) */
                - outer_distribution ; 외측 테이블에 대한 할당 방법을 기술
                - inner_distribution ; 내측 테이블에 대한 할당 방법을 기술
                - 할당 방법 :
                    - HASH: 해시 함수를 수행한 결과값을 이용하여 소비자 프로세스의 로우들을 할당
                     - BROADCAST : 외측 테이블 전체 로우를 모든 CONSUMER 프로세스로 보냄
                   - PARTITION : 조인 대상 테이블이 조인 키 칼럼으로 파티션되어있을 경우 파티션 키 값을 이용하여 로우들을

                                           소비자 프로세스에 할당
                   - NONE ; 조인 대상 로우들을 랜덤하게 할당

ex)     SELECT /*+ ORDERED PQ_DISTRIBUTE (b HASH, HASH) USE_HASH(b) */ ...
        FROM TAB1 a, TAB2 b
          WHERE a.col1 = b.col2;

        SELECT /*+ PQ_DISTRIBUTE ( b BROADCAST, NONE) USE_HASH(b) */ ...
        FROM TAB1 a, TAB2 b
        WHERE a.col1 = b.col2;
*** ????

PARALLEL_INDEX ; 파티션 인덱스에 대한 인덱스 범위 스캔을 병렬로 수행하기 위한 병렬도를 지정

ex) SELECT /*+ PARALLEL_INDEX(table1,index1, 3) */ ...

NOPARALLEL_INDEX ; parallel 파라메터를 무시하여 병렬 인덱스 범위 스캔을 하지 않음
                                버젼에 따라 NO_PARALLEL_INDEX

ex) SELECT /*+NOPARALLEL_INDEX (m mem_join_idx) */ ...
    FROM members m
    WHERE join_date BETWEEN '20040101' AND '20051231';

3.3.6. 액세스 수단 선택을 위한 힌트

FULL ; 전체 테이블 스캔 방식으로 유도
ex) SELECT /*+ FULL (t) */ ...
    FROM BIG_TAB T
    WHERE cre-date >='20050101';

HASH ; 해쉬 스캔 방식으로 액세스 하도록 유도하는 힌트
ex) /*+ HASH(TABLE_NAME) */

CLUSTER ; 클러스터 인덱스를 통해 스캔하도록 유도
ex) /*+ CLUSTER(table_name) */

INDEX ; 인덱스 범위 스캔을 유도 (뷰의 경우 뷰쿼리 내부의 테이블 인덱스 지정가능)
ex) SELECT /*+ INDEX(s sales_pk) */ ...
    FROM sales s
    WHERE sale_date='20051015';

    SELECT /*+ INDEX(VIEW_cust cust cname_idx_ */ ...
    FROM VIEW_cust
    WHERE cust_name = 'TOMCAT';

NO_INDEX ; 지정한 인덱스를 제외하고, 다른 액세스 방법을 고려하도록 유도
                   INDEX,INDEX_ASC,INDEX_DESC,INDEX_COMBINE,INDEX_FFS등을 함께사용하면 두 힌트 모두 무시

ex) SELECT /*+ NO_INDEX(m member_pk) */ ...
    FROM members m
    WHERE member_id > 10200;

INDEX_ASC ; 지정한 인덱스를 인덱스 컬럼 값의 오름차순으로 범위 스캔하도록 유도

ex) SELECT /*+ INDEX_ASC(a idx01) */ ...
    FROM tab1 a
    WHERE col1 LIKE 'ABC%';

INDEX_DESC ; 지정한 이덱스를 인덱스 컬럼값의 내림차순으로 범위 스캔하도록 유도

ex) SELECT /*+ INDEX_DESC(a idx01) */ ...
    FROM tab1 a
    WHERE col1 LIKE 'ABC%';

INDEX_COMBINE ; 2개 이상의 인덱스를 비트맵 인덱스로 변경/결합하여 테이블을 액세스 하는 방식으로 유도하는 힌트

ex) SELECT /*+ INDEX_COMBINE (e sal_bix hiredate_bix) */ ...
    FROM emp e
    WHERE sal>3000 AND hiredate < '20050715';

INDEX_FFS ; 인덱스 전체 범위를 스캔하는 방식으로 유도하는 힌트, 다중 블록을 스캔

ex) SELECT /*+ INDEX_FFS (table_name index_name) */ ...

NO_INDEX_FFS ; 고속 전체 인덱스 스캔 방식을 제외한 다른 액세스 방법을 사용하도록 유도

INDEX_JOIN ; 두개 이상의 인덱스들만으로 조인을 수행하도록 유도 ( 인덱스가 필요로 하는 모든 컬럼의 값을 가져야함)

ex) SELECT /*+ INDEX_JOIN(e SAL_BMP HIREDATE_IX) */sal,hiredate
    FROM emp e
    WHERE hiredate < sysdate and sal > 2000;

INDEX_SS ; 인덱스 스킵 스캔 방식으로 인덱스를 액세스 하도록 유도

ex) SELECT /*+ INDEX_SS (e emp_idx_ */ ..
    FROM emp e
    WHERE hiredate = sysdate;

** 다시 한번더 확인

NO_INDEX_SS ; 지정한 테이블의 인덱스에 대한 스킵스캔을 제외한 다른 액세스 방법을 사용하도록 유도

INDEX_SS_ASC ; 인덱스 스킵 스캔 방식에서 오름차순으로 읽도록 유도

INDEX_SS_DESC ; 인덱스 스캡 스캔 방식에서 내림차순으로 읽도록 유도

ex) SELECT /*+ INDEX_SS_DESC(e ename_ix) */ ..
    FROM employees e
    WHERE first_name = 'Steven';

3.3.7. 쿼리형태 변형(Query Transformation)을 위한 힌트

USE_CONCAT ; OR 이나 IN 연산자를 별도의 실행 단위로 분리하여 실행계획을 수립하고 연결하는 통합실행계획을 수립하도록 유도

ex) SELECT /*+USE_CONCAT */
    FROM emp
    WHERE job ='CLERK' OR deptno=10;

NO_EXPAND ; OR 이나 IN을 연결 실행계획으로 처리되지 않도록 사용 (USE_CONCAT의 반대)

ex) SELECT /*+ NO_EXPAND */ ..
    FROM CUSTOMER
    WHERE CUST_TYPE IN ('A','B');

REWRITE ; M-VIEW(실체뷰) 를 사용할때 뷰에 사용된 테이블을 직접액세스하거나 M-VIEW를 액세스하는 것중 유리한것을 석택하도록 쿼리 재작성작업을 함

ex) SELECT /*+REWRITE (sales_mv) */
            c.cust_id, MIN(c.cust_name), COUNT(distinct order_id)
    FROM sales s, order o, customer c
    WHERE s.order_id=o.order_id
    AND o.order_cust = c.cust_id
    GROUP BY c.cust_id;

NOREWRITE ; QUERY_REWRITE_ENABLED 파라메터가 TRUE로 정어되어있어도 이를 무시
                     M-view가 있어도 원 테이블로 직접 계산을 유도함으로 최신값으로 결과로 출력 할수있음

ex) SELECT /*+ NOREWRITE */ sum(s.amount_sold) AS dollars
    FROM sales s, times t
    WHERE s.time_id = t.time_id
    GROUP BY t.calendar_month_desc;

MERGE ; 뷰쿼리 병합이 가능함에도 불고하고 뷰쿼리 병합이 일어나지 않을 경우
ex) SELECT /*+ MERGE(table_name) */ ...

NO_MERGE ; 뷰쿼리 병합이 일어나지 않도록 요구 하는 힌트
ex) SELECT /*+ NO_MERGE(table_name) */ ..

STAR_TRANSFORMATION ; 소량의 데이터를 가진 여러개의 디멘전 테이블과 팩트 테이블의 개별 비트맵 인덱스를 이용하여 처리 범위를 줄이는 조인방식

ex) SELECT /*+ STAR_TRANSFORMATION */
                d.dept_name, c.cust_city, p.product_name, SUM(s.amount) sales_amount
    FROM SALES s, PRODUCTS t, CUSTOMERS c, DEPT d
    WHERE s.product_cd = t.product_cd
        AND s.cust_id = c.cust_id
        AND s.sales_dept = d.dept_no
        AND c.cust_grade between '10' and '15'
        AND d.location = 'SEOUL'
        AND p.product_name IN ('PA001','DR210')
    GROUP BY d.dept_name, c.cust_city, p.product_name;

NO_STAR_TRANSFORMATION ; 스타변형 조인을 하지 않도록 유도

FACT ; 스타 변형 조인에서 팩트 테이블을 지정하기 위하 사용
NO_FACT ; 지정한 테이블을 팩트 테이블로 인정하지 말아 달라고 요구

UNNEST ; 서브 쿼리와 메인쿼리를 합쳐 조인 형태로 변형하도록 하는 실행계획을 생성하도록 유도하는 힌트

ex) SELECT /*+ UNNEST (@qb) */ ..
    FROM emp e
    WHERE e.deptno IN (SELECT /*+ QB_NAME(qb) */ d.dept no
                               FROM dept d
                                WHERE d.oc='DALLAS');

NO_UNNEST ; UNNESTING 을 하지 않도록 유도

3.3.8. 기타 힌트

APPEND ; INSERT문에서 사용, SGA를 거치지 않고 'DIRECT-PATH'방식으로 직접 저장공간으로 입력 (최고 수위점 다음위치에 저장)
ex) INSERT /*+ APPEND */ INTO TAB2
    SELECT *
    FROM TAB1
    WHERE COL1 >= '20050101' ;

NOAPPEND ; INSERT시 'CONVENTIONAL-PATH'방식으로 수행 (직렬모드)

CACHE ; FULL 스캔 방식으로 읽혀진 블록을 LRU 리스트의 최근 사용위치에 머물도록 하여 계속 메모리에 머물수 있도록 처리
ex) SELECT /*+ FULL (t) CACHE(t) */ last name
    FROM tech_spec t;

NOCACHE ; FULL 스캔방식으로 읽혀진 블록을 LRU 리스틔 끝에 위치하도록 유도(메모리에서우선제거), 옵티마이져가 블록을 관리하는 일반적방법

ex) SELECT /*+ FULL(m) NOCACHE (m) */ member_name
    FROM members m;

CARDINALITY ; 옵티마이져에게 해당쿼리나 일부구성에 대한 카디널리티 에상값을 제시하여 실행계획수립에 참조토록 함
                       테이블을 지정하지 않으면 전체 쿼리를 수행한 결과로 얻어진 총건수로 간주

ex) SELECT /*+ CARDINALITY ( s 9999 ) */ ...
    FROM sale s, prod p
    WHERE s.sale_date >= '20050901'
    AND p.prod_id LIKE 'ABEC%';

CURSOR_SHARING_EXACT ; CURSOR_SHARING 초기화 파라메터가 'EXACT' 일경우와 동일한 결과 , 리터럴 값을 바인드 변수로 변경하지 않음

ex) SELECT /*+ CURSOR_SHARING_EXACT */ *
    FROM EMP
    WHERE ENAME = 'SCOTT';

DRIVING_SITE ; 원격 테이블과 조인을 수행할 사이트를 지정하여 분산 쿼리를 최적화 하는데 적용

ex) SELECT /*+ DRIVING_SITE(b) */
    FROM cust a, order@ord_svr b
    WHERE a.join_date >='20050101'
    AND b.order_date >= TO_CHAR(SYSDATE-7,'YYYYMMDD');

DYNAMIC_SAMPLING ; 통계정보를 가지고 있지 않거나 ,에러등의 문제로 사용할수 없게되거나, 너무 오래되어 신뢰할수 없을 때 적용
                            통계정보가 있는 하나의 테이블에 사용할경우 무시됨

ex) SELECT /*+ DYNAMIC_SAMPLING(e 1) */ ...
    FROM EMP
    WHERE ENAME = 'SCOTT';

PUSH_PRED ; 뷰나 인라인뷰의 외부에 있는 조인 조건을 뷰 쿼리 내로 삽입하는 힌트

ex) SELECT /*+ NO_MERGE(v) PUSH_PRED(v) */ ...
    FROM member m,
        ( SELECT carrier_name, carrier_duration
          FROM member_carrier
          WHERE carrier_type IN ('1','2','4') ) v
    WHERE m.member_id = v.member_id(+)
    AND    m.member_type = '1001';

NO_PUSH_PRED ; 뷰나 인라인 뷰의 외부에 있는 조인 조건을 뷰쿼리 내료 삽입하지 않도록 하는 힌트

ex) SELECT /*+ NO_MERGE(v) NO_PUSH_PRED(v) */ *
    FROM employees e,
        (SELECT manager_id
          FROM employees) v
    WHERE e.manager_id = v.manager_id(+)
        AND e.employee_id = 100;

PUSH_SUBQ ; 머지 되지않는 서브 쿼리를 최대한 먼저 수행할 수 있도록 실행계획을 수립하기를 요구

ex) SELECT /*+ PUSH_SUBQ(@qb) */ ..
    FROM sales
    WHERE item = :b1
        AND saltype IN (SELECT /*+ QB_NAME(qb) */ code_id
                           FROM code_table
                           WHERE code_type='SALTYPE')
        AND saldate between :b2 and :b3;

QB_NAME ; 쿼리 블록에 이름을 부여하여 외부의 다른 힌트에서 참조할수 있도록 함

ex) SELECT /*+ UNNEST (@qb) */
    FROM emp e
    WHERE e.deptno IN (SELECT /*+ QB_NAME(qb) */ d.deptno
                                FROM dept d
                                WHERE d.loc = 'DALLAS');

REWRITE_ON_ERROR ; 적합한 M-VIEW가 존재하지 않아 쿼리 재생성을 할수 없을 경우

ORA-30393 에러를 유발하여 쿼리 수행을 중단

역시 옵티마이져에 대한 전체 적인 지식이 받힘이 안되면 사용하는것도 이해하는것도 힘들거 같습니다.
또 실제로 적용해보고 사용해보고 각 건의 경험이 꽤 중요할꺼같습니다.

4.1. 인덱스 선정기준(4.1.1.-4.1.5.)

balto — Mon, 17 Aug 2009 10:35:23 +0900

(편집이 잘 안될 수도 있어 파일 첨부했습니다.)

4장 인덱스 수립전략

(무엇을 배우는가?)

- 최소의 인덱스로 최대의 액세스 형태를 만족시켜야 한다.

- 인덱스 구성을 위한 종합적인 전략을 수립하는 절차를 설명한다.

4.1. 인덱스의 선정 기준

4.1.1. 테이블 형태별 적용기준

가) 적은 데이터를 가진 소형 테이블

- 적은 데이터란 DB_FILE_MULTIBLOCK_READ_COUNT 값 보다 크기가 작은 블럭을 가진 테이블이라 가정하자.

- 인덱스 유무가 성능에 영향을 미치지 않으므로 인덱스를 안 만들 수도 있다

(인덱스를 생성하는 것이 나쁘다는 애기는 아니다.)

- 기본키에는 인덱스를 생성하는 것을 권장한다.

- 인덱스 존재 여부는 조인의 실행 계획이나 무결성에 영향을 미친다.

- 인덱스 일체형 테이블을 구성하는 것도 좋은 방법이다.(IOT)

- 대량의 반복이 발생할 때는 인덱스가 조금이라도 효과가 있을 수 있다.

나) 참조되는 역할을 하는 중대형 테이블

- 고객테이블 같이 참조 위주 테이블, 데이터는 많고 액세스 조건이 일정하며, 랜덤액세스되며 데이터증감이 적다.

- 좁은 범위만 필요하거나 조인때 내측루프에 기본키에 의해 연결

- 인덱스를 많이 만들어 두는 것이 효과적이며, PCTFREE를 0으로 만들어도 좋음.

- 간혹 인덱스 재생성하면 된다.

다) 업무의 구체적인 행위를 관리하는 중대형 테이블

- 매출정보테이블 같이 액세스 조건이 다양하고, 데이터가 꾸준히 증가한다.

- B-tree 인덱스가 유리하고 결합인덱스가 많이 사용되며 결합인덱스 컬럼 구성에 따라 영향을 받는다.

- 인덱스 전략 수립에 신중해야 한다. 액세스 유형을 잘 수집하고 인덱스 조합을 잘 찾는다.

- 인덱스 추가시 데이터 증감에 따른 속도 저하도 가능하므로 주의한다.

- 비트맵인덱스를 고려해볼 수도 있다.(배치처리로 다량의 데이터가 추가되고, 다양한 액세스가 발생한다면) ?

라) 저장용 대형 테이블

- 로그기록처럼 대량의 테이블에 지속적으로 데이터가 입력된다. 저장이 목적이며 액세스 형태가 다양하지 않다.

- 삭제나 갱신이 거의 없기 때문에 PCTFREE를 0으로 둘 수 있다.

- PRIMARY KEY를 두지 않을 수 있으며 필요시 UNIQUE INDEX를 생성해 준다.

- 파티션을 고려할 수 있다.(오래된 데이터는 참조 가능성이 줄어듦으로 최근의 파티션에 많은 인덱스를 생성하고 그렇지 않은 경우는 최소한의 인덱스를 생성한다.

- 특정파티션에 인덱스를 만들기 위해 USQBLE/UNUSABLE 기능을 사용한다.

(UNUSABLE 파티션에 질의를 하면 오류이므로 FROM 절에 특정 파티션을 지정한다.

4.1.2. 분포도와 손익분기점

- 인덱스가 처리범위를 줄이는데 얼마나 효율적인가(충실한가는) 분포도와 관련있다.

- 보통은 컬럼의 분포도가 10-15%를 넘지 않아야 효과가 있다(왜?)

(넘으면 전체 테이블 스캔이 더 유리하다)

비교 : 전체테이블 100 블록 vs 인덱스 1블록 + 0.1 * ...

- 각 컬럼의 분포도는 높아도 결합인덱스의 분포도는 낮아진다.

- 클러스터링을 할 경우 클러스터링 팩터의 향상은 인덱스 액세스 비용을 낮춘다.

- 손익분기점을 넘는 경우라도 부분범위를 액세스할 경우 손익이 생긴다.

(예, 30% 손익분기점이라도 1/100만 액세스했을 경우 이익이다)

4.1.3. 인덱스머지와 결합인덱스 비교

- 검색조건이 한개의 테이블에 2개 속성인 경우 비교

(인덱스머지)

- 인덱스머지는 INDEX1과 INDEX2에서 검색하여 인덱스에 저장된 ROWID를 비교하여 검색(테이블 액세스하기 전에 ROWID를 모두 알아낸다)

- 그러나, 두 인덱스가 비슷한 분포도를 가질때 유리, 많이 차이나면 적은쪽 인덱스만 이용하여 테이블을 직접액세스하는 것이 유리(한쪽이 = 조건이 아닐 경우)

x1 + x2 + selected blocks(c1,c2) > x1 + selected blocks(c1)

예를들면 : 1 + 3 + 1 > 1 + 2 (?)

(결합인덱스)

- 결합인덱스로부터 ROWID를 모두 찾아낸다)

4.1.4 결합인덱스의 특징

- 결합인덱스의 단점

컬럼 중 일부만 조건을 받거나 = 아닌 연산자가 많을 경우

가) 분포도와 결합순서의 상관관계

- 인덱스 결합시 분포도가 좋은 컬럼이 앞에 있는 것이 좋은가?

=로 비교되면 순서는 별 영향이 없다. B-tree 검색시 영향을 받지 않는다.

나) = 이 결합순서에 미치는 영향

- 첫번째 조건이 = 이면 첫번째 조건을 만족하는 것들 중 두번째 조건의 범위를 액세스 한다.

- 첫번째 컬럼이 = 이 아닌 범위 조건이 오면 첫번째 조건의 범위를 모두 검색하여 두번째 조건을 만족하는 것을 찾는다.

- 결론적으로 결합인덱스에서는 = 조건인 컬럼이 먼저 있어야하므로 분포도 보다 컬럼의 순서가 중요하다.

다) IN 연산자를 이용한 징검다리 조건

- 속성1이 = 검색이 주이고 속성2가 =이 아닌 경우가 많다면 (속성2, 속성1) 결합인덱스를 생성한다.

- 그러나 가끔씩 속성1이 LIKE나 BETWEEN을 사용하고 속성2가 =을 사용한다면 결합인덱스 (속성2, 속성1)을 또 만든다.(?)

- 결합인덱스에서 IN과 BETWEEN 연산자 차이 -> 결합인덱스에서 필요한 부분만 검색

- 결합인덱스에서 IN 연산자의 징검다리 효과(그림 1-4-5)

WHERE col2 IN (111,112) AND col1 ='A'

=> WHERE (col2=111 AND col1='A') OR (col2=112 AND col1='A')

라) 처리범위에 직접적인 영향을 주지 못하는 컬럼의 추가 기준

- = 비교가 아니지만 결합인덱스에 포함을 시킬지 여부

- = 비교가 아니어도 결합인덱스에 있으면 인덱스에서 검색을 시도하므로 ROWID를 직접 찾을 수 있다.

- SELECT 리스트에만 사용되는 컬럼도 경우에 따라서는 필요하다 - 인덱스조인을 유도(?)

4.1.5. 결합인덱스의 컬럼 순서 결정 기준

- 결합인덱스 컬럼 순서 결정 우선순위

1단계 : 항상 사용하는가?

2단계 : 항상 = 로 사용하는가?

3단계 : 어느 것이 더 좋은 분포도를 갖는가?

4단계 : 자주 정렬되는 순서는 무엇인가?

5단계 : 부가적으로 추가시킬 컬럼은 어떤 것으로 할 것인가?

- 예) 다음 예에서 1개의 인덱스를 생성한다면?

① c1(=), c2(between), c3(=)

② c2(=), c3(between), c4(>)

③ c1(=), c2(=), c3(like)

④ c1(=), c2(=), c4(>)

1단계 : 항상 사용하는가?

=> c2(4번), c1(3번), c3(2번), c4(2번)

c2가 가정 먼저.

그렇지만 c2이 카디널리티가 높지 않아 인덱스스킵스캔으로한다면 ②번은 c1(=)이 있다고 본다. 정답은 c1이 가장 먼저.

아니면 ② 번을 따로 만족하는 인덱스를 만든다면 c1=c2=3번이 된다.

2단계 : 항상 = 로 사용하는가?

=> c1=c2=3회라고 가정하면 c1은 항상 =를 사용하므로 정답은 c1+c2

3단계 : 어느 것이 더 좋은 분포도를 갖는가?

=> ①번의 c2를 BETWEEN을 IN으로 바꾸면 = 처리가 가능하므로 c1+c2, c2+c1 2개의 정답이고, c1과 c2 중 분포도가 좋은 것을 앞으로한다.

주의 : 액세스 형태가 바뀌면 인덱스도 바뀌어야하므로 액세스형태를 예측하여 바꾼다. 예를 들면 =가 아닌 다른 연산자 사용 가능성을 검토한다.

4단계 : 자주 정렬되는 순서는 무엇인가?

=> 정렬이 필요한 속성을 감안한다. 정렬이 필요한 속성을 인덱스로 했을 경우 부분범위처리에도 유리하다.

5단계 : 부가적으로 추가시킬 컬럼은 어떤 것으로 할 것인가?

=> c3과 c4는 체크기능을 담당한다. c3가 앞에오면 유리한 경우는 ①③번, c4가 앞에오면 유리한 경우는 ④번, 같은 경우는 ②번 경우이므로 c1+c2+c3+c4로 생성.

만약 ④가 빈번하게 사용된다면 c1+c2+c4로 생성.

3.2.2. 데이터 연결을 위한 실행 계획

balto — Thu, 06 Aug 2009 12:43:37 +0900

* 한글문서를 복사하다보니 편집대로 복사가 안되었습니다.

(첨부파일에 한글 문서 첨부하였습니다. WIKI 쪽에 붙일떄는 제대로 옮기죠)

3-2-2-데이터연결을위한실행계획-4.hwp

3.2.2. 데이터 연결을 위한 실행계획

* 주요 내용은 조인, 서브쿼리 등 여러 개의 테이블이 연결될 때 실행계획에 대한 설명

(일반 조인연산) Nested Loop Join, Sort Merge Join, Hash Join

(특별 조인연산) Semi Join, Cartesian Join, Outer Join, Index Join

* 조인을 위한 힌트절

조인 알고리즘힌트문

Nested LoopUSE_NL(table1, table2)

Sort MergeUSE_MERGE(table1, table2)

Hash JoinUSE_HASH(table1, table2)

3.2.2.1. Nested Loop Join (내포 조인, 중첩루프 조인)

- 기본적인 조인 형태이다.

- 프로그램 구조

NestedLoops

{ (outer loop) - 선행테이블(Driving Table)

(inner loop) - 후행테이블(Driven Table)

(process join)

}

- 실행계획 분석

SELECT STATEMENT

NESTED LOOPS

TABLE ACCESS (CLUSTER) OF table1 (outer loop)

INDEX (RANGE SCAN) OF table1index.

TABLE ACCESS (BY ROWID) OF table2(inner loop)

INDEX (RANGE SCAN) OF table2index

- Advanced Nested Loop Join

1:M 테이블의 M쪽 테이블의 ROWID를 찾아 테이블을 한 번에 접근하는 방법.

TABLE ACCESS (BY INDEX ROWID) OF table1

NESTED LOOPS

TABLE ACCESS (BY INDEX ROWID) OF table2(outer loop)

INDEX (RANGE SCAN) OF table2index(NON-UNIQUE)

TABLE ACCESS (BY INDEX ROWID) OF table1(inner loop)

INDEX (RANGE SCAN) OF table1index(NON-UNIQUE)

3.2.2.2. Sort Merge Join(정렬병합조인)

- Nested Loop 조인에서 적절한 인덱스가 존재하지 않아서 조인의 대상 범위가 넓을 때는 랜덤 액세스로 인해 비효율적인 점을 개선하기 위한 방법이다.

- 두 집합을 스캔하여 정렬한 후 머지한다.

- 대용량을 처리해야하는 경우는 정렬의 부담이 크기 때문에 이 방법보다는 해시조인 방법을 사용한다.

- 조인 연결고리 연산자가 ‘=’이 아닌 경우(LIKE, BETWEEN, 부등호)일 경우 Nested Loop 조인 보다 유리하며 해시조인은 이 경우 사용할 수 없다.

- 정렬된 집합이 준비되어있거나 인덱스를 이용하여 정렬이 가능할 때 유리하다.

- Sort Merge 조인의 실행계획 예(한쪽을 인덱스 정렬)

MERGE JOIN

SORT(JOIN)

TABLE ACCESS (BY ROWID) OF table1

INDEX (RANGE SCAN) OF table1index

SORT(JOIN)

TABLE ACCESS(FULL) OF table2

- Sort Merge 조인의 실행계획 예(한쪽을 정렬된 인덱스 사용)

MERGE JOIN

TABLE ACCESS (BY ROWID) OF table1

INDEX (RANGE SCAN) OF table1index

SORT(JOIN)

TABLE ACCESS(FULEE) OF table2index

3.2.2.3. Hash Join(해시 조인)

- 해싱함수를 이용한 조인, 동등조인(=)일 경우만 사용 가능

- Hash Join의 실행계획 예(인덱스 처리된 스캔과 전체테이블 스캔)

HASH JOIN

TABLE ACCESS (BY INDEX ROWID) OF table1

INDEX (RANGE SCAN) OF table1index

TABLE ACCESS(FULEE) OF table2

- Hash Join의 실행계획 예(인라인뷰와 전체테이블을 해시조인한 후 다른 테이블 해시조인)

HASH JOIN

HASH JOIN(outer)

VIEW

SORT(GROUP BY)

INDEX (RANGE SCAN) OF BPM01T_index(NON-UNIQUE)

TABLE ACCESS(FULEE) OF BPM50T

INDEX (RANGE SCAN) OF BPM38T_index(NON-UNIQUE)

3.2.2.4. Semi Join(세미 조인)

- 조인의 정의에서 자연조인에서 한쪽 릴레이션의 속성은 제거되고 다른 한쪽의 속성만 나타나는 조인연산으로 주로 서브질의에 의한 조인을 말한다.

- 세미조인의 관계대수 정의

R(X), S(Y)의 조인 애트리뷰트를 Z=X∩Y라 하면, R?S= R?N(∏X∩Y(S) = ∏X(R?S)

- 1:M의 조인에서 서브질의의 결과가 1인 경우 예

(SQL 문)

SELECT EMP_ID, EMP_NM, AMT

FROM BPM01T

WHERE ORG_CD=:org_cd

AND PAY_CD IN ( SELECT PAY_CD FROM BPM40T WHERE ...) (실행계획)

NESTED LOOPS

VIEW

SORT(unique)

TABLE ACCESS (BY ROWID) OF BMP40T

INDEX (RANGE SCAN) OF ..

TABLE ACCESS(BY ROWID) OF BPM01T.

INDEX (RANGE SCAN) OF ..

3.2.2.5 Cartesian Join(카티젼조인)

- 조인되는 2 테이블간에 공통 속성이 없어서 조인연산이 카티젼곱으로 계산되는 경우이다.

- (실행계획의 예)

MERGE JOIN(CARTESIAN)

VIEW

SORT(GROUP BY)

TABLE ACCESS (FULL) OF table1

SORT(JOIN)

INDEX (RANGE SCAN) OF tableindex (UNIQUE)

- 카티젼 조인이 나타나는 경우

(1) 2개의 테이블을 합하기 위하여 고의적으로 카티젼조인을 할 경우

(2) 스타조인을 하기 위하여 디멘전 테이블을 조인하는 경우

(3) 3개 이상 테이블 조인시 조인 순서 잘못으로 나타난다.

3.2.2.6. Outer Join(아우터 조인)

- 아우터조인은 조인에 참여하지 못하는 테이블도 결과에 포함시킬 경우 사용하는 조인이다.

[1] Nested Loop 아우터조인

- 아우터 조인의 아우터루프가 결정되기 때문에 조인시 신중해야 한다.

- 힌트는 USE_NL(table1, table2)와 동일하지만 실행계획에는 NESTED LOOPS(OUTER)로 나타난다.

[2] Hash 아우터조인

- Nested Loop 조인으로는 용량이 많아 부담되는 경우 사용한다.

- 내측 조인 집합이 해시테이블로 생성되어 연결된다.

- (실행계획 예)

HASH(GROUP BY)

HASH JOIN(OUTER)

TABLE ACCESS (FULL) OF table1 (outer table)

INDEX(RANGE SCAN) OF tableindex (inner table)

- 조인뷰와 조인인라인뷰와 아우터조인 수행 - 뷰의 병합이나 조건절 진입이 허용되지 않고 뷰의 전체 집합이 독립적으로 수행된 결과와 아우터조인을 수행한다(실행 예 참조)

[3] SORT Merge 아우터조인

- Nested Loop 조인으로 문제가 있을 때 사용

[4] 전체 아우터조인

- 양쪽 데이블 모두에 아우터 조인을 실행할 경우

- 실행계획의 예

SELECT STATEMENT

VIEW

UNION-ALL

HASH JOIN(OUTER)

TABLE ACCESS (FULL) OF emp

TABLE ACCESS (FULL) OF employee

HASH JOIN(ANTI)

TABLE ACCESS (FULL) OF employee

TABLE ACCESS (FULL) OF emp

(주의) : (+) 연산자를 사용하여 아우터조인시 주의할 점 - 내측테이블에 조건을 기술할 경우(p230 참조)

3.2.2.7 인덱스조인

- 인덱스간의 해시조인을 통하여 액세스하는 방법이다.

3.2.3. 연산 방식에 따른 해시조인

3.2.3.1. IN-List 탐침 실행계획

- IN 사용예(WHERE 조건)

SELECT order_id, order_type, order_amount

FROM orders

WHERE order_type IN (1,2,3)

- IN 실행계획 예 - IN 조건에 있는 비교만큼 반복한다.

SELECT STATEMENT

INLIST ITERATOR

TABLE ACCESS (BY INDEX ROWID) OF table

INDEX (RANGE SCAN) OF tableindex (NON-UNIQUE)

- IN을 사용해도 비교값이 하나인 경우 ITERATOR가 안나타난다(그런경우가 있을까요?)

- 조인에서 나타나는 INLIST 실행계획 - table2의 속성에 IN 조건이 있을 경우

SELECT STATEMENT

NESTED LOOPS

TABLE ACCESS (BY INDEX ROWID) OF table1

INDEX (RANGE SCAN) OF tabelindex (NON-UNIQUE)

INLIST ITERATOR

TABLE ACCESS (BY INDEX ROWID) OF table2

INDEX (RANGE SCAN) OF table2index (NON-UNIQUE)

3.2.3.2. Concatenation(연쇄) 실행계획

- OR 조건으로 연결된 경우(WHERE 조건)

- 실행계획 예(1개의 테이블에 OR 조건이 있을 경우)

CONCATENATION

TABLE ACCESS (BY INDEX ROWID) OF table1

INDEX (RANGE SCAN) OF table1index1 (NON-UNIQUE)

TABLE ACCESS (BY INDEX ROWID) OF table1

INDEX (RANGE SCAN) OF table1index2 (NON-UNIQUE)

- 실행계획 예(조인의 연결고리에 OR 조건이 있을 경우)

(SQL문의 예)

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)

(실행계획의 예 1 : USE_CONCAT 힌트를 사용한 경우)

SELECT STATEMENT

CONCATENATION

NESTED LOOPS

TABLE ACCESS (BY INDEX ROWID) OF order_item

INDEX (RANGE SCAN) OF order_item_index (NON-UNIQUE)

TABLE ACCESS (BY INDEX ROWID) OF orders

INDEX (UNIQUE SCAN) OF order_index (UNIQUE)

NESTED LOOPS

TABLE ACCESS (BY INDEX ROWID) OF order_item

INDEX (RANGE SCAN) OF order_item_index (NON-UNIQUE)

TABLE ACCESS (BY INDEX ROWID) OF orders

INDEX (UNIQUE SCAN) OF order_index (UNIQUE)

(실행계획의 예 2 : NO_EXPAND 힌트를 사용한 경우)

SELECT STATEMENT

HASH JOIN

TABLE ACCESS (BY INDEX ROWID) OF order_item

INDEX (RANGE SCAN) OF order_item_index (NON-UNIQUE)

TABLE ACCESS (FULL) OF orders

- OR 조건이 사용될 경우 CONCAT 실행계획이 불리한 경우

(1) 조인의 연결고리가 OR 조건이고 조인의 상대방이 넓은 처리범위를 가질 때

(2) 동일조건의 OR 조건 : IN-List 탐침이 더 유리

(3) 처리범위를 줄일 수 있는 다른 액세스 경로가 있을 때

(4) OR 조건들 중 너무 많은 처리범위를 가진 것들이 존재할 때

3.2.3.3. Remote(원격) 실행계획

- 분산되어 있는 테이블을 액세스 할 때 실행계획

- 실행계획 예(원격 테이블 접근)

(SQL문의 예)

SELECT c.customer_name, count(*)

FROM customers c, orders@crm_db o

WHERE c.cust_id=o.cust_id AND o.order_number=:b1

GROUP BYc.customer_name;

(실행계획의 예 : REMOTE)

SELECT STATEMENT

SORT(GROUP BY)

NESTED LOOPS

REMOTE

TABLE ACCESS (BY INDEX ROWID) OF customers

INDEX (RANGE SCAN) OF customer_index (UNIQUE)

- 원격테이블 액세스에서 문제는 원격테이블을 랜덤액세스하거나 원격테이블이 조인의 내측에 있어서 액세스를 빈번히 해야하는 경우이다.

3.2.3.4. Sort Operation(정렬처리) 실행계획

- 정렬작업의 종류

SORT(UNIQUE), SORT(AGGREGATE), SORT(GROUP BY), SORT(JOIN), SORT(ORDER BY)

(1) SORT UNIQUE

- SELECT 문에서 DISTINCT를 사용했을 경우

- subqyery에서 IN 다음에 사용되는 부속질의 결과

(2) SORT AGGREGATE

- GROUP BY 문을 사용하지 않은 상태에서 SUM, COUNT, MIN, MAX, SUM을 사용했을 때

- 실제 SORT를 하지는 않고 결과 값을 구한다.

(3) SORT(GROUP BY)

- GROUP BY 계산을 위하여 정렬을 수행한다.

- 데이터 용량이 많을 경우 HASH(GROUP BY)를 사용한다.

- 인덱스가 키값으로 정렬되어있는 경우 정렬이 필요없으므로 SORT(GROUP BY NOSORT)로 나타난다.

3.2.3.5. Set Operation(집합처리) 실행계획

(1) 합집합(UNION 연산자)

- UNION과 UNION ALL (UNION은 최소공배수 집합을 구한다)

- 실행계획 예 1(OR를 사용할 경우 비효율적인 사례)

(SQL문의 예)

SELECT order_id, order_date, status

FROM orders o

WHERE order_date > :b1 OR

order_id IN (SELECT order_id FROM order_item

WHERE item_group='A');

(실행계획의 예 : full table 스캔이 일어난다)

SELECT STATEMENT

FILTER

TABLE ACCESS (FULL) OF orders

TABLE ACCESS (BY INDEX ROWID) OF order_item

INDEX (RANGE SCAN) OF order_item_index (NON-UNIQUE)

- 실행계획 1을 UNION 연산자를 사용하여 개선한 경우

(SQL문의 예)

SELECT order_id, order_date, status

FROM orders

WHERE order_date > :b1

UNION

SELECT order_id, order_date, status

FROM orders

WHERE order_id IN

(SELECT order_id FROM order_item WHERE item_group='A');

(실행계획의 예 2 : UNION-ALL 사용)

SELECT STATEMENT

SORT(UNIQUE)

UNION-ALL

TABLE ACCESS (BY INDEX ROWID) OF orders

INDEX (RANGE SCAN) OF order_index

NESTED LOOPS

VIEW

SORT(UNIQUE)

TABLE ACCESS (BY INDEX ROWID) OF order_item

INDEX (RANGE SCAN) OF order_item_index (NON-UNIQUE)

TABLE ACCESS (BY INDEX ROWID) OF orders

INDEX (UNIQUE SCAN) OF order_pk_index (UNIQUE)

(2) 교집합(INTERSECTION 연산자)

- 2집합을 정렬하여 머지한다.

- 실행계획 예

(SQL문의 예)

SELECT resist_noFROM employees

WHERE dept_no=:b1

INTERSECT

SELECT social_noFROM billboard

WHERE cre_date=:b2

(실행계획의 예)

SELECT STATEMENT

INTERSECTION

SORT(UNIQUE)

TABLE ACCESS (BY INDEX ROWID) OF employees

INDEX (RANGE SCAN) OF employee_index(NON-UNIQUE)

SORT(UNIQUE)

TABLE ACCESS (BY INDEX ROWID) OF billboard

INDEX (RANGE SCAN) OF billboard_index(NON-UNIQUE)

(3) 차집합(MINUS 연산자)

- 실행계획 예

(SQL문의 예)

SELECT resist_noFROM employees

WHERE dept_no=:b1

MINUS

SELECT social_noFROM billboard

WHERE cre_date=:b2

(실행계획의 예)

SELECT STATEMENT

MINUS

SORT(UNIQUE)

TABLE ACCESS (BY INDEX ROWID) OF employees

INDEX (RANGE SCAN) OF employee_index(NON-UNIQUE)

SORT(UNIQUE)

TABLE ACCESS (BY INDEX ROWID) OF billboard

INDEX (RANGE SCAN) OF billboard_index(NON-UNIQUE)

3.2.3.6. COUNT(STOP) 실행계획

- ROWNUM을 사용했을 때 나타난다.(2부의 부분처리 내용 참조)

- ROWNUM은 SQL 문을 수행한 결과에 사용되는 가상의 일련번호이다.

- (*) 가상컬럼 : SYSDATE, USER, ROWID, LEVEL(connect by 사용시)

- 실행계획 예 1: ROWNUM을 이용한 결과 튜플 수 20개로 제한

(SQL문의 예)

SELECT *

FROM orders

WHERE order_date=:b1 AND acc_deptno=:b2 AND ROWNUM <=20;

(실행계획)

SELECT STATEMENT

COUNT(STOPKEY)

TABLE ACCESS (BY INDEX ROWID) OF orders

INDEX (RANGE SCAN) OF order_index(NON-UNIQUE)

- 실행계획 예 2: ROWNUM을 이용한 결과 튜플 수 1개로 제한

(SQL문의 예)

SELECT /*+ index-desc(s, subject_prgs_pk) */ subject_seq

FROM subject_prgs s

WHERE subject_id=:b1 AND subject_id <= :b2 AND ROWNUM=1;

(실행계획)

COUNT(STOPKEY)

INDEX (RANGE SCAN DESCENDING) OF subject_prgs_pk(UNIQUE)

- 실행계획 예 3: 복잡한 ROWNUM 예 /*?*/는 SQL문의 해당되는 처리 위치

(SQL문의 예)

SELECT MIN(DECODE(ROWNUM,1,rnum2)), plan_time

FROM (SELECT rnum * - 1 rnum2, MIN(plan_time) plan_time

FROM (SELECT ROWNUM rnum1, plan_time/*1*/

FROM subjects

WHERE role_cd='1007'

GROUP BY plan_time, ROWNUM

GROUP BY rnum * -1)/*2*/

WHERE ROWNUM <= 10;/*3*/

(실행계획)

COUNT(STOPKEY)/*3*/

VIEW

SORT(GROUP BY STOPKEY)

VIEW

SORT(GROUP BY)/*2*/

COUNT/*1*/

TABLE ACCESS (BY INDEX ROWID) OF subjects

INDEX (RANGE SCAN) OF subject_index(NON-UNIQUE)

3.2. 실행계획의 유형 (1/3) - 3.2.1. 스캔의 기본유형

휘휘 — Sun, 02 Aug 2009 20:10:32 +0900

이번장은 이론적인 내용보다는 옵티마이져의 세부 형태에대한 소개라
한번씩 책을 정독하시고 실습을 병행해보는게 좋을꺼같습니다. ^^
지겨운 내용..

3.2. 실행 계획의 유형

실행계획은 크게 나누어 네가지로 구분

스캔을 위한 실행계획
데이터 연결을 위한 실행계획
각종 연산을 위한 실행계획
기타 특수한 목적을 처리하는 실행계획

3.2.1. 스캔(Scan) 의 기본유형

스캔의 기본유형

전체 테이블 스캔(Full Table Scans)
로우실별자 스캔(Rowid Scans)
인덱스 스캔(Index Scans)
클러스터 액세스(Cluster Access)
해쉬 액세스(Hash Access)
표본 테이블 스캔 (Sample Table Scans)

3.2.1.1. 전체테이블 스캔

High Water Mark(최고 수위선)

사용된 저장공간의 총합계나 데이터를 넣기 위해 포맷된 영역을 표시하는 것
전체 테이블 스캔은 다중 블록 단위(한번의 I/O)로 메모리에 옮겨진후 순차적으로 읽힘
DB_FILE_MULTIBLOCK_READ_COUNT ; 한번에 액세스하는 블록의 양을 정의

EX)
소형은 인덱스를 경유하더라도 인덱스를 통한 랜덤 엑세스한 자료가 대부분 비슷한 블록이기 때문에

FULL스캔이 유리할수도있음

대형은 이미 액세스한 블록을 공유할 확률이 낮아지므로 랜덤에 대한 부하가 증가 ,

DISK의 ACCESS ARM의 이동이 많아질수 있음

전체 테이블 스캔을 선택하게 되는 경우

적용가능 인덱스의 부재: 인덱스가 없을경우, 결합인덱스의 선두 컬럼이 존재 하지 않을때, 인덱스가 가공될경우
(경우에 따라 인덱스 스킵 스캔이 적용되면 인덱스 사용이 가능)

넓은 범위의 데이터 액세스: 인덱스 사용시의 비용이 클 경우

소량의 테이블 액세스: 최고수위내의 블록이 db_file_multiblock_read_count 내에 있을 경우

병렬처리 액세스: 병릴처리는 전체테이블 스캔이 더욱효과적

'Full'힌트를 적용: 'full(table_alias)' 힌트 사용

실행계획상 oracle 9i 이전에는 해당단계에서 액세스한 로우수가 표기 됬지만 그이후부터는 최종결과만 표기
trace 관련 참고
http://develop.sunshiny.co.kr/130?category=10
http://blog.naver.com/themisoo/110004154686

3.2.1.2. Rowid 스캔

Rowid 스캔은 인덱스를 경유하여 테이블을 액세스하는 과정에서 발생

3.2.1.3. 인덱스 스캔

분류

인덱스 유일 스캔 (Index Unique Scan)
인덱스 범위 스캔 (Index Range Scan)
인덱스 역순 범위 스캔(Index Range Scans Descending)
인덱스 스킵 스캔(Index Skip Scan)
인덱스 전체 스캔 (Full Scan)
인덱스 고속 전체 스캔(Fast Full Index Scan)
인덱스 조인(index Join)
비트맵 인덱스 (Bitmap Index)

가) 인덱스 유일 스캔(Index Unique Scan)

인덱스가 기본키나 유일 인덱스(Unique Index)로 생성되어야 함 (조건절에서 = 비교)
Index(table_alias index_name) hint 를적용하면 사용

나)인덱스 범위 스캔 (Index Range Scan)

브랜치 블록을 경유하여 시작 리프 블록을 찾은후 계속해서 연결된 리프 블록을 스캔하다가 종료점에서 멈춤
= , < , <=, >, >=, BETWEEN, LIKE (%가 선두일경우 불가능)

다) 인덱스 역순 범위 스캔(Index Range Scan Descending)

역순으로 데이터를 액세스 (최대값으로 시작하여 최소값이 될때까지 수행)
가장최근의 자료를 먼저검색하는 등의 경우
Orderby .. Desc를 할대 옵티마이저의 판단에 따르거나 'Index_desc (table_alias index_name)' 힌트 사용

라) 인덱스 스킵 스캔 (Index Skip Scan)

인덱스의 선행컬럼이 사용되지않아도 후행인덱스를 사용하여 스캔
결합인덱스일때 상위에 카디널리티가 낮은 컬럼들이 위치할 경우 상위 값들로 브랜치 블록이 구성될수 있음

ex) col1,col2,col3 ||로 연결하여 col4 인덱스를 만들경우

col1,col2가 낮은 카디널리티를 가지고

col3이 높다면 브랜치 블록은 col1,과 col2가 차지

- sal_typ(매출유형) + item_cd(상품코드) + sal_dt(매출일자)' 로 구성되어있을때 조건으로

item_cd와 sal_dt만 사용되고 sal_typ가 (D,E,L) 세종류일경우
조건절에 SAL_TYP IN ('D','E','L') 을 추가한것과 동일한 효과를 볼수 있음

논리적 서브 인덱스

결합인덱스에서 실제 조건절에 사용되지 않은 컬럼을 논리적으로 인덱스를 사용한 형태로 만들어진 INDEX_SS', 'INDEX_SS_ASC', 'INDEX_SS_DESC' _ 인덱스 스킵스캔사용 흰트 (정상,역순스캔) 사용이 필요없을경우 'NO_INDEX_SS'

마) 인덱스 전체 스캔(INDEX FULL SCAN)

테이블을 대체
쿼리 내에 사용된 어떤 테이블들의 모든 컬럼들이 그 인덱스에 모두 존재하고
인덱스 컬럼 중에서 최소한 NOT NULL 인 컬럼이 하나는 존재할때

ex) select count(*) from user_table 수행시 not null인컬럼이 있을경우 그 컬럼의

인덱스를 전체스캔하여 결과 도출

바) 인덱스 고속 전체 스캔 (Index Fast Full Scans)

쿼리를 위해 사용된 모든 컬럼이 인덱스에 포함되어있을 경우
인덱스만 스캔하고 테이블 액세스는 하지 않음
table과 같이 I/O에 다중 블록 액세스를 함
비트맵 인덱스에서는 적용 불가
INDEX_FFS(TABLE_ALIAS INDEX_NAME) 힌트 사용
NO_INDEX_FFS( table_alias index_name) 힌트로 해제

3.2.1.4. B-tree 클러스터 액세스 (Cluster access)

단일 컬러스터링 : 대량의 범위 처리의 효율화조인의 효율적인 연결: 두개 이상의 테이블을 하나의 클러스터에 저장하는 방법

1:M 관계를 가진 두 테이블을 클러스터링
1쪽의 테이블을 클러스터 키로 액세스하면 하나의 로우가 나타 나고
M쪽을 액세스하면 여러개의 로우가 나타남

3.2.1.5. 해쉬 클러스터 액세스 (Hash cluster access)

해쉬 함수를 통하여 데이터를 액세스 'like,<>,>,>=,<,<='등을 사용했다면 함수에 적용이 불가능
= 이나 'between' ,'in'으로 적용가능한 테이블에 적용
자주 갱신이 없는 자료
로우의 길이의 편차가 심하지 않고 크기에 대한 예측이 가능한 테이블
인덱스를 경유 하지 않기 때문에 바로 테이블을 해쉬로 액스스

사용예
-우편번호 테이블
-시스템
-시스템 사용자 정보

3.2.1.6. 표본 테이블 액세스 (Sample Table scan)

테이블의 데이터 중에서 사용자가 부여한 비율 만큼의 데이터를 익고 그중에서 조건을 만족하는 로우를 리턴
자료 분석등의 작업에서 표본 자료가 필요할 경우 사용

select ....
from table_name sample { block option} (sample percent)
where ....
group by ....
having ....
order by ....

sample block (sample percent) : 전체 액세스 대상 블록에서 지정하 비율 만큼 읽은후 조건을 확인
sample (sample percent) : 모든 블록을 액세스하여 지정한 비율만큼의 로우를 읽음
sample percent 는 0,100을 제외한 0.000001 에서 99.999999 까지
CBO에서 지원 ' role'힌트를 사용해도 비용기준으로 수행

SELECT STATEMENT
TABLE ACCESS (SAMPLE) OF 'EMP'

데이터 마이닝에서 활용

- 데이터 정재를 위해 데이터의 오류 패턴을 찾을 경우 활용
- 테스트를 위한 표본 데이터를 생성할때 적용

데이터 마이닝

- 숨겨진 패턴과 관계를 찾아내어 의미 있는 정보를 발견해 내는 것
- 신뢰도가 높은 충분한 자료를 사용
- 너무 많을 경우 분석이 어려울수 있으므로 사용

옵티마이져의 실행계획을 보는 방법에 대한 내용들입니다.
CBO가 어떤 인덱스로 어떻게 액세스를 진행하느냐를 바로 해설할수 있도록
꾸준한 연습과 분석을 해야할거같습니다.

작성자: tofriend(남송휘)
작성일: 2009년 08월 02일
참고문헌 : 새로쓴 대용량데이터 베이스 1 - 이화식 , 기타 문서

3.1 SQL의 실행계획 (2/3)

운영자 — Fri, 24 Jul 2009 20:53:49 +0900

3.1.2.3. 옵티마이져 목표(Goal)의 선택
가) 옵티마이져 모드의 종류

초기 결과 최적화(First_rows)
- 일부라도 우선 수행
- 비용기준과 경험적 방법(Heuristic method)을 혼합한 접근 방법
- 주어진 상황에 따라 선택하여 최적화를 수행
전체결과 최적화(All_rows)
- 전체가 모두 수행
- CBO의 기본모드
- 사용자 입장에서는 전체를 모두 처리하는것보다 First_Rows 모드가 일반적일수 있음
- 부분처리를 원하는 SQL을 All_rows모드로 처리하면 심각한 속도 저하가 올수 있음
First_Rows_n ??
- n 은 처리를 원하는 범위
- 비용기준으로 최적화를 접근

나) 옵티마이져 모드의 결정 기준

기존 시스템이 있다면
- 'CHOOSE'모드가 있던 버전 - FIRST_ROW
- 신버전에서 개발된 버전 - FIRST_ROWS_N
- 배치처리 위주 - ALL_ROWS
옵티마이져 모드 변경
- 충분한 준비후 작업
모드 변경후 기존 SQL 을 위한방법
- 파라메터 수정
- 옵티마이져 버전을 지정하는 파라메터를 과거 버전으로 지정
- OPTIMIZER_FEATURES_ENABLE (옵티마이져 버전 지정)
기존 방법에서 안정화를 하려는것보다 신기술에대한 적용이 바람직
OUTLINE 이용
- 요약본을 생성하여 이를 참조

다) 옵티마이져 모드와 관련된 파라메터 지정

CURSOR_SHARING
- SQL문내의 상수값을 변수로 전환하여 파싱
- 옵션
- FORCE ; 상수로 입력되어도 변수로 인정 (SIMILAR보다 등급이 높음)
- SIMILR ; 상수로 입력되어도 변수로 인정
- EXACT(default) ; 대,소문자,공백,비교 상수값이 달라도 공유 불가
- OUTLINE 적용하려면 FORCE,SIMILAR적용 필수
DB_FILE_MULTIBLOCK_READ_COUNT
- 전체 테이블 스캔이나 인덱스 고속전체스캔을 할때 한번의 I/O로 읽어야하는 블록수 지정에 사용
- Default 8 (대용량의 배치 위주라면 수치증가 시키는 것이 유리)
OPTIMIZER_INDEX_CACHING
- 인덱스를 통해 반복 랜덤액세스할때 블럭들이 버퍼에 캐쉬되어있을 확률을 지정 (Nested Loops 조인이나 IN-List 탐침수행시)
- Default 0 (반복랜덤엑세스가 빈번한 인덱스 블록이 많다면 값 증가)
OPTIMIZER_INDEX_COST_ADJ
- 비용계산시 인덱스의 액세스 비중을 조정하는 역할
- (Default) 100 (정상인 비용을 그대로 적용)
- 10 (1/10으로 계산)
- 인덱스액세스 경우가 전체 테이블 스캔으로 자주 일아날경우 조정할 필요있음
'choose'모드
- 옵티마이져의 비용기준으로 이전 과도기에 사용
- 통계정보가 없을경우 규칙으로 전환
Optimizer_dynamic_sampling
- 동적 표본화 (Dynamic Sampling)
- 기존 choose모드의 대체 개념
- 소량의 표본을 동적으로 자동으로 추출하여 통계정보로 활용 (소량이나 자주 수행될경우 적용하지 않아야함)
- 통계정보 미보유,사용불능(에러등),너무오래된경우 적용
- 파라메트값: 0 사용하지 않음
- 기본블록수 32, 레벨증가시 2의 제곱만큼 증가
- 전체 테이블 표본으로 할경우 10레벨
인용:
옵티마이져 모드 변경시 상황을 깊히 고려해야하며 필요에따라 세션만 적용하는 등의 방법으로 사용하여야한다

3.1.2.4. 실행계획의 고정화(Stability)

아우트라인(Outline)
- 동일한 실행계획을으로 재현할수 있도록 도와주는 최소한의 참조정보를 보유
- 범용적,개별적으로 관리 하거나 적용,금지 시킬수 있으며 필요시 편집과 익스포트와 임포트역시 가능
- 필요 조건에 따른 카테고리 별로 관리하는것도 좋음

가) 아우트라인의 생성과 조정

패키지
- DBMS_OUTLN 과 DBMS_OUTLN_EDIT
- CREATE_OUTLINE: 지정된 건을 공유 커서에서 찾아 아우트라인을 생성
- CLEAR_USED: 지정한 아우트라인을 제거
- DROP_BY_CAT: 지정한 카테고리에 속한 아우트라인을 제거
- DROP_UNUSED: SQL파싱에 사용된적이 없는 아우트라인을 제거
- UPDATE_BY_CAT : 어떤카테고리를 새로운 카테고리로 변경
- GENERATE_SIGNATURE: 지정한 SQL문에 대한 식별자 생성
- 생성
  - CREATE_STORED_OUTLINES (파리메터) 를 특정세션에 지정하여 생성 시작,종료 하는것이 일반적임
  - TRUE나 FALSE를 지정하거나 카테고리 명 직접 지정가능
  - TRUE; 'DEFAULT' 카테고리로 생성
- 사용
  - USE_STORED_OUTLINES 를 지정하여 사용
  - TRUE나 FALSE, 카테고리 명지정가능
  - ALTER OUTLINE 으로 아우트라인 이름이나 카테고리 변경, 재생성도 가능
PRIVATE 아우트 라인

나) 아우트라인의 관찰

USER_OUTLINES
USER_OUTLINE_HINTS
권한이 허용하면 DBA나 ALL로도 가능

코드:

수행 SQL
SELECT empno,ename,job,loc
from emp e,dept d
where e.deptno=d.deptno and e.empno=7856
order by loc

NAME : SYS    SYS_OUTLINE_050921102126523 -------------(A)
CATEGORY : LHS001 ---------------------------------------(B)
USED        : UNUSED
TIMESTAM : 05 / 09 /16
VERSION : 9.2.0.1.0
SQL_TEXT : SELECT EMPNO, ENAME, JOB LOC FROM EMP E,DEPT D WHERE
                 e.deptno=d.deptno and e.empno=:"SYS_B_0" order b loc ----(C)
SIGNATURE : 2E945C676F84B939E862C3F6D8A4E7CF -----------------(D)

A) 아우트라인의 명칭(자동으로 생성된것)
B) 생성시 부여한 카테고리
C) CURSOR_SHARING 값이 SIMILAR로 지정, 조건절의 상수값은 바인드 변수로 변경되어 생성
D) RAW 타입으로 생성된 SQL 식별자 , 동일 SQL을 다른 카테고리에 생성하면 값은 동일 A의 명칭은 변경됨

코드:

create outline emp_j_otl
on SELECT emp.empno,emp.deptno,dept.loc FROM emp,dept
  WHERE emp.deptno=dept.deptno;


SELECT * FROM USER_OUTLINES WHERE name='EMP_J_OTL';

NAME     CATEGORY     USED     TIMESTAMP     VERSION     SQL_TEXT     SIGNATURE     COMPATIBLE     ENABLED     FORMAT
EMP_J_OTL     DEFAULT     UNUSED     2009.07.21
21:42:14     10.2.0.1.0     SELECT emp.empno,emp.deptno,dept.loc FROM
emp,dept WHERE
emp.deptno=dept.deptno     AF8B1B1EB1ED3CD0C9523EF77C200ACA     COMPATIBLE     ENABLED     NORMAL


실테이블에서 확인
OUTLN.OL$HINTS
SELECT * FROM OUTLN.OL$HINTS;


_NAME     HINT#     CATEGORY     HINT_TYPE     HINT_TEXT     TABLE_NAME     COST     CARDINALITY     JOIN_PRED
EMP_J_OTL     1     DEFAULT     19     USE_NL(@"SEL$1"
"EMP"@"SEL$1")     EMP     4.00691534303308     4     EMP.DEPTNO =
DEPT.DEPTNO
EMP_J_OTL     2     DEFAULT     40     LEADING(@"SEL$1" "DEPT"@"SEL$1" "EMP"@"SEL$1")           0     0      
EMP_J_OTL     3     DEFAULT     101     INDEX(@"SEL$1"
"EMP"@"SEL$1"
("EMP"."DEPTNO"))     EMP     .000160523044559189     2      
EMP_J_OTL     4     DEFAULT     2     FULL(@"SEL$1" "DEPT"@"SEL$1")     DEPT     3.0046162831899     2      
EMP_J_OTL     5     DEFAULT     111     OUTLINE_LEAF(@"SEL$1")           0     0      
EMP_J_OTL     6     DEFAULT     113     ALL_ROWS           0     0      
EMP_J_OTL     7     DEFAULT     109     OPTIMIZER_FEATURES_ENABLE('10.2.0.1')           0     0      
EMP_J_OTL     8     DEFAULT     108     IGNORE_OPTIM_EMBEDDED_HINTS      

실행계획
Execution Plan
----------------------------------------------------------
Plan hash value: 1619650520

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     4 |    92 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP     |     2 |    24 |     1   (0)| 00:00:01 |
|   2 |   NESTED LOOPS              |         |     4 |    92 |     4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL        | DEPT    |     2 |    22 |     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN         | EMP_IDX |     2 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

인용:

아웃라인 참고
http://stanford.edu/dept/itss/docs/orac ... m#i2119940

다) 옵티마이져 업그레이드 시의 적용

규칙기준-> 비용기준
기존 실행계획을 유지하면서 점차적으로 비용기준에 맞추어 가도록 하는전략
- 1) 특정 카테고리를 주고 아우트 라인을 생성
- alter session set create_stored_outlines = category_name;
- 전체적으로 적용시 alter system
- 2) 대부분의 SQL대해 아우트 라인이 생성되도록 충분한 기간을 두고 수집
- 3)아우트 라인 종료 ' create_stored_outlines' 파라메터를 'false'로 지정
- 4) dbms_stats 패키지를 이요하여 통계정보를 생성
- 5) 옵티마지여 모드를 Rule에서 Choose로 변경
- 6) 현세션만적용시 Alter Session ,전체 Alter system 을 이용
옵티마져의 버전업등의 각 상황에 맞게 아웃라인을 생성해서 단계적으로 적용하며
생성시 작업의 성격, 테이블의 성격등 업무특성에 맞게 카테고리 별로 생성시켜 두고 활용하는게 좋다

3.1.2.5. 옵티마이져의 한계

인용:

비용기준 옵티마이져는 완벽할수 없는통계정보를 토대로 정확한 처리범위를 예측할수 없다는 것
다양한 사용 형태를 만족할 수 있도록 종합적이고 전략적인 차원에서 적절한 인덱스나 클러스터링을 결정하고, 수준높은 sql을 구사하는것이 반드시 필요하다는 것