대용량 데이터베이스 솔루션 1권 #12 JOIN

JOIN 수행속도 결정 요소

1. Driving

 3개의 Table의 Data수가 각각 2, 1000, 10000 개일때 뭐부터 Driving 되어야 할까 ? 

 가장 Data수가 적은 쪽부터 Driving 되어야 한다.

 INNER JOIN의 경우 Driving 되는 Table의 Data양보다 더 많이 JOIN이 일어날수가 없다.

 왼쪽은 최소 10,000회 이상의 JOIN이 발생하겠지만, 오른쪽은 최대 6번 이하의 JOIN이 발생한다.

 물론 두 가지 경우 다 답은 같다.

2. JOIN 순서

 TABLE1 을 고정한 후 TABLE 2,3 순서와 3,2 순서로 JOIN을 한다면 어떻게 될까 ?

 물론 두 가지 경우 다 답은 똑같겠지만, 첫번째 JOIN에서 성공한 Data의 수는 다음 JOIN에서의 일량에 영향을 미친다.

SELECT CHULNO, CHULDATEM, CUSTNAME
  FROM CUSTOMER Y, CHULGOT X
 WHERE X.CUSTNO = Y.CUSTNO
   AND X.CHULDATE = '941003'
   AND Y.NATION = 'KOR';

위의 SQL문의 실행 계획은 다음과 같다.

NESTED LOOPS
  TABLE ACCESS BY ROWID CUSTOMER
    INDEX RANGE SCAN CU_NATION
  TABLE ACCESS BY ROWID CHULGOT
    AND-EQUAL
      INDEX RANGE SCAN CH_CHULNO
      INDEX RANGE SCAN CH_CHULDATE

결과는 1줄이고 0.15 sec가 나왔다.

CHULGOT 에서 2개의 INDEX를 MERGE SORT 하여 처리하였다.

이제 TABLE 순서만 바꿔서 실행해 보겠다.

단 /*+ ORDERD */ 라는 HINT를 썼다고 가정한다.

SELECT CHULNO, CHULDATEM, CUSTNAME
  FROM CHULGOT X, CUSTOMER Y
 WHERE X.CUSTNO = Y.CUSTNO
   AND X.CHULDATE = '941003'
   AND Y.NATION = 'KOR';

위의 SQL문의 실행 계획은 다음과 같다.

NESTED LOOPS
  TABLE ACCESS BY ROWID CHULGOT
    INDEX RANGE SCAN CU_CHULDATE
  TABLE ACCESS BY ROWID CUSTOMER
      INDEX UNIQUE SCAN PK_CUSTNO

결과는 1줄이고 0.04 sec가 나왔다.

JOIN에서 PK를 사용할 수 있게끔 순서만 바꿔주었는데도 실행계획에서 차이가 난다.

이런 문제에 익숙해 지기 전까지는 JOIN 순서를 바꿔보면서 실행계획을 비교해보면서 연습하면 좋다.

3. INDEX 영향

 JOIN에 사용된 KEY에 대해서 양쪽 다 INDEX가 있으면 어느쪽을 먼저 Driving 해도 짧은 시간에 수행이 가능하다.

 하지만, 양쪽 중 한쪽만 INDEX가 없는 경우라면 반드시 INDEX가 없는 쪽이 먼저 Driving 되어야 한다.

 만약 반대의 경우라면 실행속도가 많이 느려진다.

 양쪽 모두 INDEX가 없으면 SORT MERGE 방법으로 처리한다.

SELECT CHULNO, CHULDATEM, CUSTNAME
  FROM CUSTOMER Y, CHULGOT X
 WHERE X.CUSTNO = Y.CUSTNO
   AND X.CHULDATE = '941003'
   AND Y.NATION = 'KOR';

위의 SQL문의 실행 계획은 다음과 같다.

NESTED LOOPS
  TABLE ACCESS BY ROWID CUSTOMER
    INDEX RANGE SCAN CU_NATION
  TABLE ACCESS BY ROWID CHULGOT
    AND-EQUAL
      INDEX RANGE SCAN CH_CHULNO
      INDEX RANGE SCAN CH_CHULDATE

결과는 1줄이고 0.15 sec가 나왔다.

이제 X.CUSTNO를 가공하여 INDEX를 사용하지 못하게 해보겠다.

SELECT CHULNO, CHULDATEM, CUSTNAME
  FROM CHULGOT X, CUSTOMER Y
 WHERE RTRIM(X.CUSTNO) = Y.CUSTNO
   AND X.CHULDATE = '941003'
   AND Y.NATION = 'KOR';

위의 SQL문의 실행 계획은 다음과 같다.

NESTED LOOPS
  TABLE ACCESS BY ROWID CHULGOT
    INDEX RANGE SCAN CU_CHULDATE
  TABLE ACCESS BY ROWID CUSTOMER
      INDEX UNIQUE SCAN PK_CUSTNO

결과는 1줄이고 0.04 sec가 나왔다.

X Table이 먼저 Driving 되게 되어 실행계획이 바뀌어서 결과적으로 MERGE SORT 작업이 사라졌다.

양쪽 모두 INDEX를 사용하지 못하게 하면 어떻게 될까 ? 당연히 MERGE-SORT JOIN을 할 것이다.

4. 실행계획 

SELECT a.FLD1, b.FLD2 FROM TAB2 b, TAB1 a
 WHERE a.KEY1 = b.KEY2 AND b.FLD2 like 'A%' AND a.FLD1 = '10';

 위의 SQL문 실행시 TAB1 은 EQUAL 이고 TAB2 는 LIKE 라서 실행계획은 TAB1을 먼저 Driving 한다. 그런데 TAB1의 Data가 TAB2 보다 더 많아서 결과적으로는 더 많은 Table Access가 발생하여 더 느리게 된다.

 만약 TAB1의 INDEX를 못쓰게 막으면 TAB2가 먼저 Driving되는데 그 Data량 자체가 작기 때문에 더 빠르게 Access가 가능해진다.

5. 처리범위

<pre>SELECT a.FLD1, b.FLD2 FROM TAB2 b, TAB1 a
 WHERE a.KEY1 = b.KEY2 AND b.FLD2 = 'ABC' AND a.FLD1 = '10';
</pre>

 위의 SQL문을 Rull-based Optimizer에서 실행시 둘 다 EQUAL 조건이므로 나중 조건인 FLD1 = '10'부터 처리를 한다.

 Cost-based Optimizer의 경우는 각 INDEX별 Cost를 계산하여 Data량이 적은 것부터 먼저 읽는다.

 개선 방법은 3가지 정도를 예시로 들수 있다.

1. ANALYZE TABLE 명령어를 이용하여 Cost-baed Optimizer를 사용하라.

2. HINT (/*+ ORDERED */)를 활용하여 앞에서부터 읽게끔 한다.

3. SUPPRESSING (a.FLD1 || '' = '10')을 사용하여 원하는 Table부터 읽게끔 한다.

ACCESS PATH의 설정

JOIN 된 Table 들의 ACCESS 경로는 INDEX를 활용하여 결정이 가능하다.

SELECT A1,A2, B1,B2, C1,C2 FROM TAB1 x, TAB2 y, TAB3 z WHERE x.A1 = y.B1 AND z.C1 = y.B2 AND x.A2 = '10' AND y.B2 LIKE 'B%';

위와 같은 Table 들에서 오른쪽 SQL 문이 실행된다고 가정했을 경우 INDEX를 어떻게 설정하느냐에 따라서 ACCESS PATH가 결정된다.

* CASE 1

- TAB1 (A2)  , TAB2 (B1, B2) , TAB3 (C1) 에 INDEX를 생성한 경우

- ACCESS PATH

  1. TAB1 : A2 = '10'

  2. TAB2 : B1 = A1 AND B2 LIKE 'B%'

  3. TAB3 : C1 = B2

* CASE 2

- TAB1 (A1, A2) , TAB2 (B2) , TAB3 (C1) 에 INDEX를 생성한 경우

- ACCESS PATH

  1. TAB2 : B2 LIKE 'B%'

  2. TAB3 : C1 = B2

  3. TAB1 : A1 = B2 AND A2 = '10'

* CASE 3

- TAB1 (A1, A2) , TAB2 (B2) 에 INDEX를 생성한 경우

- ACCESS PATH

  1. TAB3 : Full table scan

  2. TAB2 : B2 = C1 AND B2 LIKE 'B%'

  3. TAB1 : A1 = B1 AND A2 = '10'

위와 같이 일단 3가지의 예를 들어보았다. 어느 경우가 가장 유리한지는 각 Table의 특성 및 Data 개수에 따라서 달라진다.

ACCESS PATH 결정을 생각할 때는 아래 사항들을 고려하면 된다.

1. 상수값을 받을 수 있어야 ACCESS 자격이 획득된다.

2. 어떤ACCESS PATH가 가장 유리한가 ?

3. 그럼 어떤 INDEX 구조가 되어 있어야 하나 ?

JOIN 과 LOOP-QUERY 의 차이

JOIN 은 SQL문 한번으로 실행을 시키는 방법이지만,

LOOP-QUERY는 PL/SQL 등을 활용하여 TAB1 에 SQL문을 실행시켜서, 그 결과를 가지고 다시 여러개의 SQL문을 생성하여 TAB2를 ACCESS 하여 결과를 가져오는 방법이다. 즉, SQL문을 여러번 불러서 실행시킨다.

1. TAB1에 의한 전체범위 처리 일 경우

모든 DATA를 전체범위 처리할때는 무조건 JOIN이 더 빠르다. SQL문 하나로 실행되므로 DB CALL 도 최소이며, OPTIMIZER가 최종 작업을 판단하여 EXCUTE PLAN을 수립할 수 있기 때문이다. LOOP-QUERY의 경우는 여러개의 SQL문이므로 DB CALL도 여러개이며, 각각의 작은 단위별로 별도의 EXCUTION PLAN을 수립하므로 OPTIMIZER는 전체 일에 대해서는 알 수 없으며 그냥 시키는 대로 단위 일을 하는 잡부 역할만을 수행한다.

하지만, 부분범위 처리에 있어서는 LOOP-QUERY가 더 유리 할수도 있다. 특히 TAB1 만의 1차 가공으로 그 범위를 많이 줄일 수 있으면 있을수록 LOOP-QUERY 가 더 효율적일 수가 있다.

2.TAB1에 의한 GROUP BY

TAB1의 특정항목에 대해서 GROUP BY를 해야 한다면 JOIN이 무조건 불리하며 LOOP-QUERY가 항상 유리하다. JOIN의 경우 JOIN을 모두 다 한 후에 SORT를 한 뒤 GROUP BY를 하지만, LOOP-QUERY의 경우 TAB1 만을 가지고 SORT한 뒤 GROUP BY 처리를 하여 그 TAB2 와의 처리량을 많이 줄일 수가 있다. 한쪽 범위를 먼저 줄일 수가 있다면 절대 먼저 JOIN 하지 마라. LOOP-QUERY를 쓰지 않으려면 최소한 INLINE VIEW 형식의 SUBQUERY 형식이라도 활용하라.

Nested Loop JOIN / Sort Merge JOIN 비교

1. Nested Loop JOIN

  어느 한쪽을 먼저 Driving 하여 그 결과로 다른 쪽과 JOIN

- 순차적 (부분범위처리 가능)

- 종속적 (먼저 처리되는 테이블의 처리범위에 따라 처리량 결정)

- Single-Block Random I/o 위주라 대량 Data JOIN시 엄청 느려짐

- 연결고리 상태에 따라 영향이 큼

- 주로 좁은 범위 처리에 유리 ( OLTP )

2. Sort Merge JOIN /*+ use_merge(TABLE1, TABLE2) */

  양쪽을 읽어서 Sort를 한 다음에 순서대로 비교하면서 JOIN

  주로 GROUP BY, ORDER BY 를 사용하면 Optimizer가 Sort Merge JOIN을 많이 사용함

- 동시적 (무조건 전체범위처리)

- 독립적 (자기의 처리범위만으로 처리량 결정)

- 스캔(Scan) 액세스 위주

- 연결고리 상태에 영향이 없음

- 주로 넓은 범위 처리에 유리 ( BATCH 작업, 통계처리 )

JOIN 방법의 결정

1. Driving Table을 뭐로 할지 결정

2. 부분범위처리가 가능한가 ?

    2.1 불가능

        3.1 Driving 조건을 맏아서 Driving Table의 범위를 줄일수 있나 ?

             3.1.1 줄일수 있다면 Nested Loop JOIN

             3.1.2 줄여봐야 별로 유리해지는게 없다면 Sort Merge JOIN

    2.2 가능

        3.2 Driving 조건이 넓은가 ? (10만건이상). 만약 좁다면 Nested Loop JOIN

        4. Check 조건의 범위가 좁은가 ? 넓다면 그냥 Nested Loop JOIN

        5. Driving 과 Check 조건을 바꿀수 있나 ?

           5.1 가능하다면 Driving Table을 바꾸어서 Nested Loop JOIN

           5.2 불가능하다면 그냥 Sort Merge JOIN

​

STAR JOIN​

- DW 환경에서 많이 쓰인다.
- 주로 Program을 이용한 방법이 아닌 SQL문으로 처리를 한다.

- 사용법은 아래와 같다.

  1. 여러 개의 작은 Table 들을 Cartesian Product로 JOIN 한다. (묻지마 JOIN)

  2. 그 결과 만들어진 Table 과 대용량 Data Table (FACT) 와 JOIN 한다.

  3. 결과 Data를 원하는 용도로 Group By 등을 활용하여 사용한다. (Random Access를 현저하게 감소시킬 수 있다.)

- /*+ STAR */ Hint로 사용한다.

 => 하지만 잘 안먹힌다. 그냥 Hint에 의존하지 않고 직접 위의 방법대로 처리하는게 차라리 속편하다.

     Index 를 활용하기 좋게 잘 생성한 다음에 가능하 inline view (ordered), use_nl 힌트로 제어를 하는 방법을 활용한다.

- 단점으로는 새로운 Table을 추가하는 작업이 어렵다.

  => 그 단점의 해결 방법으로는 아래와 같은 SNOWFLAKE Model로 만드는 방법이 있긴한데...

      결과적으로는 절대 그렇게 하지마라. 배보다 배꼽이 크다. FACT Table의 크기가 엄청나게 커져야 한다.

       

HASH JOIN

- 연산에 의한 JOIN이다. CPU 성능만 좋다면 어마어마한 속도를 보장받을 수 있다.

  (Oracle은 비교적 안정적으로 사용가능하지만, 다른 DBMS에서는 솔직히.... 안정적이지 못하다.)

  다른 JOIN 방식의 단점들을 보완한 JOIN 방법이다.

  ( Nested loop JOIN은 Random-Single Block I/O가 너무 많고, Sort Merge JOIN은 Sort Table Segment가 많이 필요하다.)

- Data가 적은 Table을 먼저 Driving 해야 한다.

  작은 Table JOIN 큰 Table 에서 이용시 최적이다.

  ( Sort Merge JOIN 보다 20배이상 더 빠르다.)

- 수행되는 순서

1. 먼저 Driving 되는 Table에서 Hash 함수를 이용하여 Memory에 저장한다.

2. 다른 Table의 Data에 Hash 함수를 적용한다.

3. 그 결과 Driving의 Hash 결과와 일치하는 것이 있으면 JOIN

대용량 데이터베이스솔루션 1

작가

이화식

출판

엔코아컨설팅

발매

1996.03.01

평점

리뷰보기