다른 명령
SQL 조인
Nested Loop 조인
Nested Loop Join 의 개념
- 예시) USE_NL(각각 테이블에 어떤 컬럼에 인덱스를 이용 할것 인가? )
SELECT 고객.* ,주문.* FROM 고객 -- 1) [고객]테이블은 어떤 컬럼에 인덱스가 있으면 좋은가? JOIN 주문 -- 2) [주문]테이블은 어떤 컬럼에 인덱스가 있으면 좋은가? ON 주문.고객번호 = 고객.고객번호 WHERE 고객.고객명='홍길동' AND 주문.주문일자='201909';
- 고객(outer)은 WHERE절의 = 조건(고객명) 인덱스 여부,
- 주문(inner)은 고객번호 컬럼 인덱스 여부가 N/L조인의 성능을 결정.
NL조인 시 인덱스 의 중요성
인덱스 오브젝트 장점
record_voice_over
- 인덱스 오브젝트의 장점
- 단일 칼럼을 조인의 연결고리로 사용할 경우 전체 테이블 row size 가 100바이트이고 인덱스 칼럼의 크기가 10바이트라면, 전체 테이블을 검색하는 것과 인덱스를 검색하는 것은 절대적인 양으로도 10배 차이가 발생
- 인덱스는 정렬되어 있기 때문에 검색 알고리즘을 적용할 수도 있으며, 한번 읽어진 인덱스 블록들은 buffer cache 에 어느 정도는 남겨져 있기 때문에 반복적인 I/O 양은 최소화
인덱스 조건
record_voice_over
- outer 테이블은 WHERE절의 = 조건의 인덱스가 중요
- inner 테이블은 조인조건 컬럼의 인덱스가 중요
- outer 테이블 조회 후 1건씩 순차적으로 inner 테이블에 접근
- INDEX 구성
- EMP.IX_EMP_01 ( DEPTNO)
- DEPT.IX_DEPT_01 ( DEPTNO))
SELECT /*+ USE_NL(B) LEADING(A) */ ENAME,JOB,B.DNAME FROM EMP A JOIN DEPT B ON A.DEPTNO = B.DEPTNO ------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 17 (100)| | 14 |00:00:00.01 | 11 | | 1 | NESTED LOOPS | | 1 | 14 | 672 | 17 (0)| 00:00:01 | 14 |00:00:00.01 | 11 | | 2 | NESTED LOOPS | | 1 | 14 | 672 | 17 (0)| 00:00:01 | 14 |00:00:00.01 | 10 | | 3 | TABLE ACCESS FULL | EMP | 1 | 14 | 364 | 3 (0)| 00:00:01 | 14 |00:00:00.01 | 7 | |* 4 | INDEX RANGE SCAN | IX_DEPT_01 | 14 | 1 | | 0 (0)| | 14 |00:00:00.01 | 3 | | 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 14 | 1 | 22 | 1 (0)| 00:00:01 | 14 |00:00:00.01 | 1 | -------------------------------------------------------------------------------------------------------------------------------------
LEADING 이나 ORDERED 힌트와 같이 사용 추천
- INDEX 구성
- EMP.IX_EMP_01 ( DEPTNO)
- DEPT.IX_DEPT_01 ( DEPTNO))
SELECT /*+ USE_NL(A,B) LEADING(A) */ ENAME,JOB,B.DNAME FROM EMP A JOIN DEPT B ON A.DEPTNO = B.DEPTNO ------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 17 (100)| | 14 |00:00:00.01 | 11 | | 1 | NESTED LOOPS | | 1 | 14 | 672 | 17 (0)| 00:00:01 | 14 |00:00:00.01 | 11 | | 2 | NESTED LOOPS | | 1 | 14 | 672 | 17 (0)| 00:00:01 | 14 |00:00:00.01 | 10 | | 3 | TABLE ACCESS FULL | EMP | 1 | 14 | 364 | 3 (0)| 00:00:01 | 14 |00:00:00.01 | 7 | |* 4 | INDEX RANGE SCAN | IX_DEPT_01 | 14 | 1 | | 0 (0)| | 14 |00:00:00.01 | 3 | | 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 14 | 1 | 22 | 1 (0)| 00:00:01 | 14 |00:00:00.01 | 1 | -------------------------------------------------------------------------------------------------------------------------------------
USE_NL 괄호 안의 테이블은 NL조인 적용 대상 테이블
SELECT /*+ USE_NL(A,B) LEADING(B) */ ENAME,JOB,B.DNAME FROM EMP A JOIN DEPT B ON A.DEPTNO = B.DEPTNO ------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | | 6 (100)| | 14 |00:00:00.01 | 10 | | 1 | NESTED LOOPS | | 1 | 14 | 672 | 6 (0)| 00:00:01 | 14 |00:00:00.01 | 10 | | 2 | NESTED LOOPS | | 1 | 20 | 672 | 6 (0)| 00:00:01 | 14 |00:00:00.01 | 9 | | 3 | TABLE ACCESS FULL | DEPT | 1 | 4 | 88 | 3 (0)| 00:00:01 | 4 |00:00:00.01 | 7 | |* 4 | INDEX RANGE SCAN | IX_EMP_01 | 4 | 5 | | 0 (0)| | 14 |00:00:00.01 | 2 | | 5 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 4 | 104 | 1 (0)| 00:00:01 | 14 |00:00:00.01 | 1 | ------------------------------------------------------------------------------------------------------------------------------------
HASH 조인(HASH JOIN)
해시 함수를 이용하여 조직 테이블(데이터 작은쪽)에 해시테이블을 만들고 실적집계 테이블(데이터 많은쪽)을 탐색하면서 조인.
- Hash Join에서는 작은 테이블을 먼저 접근하는 것이 성능이 좋음(해시테이블을 빨리 build 하기때문).
이후 큰 테이블을 접근하여 해시 함수를 사용해서 순차적으로 해시 테이블로 탐색 한다.
- 대량 데이터를 처리하는 배치성 프로그램에 적합한 조인 방식.
HASH 조인의 특징
- Hash Join 성능을 좌우하는 두 가지 포인트
- 한 쪽 테이블이 가용 메모리에 담길 정도로 충분히 작아야 함
- Build Input 해시 키 칼럼에 중복 값이 거의 없어야 함 (조인컬럼의 값이 중복이 많을 수록 해시키 충돌에 의해 성능은 저하된다.)
- Inner루프로 Hash Area에 생성해둔 해시테이블을 이용한다는 것 외에 NL조인과 유사하다.
- 해시테이블 만들 때(Build Input)는 전체범위처리가 불가피하나, Probe Input을 스캔하는 단계는 부분범위 처리가능하다.
- 해시조인은 해시테이블이 PGA영역에 할당 되므로, NL조인보다 빠르다.
- NL조인은 Outer테이블에서 읽히는 레코드마다 Inner쪽 테이블 버퍼캐시 탐색을 위해 래치획득을 반복하나, 해시조인은 래치 획득과정없이 PGA에서 빠르게 데이터를 탐색할 수 있다.
HASH 조인 장점
- NL조인처럼 조인시 발생하는 Random엑세스 부하가 없다.
- 소트머지조인처럼 조인전에 양쪽 집합을 정렬해야 하는 부담이 없다.
HASH 조인 단점
- 해시테이블을 생성하는 비용이 수반
- Build Input이 작을 때 효과적이다.(PGA에 할당되는 Hash Area에 담길정도로 충분히 작아야 함)
- 해시키 값으로 사용되는 컬럼에 중복값이 거의 없을 경우 효과적이다.(중복값이 많을수록 성능이 저하된다.)
HASH 조인을 고려해야 할때
- 조인 칼럼에 적당한 인덱스가 없어 NL Join이 비효율적일 때
- 조인 칼럼에 인덱스가 있더라도 NL Join 드라이빙 집합에서 Inner 쪽 집합으로의 조인 액세스량이 많아 Random 액세스 부하가 심할 때
- Sort Merge Join 하기에는 두 테이블이 너무 커 소트 부하가 심할 때
- 수행빈도가 낮고 조인할 때
HASH JOIN 예시
SELECT /*+ USE_HASH(B) */ A.조직명,A.조직코드,SUM(B.실적),... FROM 조직 A , 실적집계 B WHERE A.조직코드 = B.조직코드. -- hash 함수를 사용 하여 해시키 생성 후 조인 AND A.사업부 = 'ㅇ사업부서명'. -- index를 사용 하여 성능 개선 AND B.집계년월 = '201908' -- index를 사용 하여 성능 개선 GROUP BY A.조직명,A.조직코드
(설명)
1. 조직 테이블(A)에서 사업부가 ‘ㅇ사업부서명’조회 후, 조인 컬럼인 조직코드를 해시 함수로 분류한 다음, 해시테이블 을 생성. (Build Input , 해시 함수를 이용해 해시 테이블 구성)
2. 실적집계 테이블(B) 에서 집계년월이 ‘201908’를 조회 후, 조인 컬럼인 조직코드를 해시 함수로 변환 후 해시 테이블로 순차 적으로 접근한다. (Probe Input 해시 함수를 통하여 해시 테이블을 탐색)
- 조회 조건 컬럼인 A.사업부 컬럼과 B.집계년월 컬럼의 인덱스를 사용하여 성능을 개선하고 ,
- 조인 컬럼인 A.조직코드 = B.조직코드는 인덱스가 존재하더라도 사용되지 않는다.
소트머지 조인 (Sort Merge Join)
개요
- 양쪽 테이블의 처리범위를 각자 Access하여 Sort(정렬)한 결과를 차례로 Scan하면서 연결고리의 조건으로 Merge하는 방식
- 조인의 대상범위가 넓을 경우 발생하는 Random Access를 줄이기 위한 경우나 연결고리에 마땅한 인덱스가 존재하지 않을 경우 해결하기 위한 조인
Sort Merge Join의 특징
- 연결을 위해 랜덤 액세스를 하지 않고 스캔을 하면서 수행
- Nested Loop Join처럼 선행집합 개념이 없음
- 정렬을 위한 영역(Sort Area Size)에 따라 효율에 큰 차이 발생
- 조인 연산자가 '='이 아닌 경우 nested loop 조인보다 유리한 경우가 많음
Sort Merge 사용 시 주의사항
- 두 결과집합의 크기가 차이가 많이 나는 경우에는 비효율적
- Sorting 메모리에 위치하는 대상은 join key뿐만 아니라 Select list도 포함되므로 불필요한 select 항목 제거
조인시 성능 튜닝 요소
NL조인
- NL조인은 Random 액세스 발생량에 의해 성능 결정
- WHERE 조건이 없는 경우에는 드라이빙테이블(OUTER)은 작은 집합을 드라이빙하는 것이 유리.
- WHERE 조건이 있는 경우에는 WHERE 조건절 컬럼의 인덱스 존재 및 구성여부에 따라 성능이 결정되며, 작은 쪽 집합을 드라이빙하는 것이 유리.
- INNER쪽 테이블의 조인 컬럼에 인덱스 존재 여부가 성능에 결정적인 요소.
- NL조인에서는 Random 액세스 발생량 외에도 Inner 쪽 인덱스 구성 및 조건절 연산자 형태에 따라 성능이 크게 좌우되는데, 인덱스 스캔 효율에 차이가 생기기 때문.
NL조인 Prefetch
NL조인 Batching
해시 조인
- Build Input을 작은 쪽 테이블로 드라이빙하는것이 유리 (SWAP_JOIN_INPUTS 힌트로 조절)
- 해시조인은 Hash Area에 Build Input을 모두 채울 수 있느냐가 관건
소트머지 조인
- 소트머지 조인과 해시조인에서도 순서가 중요
- 소트머지 조인은 PGA상에서 정렬된 집합(Sort Area)을 통해 조인 액세스가 일어나기 때문에 Random 액세스 발생량 보다 소트 부하에 의해 성능 결정됨.
- 디스크 소트가 발생할 정도의 큰 테이블을 포함할 때는 큰 테이블을 드라이빙하는 것이 빠르지만 메모리 소트방식으로 조인할 때는 적은 쪽 테이블을 드라이빙하는 것이 조금 더 빠름
select /*+ ordered use_merge(e) */ d.deptno, d.dname, e .empno, e .ename from dept d, emp e where d.deptno = e.deptno