Join SQL Server 2005의 Bulk Insert 성능 비교http://blog.naver.com/jblimn?Redirect=Log&logNo=110045358481 조인 빈도 : Nested Loop Join > Hash Join > Sort Merge Join 현상세계의 있는 데이터는 모두 비정형데이터입니다.
그러다보니 중복된 데이터도 생기고, 데이터들간의 구분 및 관계가 모호하게 되는 것을 피하기 위해서 RDBMS 에서는 논리적모델단계에서 각 데이터들을 자기 위치에 맞게 “해쳐모여” 시키게 되죠. 이걸보고 정규화(Normalization)라구 부르고, 위와 같은 단계를 정규화한다(Normalize)고 부릅니다.
근데 문제는 이러한 데이터들은 다시 실무나 일상생활에 사용하기 위해서 다시 비정형화된 데이터 형태로 만들어줘야하는 건데요. 이때 이걸 가능케 하는 SQL 의 기능이 바로 Join 입니다. Join 을 통해서 정규화된 데이터들을 이리저리 관계있는 것끼리 묶어서 사용자가 원하는 비정규화(Denormalized)된 데이터를 만들어 주는겁니다.
즉 Join 은 RDBMS 를 제대로 사용한다면 절대 피할 수 없는 기능인데요. 실제 Join 이 문법이 어떻게 되느냐는 책들도 많고, 공부할 자료도 널렸으니 차치하고, RDBMS 내서 Join 을 어떻게 처리하는지에 대해서 한번 써볼까합니다.
Join 을 수행하는 방법을 영어로 쓰면 Join method(쩝..ㅋ)라고 하는데, 이러한 Join method 에는 크게 3가지가 있습니다. (DBMS 종류에 따라 Join Method 가 더 존재할 수 있습니다만, 3개정도만 이해하시면 나머지는 어렵지 않게 이해하실 수 있습니다) • Nested Loop Join • Sort Merge Join • Hash Join 각각의 경우를 살펴보면 되겠네요. 생각보다 쉽답니다.
Nested Loop Join 선행적 특징을 작는데 먼저 액세스되는 테이블의 처리범위에 의해 처리량이 결정됨 Driving Table에 의해 범위가 결정되며 Driving Table의 범위가 적을수록 수행속도는 빨라진다 고로 Driving Table을 어던 테이블로 결정하느냐가 중요하다 -. /*+ use_nl (테이블) */ -. 나중에 처리되는 테이블은 앞서 처리된 값을 받아 액세스하게됨, 즉 값을 받아서 처리범위가 정해짐 -. Driving Table의 인덱스 액세스는 첫번 로우만 Random Access이고, 나머지는 Scan, 연결작업은 Random Access임 -. 연결되는 방향에 따라 사용되는 인덱스들이 달라질 수 있음 -. 연결고리 인덱스 유무에 따라 액세스 방향 및 수행속도에 많은 차이가 있음 -. 연결작업 수행 후 체크되는 조건으로 부분범위처리를 하는 경우에는 조건의 범위가 넓거나 없다면 오히려 빨라짐 -. 전체가 아닌 부분범위 처리를 하는 경우 유리함 -. 조인되는 테이블중 어느 한쪽의 추출된 결과를 받아야 처리범위를 줄일 수 있는 상태라면 항상 유리함 -. Driving Table의 처리량이 많거나 연결 테이블의 Random Access량이 많을 경우에는 분리함 -. 일반적으로 처리량이 적은 경우로서 Random Access를 많이 하므로, 온라인 어플리에서 유리함 -. Driving Table의 선택이 관건임
우리말로 어떻게 번역되어있지는 잘 모르겠습니다. 무슨 내포질의라고 번역해놨던거 같은데 그냥 영어 그대로 읽으면 됩니다.(믿으세요.. 필드에서 절대 내포질의란 말 들으실 일 없을겁니다) 이러한 Join 방법은 아주 간단합니다. T1 과 T2 테이블에 대해서 Nested Loop Join 을 수행한다면 T1과 T2 중 먼저 읽을 테이블을 고릅니다. 이를 Driving 테이블이라고 하구요. 일량을 결정하는 매우 중요한 Factor입니다. 물론 이건 DBMS 가 알아서 해줍니다.여러분은 DBMS가 고른게 맞는건지 검증만 하면 됩니다.
T1 테이블을 Driving 테이블로 정했다고하면, T1테이블에서 한개의 Row를 꺼냅니다. 그 다음 T2테이블에 접근해서 조건에 맞는 Row를 매칭하여 리턴합니다. 그 다음 T1에서 또 한개의 Row를 꺼내고 또 그걸 가지고 T2로 가고 … 이걸 데이터를 다 리턴할때까지 반복합니다. 간단하죠 ?
이런 Nested Loop Join 은 다음과 같은 특징을 가집니다. • 위에서 언급했던 것처럼 어떤 테이블을 먼저 읽을지가 매우 중요합니다. 그리고 Driving 테이블에서 얼만큼 조금 읽게 하는가가 중요한 Factor입니다. Driving 테이블에서 데이터를 원하는 데이터만 쏙 빼서 읽게하는게 중요합니다. (Index를 효율적으로 사용해야합니다.) • Join 조건에 해당 하는 컬럼에 Index 가 필수입니다. 그래야 Driving 테이블에서 한건 가지고, 그 값을 가지고 다른 테이블을 찾아갈때 해당 테이블을 FULL SCAN 하게 하면 말짱 도로묵입니다. (사실 이런경우 Nested Loop 실행계획이 거의 나오지 않습니다) Index SCAN 하도록 해야합니다. Join 조건에 Index는 필수이고, Index를 사용못하게 하는 함수사용이나 기타 연산자, 데이터 형식의 불일치 이런건 쥐약입니다. • Nested Loop Join 의 가장 강점은 조건에 맞는 데이터를 모두 맞춰보지 않고, 일단 맞는 것 부터 보내주기 때문에 응답속도가 매우 중요한 업무에서 최적의 성능을 발휘합니다. 일단 맞는 놈부터 튀어나오니까 조건에 맞는 데이터가 모두 검색될때까지 기다릴 필요가 없습니다. 대용량데이터베이스 라는 책에서는 이런 성질을 이용한 기법을 부분범위처리라 하여 매우 심도있게 다루고 있습니다. Sort Merge Join 일반적으로 배치작업에서 주로 사용되며, 각 테이블을 Sort한 후 Merge 하는 조인을 말한다 -. /*+ use_merge(테이블) */ -. 동시에 각각의 테이블이 자신의 처리범위를 액세스하여 정렬해둠 -. 각 테이블은 어떠한 상수값도 서로 영향을 주지 않으며, 주어진 상수값에 의해서만 각자 범위를 줄이게됨 -. 전체범위처리를하며 부분범위처리를 할수 없음 -. 자신의 처리범위를 줄이기 위해 인덱스를 사용하는 경우에만 Random Access이고, Merge작업은 Scan방식 -. 선택적으로 연결고리가 되는 컬럼은 인덱스를 사용하지 않음 -. 조인의 방향과는 상관없음 -. Equal 조인에서만 가능 -. 처리량이 많은 경우로 Random Access를 하지 않음으로 전체범위처리에 유리 -. 자신의 처리범위를 인덱스를 통해 어떻게 줄이느냐가 관건 -. 상수값을 받아 줄여진 범위가 30%이상이면 Sort Merge가 유리
-
연결고리리 인덱스가 없거나 대용량 자료 조인일 경우 사용 정렬을 수행하기 위한 공간의 크기만큼 메모리를 할당 받아 사용 메모리가 부족할 경우 정렬 자료가 temporary 테이블 스페이스를 사용하여 정렬 수행 단점» 대기 시간 발생(조인 집합의 크기가 많이 차이 나면 정렬하는 시간 차이 발생) 빠른 응답이 필요한 프로그램에서 지향 Equal 조건 뿐만 아니라 범위 조건에 대해서도 조인 수행
- 이 Join 방법은 T1 테이블 T2 테이블 모두 따로따로 Join Key 값으로 Sort 해버립니다. 그 다음 이를 Merge 하는 방법으로 Join 을 하는데, Sort 해버리고 Sorting 된 상황에서 Join 이 들어가니까 Join 시에 발생하는 Random Access 부하가 매우 적습니다. Locality 를 만족하니까요.
예를 들어 T1 테이블과 T2 테이블에 대해서 Sort Merge Join 을 한다고 하면, T1테이블을 정렬하고, T2테이블을 정렬해서 정렬된 값을 통해서 Join 해서 답을 리턴합니다.
이러한 Sort Merge Join 은 다음과 같은 특성을 가집니다. • Nested Loop Join 에서 꼭 필요했던 Join 조건의 Index가 전혀 필요없습니다. 이 말은 Nested Loop 로 풀려야할놈이 Index가 없어서 Sort Merge 로 풀리는 경우도 있다는 말이겠죠 ? • Nested Loop Join 에서는 Driving 테이블에 대한 Index Scan 이 중요했다면 이 방법은 양테이블 모두 Index 를 통해서 데이터를 줄일 수 있도록 해야합니다. 데이터가 줄수록 Sort 하는 비용이 적어지지고, Sort 대상이 양 테이블 모두이니 당연한 결과입니다. • Nested Loop Join 에서는 결과가 바로 나옵니다만, 이 Join 을 사용하게 되면 모든 답을 DBMS 에서 구한다음 한방에 나옵니다. 부분범위처리를 사용하지 못하는 Join 으로 OLTP 환경에서는 지향되어야 하는 Join 방법이나 OLAP 환경에서는 더 우수한 성능을 나타낼 수 있습니다.
Hash Join Hash Function을 이용해서 메모리와 CPU를 많이 사용해서 일반적으로 배치작업에서 주로 사용됨
해쉬 함수를 사용하여 두 집합의 자료를 결합하는 조인으로 NL 조인의 단점을 극복하고, Sort Merge 조인의 성능을 보다 개선 시키기 위해 탄생
- (1) 상대적으로 데이타가 적은 집합 검색
(2) Hash 함수에 의한 정렬 -> 해쉬 Bucket 구성
(3) 큰 집합을 읽어 Hash 함수 적용 후 hash Bucket 에서 확인
해쉬 버킷이 조인집합에 구성되어 해쉬 함수 결과를 저장해야 하며, 이러한 처리에는 많은 메모리와 CPU 자원이 소모된다.
하드웨어 자원이 넉넉한 상황에서는 다른 조인에 비해 효율적인 수행이 가능 반대로 부족한 상황에서는 다른 조인 방법보다 오히려 느릴 수 있음
장/단점
Equal(=) 조인에서만 가능 대용량 데이터 처리가 요구되는 OLTP, DW에서 사용 집합 사이에 차이가 클 수록 효율적이다.
이걸 이해하시려면 Hash 의 개념을 아셔야 합니다. Hash 는 컴퓨터과학에서 자주사용되는 용어로, x 을 넣었을때 해당 결과인 함수 f(x)가 x가 매칭되어야 하는 주소값리턴하게 하도록 하는기법. 대충 이렇게 이해하시면 됩니다.
즉, 이 Join 방법을 사용하면 더 적은 테이블에 대해서 Hash Table을 만들어놓습니다. 이 Hash 테이블은 값을 Hash function 에 넣어서 구성한 일종의 임시테이블이 되겠죠. 물론 이놈은 메모리에 있도록 해야겠죠 ? 그 다음 T2테이블을 다 읽어서 하나씩 꺼내 T2의 있는 값을 가지고 hash table 을 탐색합니다. 그럼 T1이 존재하는 Row의 위치가 튀어나옵니다. 이를 통해 join 을 하는 방식입니다. 중간에 Hash Table을 만든다는 것때문에 여러모로 Sort Merge Join 과 비슷한 점이 많은 Join 방법입니다만, Sort merge 와는 달리 Join 하는 데이터의 건수 차이가 클수록 효과는 점점 좋아집니다. 더 적은 Hash Table 을 만들어서 사용할 수 있기 때문입니다.
Sort Merge 와 사용법이 거의 비슷한데, 데이터량 차이가 큰 테이블들에 대해서 Join 할때, (예를 들면 T1이 100건, T2가 1억건) 좋다. 이렇게 이해하심됩니다. 아 Hash join 은 hash 특성상 등호 (=) 조건에만 사용됩니다. 부등호 조건이면 당근 Sort Merge 할겁니다.
여기까지 Join Method 에 대해 뭐 대충 적어봤는데, 정확성에 대해 100% 보장 못합니다. 뭐 대충 맞겠죠. 하지만 하나하나 따지고 들면 .. 할말은 없을겝니다.
“내가 직접 보고 듣지 못한건 믿지말라. 같은 동료도 믿지말라.” 가 점점 신조가 되어가면서 다른 분들에게도 알게모르게 강요하는 고약한 품성이 생겼나봅니다. 하지만 틀린점이 있다면 겸허히 고치겠으니 피드백주세요 ㅋ
마지막으로 이걸 왜 알아야하냐면요… PLAN 을 볼때 꼭 필요하답니다. PLAN 에 “내가 무슨 Join 했거덩?” 이라고 알려주니까요. 알아야 면장을 하고, 알아야 튜닝을 하겠죠. ㅋ