skymong9.egloos.com

Activation...

포토로그



[MSSQL] Union All 관련 (퍼옴) my dev_study

나름대로 풀어본 쿼리문의 원리(UNION ALL편) | Programming2003-01-10 오전 10:37:40
류일환 번호: 513 / 읽음:2,431
나름대로 풀어본 쿼리문의 원리 3
UNION ALL편

UNION ALL을 쓸라 하니.. 예제를 만들기가 참 어렵네여.
읽으시면, 이사람 억지쓰네 라구 생각되더라도 참아주시길…ㅜ.ㅡ
그리고 모든 문제를 해결하는데는 여러가지 방법이 있을 수 있다는걸 항상 기억하시구여,
생각의 전환이 진보를 가져올수 있다는 점을 인식하시고... 다른 방법들을 많이 생각하면서 보시면 좋겠네요^^

우선 간단히 UNION ALL을 말씀 드리자면, 이 것도 어디서 본듯했던 말인데, JOIN의 한 종류라는 겁니다.
(대용량 데이터베이스 솔루션같군여…)
조인은 두개의 테이블에 있는 내용을 옆으로 펼치게 됩니다. 반면 UNION ALL은 두 개의 테이블을 위 아래로 합치게 된다는 겁니다.
JOIN이 수평적 결합이라면, UNION ALL은 수직적 결합이 되는겁니다.
1편에 CASE문을 설명할 때 밑으로 떨어진 데이터를 옆으로 펼치기 위해 사용한다고 했는데,
UNION은 이와는 반대로, 옆으로 펼쳐진 데이터를 밑으로 펼칠 수 있습니다..
그러니까, UNION은 세로라는걸 항상 명심하시면 될 거 같네요.
그리고, 위에서 UNION ALL을 얘기하다가 갑자기 UNION으로 바꼈는데, 이건 두 문장이 같은 기능을 하기 때문입니다.
(같다고 하기에는 성능에 많은 차이가 있을 수 있지만요..)
두 문자의 차이는 UNION ALL은 수직의 데이터들을 결합시키는데 있어서 중복된 데이터들도 중복된채로 결합을 시킨다는 겁니다.
반면, UNION은 결합시 중복된 데이터들을 제거합니다.
그러므로 UNION은 결합하면서 UNION ALL보다 비용이 더 소비될 수 밖에 없겠죠.


간단한 예제를 만들어 보겠습니다.(이런 예제 만드는데도 30분정도 걸리는구여..ㅡ.ㅜ)
테이블을 만들구여,

CREATE TABLE #OLD_SALE
( SaleNo int identity(1,1) not null primary key --판매번호
,SaleYmd char(8) not null default '' --판매일자
,CustID char(12) not null default '' --구입한 고객번호
,ItemCode char(4) not null default '' --판매 품목
,qty int not null default 0 --판매량
)

#OLD_SALE과 동일한 #NEW_SALE 테이블을 만듭니다.

SELECT * INTO #NEW_SALE FROM #OLD_SALE

SELECT INTO를 했을 경우는 키가 복제는 안되더라구여, 그래서 키를 추가합니다.

ALTER TABLE #NEW_SALE
ADD CONSTRAINT pk_NEW_SALE PRIMARY KEY (SaleNo)


다음에 시험데이터들을 집어넣습니다.

INSERT #OLD_SALE VALUES('20021010','ryu','0001',10)
INSERT #OLD_SALE VALUES('20021020','cho','0001',10)
INSERT #OLD_SALE VALUES('20021110','nam','0001',10)
INSERT #OLD_SALE VALUES('20021114','kim','0001',10)
INSERT #OLD_SALE VALUES('20021120','kim','0001',10)
INSERT #OLD_SALE VALUES('20021229','ryu','0001',10)

INSERT #NEW_SALE VALUES('20030110','cho','0001',10)
INSERT #NEW_SALE VALUES('20030111','nam','0001',10)

UNION ALL문장은 다음과 같습니다.

SELECT * FROM #OLD_SALE
UNION ALL
SELECT * FROM #NEW_SALE

결과는 다음과 같구여.

SaleNo SaleYmd CustID ItemCode qty
----------- -------- ------------ -------- -----------
7 20021010 ryu 0001 10
8 20021020 cho 0001 10
9 20021110 nam 0001 10
10 20021114 kim 0001 10
11 20021120 kim 0001 10
12 20021229 ryu 0001 10
1 20030110 cho 0001 10
2 20030111 nam 0001 10

이 UNION ALL을 유용하게 쓰실 수 있는 분야가 DB의 성능 향상을 위한 테이블의 수평분할에 사용하실 수 있습니다.
저도 이론적으로 공부만 해놓고 실제로 써본적은 없지만, 절로 공감이 가는 성능향상 방법입니다.
일단, 위의 예제와 같이 판매 테이블이 있는 회사에서, 해당 판매 건수가 발생되는 양이 어마어마하다면,
그것을 한 개의 테이블로 보관하는 것은 여러가지로 불리하게 됩니다.
이걸 설명하자면, 또 여러가지 얘기가 늘어질 수 밖에 없지만, 흥미로운 얘기니까 함 끝까지 해보죠..(잘은 모르지만..^^)

우선 데이터베이스 모델링에 보면, OLAP환경과 OLTP환경이 있습니다.
여러분이 쉽게 접할 수 있는 분야는 OLTP환경입니다. OLTP는 online transaciotn 어쩌구 저쩌구인데,
요점은 실시간 데이터의 처리가 주로 되는겁니다. 저희가 은행에서 입금하고 출금하는 작업들이 이에 속하죠,
이런 시스템은 잦은 데이터의 변경이 주로 된다는 겁니다. 그러므로, 그에 알맞은 데이터모델링이 필요하다는 거죠.
논리적인 모델링과 더불어 물리적인 구현을 할 때도, INSERT, UPDATE의 성능을 향상 시키기 위해 인덱스에 적절한 FILLFACTOR설정도 필요하구여, 기타 여러가지 요소들을 고려해서 최상의 OLTP환경을 만들어야겠죠.

그리고 OLAP는 Online Aly.. 어쩌구 저쩌구인데여..(영어가 워낙 짧아서리..ㅜ.ㅡ;) 데이터의 분석을 위해 사용되는 환경이죠,
저도 이 분야에 대해서는 자세히는 모르지만(OLTP도 모르긴 마찬가지지만…ㅜ.ㅡ.. 모든걸 줏어들은 얘기로 떼우고 있죠^^)
경영 분석등을 위해서 쓰이는 거죠.
OLAP 환경은 UPDATE, INSERT 같은 데이터의 변경 사항이 아닌, 주로 SELECT를 위해 사용된다는 겁니다.
데이터의 통계등을 만들기 위해서 효율적인 SELECT를 구사할 수 있도록 될 수 있는 한 유용한 인덱스들을 많이 만들어 놓고,
인덱스의 FILLFACTOR도 될수 있는데로 꽉꽉 채워넣는다는 겁니다.
(FILLFACTOR에 대해서는 난중에.. 언제 기회가 닿으면 얘기를 해보죠..^^ 책 찾아보심 많이 나왔있습니다.)

제가 얘기하려던건, 수평분할 얘기였는데, 하다보니, OLAP, OLTP가 튀어나와서 얘기가 많이 세었군요.
그럼, 다시 돌아가서, 1년에 쌓이는 양이 어마어마할 때 OLD SALE 테이블과, NEW SALE 테이블을 만들어서
NEW SALE 테이블에는 지금 사용하는 데이터들을 넣어놓고 사용하는 겁니다.
지금이 2003년이니까, 2003년 데이터들은 NEW SALE테이블에 넣고 사용하는거죠,
그리고 2002년까지의 데이터들은 OLD SALE 테이블에 넣는 겁니다.
이유는 OLD SALE테이블에 있는 내용은 NEW SALE에 비교할 때 수정할 일은 거의 없고, 조회할 일만 있을거라는 겁니다.
그러므로 OLD SALE 테이블에는 FILLFACTOR를 100%로 채운 인덱스들을 만들구여,
NEW SALE테이블에는 OLTP효과가 뛰어날 수 있도록 FILLFACTOR도 여유 공간을 남겨놓고,
UPDATE, INSERT에 유용하도록 인덱스를 만들어가는 겁니다.
그러면, 전체적인 성능향상을 이룰 수 있다고 많은 책들에 나와있네여.. 저도 공감하는 바입니다. 해보지는 못했지만여…

짐까지 말이 참 많았군여… UNION ALL을 설명할려 구랬는데 왜이렇게 쓸데없는 말들로 공간을 메꾸고 있는건지… ㅡ,.ㅡ

자 이번에는 좀 더 UNION ALL을 활용해 보도록 하겠습니다.
우선 다음과 같은 테이블을 만듭니다.(CASE편에서 출현했던 그넘있니다.)

CREATE TABLE #ITEM --품목 테이블
( itemCode char(04) not null primary key --품목코드
,itemName varchar(20) not null default '' --품목이름
)

데이터를 넣구요

INSERT #ITEM VALUES('0001','핸드폰') INSERT #ITEM VALUES('0002','컴퓨터')
INSERT #ITEM VALUES('0003','오디오') INSERT #ITEM VALUES('0004','비디오')

이런 품목데이터들이 있는데, 전체적으로 품목이 변경되는 일이 발생되었습니다.

SELECT *
INTO #NEW_ITEM
FROM #ITEM

NEW_ITEM테이블의 품목들을 다음과 같이 변경시킵니다.

DELETE #NEW_ITEM WHERE itemCode = '0003'
INSERT #NEW_ITEM VALUES ('0005','카메라')
UPDATE #NEW_ITEM SET itemName = '세탁기' WHERE itemCode = '0001'

여기서, UNION ALL은 잠시 접고, 3가지 문제에 대한 쿼리를 풀어보겠습니다.

#NEW_ITEM에는 있지만, #ITEM에는 없는 데이터를 뽑아내는 방법(변경되면서 삭제된 데이터)
다음은 NOT IN을 사용한 방법입니다.
SELECT *
FROM #NEW_ITEM

WHERE itemCode NOT IN(SELECT itemCode FROM #ITEM)
다음은 NOT EXISTS를 사용한 방법입니다.
SELECT *
FROM #NEW_ITEM A
WHERE NOT EXISTS(SELECT * FROM #ITEM WHERE itemCode = A.itemCode)

이번엔 #ITEM에는 있지만 #NEW_ITEM에 없는 데이터를 뽑아내는 방법입니다.(새로 추가된 데이터)
SELECT *
FROM #ITEM
WHERE itemCode NOT IN(SELECT itemCode FROM #NEW_ITEM)

이번엔 명칭이 틀린 품목을 뽑아내는 방법입니다.(명칭 변경된 데이터)
SELECT *
FROM #ITEM A JOIN #NEW_ITEM B ON (A.itemCode = B.itemCode AND A.itemName <> B.itemName)


위의 3가지 경우에 대한 쿼리는 조인과, NOT IN, NOT EXISTS를 사용해서 풀고 있습니다.
이것에 대한 설명은 생략할께여.. 이것까지 설명했다가는 언제 끝날줄 몰라서리…ㅡ.ㅜ
그리고, 모든 분들이 잘 아시리라 굳게 믿습니다.^^
UNION ALL을 사용해서 구현하고자 하는 것은 위의 3가지 조건을 모두 만족하는 하나의 쿼리를 만들고자 하는 겁니다.
그러니까, 새로 추가된 데이터, 삭제된 데이터, 변경된 데이터를 하나의 쿼리로 해결하자는 겁니다.
우선 간단하게 위의 3가지 문제를 한번에 해결하기 위해서는 다음과 같이 쿼리를 만듭니다.

SELECT 상태 = '추가',*
FROM #NEW_ITEM
WHERE itemCode NOT IN(SELECT itemCode FROM #ITEM)
UNION ALL
SELECT 상태 = '삭제',*
FROM #ITEM
WHERE itemCode NOT IN(SELECT itemCode FROM #NEW_ITEM)
UNION ALL
SELECT 상태 = '변경',A.*
FROM #ITEM A JOIN #NEW_ITEM B ON (A.itemCode = B.itemCode AND A.itemName <> B.itemName)
UNION ALL
SELECT 상태 = '변경없음',A.*
FROM #ITEM A JOIN #NEW_ITEM B ON (A.itemCode = B.itemCode AND A.itemName = B.itemName)

결과는 다음과 같습니다.

상태 itemCode itemName
-------- -------- --------------------
추가 0005 카메라
삭제 0003 오디오
변경 0001 핸드폰
변경없음 0002 컴퓨터
변경없음 0004 비디오

이 쿼리에 대해서는 설명이 없어도 쉽게 이해하시리라 믿습니다.
위 쿼리에 대해서 실행 계획을 보면, 4개의 쿼리를 실행해서 합친다는 것을 알 수 있습니다.
그만큼 쿼리를 4번 한다는 비용이 들어가게 됩니다. 같은 테이블을 4번씩 액세스를 한다는 거죠,
물론 한번만 접근하게 되면, 메모리에 올라와서 속도상에 크게 상관은 없지만, 아무래도 같은 테이블을 4번씩이나 뒤진다는건,
뭔가 아깝습니다.(쿼리 튜닝의 경우도 이 경우를 생각하시고 하시면 될 겁니다. 테이블에 대한 검색을 최소한으로 할 수 있는 방법으로 구현을 해나가는 거죠)
다음과 같이 쿼리를 만들어 봅니다.

SELECT CASE WHEN newCode ='' THEN '삭제'
WHEN oldCode ='' THEN '추가'
WHEN newName <> oldName THEN '이름변경'
ELSE '변경없음' END, *
FROM (SELECT itemCode,
newCode = MAX(newCode), newName = MAX(newName),
oldCode = MAX(oldCode), oldName = MAX(oldName)
FROM (
SELECT A.itemCode,
newCode = A.itemCode, newName = A.itemName,
oldCode = '', oldName = ''
FROM #NEW_ITEM A
UNION ALL
SELECT B.itemCode,
newCode = '', newName = '',
oldCode = B.itemCode, oldName = B.itemName
FROM #ITEM B ) A1
GROUP BY itemCode) A2
ORDER BY newCode

좀 복잡하죠. 어쩌면, 제가 하면서 쓸데 없이 들어간 문장이나 잘못된 처리가 있을지도 모르겠습니다.
사실, 결과만 보고 가다보면, 잘못된 쿼리를 만들기가 일수인지라…
결과는 다음과 같습니다.

itemCode newCode newName oldCode oldName
-------- -------- ------- ---------- ---------- -------
삭제 0003 0003 오디오
이름변경 0001 0001 세탁기 0001 핸드폰
변경없음 0002 0002 컴퓨터 0002 컴퓨터
변경없음 0004 0004 비디오 0004 비디오
추가 0005 0005 카메라

보시면, 위의 결과보다 쫌더 그럴싸하지 않습니까..(아니면 말구여…)
사실은 위의 UNION ALL의 문장이 제가 회사에서 병원 청구 심사프로그램 개발할 때,
원래 오더와 심사하면서 새로 추가되거나 변경되거나 삭제된 오더를 한번에 알아내기 위해서 사용했던 쿼리문을 따온건데여,
그 때는 이것보다, 뭔가 더 실용적이었는데..
지금은 예제를 보여주기 위해 만든거라 그런지 그렇게 실용적이란 생각은 들지 않는군여..
아무튼 그렇구여, 이젠 설명을 해보죠.
전에 말씀드렸듯이 위의 쿼리처럼 복잡한 것은 FROM절의 ()안의 문장부터 분석을 하는겁니다.
보시면, FROM절의 ()안에 또다른 FROM ()가 있는 것을 보실 수 있을겁니다. 그러므로 요놈부터 분석을 합니다.
(물론, 이런건… 제 맘입니당.^^ 여러분들도 여러분 편한데로 분석을 하시면 됩니다.^^)

(
SELECT A.itemCode,
newCode = A.itemCode, newName = A.itemName,
oldCode = '', oldName = ''
FROM #NEW_ITEM A
UNION ALL
SELECT B.itemCode,
newCode = '', newName = '',
oldCode = B.itemCode, oldName = B.itemName
FROM #ITEM B ) A1

가장 안쪽의 ()안에 있는 문장이 다음과 같습니다. 위의 문장만 따로 실행 시켜 보시면(괄호와 A1을 빼셔야 합니다.)
다음과 같은 결과가 나옵니다.

itemCode newCode newName oldCode oldName
-------- ------- -------------------- ------- --------------------
0005 0005 카메라
0001 0001 세탁기
0002 0002 컴퓨터
0004 0004 비디오
0001 0001 핸드폰
0002 0002 컴퓨터
0003 0003 오디오
0004 0004 비디오

UNION ALL 위쪽에 있는 쿼리문은 NEW_ITEM에 데이터들을 newCode와 newName에 뿌려놓고,
oldCode와 oldName칸을 비워놓습니다.
그리고, 아래쪽에 쿼리는 반대로 ITEM의 데이터들을 oldCode와 oldName에 뿌려놓고, newCode와 newName을 비워놓습니다.
그리고 이 결과를 FROM으로 받는 쿼리문은 다음과 같죠.

(SELECT itemCode,
newCode = MAX(newCode), newName = MAX(newName),
oldCode = MAX(oldCode), oldName = MAX(oldName)
FROM (위쪽의 쿼리) A1
GROUP BY itemCode) A2

보시면, itemCode로 GROUP BY를 수행하는걸 볼 수 있습니다. 그리고, 나머지 컬럼들을 max값으로 뽑아내 버립니다.
여기서 다시 잠깐 가장 내부쿼리의 결과를 보시면 다음과 같습니다.

itemCode newCode newName oldCode oldName
-------- ------- -------------------- ------- --------------------
0005 0005 카메라
0001 0001 세탁기
0002 0002 컴퓨터
0004 0004 비디오
0001 0001 핸드폰
0002 0002 컴퓨터
0003 0003 오디오
0004 0004 비디오

여기서 itemCode를 GROUP BY컬럼으로하고 나머지를 MAX로 뽑아내니까,
0005같은 경우는 newCode와 newName에는 데이터가 있지만, oldCode와 oldName은 ‘’값이 될겁니다.
그러므로 이 데이터는 새로 추가되었다는걸 판단할 수 있죠.
그리고, 0001같은 경우는 newName의 MAX값은 세탁기이구여, oldName의 MAX는 핸드폰이 됩니다.
oldName과 newName의 이름이 틀리니까 새로 변경된 데이터라는걸 알 수 있겠죠,
그리고, 0004번 같은 경우는 newName의 MAX는 비디오, oldName의 MAX도 비디오이므로 변경없는 데이터라는걸 알 수 있죠.

마지막으로 가장 외부의 쿼리문은 다음과 같죠.

SELECT CASE WHEN newCode ='' THEN '삭제'
WHEN oldCode ='' THEN '추가'
WHEN newName <> oldName THEN '이름변경'
ELSE '변경없음' END, *
FROM (내부쿼리) A2
ORDER BY newCode

A2의 결과를 CASE별로, newCode가 ‘’이면 삭제된 데이터로 판단을 하고, oldCode가 ‘’이면 추가된 데이터이구여,
newName과 oldName이 같지 않으면 이름변경으로 판단을 한다는 겁니다.
여기서 중요한건, CASE의 WHEN절의 순서가 newName<>oldName이 젤 위로 와서는 안된다는 겁니다.
가장 먼저 걸리는 조건으로 CASE문이 판단되어 버리기 때문에 추가나 삭제된 데이터도 이름변경으로 나올 수 있으니까요.
이상입니다..
아.. 이번엔, 너무 쓸데없는 말이 길었네요..
사실은 어젯밤에 상관서브쿼리를 올려놓고, 내용이 너무 부족한거 같아서 상관 서브쿼리에 대해서 추가적인 내용들을 만들어 볼까 했는데, 다음으로 미루고 UNION ALL을 먼저 올렸습니다.

나름대로 원리를 펼치는게 참 힘드네여..
제 스스로 말이 맞는지 틀린지도 모르면서 써내려갈려니까…
아무튼. 잘하시는 분들에게는 별 도움이 안 되겠지만, 다른 분들에게라도 조금의 도움이라도 되었으면 좋겠네여.
음. 또 한마디 하자면, SQL 공부하시는 분들… 책을 많이 보시라는 겁니다.
저 자신도 책을 많이 보지는 않지만요(게을르고, 책보면 졸려서리..ㅡ.ㅡ), 실력을 늘리는 가장 좋은 방법은 책을 많이 보는 겁니다.
그리고, QA란에 질문을 올리시기 전에 나름대로 한번 생각을 해보시는거 하구여.
스스로 한번 찾아보시는 거.. 참 중요하다고 생각됩니다.
얼마나 좋은 세상인지, 쿼리분석기나 EM을 열어놓고 F1만 누르면 바로 BOL이 뜨지 않습니까?
거기서 모르는 내용을 직접 찾아볼 수 있다는 능력만 갖추어도 일반 SQL 사용자와는 크게 차이가 나는거라 생각 됩니다.

담엔 몰써야 될지 한번 생각해보겠습니다.^^

이틀동안 회사일을 뒤로한채… 글을 써부렸더니… 회사일이 많이 밀리는군여…^^



출처 : 데브피아

덧글

댓글 입력 영역