조인 원리와 활용

1. Nested Loop 조인

• NL 조인은 Random 액세스 위주의 조인 방식이고 한 레코드씩 순차적으로 진행하기 때문에 대량의 데이터를 조인할 때 비효율적이다. 따라서 NL 조인은 소량의 데이터를 처리하거나 부분범위처리가 가능한 OLTP 환경에 접합한 방식이다.
• NL 조인 메커니즘을 따라 각 단계의 수행 일 량을 분석해서 조인 순서를 변경하거나 Random 액세스 발생량을 줄이기 위해 인덱스 칼럼을 추가를 할 수 있다. 하지만 결과적으로 NL조인이 효과적이지 않다면 해시 조인이나 소트 머지 조인을 검토할 수 있다.

2. 소트 머지 조인

• 소트 머지 조인은 양쪽 집합을 조인 컬럼 기준으로 정렬하고 정렬된 양쪽 집합을 서로 머지한다. Sort Area는 PGA 영역에 할당되는데 PGA는 프로세스만을 위한 독립적인 메모리 공간이기 때문에 데이터를 읽을 때 래치 획득과정이 없어 NL 조인에 비해 빠르다. 단 NL조인에 없던 소트 부하가 생긴다. 소트 머지 조인은 첫 번째 테이블에 소트 연산을 대체할 인덱스가 있고 조인 조건식이 = 이 아닐 때 유용하다.
• 첫 번째 테이블이 정렬이 되어 있다면 첫 번째 테이블은 Sort Join 과정을 생략할 수 있지만 두 번째 테이블이 정렬되어 있어도 Full 스켄 방식으로 데이터를 가져온다. 단 정렬이 되어 있기 때문에 정렬이 되지 않은 것보다 빠르긴 하다. 이러한 특징을 활용하면 부분범위 처리가 가능한데 두 번째 테이블은 항상 정렬을 수행하기 때문에 전체범위처리가 불가피하지만 첫 번째 테이블은 중간에 읽다가 멈출 수 있다. 

3. 해시 조인

• 해시 조인은 둘 중 작은 집합(Build Input)을 읽어 Hash Area에 해시 테이블을 생성하고 반대쪽 큰 집합(Probe Input)을 읽어 해시 테이블을 탐색하면서 조인하는 방식이다. 해시 조인은 NL 조인과 다르게 Random 액세스 부하가 없고 소트 머지와 다르게 양쪽 집합을 정렬하는 부담도 없다. 단지 해시 테이블을 생성하는 cpu와 메모리 비용이 들어간다. 따라서 Build Input이 작아야 효과적이다. 만약 Build Input이 크면 Hash Area 크기를 초과해 디스크에 썼다가 읽는 과정에서 성능이 저하된다. 또한 해시 키 값으로 사용되는 칼럼에 중복 값이 거의 없어야 해시버킷을 찾아가는 비용이 줄어들어 효과적이다.
• 해시 조인도 PGA에서 빠르게 데이터를 탐색하고 해시 테이블을 만드는 단계는 전체범위처리가 불가피하지만 Probe Input을 스캔하는 단계는 부분범위처리가 가능하다.
• 해시 조인은 조인 컬럼에 적당한 인덱스가 없어 NL 조인이 비효율적이거나 있더라도 드라이빙 테이블에서 Inner 쪽 집합으로의 조인 액세스량이 많아 Random 액세스 부하가 심할 때, 소트 머지 조인하기에는 소트 부하가 심할 때, 수행빈도가 낮고 쿼리 수행 시간이 오래 걸리는 대용량 테이블을 조인할 때 효과적이다.

4. 조인 순서의 중요성

• NL 조인, 소트 머지 조인, 해시 조인 모두 드라이빙 테이블(첫 번째 테이블)이 작을 때 효과적이다. 단 소트 머지 조인에서 첫 번째 테이블이 정렬이 되어 있다면 Sort Join 과정을 생략할 수 있기 때문에 큰 테이블을 드라이빙할 때 더 빠를 수도 있다.

5. Outer 조인

• NL 조인과 소트 머지 조인은 Outer 조인할 때 방향이 한쪽으로 고정되며, Outer 기호(+)가 붙지 않은 테이블이 항상 드라이빙 테이블로 선택된다. leading 힌트를 사용해도 변경은 불가능하다. 따라서 불필요한 Outer 조인일 경우 최대한 사용하지 않는 편이 성능상 좋다.
• 해시 조인도 9i까진느 방향이 고정됐었지만 10g에서 Right Outer 해시 조인이 도입되었다. 
• Full Outer 조인은 ANSI Full Outer 조인을 사용하면 된다. 하지만 실행계획을 보면 내부적으로 기존 방식인 Left Outer 조인 + Union all + Anti 조인을 사용하고 있어 두 테이블을 각각 두 번씩 액세스 하는 비효율을 가지고 있다. 오라클 11g부터는 /*+ opt_param('_optimizer_native_full_outer_join', force') */ 힌트를 사용해서 비효율을 없앨 수 있다.

6. 스칼라 서브쿼리를 이용한 조인

• 스칼라 서브쿼리를 수행횟수를 최소화하기 위해서 오라클은 입력 값과 출력 값을 내부 캐시에 저장해 두고 사용한다. 스칼라 서브쿼리의 캐싱 효과는 입력 값의 종류가 소수여서 해시 충돌 가능성이 적어야 효과적이지만 값의 종류가 많은 경우 오히려 성능이 저하된다. 또한 스칼라 서브쿼리를 사용하면 NL 조인에서 Inner 쪽 인덱스와 테이블에 나타나는 버퍼 Pinning 효과도 사라진다.
• 스칼라 서브쿼리로 두 개 이상 값을 리턴하고 싶을 때는 인라인뷰의 결과로 여러 칼럼을 cancat 하여 합친 것을 substr로 나눠서 사용하면 된다.

7. 조인을 내포한 DML 튜닝

• 참조 테이블과 두 번 조인하여 업데이트 하는 경우 비효율이 있기 때문에 수정 가능 조인 뷰를 사용하거나 Merge 문을 활용하자.
• 여러 테이블에 Insert 하는 다중 테이블 Insert문도 활용하면 야간 배치 프로그램의 효율을 올릴 수 있다.

8. 고급 조인 테크닉

• 월별 누적매출은 분석함수를 사용하면 된다.
• 데이터 복제 기법은 dual 테이블을 활용해서 connect by level = 2로 복사할 수 있다.
• 상호배타적 관계의 조인은 Outer 조인이나 Union all을 이용할 수 있다.
• 반정규화는 성능을 위한 최후의 수단으로 생각해야한다.
• 업무적으로 이미 통용되는 식별자이거나 유연성/확장성을 고려해 인조 식별자를 설계하는 경우를 제외하면 논리적인 데이터 모델링 단계에서는 가급적 인조 식별자를 두지 않는 것이 좋다.
• rowid를 잘 사용하면 성능상 정말 좋다.

 

- 참고 서적 : 오라클 성능 고도화 원리와 해법 2 -