1 개요

2 예제

3 In-Line View 정리

옵티마이저는 실행계획을 세우기 위해 통계정보를 필요로 한다. MSSQL Server의 통계정보는 자동으로 Update되는 것이 기본값이다. 비용기반의 옵티마이저는 적절한 실행계획을 세우기 위해 통계정보를 통하여 예상되는 비용을 측정하여 가장 낮은 비용의 실행계획을 택하여 실제로 SQL문을 수행한다.

대부분의 외부키는 인덱스가 생성되어야 옵티마이저는 좋은 실행계획을 세운다. 물론 데이터의 양(테이블의 크기 0.5 ~ 1MB 정도)이 얼마 되지 않는 것은 그냥 풀 스캔해도 무방할 것이다. 중요한 것은 데이터의 양은 행의 수가 아니라는 것이다. 반드시 Byte단위로 측정되어야 한다. 데이터 타입이 char(1)이고, 컬럼이 1000개인 테이블에서 1건을 읽어오는 것이나 데이터 타입이 char(1)인 하나의 컬럼에서 1000건을 읽어오는 것은 똑 같은 데이터 양이다. 데이터 건수에 집착하는 것은 안 좋다.

데이터를 연결할 때는 먼저 결과집합을 확인 후 인덱스 정보를 파악한 후 어떻게 하면 가장 적은 양의 데이터를 읽을까를 생각하여야 한다. 우리는 앞서서 Stream Aggregate 연산자를 보았다. 옵티마이저가 Distinct를 보장하기 위해서 사용된 연산자이다. 필자는 이러한 옵티마이저의 행동을 본받아 Distinct를 보장하기 위해 중간 결과 집합을 생성하여 조인의 횟수를 줄여 볼 것이다. 다음 예를 통해 인덱스를 어떻게 활용해야 하는가를 확인해 보자.

인덱스 정보

MMINOUTHEADER : A
-. FormID, InoutDate(-)
-. MoreOriginNo
-. OriginNo
-. WkItem
-. WkNo
-. SiteCode, InoutNo, InoutGubun
 
MMINOUTITEM : B
-. InoutDate(-), WhCode
-. InoutNo
-. IrNo, IrSerNo
-. ItemCode
-. ItemDaySerNo
-. SiteCode, InoutNo, InoutGubun, InoutSerNo

SELECT  A.WKLINE,SUM(B.INOUTQTY) AS QTY
FROM  MMINOUTHEADER A INNER JOIN MMINOUTITEM B
ON A.INOUTNO = B.INOUTNO
WHERE A.SITECODE = B.SITECODE
AND       A.INOUTDATE = '20040601'
GROUP BY A.WKLINE 

--(8개 행 적용됨)
--'mmInoutItem' 테이블. 스캔 수 1, 논리적 읽기 수 33950, 물리적 읽기 수 63, 미리 읽기 수 8100.
--'mmInoutHeader' 테이블. 스캔 수 1, 논리적 읽기 수 24783, 물리적 읽기 수 0, 미리 읽기 수 14.

해시조인으로 풀렸다. 겨우 8건을 가져오기 위해 엄청나게 데이터를 읽어온 것이 보인다. B테이블에는 적절한 인덱스(InoutDate(-), WhCode)가 있으나 A테이블에는 적절한 인덱스가 있지 못하다. 결합인덱스에 대한 것은 이미 자세히 이야기하였으니 FormID, InoutDate(-) 인덱스를 사용했음에도 불구하고 좋은 실행계획이 왜 나오지 않았는지를 알 것이다. 그러므로 A테이블은 모두 읽어야 한다. 그나마 다행인 것은 SiteCode + InoutNo로 시작하는 인덱스가 두 테이블에 모두 있다고 이를 통해 연결을 할 수 있다는 것이다. 또한 결과집합은 WKLINE별 집계이다. WKLINE을 살펴보니 8종류이다. 그러므로 조인의 횟수를 줄이기 위해서 GROUP BY를 이용하여 중간결과집합을 만들었다. 그러므로 8개에 대해서만 JOIN을 하므로 자연스럽게 NESTED LOOP JOIN으로 풀렸다.

SELECT A.WKLINE, B.INOUTQTY
FROM (SELECT  MIN(WKLINE) WKLINE, MIN(INOUTNO) INOUTNO
         FROM MMINOUTHEADER
         WHERE INOUTDATE = '20040601' AND SITECODE = 'N100'
         GROUP BY WKLINE) A
INNER JOIN (SELECT INOUTNO, SUM(INOUTQTY) INOUTQTY
                 FROM MMINOUTITEM 
                 WHERE SITECODE = 'N100'
                 GROUP BY INOUTNO) B
ON A.INOUTNO = B.INOUTNO

--'mmInoutItem' 테이블. 스캔 수 8, 논리적 읽기 수 33, 물리적 읽기 수 0, 미리 읽기 수 0.
--'mmInoutHeader' 테이블. 스캔 수 1, 논리적 읽기 수 24783, 물리적 읽기 수 0, 미리 읽기 수 0.

이것은 차선책이다. 만약 다른 SQL에 영향을 끼치지 않는다면 FormID + InoutDate(-) 로 구성된 인덱스의 InoutDate(-) + FormID 로 순서를 조정하면 간단히 끝난다. 다음은 수정전의 쿼리를 실행한 것이다.

'mmInoutItem' 테이블. 스캔 수 5115, 논리적 읽기 수 22536, 물리적 읽기 수 0, 미리 읽기 수 0.
'mmInoutHeader' 테이블. 스캔 수 1, 논리적 읽기 수 21502, 물리적 읽기 수 0, 미리 읽기 수 0.

중요한 것은 별로 입/출력이 향상되지 않은 것이다. 이것은 클러스터링 팩터가 안 좋기 때문이다. 조건 INOUTDATE = '20040601'에 해당하는 데이터 행이 여러 페이지에 분포되어 있기 때문이다. 또한 날짜 조건에 따라서 성능이 좌우되므로 INOUTDATE의 선택도는 반드시 확인해 보아야 한다. 최선책은 인덱스 순서를 조정하고, 조인의 횟수를 줄이기 위해 중간결과집합을 만드는 것이다.

'mmInoutItem' 테이블. 스캔 수 8, 논리적 읽기 수 33, 물리적 읽기 수 0, 미리 읽기 수 0.
'mmInoutHeader' 테이블. 스캔 수 1, 논리적 읽기 수 21502, 물리적 읽기 수 0, 미리 읽기 수 0.

위의 예제에서 처음보다는 많이 I/O가 줄었음이 확인되었다. 또한 일부는 전체 범위로 처리하고, 전체적으로는 부분 범위 처리를 하였다. 그러나 이것도 최선책은 아니다. 가능하다면 InOutDate에 클러스터드 인덱스를 설치하는 것이 좋다. 또한 고객측에게 물어본 결과 최근의 데이터를 주로 접근하므로 DESC로 인덱스를 생성하면 금상첨화이다. 역시 이상적인 데이터의 접근은 클러스터링 팩터에 의해 무너져 버리고 말았다.

연결을 횟수를 줄임으로써 엄청난 효과가 있음을 앞에서 보았다. 또한 일부는 전체범위처리를 하되 전체 쿼리는 부분범위처리가 되도록 한 것을 주요해서 보아야 한다. 이러한 것은 중간결과집합이 빠르게 처리될 수 있다는 가정하에 수행속도를 줄일 수 있었다. 만약 중간결과집합을 만드는데 오래 걸린다면 당연히 전체적인 성능은 떨어진다. 또 한가지 주의해야 하는 것은 InLine View가 나중에 읽혀지는 집합이 되어 버린다면 엄청난 비효율을 가져올 수 있음을 알아야 한다. 실제로 Left Outer Join이 필요하지 않음에도 불구하고 Left Outer Join으로 인해 실행계획이 고정된 경우 엄청난 비효율을 가져온다는 것을 알아야 한다. 이전의 아래 예가 비슷한 경우이다. 물론 억지로 만들기는 했지만 실제로 이러한 경우가 생기는 경우가 많이 있다.

SELECT A.*
FROM Product A INNER JOIN (SELECT ProductID 
                             FROM SalesOrderDetail
                             WHERE SalesOrderID > 10000
                             GROUP BY ProductID) B
ON A.ProductID + '' = B.ProductID
OPTION (FORCE ORDER, LOOP JOIN)

중요한 것은 SELECT한 결과도 하나의 집합이므로 인라인 뷰를 잘 사용하면 집합적으로 생각 할 수 있다는 것이다. 물론 웬만한 경우가 아니라면 인라인 뷰를 사용하지 않는 경우가 좋다. 인라인 뷰는 결과의 가공의 경우나 조인의 횟수를 줄이기 위한 중간결과집합을 만들 경우 사용하면 된다. 또 한가지 생각해 보아야 할 것은 인라인 뷰 밖의 조건과 안쪽의 조건이 잘 사용되는가를 판단해야 할 것이다. MSSQL Server는 오라클등과 같은 DBMS와 다소 차이가 있으므로 해당 DBMS의 옵티마이저에 맞게 SQL문을 작성해야 한다.