조인 최적화

조인유형

• 크로스 조인 (카티전 프로덕트) = 한테이블의 모든 로우와 다른 테이블의 모든 로우를 한 번씩 결합
• 세타 조인 (이너 조인) = 모든 로우에 대한 결합 이후 필요한 로우만 반환하는 결합
• 이쿼 조인 (동치 조인) = '=' 연산자를 이용한 결합
• 셀프 조인 (자기 조인) = 테이블 자신과 조합되는 경우
• 아우터 조인 = 세타 조인과 동일하며 보존하고자 하는 테이블의 모든 데이터도 같이 표시한다.
• 구형 문법에는 "( " "+" " )"를 이용하여 아우터 조인을 사용한다. 신형문법은 LEFT , RIGHT , FULL OUTER JOIN 사용한다.
• LEFT , RIGHT JOIN PARTITION BY (파티션화 아우터 조인) 특정 컬럼을 기준으로 부분집합으로 분할되고 이를 기준으로 다른 테이블과 아우터 조인을 한다.
• 세미 조인 = 한 테이블의 로우중에 다른 테이블과 일치하는 로우만 반환한다.
• 안티 조인 = 한 테이블의 로우중에 다른 테이블과 일치하지않는 로우만 반환한다.
• 래터럴 인라인 뷰 = FROM절의 앞에 오는 다른 테이블을 참조하는 상관관계가 포함된 인라인뷰이다.

제한 조건 VS 조인 조건

SELECT <column>
FROM <table1> [OUTER] JOIN <table2> ON ( <조인 조건> )
WHERE <제한 조건>

• 두데이터의 집합이 조인 조건을 사용하여 조인된다.
• 조인에 의해 반환된 결과 집합에 대해 제한 조건이 적용된다.

네스트드 루프 조인

특징

• 좌측 입력(아우터 루프)은 한번만 수행된다. 우측 입력(이너 루프)은 여러번 수행 될수 있다.
• 전체 로우의 처리가 끝나기 전에 첫 로우를 반환할 수 있다.
• 제한 조건과 조인 조건 모두에 적용되는 인덱스를 활용할 수 있다.
• 모든 유형의 조인을 지원한다.

2개 테이블 간 조인

• 상관결합유형 - 첫번째 자식이 두번째 자식의 실행을 제어한다.
• 아우터루프(드라이빙 테이블)의 개수만큼 이를 이너루프(드리븐 테이블)에 반환하며 이너루프의 선택도에 따라 풀스캔 또는 인덱스 스캔을 선택해야 한다.

버퍼 캐시 프리페치

• 풀테이블 스캔을 제외한 모든 액세스 패스는 캐시 미스 이벤트를 만나면 싱글 블록 물리적 읽기를 수행한다.
• 이는 네스티드 루프 조인에 최악의 성능을 제공한다. 따라서 이를 개선하기위해서 싱글블록 I/O를 멀티블록 I/O로 바꾸는 최적화 기법이 필요하다.

• 테이블 프리페칭 (NLJ_PREFETCH, NO_NLJ_PREFETCH)
• 배칭(NLJ_BATCHING , NO_NLJ_BATCHING)
• 버퍼캐시 프리웜(Prewarm) = 인스턴스 재기동 후 네스티드 루프 조인이 수행될 ? 잠시 동안 발생한다.
• 최적화 기법이 사용되었는지 확인하는 유일한 기법은 서버프로세스가 수행한 wait event를 분석하는 것이다.

• db file sequential read 단일블록 읽기 / 최적화기법이 사용되지 않은 상태
• db file scattered read or db file parallel read 멀티블록 읽기 / 최적화 기법이 사용된 상태

머지 조인

• 개념 : 조인 조건의 컬럼을 기준으로 정령한다. 이후 두개의 영역에 데이터가 병합된다.

특징

• 좌측 입력은 한번만 실행
• 우측은 한번 이하 실행
• 조인 컬럼 기준의 정렬
• 양측 입력을 모두 읽고 정렬완료 후에 첫 로우 반환
• 모든 유형의 조인을 지원한다.

작업 영역

• 머지 조인은 2개의 작업 영역
• 1) 메모리 내 정렬
• 2) 디스크 기반 정렬 (원패스 , 멀티패스)

해시 조인

• 개념 : 해시조인 처리 1. 빌드 입력 , 2. 프루브 입력
• 빌드 입력은 모든 로우를 메모리상의 해쉬테이블을 구성한다.
• 해쉬 키는 조인조건의 컬럼을 사용하여 계산된다.
• 일단 빌드 입력의 모든 데이터가 해시 테이블에 들어가면 프루브 입력 데이터의 처리가 시작된다.
• 해시 테이블을 대상으로 각 로우들을 검색(prob)하여 조인조건을 만족시키는지 확인한다.
• 만족시키는 로우만 반환한다.

• 빌드 입력은 단한번만 이루어진다.
• 프루브 입력은 한번이하로 발생한다.
• 해시 테이블은 빌드 입력에 대해서만 구성된다.
• 첫번째 로우를 반환하기 전에 처리가 완료되어야 하는 입력은 빌드 입력뿐이다.
• 크로스 조인과 세타 조인 , 파티션화 아우터 조인은 지원하지 않는다.

작업영역

• 해시 조인의 처리에는 해시 테이블을 저장하기 위한 메모리상의 작업 영역이 사용된다.
• 만약 부족할 경우 임시 세그먼트를 이용한다.

인덱스 조인

• index join은 해쉬 조인을 통해서 이루어진다.
• 테이블의 2개의 인덱스를 이용하여 큰 테이블 스캔을 피하는 것인다.
• 두 집합간의 조인은 rowid를 기반으로 하고 있다.
• index_join 힌트를 통해서 구현한다.

아우터 조인

• 3기지 방법의 조인을 모두 지원하며 실행계획에 OUTER란 키워드만 추가될 뿐이다.

조인 방법의 선택

• 쿼리 옵티마이저의 최적화 목표 , 즉 first-rows 와 all-rows 최적화
• 최적화될 조인의 유형과 제한 조건의 선택도
• 조인의 병렬 수행 여부

FIRST-ROWS 최적화

• 첫번째 로우를 얼마나 빠른시간에 반환하느냐를 결정 따라서 NESTED LOOP JOIN을 통한 최적화가 많다.

ALL-ROWS 최적화

지원되는 조인 방법

병렬 조인

• 모든 조인은 병렬로 수행이 가능하다.

파티션 와이즈 조인

• 쿼리 옵티마이저가 MERGE / HASH JOIN 시 사용하는 최적화 기법
• CPU , Memory , Network 등 리소스를 줄이는 목적이며 full과 partial 중 하나를 사용한다.

완전 파티션 와이즈 조인 (full)

• 이는 동일한 파티션키로 구분되는 각각의 테이블 파티션별로 슬레이브 프로세스를 두어 각 파티션별로 조인 작업을 수행한다.
• 완전 파티션 와이즈 조인은 모든 파티셔닝 방법을 지원하며 파티션과 서브 파티션의 조인도 지원한다. (조인키 = 파티션키)

부분 파티션 와이즈 조인 (partial)

• 2개의 테이블을 동일 기준으로 파티션할 필요가 없다.
• 한쪽 테이블만 조인 키를 기준으로 하여 파티션되어 있으면 충분하고, 다른 쪽 테이블은 파티션 여부에 관계없이 조인 키를 기준으로 하여 동적으로 파티션된다.
• 실제로 부분 파티션 와이즈 조인을 사용한다고 해서 항상 성능이 개선되는 것은 아니다.
• 대체로 일반 조인이 부분 파티션 와이즈 조인보다 빠를 것이다.

스타 변환

• 스타변환은 스타스키마에서 사용되는 최적화 기법이다.
• 이 유형의 스키마는 커다란 중심 테이블인 팩트(fact)테이블과 여러 개의 다차원 테이블로 구성된다.
• 팩트 테이블이 차원테이블을 참조한다.

스카 스키마 최적화

• 제한 조건을 가지는 각 차원 테이블을 평가한다.
• 결과 차원 키들을 조합하여 목록을 생성한다.
• 이 목록을 사용하여 팩트 테이블에서 일치하는 로우를 추출한다.

오라클에서 이기능을 사용하기 위핸 조건

• star_transformation_enabled 파라미터 (temp_disable 또는 TRUE)
• 각각의 조인 조건에 대해 팩트 테이블에 인덱스가 생성되어야 한다. (최적화 : 외래키 + 비트맵인덱스)

스타 변환의 수행 단계 요약

• 차원 테이블들과 이에 대응하는 팩트 테이블의 비트맵 인덱스를 조인한다. 제한조건이 적용된 차원테이블에만 필요하다.
• 결과 비트맵을 병합 rowid를 반환한다. 이 rowid를 사용하여 팩트 테이블을 액세스한다.
• 차원 테이블과 팩트 테이블에 추출된 데이터를 조인한다. where절 이외의 select , group by , order by 절에서 참조하는 컬럼을 가진 차원 테이블에서만 필요하다.

또한 2가지 최적화를 적용할 수 있다.

• temporary table : 차원 테이블의 이중 처리 방지하기 위해서 결과 로우를 임시 테이블에 저장한다. (star_transformation_enabled = TRUE)
• 비트맵 조인 인덱스 : 차원 테이블과 팩트 테이블 간의 비트맵 인덱스 간의 조인을 피하는 것이다.

• 스타 변환은 비용 기반의 쿼리 변환이다. 쿼리 옵티마이저가 논리적으로 합당한지 판단 및 임시 테이블 / 비트맵 조인 인덱스 생성 등을 고려한다. (star_transformation , no_star_transformation hint)