2009/01/11 19:47
1. Big 3 조인의 비교
1) 조인의 정의와 비교
연관된자료는 하나의 테이블에 저장될 수도 있지만
메모리의 한계, 저장공간의 제약, 논리적 분할 > [제3 정규화] > 여러테이블에 나누어 저장 > [조인] > 결합
조인 : 관계형 데이터 베이스에서는 포인터나 링크 같은 물리적인 연결고리를 사용하지 않고 논리적인 연결을 사용하는데 그것이 바로 두 테이블간의 조인
Big 3 조인 빈도 : Nested Loop Join > Hash Join > Sort Merge Join
조인의 필요성
- 관계형 데이터베이스를 잘 활용하기 위해서는 데이터모델링이 중요함
- 요구사항분석, 개념적 데이터모델링(CDM), 논리적 데이터모델링 (LDM), 물리적 데이터 모델링(PDM) 단계에서 가장 중요한 산출물이 ERD(Entity Relationship Diagram)이며, 가장 중요한 기술적 기법이 데이터 정합성 확보와 데이터 저장공간 절약을 위해 모델링 과정에서 진행되는 정규화(Normalization) 과정임
2. Nested Loop Join
1) Nested Loop Join 의 개념
두개 이상의 테이블에서, 하나의 집합을 기준으로 순차적으로 상대방 테이블의 row 를 결합하여 원하는 결과를 추출하는 테이블 연결 방식
결합하기 위해 기준이 되는 테이블 : driving 테이블 또는 outer 테이블
결합되어지는 테이블 : driven 테이블 또는 inner 테이블
NL 조인에서는 드라이빙 테이블의 각 row 에 대하여 loop 방식으로 조인이 되는데 드라이빙 테이블의 집합을 어느정도 줄일 수 있는가에 따라 NL 조인의 성능이 결정
· 인덱스 오브젝트의 필요성
- 드라이빙 테이블이 한 row 씩 반복해 가면서 inner 테이블과 조인이 이루어짐
- inner 테이블의 연결 고리 칼럼에서 조인하고자 하는 값을 빠르게 검색할 수 있는 방법이 있어야함
- inner 테이블의 크기가 적다면 테이블 전체를 메모리에 읽어서 반복적으로 검색하는 것이 빠름
- 하지만 실제 업무 환경에서 발생하는 대용량의 자료에 대해서는 비용과 직결되는 모메로의 유한성 때문에 이런 방식으로 처리할 수 없음
- 조인되는 값들의 cardinality 가 높을 수록, 한 번 스캔되어 조인된 자료가 다음 row 에서 조인에 사용될 확률이 낮아지기 때문에 스캔에 의한 조인 효율은 저하 -> 원하는 값이 존재하는 지 빠르게 확인하기 위한 목적과 그 값에 대한 데이터를 빠르게 읽어 내기 위해서 인덱스 오브젝트는 NL 조인에서 (특히 inner 테이블의 액세스 시) 반드시 필요
· 인덱스 오브젝트의 장점
- 단일 칼럼을 조인의 연결고리로 사용할 경우 전체 테이블 row size 가 100바이트이고 인덱스 칼럼의 크기가 10바이트라면, 전체 테이블을 검색하는 것과 인덱스를 검색하는 것은 절대적인 양으로도 10배 차이가 발생
- 인덱스는 정렬되어 있기 때문에 검색 알고리즘을 적용할 수도 있으며, 한번 읽어진 인덱스 블록들은 buffer cache 에 어느 정도는 남겨져 있기 때문에 반복적인 I/O 양은 최소화
> 위와 같은 이유로 인해 조인된 결과의 집합이 두 집합의 카테시안 곱에 비해 10~15% 이하라면, 인덱스에 의한조인에서 발생하는 랜덤 액세스의 오버헤드가 전체 테이블을 검색하는 비용보다 적게 되어 조인이 더 효율적임
> 드라이빙 테이블을 적절하게 선택하여 조인의 회수를 줄일 수 있다면 NL 조인의 성능은 훨씬 개선될 것
- NL 조인에서의 튜닝이란 드라이빙 테이블을 선정하는 기준과 드리븐 테이블에 사용될 적절한 인덱스의 선정을 의미
2) Nested Loop Join 의 절차
두테이블 table_a와 table_b에서 joinkey_a, color, joinkey_b, size 칼럼에 인덱스가 만들어져 있다는 가정하에 두 테이블을 조인하기 위한 다음 질의문이 주어진 경우 실행 절차
4) Hash 조인의 장단점
5) Hash 조인의 파라미터
1) 조인의 정의와 비교
연관된자료는 하나의 테이블에 저장될 수도 있지만
메모리의 한계, 저장공간의 제약, 논리적 분할 > [제3 정규화] > 여러테이블에 나누어 저장 > [조인] > 결합
조인 : 관계형 데이터 베이스에서는 포인터나 링크 같은 물리적인 연결고리를 사용하지 않고 논리적인 연결을 사용하는데 그것이 바로 두 테이블간의 조인
Big 3 조인 빈도 : Nested Loop Join > Hash Join > Sort Merge Join
조인의 필요성
- 관계형 데이터베이스를 잘 활용하기 위해서는 데이터모델링이 중요함
- 요구사항분석, 개념적 데이터모델링(CDM), 논리적 데이터모델링 (LDM), 물리적 데이터 모델링(PDM) 단계에서 가장 중요한 산출물이 ERD(Entity Relationship Diagram)이며, 가장 중요한 기술적 기법이 데이터 정합성 확보와 데이터 저장공간 절약을 위해 모델링 과정에서 진행되는 정규화(Normalization) 과정임
2. Nested Loop Join
1) Nested Loop Join 의 개념
두개 이상의 테이블에서, 하나의 집합을 기준으로 순차적으로 상대방 테이블의 row 를 결합하여 원하는 결과를 추출하는 테이블 연결 방식
결합하기 위해 기준이 되는 테이블 : driving 테이블 또는 outer 테이블
결합되어지는 테이블 : driven 테이블 또는 inner 테이블
NL 조인에서는 드라이빙 테이블의 각 row 에 대하여 loop 방식으로 조인이 되는데 드라이빙 테이블의 집합을 어느정도 줄일 수 있는가에 따라 NL 조인의 성능이 결정
· 인덱스 오브젝트의 필요성
- 드라이빙 테이블이 한 row 씩 반복해 가면서 inner 테이블과 조인이 이루어짐
- inner 테이블의 연결 고리 칼럼에서 조인하고자 하는 값을 빠르게 검색할 수 있는 방법이 있어야함
- inner 테이블의 크기가 적다면 테이블 전체를 메모리에 읽어서 반복적으로 검색하는 것이 빠름
- 하지만 실제 업무 환경에서 발생하는 대용량의 자료에 대해서는 비용과 직결되는 모메로의 유한성 때문에 이런 방식으로 처리할 수 없음
- 조인되는 값들의 cardinality 가 높을 수록, 한 번 스캔되어 조인된 자료가 다음 row 에서 조인에 사용될 확률이 낮아지기 때문에 스캔에 의한 조인 효율은 저하 -> 원하는 값이 존재하는 지 빠르게 확인하기 위한 목적과 그 값에 대한 데이터를 빠르게 읽어 내기 위해서 인덱스 오브젝트는 NL 조인에서 (특히 inner 테이블의 액세스 시) 반드시 필요
· 인덱스 오브젝트의 장점
- 단일 칼럼을 조인의 연결고리로 사용할 경우 전체 테이블 row size 가 100바이트이고 인덱스 칼럼의 크기가 10바이트라면, 전체 테이블을 검색하는 것과 인덱스를 검색하는 것은 절대적인 양으로도 10배 차이가 발생
- 인덱스는 정렬되어 있기 때문에 검색 알고리즘을 적용할 수도 있으며, 한번 읽어진 인덱스 블록들은 buffer cache 에 어느 정도는 남겨져 있기 때문에 반복적인 I/O 양은 최소화
> 위와 같은 이유로 인해 조인된 결과의 집합이 두 집합의 카테시안 곱에 비해 10~15% 이하라면, 인덱스에 의한조인에서 발생하는 랜덤 액세스의 오버헤드가 전체 테이블을 검색하는 비용보다 적게 되어 조인이 더 효율적임
> 드라이빙 테이블을 적절하게 선택하여 조인의 회수를 줄일 수 있다면 NL 조인의 성능은 훨씬 개선될 것
- NL 조인에서의 튜닝이란 드라이빙 테이블을 선정하는 기준과 드리븐 테이블에 사용될 적절한 인덱스의 선정을 의미
2) Nested Loop Join 의 절차
두테이블 table_a와 table_b에서 joinkey_a, color, joinkey_b, size 칼럼에 인덱스가 만들어져 있다는 가정하에 두 테이블을 조인하기 위한 다음 질의문이 주어진 경우 실행 절차
※ 운반단위가 적을 수록 빨리 채워져 빠른 응답을 받을 수 있으나 그 만큼 FETCH 횟수가 늘어나므로 속도에 영향을 미침
운반 단위에 대한 기본값은 SQL*Plus 의 경우 SET ARRAYSIZE 로 지정할 수 있으며 기본값은 15로 설정되어 있음
이 값을 무한정 크게 한다고 해서 질의 문을 수행 속도가 빨라지지는 않음
3) Nested Loop 조인의 단점
- 데이터를 랜덤 액세스에 의하여 접근하기 때문에 그 결과 집합이 많다면 수행 속도가 저하
- 드라이빙 테이블은 테이블의 크기가 작거나, where 절 조건을 사용하여 적절히 결과 집합을 제한 할 수 있어야함
- 연결이 되는 테이블인 driven 테이블에는 조인을 위한 적절한 인덱스가 생성되어 있어야함. 연결고리가 정상적이지 안은 (인덱스가 없는) 상태에서 nested loop 조인이 수행된다면 수행 속도가 현저하게 저하될 수 있음. 이런 경우 sort merge 또는 hash 조인을 수행
- 연결 고리가 된느 칼럼은 사원 번호, 주문번호와 같이 unique 한 속성을 가진 칼럼 인덱스를 이용하거나 분포도가 좋은 칼럼인덱스를 이용할 수록 수행 속도는 향상
3. Hash Join
1) Hash 조인의 개념
Subquery 정의 : 해시 함수를 사용하여 두 집합의 자료를 결합하는 것
> 정렬되어 있지 않은 데이터들에 해시 함수를 적용하여 각 자료를 hash bucket 으로 나누어 담는다면 같은 값을 가진 자료는 같은 hash bucket 에 모이게 되어 정렬을 수행하지 않고도 보다 효율적으로 상대 집합을 검색
2) Hash 조인의 절차
운반 단위에 대한 기본값은 SQL*Plus 의 경우 SET ARRAYSIZE 로 지정할 수 있으며 기본값은 15로 설정되어 있음
이 값을 무한정 크게 한다고 해서 질의 문을 수행 속도가 빨라지지는 않음
3) Nested Loop 조인의 단점
- 데이터를 랜덤 액세스에 의하여 접근하기 때문에 그 결과 집합이 많다면 수행 속도가 저하
- 드라이빙 테이블은 테이블의 크기가 작거나, where 절 조건을 사용하여 적절히 결과 집합을 제한 할 수 있어야함
- 연결이 되는 테이블인 driven 테이블에는 조인을 위한 적절한 인덱스가 생성되어 있어야함. 연결고리가 정상적이지 안은 (인덱스가 없는) 상태에서 nested loop 조인이 수행된다면 수행 속도가 현저하게 저하될 수 있음. 이런 경우 sort merge 또는 hash 조인을 수행
- 연결 고리가 된느 칼럼은 사원 번호, 주문번호와 같이 unique 한 속성을 가진 칼럼 인덱스를 이용하거나 분포도가 좋은 칼럼인덱스를 이용할 수록 수행 속도는 향상
3. Hash Join
1) Hash 조인의 개념
Subquery 정의 : 해시 함수를 사용하여 두 집합의 자료를 결합하는 것
> 정렬되어 있지 않은 데이터들에 해시 함수를 적용하여 각 자료를 hash bucket 으로 나누어 담는다면 같은 값을 가진 자료는 같은 hash bucket 에 모이게 되어 정렬을 수행하지 않고도 보다 효율적으로 상대 집합을 검색
2) Hash 조인의 절차
3) Hash 조인의 내부적인 분류
4) Hash 조인의 장단점
5) Hash 조인의 파라미터
4. Sort Merge Join
1) Sort Merge 조인의 절차
1) Sort Merge 조인의 절차
2) Sort Merge 조인의 특징
※ 해시 조인이 활성화된 9버전 이후부터는 all_rows 옵티마이저 모드나 대량 배치 작업을 제외하고는 사용빈도가 많이 줄었지만 전체 범위 처리가 필요한 경우, non-equi 조인 조건이거나, 조인 테이블간 연결고리가 없는 경우 유용하게 사용할 수 있음.
5. 이것만은 꼭!
※ 해시 조인이 활성화된 9버전 이후부터는 all_rows 옵티마이저 모드나 대량 배치 작업을 제외하고는 사용빈도가 많이 줄었지만 전체 범위 처리가 필요한 경우, non-equi 조인 조건이거나, 조인 테이블간 연결고리가 없는 경우 유용하게 사용할 수 있음.
5. 이것만은 꼭!
더보기
