컴퓨토피아

안녕하십니까? 유일환(ryu1hwan@hotmail.com)

이번에는 CROSS JOIN에 대해 적어볼까 합니다.

환상의 CROSS JOIN이라고 제목은 거창하지만.. 실제는 안 그럴 수도 있습니다.^^

CROSS JOIN은 두 개의 집합(테이블)을 아무 조건없이 연결시키는 조인을 말합니다.

수학적인 용어(?)로 카테션 곱이라고 하더군요.

CROSS JOIN은 아무런 조건없이 두 개의 테이블을 연결하는 조인입니다.

일반 조인은 조인 조건이 참인것만 연결하지만, CROSS JOIN은 두테이블의 데이터를 무조건 연결합니다.

아무런 조건없이 테이블을 연결하므로 조인된 결과는 두 집합의 건수의 곱만큼 만들어지게 됩니다.

(여기서 집합이란 테이블내에 데이터 중 WHERE등을 통해 걸러진 결과 집합을 뜻합니다.
실제 WHERE등의 조건을 통해 줄어들지 않은 많은 데이터를 CROSS JOIN을 한다면 엄청난 부하를 가져올 수
있습니다.)

만약에 A테이블에 100건, B테이블에 10건이 있을 경우 CROSS JOIN을 수행하게 되면,

결과는 1000건이 만들어 지게 됩니다.

그러므로 CROSS JOIN을 사용할 때는 주의가 필요하죠. 진정 CROSS JOIN이 필요한 곳에만

사용을 해야 겠죠.


1. 개발 초기에 많은 테스트 데이터들을 만들고 싶을 때

CROSS JOIN의 결과는 두 테이블의 곱만큼 발생이 된다고 했습니다.

개발 초기에는 실제 운영시 만큼의 데이터들이 발생되어 있지 않으므로

실제 운영시에 발생될 문제들을 쉽게 발견할 수 없습니다. 그러므로

테스트 환경에서도 운영시만큼 데이터들을 발생시켜서 테스트를 하는 작업이 필요합니다.

다음처럼 CROSS JOIN을 사용해서 많은 테스트 데이터들을 만들어 낼 수 있습니다.
USE Northwind

SELECT COUNT(*)
FROM Orders T1

SELECT * INTO Test_Orders
FROM Orders
WHERE 1 <> 1

--저장 프로시져를 사용해서 Orders테이블의 컬럼명들을 가져온다.
sp_columns orders
--여기서 identity성질을 가진 OrderID를 제외하고 모든 컬럼명을 카피해서 다음 SQL을 만든다.

INSERT INTO Test_Orders
(CustomerID, EmployeeID, OrderDate,
RequiredDate, ShippedDate, ShipVia,
Freight, ShipName, ShipAddress,
ShipCity, ShipRegion, ShipPostalCode,
ShipCountry)
SELECT T1.CustomerID, T1.EmployeeID, T1.OrderDate,
T1.RequiredDate,T1.ShippedDate, T1.ShipVia,
T1.Freight, T1.ShipName, T1.ShipAddress,
T1.ShipCity, T1.ShipRegion, T1.ShipPostalCode,
T1.ShipCountry
FROM Orders T1
CROSS JOIN (SELECT '1' no
UNION ALL
SELECT '2' no
UNION ALL
SELECT '3' no
UNION ALL
SELECT '4' no
UNION ALL
SELECT '5' no) T2


SELECT COUNT(*)
FROM Test_Orders

결과를 확인해 보면, 830건이었던 Orders데이터를 4150건으로 만들어 낸 것을 알 수 있습니다.

이것의 CROSS JOIN의 활용이라 할 수 있죠. 데이터를 뻥튀기 시켜주는 역할을 잘 할 수 있는 놈이죠.

이와 같은 방법을 CASE문과 같이 사용한다면, 좀 더 현실에 가까운 테스트 데이터들도 만들어 낼 수가 있죠.



2. 가로로 펼쳐진 데이터들을 세로로 표현하고자 할 경우

이 방법 역시 CROSS JOIN을 사용하는 멋진 방법이라고 생각하고 있습니다.

현재 만들어진 ERD의 상태가 어떻느냐에 따라 이런 방법이 사용되어 지게 됩니다.

이번 경우는 실제 사례를 들어서 설명하겠습니다.

제가 프로그램을 개발하고 있는 테이블중에 서술형 결과라는 테이블이 있습니다.

환자의 상태를 검사하고 서술형으로 검사 결과를 입력하게 되는 테이블입니다.

테이블 구조는 다음과 같습니다.

CREATE TABLE TBL0005
( OrderKey numeric(18,0) primary key,
Msg1 varchar(500) default '',
Msg2 varchar(500) default '',
Msg3 varchar(500) default '',
Msg4 varchar(500) default '',
Msg5 varchar(500) default '',
Msg6 varchar(500) default '',
Msg7 varchar(500) default '',
Msg8 varchar(500) default '',
Msg9 varchar(500) default '',
Msg10 varchar(500) default ''
)


이 테이블에 다음과 같은 데이터들을 입력해 보도록 하죠.

INSERT INTO TBL0005 (OrderKey, Msg1, Msg2, Msg3)
VALUES (1,
'이 환자는 무슨무슨 이유로 인해',
'어쩌고 저쩌고 입니다.',
'그러므로 우찌우찌 해야 합니다.')

그리고, 서술형 결과가 아닌, 일반 수치나 문자값들이 저장되는 테이블이 있습니다.
CREATE TABLE TBL0203
( OrderKey numeric(18,0) primary key,
TestName char(10),
Result varchar(100)
)

INSERT INTO TBL0203(OrderKey, TestName, Result)
VALUES (1, 'A검사', '10')
INSERT INTO TBL0203(OrderKey, TestName, Result)
VALUES (2, 'B검사', '15')

위와 같은 서술형 결과와 문자형 데이터들을 하나의 쿼리를 통해 보여주고자 합니다.

서술형 결과 각각의 Msg1부터 Msg10까지는 문자형 결과의 Result와 각각 매치가 되는 것이죠.

두 테이블의 결과들을 하나의 결과로 만들어 내기 위해서 두 테이블을 UNION ALL로 연결해야 합니다.

SELECT TestName, Result
FROM TBL0203
UNION ALL
SELECT '서술형결과', Msg1
FROM TBL0005
UNION ALL
SELECT '서술형결과', Msg2
FROM TBL0005
UNION ALL
SELECT '서술형결과', Msg3
FROM TBL0005

이러한 방법이 있을 수 있죠.

하지만, 우리는 CROSS JOIN을 응용해서도 위와 같은 결과를 만들 수 있습니다.

CROSS JOIN을 보기 전에 위 테이블과 보여주려는 결과 형태의 문제점을 생각해 보면

한 오더의 서술형 결과의 데이터들은 가로로 저장되어 있는데 보여주려는 결과는

세로로 되어 있다는 것입니다. 만약에 서술형 결과 자체를 세로로 저장할 수 있도록

테이블이 구현되어 있다면, 이런 많은 양의 UNION ALL이나, CROSS JOIN이 필요 없겠죠.

자.. 먼저, CROSS JOIN을 하기 위해 서술형 결과 테이블의 메세지 갯수인 10만큼의 숫자가

들어가 있는 테이블이 필요합니다.

다음과 같은 TMP_NO 를 만들도록 하죠

CREATE TABLE TMP_NO
( no int primary key
)

INSERT INTO TMP_NO VALUES(1)
INSERT INTO TMP_NO VALUES(2)
INSERT INTO TMP_NO VALUES(3)
INSERT INTO TMP_NO VALUES(4)
INSERT INTO TMP_NO VALUES(5)
INSERT INTO TMP_NO VALUES(6)
INSERT INTO TMP_NO VALUES(7)
INSERT INTO TMP_NO VALUES(8)
INSERT INTO TMP_NO VALUES(9)
INSERT INTO TMP_NO VALUES(10)

위의 TMP_NO테이블과 서술형 결과 테이블을 먼저 CROSS JOIN을 해보도록 하죠.

SELECT *
FROM TBL0005 T1 CROSS JOIN TMP_NO T2

위의 쿼리문을 실행하면, 같은 값들이 10개씩 출력된 결과를 볼 수 있습니다.

그러나 이 결과들이 각각, 다른 no를 가진 것을 알 수 있습니다.

우리는 이 no를 사용해서 각 로우마다 특정 결과만을 보여줄 수 있습니다.

no가 1일때는 msg1만, no가 2일때는, msg2, no가 3일때는 msg3만 보여주는 방식입니다.

당연히 CASE문을 사용해야 겠죠.

SELECT OrderKey,
Result
FROM TBL0203 T1
UNION ALL
SELECT OrderKey,
CASE T2.no WHEN 1 THEN Msg1
WHEN 2 THEN Msg2
WHEN 3 THEN Msg3
WHEN 4 THEN Msg4
WHEN 5 THEN Msg5
WHEN 6 THEN Msg6
WHEN 7 THEN Msg7
WHEN 8 THEN Msg8
WHEN 9 THEN Msg9
WHEN 10 THEN Msg10
END
FROM TBL0005 T1 CROSS JOIN TMP_NO T2

UNION ALL의 위쪽의 쿼리는 수치형 결과에서 값을 가져오는 쿼리이고

아래쪽의 쿼리가 옆으로 펼쳐져 저장된 서술형 결과를 밑으로 떨어뜨려 보여주는 쿼리입니다.

서술형 결과가 어떻게 밑으로 떨여졌는지 확인 하기 위해 다음처럼 단계적으로 접근해보도록 하죠..

먼저 TBL0005와 TMP_NO간에 CROSS JOIN만을 수행해서 결과를 살펴봅시다.

SELECT *
FROM TBL0005 T1 CROSS JOIN TMP_NO T2

위의 쿼리를 실행해 보면 TBL0005의 한 건인 데이터가 10건이 만들어진 것을 알 수 있습니다.

만약에 TBL0005에 데이터가 2건 있었다면 각각 10건씩 총 20건의 데이터가 만들어졌을 겁니다.

10건의 데이터가 모두 동일한 값을 가지고 있지만, TMP_NO의 no컬럼만이 1부터 10까지 숫자를 가진 것을

알 수 있습니다. 만약에 TMP_NO에 1부터 20까지의 숫자가 저장되어 있다면

총 20건의 no만 다른 동일한 데이터가 만들어 졌겠죠.

여기서 TMP_NO를 10건으로 마친것은 TBL0005의 Msg가 1부터 10까지만 저장되어 있기 때문입니다.

이 결과의 특징은 모두 같은 값이고 no만 1부터 10까지 가지고 있다는 것입니다.

그럼, 이 결과 집합에 CASE문을 사용해서 no가 1일때는 Msg1을, no가 2일때는 Msg2를 이런식으로 보여준다면

우리가 원하던 대로 Msg가 1부터 10까지 밑으로 떨어진 것을 알 수 있습니다.

우리가 원하는 결과를 만들기 위해 마지막으로 가공을 한다면, Msg가 빈 값을 제거할 필요가 있겠죠..

저장도 안되어 있는 결과를 괜히 보여줘서 공간을 낭비할 필요는 없으니까요.

이것 역시, 간단합니다.

다음과 같죠.

SELECT OrderKey,
Result
FROM TBL0203 T1
UNION ALL
SELECT T.OrderKey,
T.Result
FROM (
SELECT OrderKey,
CASE T2.no WHEN 1 THEN Msg1
WHEN 2 THEN Msg2
WHEN 3 THEN Msg3
WHEN 4 THEN Msg4
WHEN 5 THEN Msg5
WHEN 6 THEN Msg6
WHEN 7 THEN Msg7
WHEN 8 THEN Msg8
WHEN 9 THEN Msg9
WHEN 10 THEN Msg10
END Result
FROM TBL0005 T1 CROSS JOIN TMP_NO T2
) T
WHERE T.Result <> ''

이와 같이 Msg가 비어 있는 값을 제거하기 위해 CROSS JOIN된 결과를 FROM절의 파생된 테이블로 적어주고

외부에서 Msg가(Result) ''이 아닌 값만 출력하는 것입니다.

이것이 CROSS JOIN을 이용해서 옆으로 펼쳐진 데이터를 밑으로 떨어뜨리는 방법입니다.

이렇듯 우리는, 숫자만 가지고 있는 임시 테이블과, 기교만 있으면 쉽게 이런 결과를 만들어 낼 수 있는 것이죠.



3. 발생되지 않은 데이터 만들어 내기

이번 역시 제가 예전에 참여했던 프로젝트 중에 사용했었던 방법입니다.

나중에 SM하시는 분들이.. 모..이런.. SQL이 있지란 생각에 지워버리지 않았을까 생각도 되지만..

그 당시에는 굉장히 많이 고민해서 만들었던 SQL문입니다.

해당 프로젝트에서는 매출이 발생되면 데이터가 발생됩니다.

간략하게 다음과 같은 테이블에 데이터가 발생된다고 할 수 있죠.

DROP TABLE TBL_SALE

CREATE TABLE TBL_SALE
( SaleDate CHAR(8), --판매일자
ProductID INT, --제품ID
TradeType CHAR(2), --거래형태
Quantity INT, --수량
CONSTRAINT pk_TBL_SALE PRIMARY KEY(SaleDate, ProductID, TradeType)
)

딱 보시면, 이 테이블에 어떤 데이터들이 들어올지 쉽게 알 수 있을 겁니다.

단지, 약간 생소한 것이라면, 거래형태라는 것이 있습니다.

어떤 거래 형태로 데이터가 발생되었는지를 저장하는 컬럼이죠.

거래형태에는 현금, 카드, 외상, 등등이 있습니다.

지금 테스트를 위해서는 현금(01), 카드(02), 외상(03), 기타(04), 이 4가지가 있다고 생각을 해보도록 하죠.

위의 테이블에 테스트를 위해 임시 데이털르 입력해 보도록 합시다.

INSERT INTO TBL_SALE (SaleDate, ProductID, TradeType, Quantity)
VALUES ('20040301', 1, '01', 3)
INSERT INTO TBL_SALE (SaleDate, ProductID, TradeType, Quantity)
VALUES ('20040301', 1, '03', 4)
INSERT INTO TBL_SALE (SaleDate, ProductID, TradeType, Quantity)
VALUES ('20040302', 1, '01', 2)
INSERT INTO TBL_SALE (SaleDate, ProductID, TradeType, Quantity)
VALUES ('20040302', 1, '03', 4)
INSERT INTO TBL_SALE (SaleDate, ProductID, TradeType, Quantity)
VALUES ('20040302', 1, '02', 1)
INSERT INTO TBL_SALE (SaleDate, ProductID, TradeType, Quantity)
VALUES ('20040302', 1, '04', 2)
INSERT INTO TBL_SALE (SaleDate, ProductID, TradeType, Quantity)
VALUES ('20040303', 1, '04', 5)

위의 데이터를 조회해보면 다음과 같죠.
SELECT *
FROM TBL_SALE

SaleDate ProductID   TradeType Quantity   
-------- ----------- --------- -----------
20040301 1           01        3
20040301 1           03        4

20040302 1           01        2
20040302 1           02        1
20040302 1           03        4
20040302 1           04        2

20040303 1           04        2

데이터를 살펴보면 3월 1일에 1번 제품이 거래형태 1번과 3번으로 발생되었습니다.

3월 2일에는 1번부터 4번까지 모든 거래형태의 데이터가 발생되었고요, 3월 3일에는 4번 거래형태만 발생되었습니다.

사용자가 위 데이터를 조회하는데 발생되지 않은 거래형태의 제품에 대해서는 0의 수량을 갖도록 보기를 원합니다.

이런 결과를 원하는 것이죠.
SaleDate ProductID   TradeType Quantity   
-------- ----------- --------- -----------
20040301 1           01        3
20040301 1           02      0 --> 실제 존재하지 않는 데이터
20040301 1           03        4
20040301 1           02      0 --> 실제 존재하지 않는 데이터

20040302 1           01        2
20040302 1           02        1
20040302 1           03        4
20040302 1           04        2

20040303 1           01      0 --> 실제 존재하지 않는 데이터
20040303 1           02        0 --> 실제 존재하지 않는 데이터
20040303 1          03      0 --> 실제 존재하지 않는 데이터
20040303 1           04      2

이런 데이터를 표현하기 위해서 클라이언트에서 로직으로 처리하는 순간, 귀찮은 코딩들이 발생되어질 것입니다.

예외사항 잡아내고, 건수 계산하고, 모.. 이런 식의 코딩들이 들어가게 되죠.

하지만, CROSS JOIN을 사용해서 저런 데이터를 미리 만들어 낸다면 클라이언트에서는 단순히 결과를 보여주기만 하
면

되는 것이죠.

제가 생각해낸 방법의 첫단계는 이렇습니다.

SELECT *
FROM TBL_SALE T1
CROSS JOIN (SELECT '01' tradeType
UNION ALL
SELECT '02' tradeType
UNION ALL
SELECT '03' tradeType
UNION ALL
SELECT '04' tradeType) T2
WHERE T1.ProductID = 1
T2에는 UNION ALL을 이용해서 모든 거래형태를 미리 만들어 놓고, TBL_SALE과 CROSS JOIN을 해버립니다.

이렇게 하면, T1의 각 데이터마다 4개의 거래 형태가 만들어지게 됩니다.

3월 1일의 데이터가 다음과 같이 나타나게 됩니다.
SaleDate ProductID   TradeType Quantity    tradeType
-------- ----------- --------- ----------- ---------
20040301 1           01        3           01
20040301 1           01        3           02
20040301 1           01        3           03
20040301 1           01        3           04
20040301 1           03        4           01
20040301 1           03        4           02
20040301 1           03        4           03
20040301 1           03        4           04
TBL_SALE의 각 데이터가 4건씩 발생된 것을 알 수 있습니다.

이 데이터를 TBL_SALE의 SaldeDate와 ProductID와 T2쪽의 tradeType을 기준으로 GROUP BY를 수행합니다.

그 다음 Quantity를 SUM을 하는 겁니다.

SUM을 할 때는 CASE를 사용해서, TBL_SALE과 T2쪽의 tradeTtype 동일한 경우만 Quantity로, 아닌 경우는 0으로
SUM을 구합니다.

이 GROUP BY와 CASE를 적용하면 원하는 결과를 얻어 낼수 있게 됩니다.

다음과 같습니다.

SELECT T1.SaleDate, T1.ProductID, T2.tradeType,
SUM(CASE WHEN T1.tradeType = T2.tradeType THEN Quantity ELSE 0 END) Quantity
FROM TBL_SALE T1
CROSS JOIN (SELECT '01' tradeType
UNION ALL
SELECT '02' tradeType
UNION ALL
SELECT '03' tradeType
UNION ALL
SELECT '04' tradeType) T2
WHERE T1.ProductID = 1
GROUP BY T1.SaleDate, T1.ProductID, T2.tradeType
ORDER BY T1.SaleDate, T1.ProductID, T2.tradeType

위 SQL을 실행하면.. 짠!!!하고. 발생되지 않은 데이터도 0으로 표시할 수 있는 것을 알 수 있습니다.

^^ 여기서는 쿼리가 간단했지만, 이 쿼리를 생각하고 응용한다면, 꾀나 유용하게 사용할 수 있습니다.

하지만, 조심할 것은 실제 데이터가 많은 양에 대해 CROSS JOIN을 수행한다면 엄청난 부하를 가져올 수도 있습니다.

그러므로 결과를 먼저 충분히 줄여놓은 다음에, CROSS JOIN을 해야 합니다.

정재우 | ㈜엔코아컨설팅 선임 컨설턴트

SQL 서버 2000에서 더욱 강화된 기능 가운데 하나가 바로 Top N 쿼리이다. 이미 알려진 대로 Top N 쿼리는 order by와 함께 사용할 경우 조건에 해당하는 데이터 중에서 상위 또는 하위의 일부분만을 추출할 수 있는 기능을 제공한다.
아마 이 기능을 가장 유용하게 사용할 수 있는 곳은 웹 게시판일 것이다. 강력한 Top N 쿼리의 기능을 활용하여 효율적인 웹 게시판을 구현하는 솔루션에 대한 내용은 다음호에서 소개하기로 하겠다.
SQL 서버 2000은 'Top N Engine'을 통해서 Top N 쿼리를 효율적으로 처리한다. 이 엔진의 개선된 알고리즘은 다음과 같다.
SQL 서버 7.0에서는 조건에 해당하는 데이터를 모두 읽어 들인 후 full sort 방식을 통해서 결과 집합을 추출했지만 SQL 서버 2000의 'Top N Engine'은 N개의 데이터를 임시로 저장할 수 있는 개체(여기서는 '버퍼'라는 용어로 대체)를 메모리에 생성한 후 이 버퍼에 저장된 가장 크거나 작은 값만을 비교 대상으로 처리한다.
여러분이 'select top 5 * from Northwind.dbo.[Order Details] order by Quantity desc'와 같은 SQL을 실행하면 다음의 알고리즘에 의해서 처리된다. 우선 5개의 데이터를 저장할 수 있는 버퍼를 생성한 후, [Order Details] 테이블의 데이터를 순차적으로 읽어 들여서 이 버퍼를 채우기 시작한다. 5개의 데이터가 버퍼에 채워지고 나면 5번째 이후에 읽혀진 데이터는 이 버퍼에 저장된 데이터의 가장 작은 Quantity 값과 비교된다. 만약 6번째로 읽어 들인 데이터의 Quantity 값이 버퍼의 최소 Quantity 값보다 크다면 이 6번째의 데이터가 버퍼에 존재하는 데이터(최소 Quantity 값을 가진)를 밀어내고 그 자리를 차지하게 된다.
Top N 쿼리는 결과 집합의 행 수를 제한하는 기본적인 기능 외에 옵티마이저의 실행 계획을 제어하거나 OR 연산자와 함께 사용시 STOP KEY의 역할 등 알려지지 않은 유용한 기능을 제공한다. 9월호에서 소개했던 '멀티 캐시의 효과'도 상관 하위쿼리에 'Top 1' 키워드를 기술하지 않으면 전혀 다른 실행 계획을 수립하게 된다.
간단한 예제를 통해서 'Top 1' 키워드가 옵티마이저의 실행 계획에 어떠한 영향을 미치는지 확인해 보자.

[리스트 1] 예제 수행을 위한 테이블 및 인덱스를 생성하는 스크립트 1
create table 지역 (
지역번호 varchar(6) not null,
지역명 varchar(30) not null,
지역설명 varchar(1000) null
)

create table 판매 (
판매번호 int identity(1,1) not null,
판매일자 varchar(8) not null,
판매지역 varchar(6) not null,
판매금액 numeric(12,0) not null,
비고 varchar(500) null
)

alter table 지역 add constraint pk_지역 primary key (지역번호)

create clustered index 판매_idx2 on 판매 (판매일자)
alter table 판매 add constraint pk_판매 primary key (판매번호)

[리스트 1]의 DDL을 살펴보자. '지역' 테이블과 '판매' 테이블이 존재하며 두 테이블은 1:M의 관계를 가지고 있다. '지역' 테이블의 데이터는 겨우 10 건에 불과하며, '판매' 테이블의 데이터는 매일 수천 건 이상 발생한다고 가정하자.
이런 상황에서 고객이 다음과 같은 요청을 한다면 고려해야 할 사항은 무엇일까?
"2003년 12월 1일부터 12월 10일 사이에 발생한 판매내역을 조회하고자 한다. 조회 시 각 판매지역에 해당하는 지역명도 같이 보여줘야 한다. 또한 '판매' 테이블의 '판매지역' 컬럼의 값이 프로그램의 버그로 인해서 가끔 공백이나 NULL 값이 들어가는 경우가 있으므로 이런 데이터는 지역명을 무시하고 '판매' 테이블의 데이터만 보여주면 된다."
'판매지역' 컬럼의 값이 '지역' 테이블에 존재하지 않더라도 '판매' 테이블의 데이터를 보여줘야 하므로 당연히 외부(Outer) 조인을 사용해야 한다. 물론 스칼라 서브쿼리를 사용해도 외부 조인과 동일한 결과를 얻을 수 있으며 내부적으로 Outer Join 방식으로 실행된다.
명시적인 외부 조인 대신 스칼라 서브쿼리를 사용하여 SQL을 작성해 보자. 필자가 작성한 SQL과 실행 계획은 [그림 1]과 같다.

[그림 1] 수정 전 실행 계획




[그림 1]의 실행 계획을 살펴보자. 밑에서 3번째 줄을 보면 Nested Loops(Left Outer Join, ...)로 기술된 부분이 있다. 괄호 안의 Left Outer Join 연산자는 이 SQL이 예상한대로 외부 조인 방식으로 수행되었음을 알려준다.
그런데 실행 계획을 보면 뭔가 아쉬운 부분이 있다. Outer 측의 '판매' 테이블에서 추출된 2000건의 데이터에 대해서 Inner 측의 '지역' 테이블이 에누리없이 2000번 액세스 되었다. 지난 회에 소개한 스칼라 서브쿼리에서의 '멀티 캐시의 효과'는 어디로 사라진 것일까?
바로 이 부분에서 Top 1 키워드가 옵티마이저의 실행 계획에 큰 영향을 미칠 수 있음을 소개하고자 한다. 스칼라 서브쿼리의 결과 집합은 항상 1건이어야 하므로 서브쿼리의 SQL에 'Top 1'을 추가해도 논리적으로 [그림 1]의 SQL과 동일하다. 수정 후 실행 결과는 [그림 2]와 같다.

[그림 2] 수정 후 실행 계획




[그림 2]의 실행 계획을 보면 '지역' 테이블의 스캔 수 및 논리적 읽기 수가 [그림 1]의 실행 계획에 비해서 100분의 1 이하로 줄어들었으며 실행 계획 자체도 달라졌음을 알 수 있다. [그림 2] 실행계획의 밑에서 4번째 줄에 '멀티 캐시의 효과'가 있었음을 나타내는 Hash Match(Cache...) 연산자가 추가되었다.
이상의 테스트를 통해서 Top N 쿼리가 옵티마이저의 실행 계획에 큰 영향을 미칠 수 있음을 확인하였다. 이번에는 Top N 쿼리를 OR 연산자와 함께 사용할 경우 옵티마이저가 얼마나 효율적인 실행 계획을 만드는지 알아보자. 참고로, OR 연산자는 대상 집합을 줄이는 것이 아니라 더 확장시키므로 옵티마이저가 최적의 실행 계획을 세우지 못하면 예상치 못한 비효율이 발생할 수 있다.

[리스트 2] 예제 수행을 위한 테이블 및 인덱스를 생성하는 스크립트 2
create table 게시판 (
bbs_id smallint not null,
글번호 int not null,
제목 varchar(200) not null,
작성자번호 int null,
조회수 int null,
작성일자 varchar(8) not null,
내용 varchar(2000) null
)

alter table 게시판 add constraint pk_게시판
　　　　　　　　　　primary key nonclustered(글번호)
create unique clustered index 게시판_uk on 게시판(bbs_id, 작성일자, 글번호)

[리스트 2]의 DDL을 살펴보자. 공지 사항이나 뉴스 등을 게시하는 간단한 형태의 게시판이다. 이 경우 각 게시판 별로 가장 최근에 작성된 글 순서대로 20 건씩 조회하고자 하는 경우 어떻게 SQL을 작성해야 할까? 단, 화면에는 이전(◀) 및 다음(▶) 버튼만 존재한다.
다양한 아이디어가 있겠지만 필자는 [리스트 3]과 같은 형태로 작성하였다. [리스트 3]의 SQL에서 사용된 4개의 변수 값은 실제로는 클라이언트 프로그램에서 넘겨주는 값이다. 지면 관계상 이전(◀) 버튼을 클릭하는 경우는 생략하고 다음(▶) 버튼을 클릭하는 경우에 대해서만 설명하도록 하겠다.

[리스트 3] 웹 게시판의 다음 버튼 클릭시 작동하는 SQL
declare @조회구분 varchar(4), @bbs_id smallint,
@작성일자 varchar(8), @글번호 int

set @조회구분 = 'NEXT'
set @bbs_id = 1
set @작성일자 = '20040630'
set @글번호 = 2000

SELECT TOP 21 BBS_ID, 작성일자, 글번호, 내용
FROM 게시판 WITH(INDEX(게시판_UK))
WHERE @조회구분 = 'NEXT'
AND BBS_ID = @BBS_ID
AND ((작성일자 < @작성일자)
OR (작성일자 = @작성일자 AND 글번호 <= @글번호))
ORDER BY 작성일자 DESC, 글번호 DESC

[리스트 3]의 SQL을 보면 'TOP 21' 구문을 사용하여 데이터를 21건씩 조회하고 있다. 이것은 21번째의 데이터가 존재하지 않으면 다음 페이지의 데이터가 없다는 의미이므로 다음(▶) 버튼을 비활성화하기 위함이다. 또한 가장 최근에 작성된 글 순서대로 조회해야 하므로 'ORDER BY 작성일자 DESC, 글번호 DESC' 구문을 사용하였다.
이 SQL의 실행 계획은 [그림 3]과 같다.

[그림 3] 다음(▶) 버튼 클릭시 SQL의 실행 계획




[그림 3]의 실행 계획을 보면 '게시판' 테이블의 스캔 수가 2이다. 이것은 WHERE 조건의OR 연산자에 의해서 내부적으로 두 개의 조건이 분리되어 실행되었음을 의미한다. 즉, (작성일자 < @작성일자) 조건과 (작성일자 = @작성일자 AND 글번호 <= @글번호) 조건이 따로 분리되어 실행된 것이다.
[리스트 3]의 SQL을 논리적으로 풀어서 재작성하면 [리스트 4]의 SQL과 논리적으로 동일하다.

[리스트 4] 재작성한 SQL
SELECT TOP 21 B.BBS_ID, B.작성일자, B.글번호, B.내용
FROM (SELECT BBS_ID, 작성일자, 글번호, 내용
FROM (SELECT TOP 21 BBS_ID, 작성일자, 글번호, 내용
FROM 게시판 WITH(INDEX(게시판_UK))
WHERE @조회구분 = 'NEXT'
AND BBS_ID = @BBS_ID
AND ((작성일자 = @작성일자 AND 글번호 <= @글번호))
ORDER BY 작성일자 DESC, 글번호 DESC
) A
UNION ALL
SELECT BBS_ID, 작성일자, 글번호, 내용
FROM (SELECT TOP 21 BBS_ID, 작성일자, 글번호, 내용
FROM 게시판 WITH(INDEX(게시판_UK))
WHERE @조회구분 = 'NEXT'
AND BBS_ID = @BBS_ID
AND ((작성일자 < @작성일자))
ORDER BY 작성일자 DESC, 글번호 DESC
) A
) B
ORDER BY 작성일자 DESC, 글번호 DESC

[리스트 4]의 SQL을 보면 약간 의아한 생각이 들 수도 있다. 인라인 뷰(파생 테이블)를 살펴 보면 서로 배타적인 조건으로 21 건씩을 추출한 후 UNION ALL을 통해서 두 결과 집합을 결합시킨다. 잘못하면 불필요하게 42 건의 중간 집합을 만든 후 절반을 버리는 비효율이 발생하지 않을까?
이 질문에 대한 대답은 [그림 4]의 실행 계획에 나와 있다.

[그림 4] 재작성한 SQL의 실행 계획




[그림 4]의 실행 계획을 살펴보자. 우려했던 것처럼 불필요한 데이터를 읽어 들이는 비효율은 발생하지 않았다. 실행 계획의 Rows를 보면 UNION ALL의 상위에서 10건이 추출되고 하위에서 11 건이 추출되었음을 알 수 있다. 기특하게도 옵티마이저는 UNION ALL의 위쪽 SQL에서 추출한 건수를 제외한 나머지 개수의 데이터만을 아래쪽 SQL에서 추출하였다. 즉, Top N 쿼리는 STOP KEY의 역할까지 훌륭하게 수행한 것이다.
이러한 Top N 쿼리의 기능을 잘 활용하면 웹 게시판을 효율적으로 구현할 수 있다. 이전 및 다음 버튼만 존재하는 간단한 게시판에서부터 응답글이 존재하는 복잡한 게시판에 이르기까지 다양한 형태의 응용이 가능하다.
지금까지 두 개의 예제를 통해서 Top N 쿼리의 숨겨진 유용한 기능에 대해서 알아보았다.
첫 번째는 스칼라 서브쿼리에 'Top 1' 구문을 추가하여 실행 계획을 변경하는 방법에 대해서 설명했다. 'Top 1' 구문을 잘 활용하면 '멀티 캐시의 효과'를 볼 수 있음을 기억하기 바란다.
두 번째는 Top N 쿼리가 STOP KEY의 기능을 포함하고 있음을 예제를 통해 설명했다. 이 기능은 효율적인 웹 게시판의 구현을 위한 필수 요소이므로 정확히 이해하기 바란다.
다음 호에서에는 Top N 쿼리의 강력한 기능을 활용하여 웹 게시판의 어느 페이지로 이동하더라도 동일한 조회 속도를 보장받을 수 있으며 불필요한 데이터는 전혀 액세스하지 않는 효율적인 웹 게시판 솔루션에 대해서 설명하도록 하겠다.

제공 : DB포탈사이트 DBguide.net