컴퓨토피아

이번 컬럼에서 다룰 주제는 "쿼리 테크닉"이다. 좀더 잘했으면, "쿼리의 정석" 정도면 좋겠는데, 아직 필자의 수준이 먼 것같다. 얼마전에 있었던 MSDN 세미나의 내용들 중에서 Deep Inside 컬럼 독자들에게 하고 싶은 말들을 위주로 작성했다.

데이터베이스는 테트리스다

많이 웃겠지만, 데이터베이스는 테트리스가 맞.다. 왜 이런 의미없어 보이는 이야기를 할까? 고민해보자. 답을 먼저 말하고 문제를 찾는 것이다. 테트리스는 무작위 도형들이 떨어져서 쌓이고, 이 데이터들은 일정한 패턴이 되면, 증발한다. 데이터베이스 또한 마찬가지이다. 무작위 데이터들이 입력되고, 이 데이터들은 정보가 변경이되고, 그리고 삭제된다. 우리는 이것을 하나의 사건이 발생해서, 변경되고 또한 폐기된다가 말한다. 모델링의 측면에서 보았을때, 데이터베이스라는 것은 일종의 정보 개체이다. 정보 개체에는 수많은 사건들이 저장된다. 우리는 단순히 인서트(Insert)라는 명령을 수행하는 것이지만, 이것은 새로운 사건을 데이터베이스에 반영하는 것이다. 수정(Update)이나 삭제(Delete) 또한 마찬가지이다.

<그림 1> 데이터베이스는 테트리스

관심가져야만 하는 부분은 대상이 되는 데이터가 무작위 데이터라는 것이다. 절대로~, 엑셀처럼 하나의 사건들이 하나의 파일에 뭉쳐있는 것이 아니다. 따라서, 여러분이 간단히 날리는 "Select * from customers"와 같은 명령도 이 데이터들을 찾으려면 SQL 서버가 열심히 뛰어다녀야 결과를 알 수 있다. 데이터베이스를 다룰때, 항상 이러한 "무작위 데이터"가 쌓여 있는 것을 연상해야 한다. 그리고 우리는 쿼리를 날림으로써 "무작위 데이터"를 "의미있는 집합"으로 변환한다.

<그림 2>에서 처럼 우리가 일반적으로 데이터베이스라고 부르는 것은 여러가지 수직적인 계층으로 나뉘어져 있다. "Northwind"와 같은 데이터베이스 카탈로그는(유콘 부터는 이 위에 스키마가 있겠지만), 여러개의 테이블과 같은 논리적인 개체(Object)들로 구분되어 있다. 이까지는 논리적이며 추상적인 구조이다. 그 밑에 여러개의 물리적인 파일들이 있다. 여러 개의 논리적인 파일 그룹 밑에 물리적인 파일들이 있을 수도 있다. 하나의 테이블이 하나의 물리적인 파일에 대응되지는 않는다. 하나의 테이블은 여러 개의 파일에 분산되어 저장될 것이다. 어떤 파일에는 좀더 많이, 어떤 파일에는 약간만 데이터들이 있을 수 있다.

<그림 2> 논리적인 데이터베이스

좀더 안으로 들어가보자. SQL 서버는 테이블 내의 데이터들을 익스텐트(Extent) 단위로 공간을 할당한다. 하나의 익스텐트에는 8개의 데이터 페이지들이 들어갈 수 있다. 하나의 익스텐트에 동일한 테이블의 내용이 들어갈 수도 있고, 서로 다른 테이블 및 인덱스 페이지들이 섞일 수도 있다.

<그림 3> Extent

익스텐트 내부의 데이터 페이지들에는 <그림 4)와 같은 정보들이 혼재되어 있다. SQL 서버에서 가장 복잡한 데이터 조회 상황은 클러스터드 인덱스가 있는 상황에서 넌클러스터드 인덱스들이 존재하는 경우이다. 이러한 경우에 넌클러스터드 인덱스를 조회하는 쿼리들은 넌클러스터드 인덱스의 루트 노드에서 조회를 시작해서 리프 노드에서 원하는 정보를 모두 취하지 못하면, 해당 정보를 가지고 있는 나머지 데이터 조각을 위해서 클러스터드 인덱스의 키값을 기준으로 클러스터드 인덱스의 루트 노드에서 나머지 노드들로 포인터를 이동해 나간다. 원하는 값을 찾았으면 해당 리프 노드에서 데이터 조회를 마치게 된다.

<그림 4> 인덱스가 있는 경우 데이터 조회

복잡한 개념들을 머리속에 외우자고 하는 것이 아니다. 다만, 한번씩 머리속에서 연상해보자는 것이다. 쿼리를 작성할때 우리가 데이터를 가져오는 대상에 대해서 조금이나마 이해하자는 것이다. 필자는 쿼리를 작성할때 다음과 같은 네 가지 개념이 쿼리의 작성이나 성능향상에 도움이 된다고 생각한다.

• 더 짧은 탐색 경로를 가지도록 하자
• 더 적은 데이터 페이지들을 읽어들이도록 하자
• 읽은 순서 그대로 사용하면 좋다
• 한번 읽어들인 데이터를 이용하자

물론, 이것 이외에도 더 있을 수 있을 것이다. 하지만, 인간의 뇌가 그리 좋지 않다. 고민해야 할 가이드 라인이 많다면, 차라리 없는 것만 못하다. 몇 가지 주요한 것들만 기억하고 나머지는 잊어버려라. 각각의 주제에 대해서 구체적으로 살펴보자.

더 짧은 탐색 경로를 가지도록 하자.

<그림 4>를 다시 살펴보자. 원하는 데이터가 넌클러스터드 인덱스 리프 노드 상에 발견되지 않으면, 클러스터드 인덱스 리프 노드까지 탐색을 진행해야만 한다. 원하는 데이터가 굳이 클러스터드 인덱스까지 갈 필요가 없다면 어떨까? 그냥, 넌 클러스터드 인덱스의 리프 노드에서 원하는 데이터를 모두 찾는다면, 탐색 경로는 그만큼 단축되게 된다. 데이터베이스 입장에서 각 노드들을 찾아서 포인터를 이동 시키는 것은 귀찮고 비용이 꽤나 소모 되는 작업이다. 대부분의 조회 작업들이 범위 검색이므로, 이러한 포인터 이동은 굉장히 자주 일어나는 작업이 된다. 예를 들어 실행 계획 상에 있는 북마크 룩업(Bookmark lookup)이 이러한 작업들이다.

옵티마이저가 클러스터드 인덱스까지 포인터를 옮길 필요가 없음으로 해서 상당한 성능 개선 효과를 볼 수 있다. 이게 바로 커버드 인덱스(Covered index)이다. 단순히, 인덱스에 조회 컬럼들을 포함시키면 성능 효과를 볼 수 있다고 기억하는 행자들이 많은데, 그렇게 이해하는 것은 너무 표피적인 이해 수준이다. 인덱스에 조회 컬럼들이 있으면 왜 성능이 좋아질까를 이해해야만 한다.

앞으로 SQL 서버는 이러한 부분을 더 강화할 예정이다. 예를 들어 확정된 이름은 아니지만, 인클루디드 인덱스(Included index)와 같이 검색 조건이 아니지만, 조회시 출력되는 컬럼들을 포함시키는 인덱스도 생겨날 것 같다. 물론, 확실한것은 제품이 나와 바야만 알겠지.

더 적은 데이터 페이지들을 읽어들이도록 하자

이 말은 두 가지 의미 정도로 이해할 수 있다. 하나의 데이터 및 인덱스 페이지에는 대략 8kb 정도의 데이터를 담을 수 있다 이 데이터 넘어서면 여러 개의 데이터 및 인덱스 페이지에 데이터가 나누어지게 된다. 만일 텍스트와 같은 BLOB 데이터가 있을 경우, 이러한 데이터들은 페이지 포인터만이 데이터 페이지에 저장되고 실제의 데이터는 외부에 저장되게 된다. 하나의 데이터 페이지에 여러 개의 로우(Row)들이 담겨질 수 있다면, 데이터 페이지의 수를 줄일 수 있다.

데이터 페이지의 수가 줄어든다면 SQL 서버의 I/O 성능이 나아지는 것이라고 할 수 있다. 같은 결과를 내는 데에 더 많은 데이터 페이지들을 읽어들이고 있다면, 그만큼 성능이 낮은 것이다. 즉, 한 로우의 크기가 커질 수록 성능이 낮아진다는 것이고, 테이블이 로우 설계가 그만큼이나 성능에 영향을 미친다. 물론, 인덱스의 경우도 마찬가지이다. 필요하지 않은 여러 개의 컬럼들이 인덱스에 포함되어 있다면, 그만큼이나 느릴 수 밖에 없다. 물론, 어떤 사람들은 사용하지 않는데 크게 문제 있을까 생각할 수도 있지만 그것은 오해이다.

데이터 및 인덱스 페이지가 조각 나 있는 것도 마찬가지 이유에서 성능 저하의 원인이다. 데이터 페이지들에는 여유 비율이라는 것이 있을 수 있다. 물론, 페이지 스플릿을 최소화하기위해 필요한 것이다. 여유 비율이 높으면 하나의 페이지에 더 적은 데이터만을 기록하게 된다. 데이터 갱신 빈도가 높아서 페이지 스플릿이 많이 일어날 수도 있다. 그럼, 데이터 페이지들에 빈 공간들이 많아 질 수 있고, 저장 공간을 효율적으로 사용하지 못하고 있을 수 있다.

그럼, 그만큼이나 많은 수의 데이터 페이지들을 읽어들여야 한다. 비효율적인 것이다.

다른 의미로, 이러한 것들이 최적화 혹은 고민하지 않을 정도의 수준일 수 있다. 그럼에도 불구하고, 개별적인 쿼리 문이 잘못 작성되어서 원하는 데이터 페이지의 범위를 잘못 산정해서 작업이 이루어질 수 있다. 무슨 이야기냐 하면, 10건의 데이터를 뽑는데, 대상을 100만건으로 해서 10건을 찾는 작업을 하고 있을 수 있다는 것이다. 10건을 찾는데 대상 조건을 잘 주면, 범위가 1000건 정도로 줄여서 작업할 수 있다. 나오는 결과는 똑같지만, 대상이 되는 건수가 100만건이면, 100만건이 들어 있는 데이터 페이지를 모두 읽어들여야 한다. 그만큼 대상이 되는 데이터 페이지 건수가 늘어나는 것이다. 이것이 바로 SARG(Search Arguments)이다.

읽은 순서 그대로 사용해라

SQL 서버의 이전 버전에서는 Group By를 하면 데이터 정렬이 Group By절의 순서대로 조정되는 경우가 있었다. 물론, 아직도 그런 경우가 빈번하지만, 이것은 확정할 수 없다. 왜냐면, SQL 서버 옵티마이저가 대량 데이터의 집계 연산 성능을 높이기 위해서 해시 매치 방식을 사용하기 떄문이다. 아쉽지만, 아직도 데이터 연산 순서가 정렬된 형태로 작업되는 것이 남아 있다. 그것이 바로 클러스터드 인덱스이다. 클러스터드 인덱스의 순서대로 데이터들이 정렬되어 있고, 정렬되어 있는 순서대로 데이터들이 출력된다. 따라서, 이러한 클러스터드 인덱스는 데이터베이스 쿼리 작업에서 Order By 연산을 추가적으로 할 필요 없게 해주는 거의 유일무이(唯一無二)한 존재인 것이다.

정렬 연산은 전체 데이터를 다시 읽어서 재배치해야 하므로 메모리나 CPU의 비용이 높은 연산이다. 데이터가 대량이 되는 경우는 비례적으로 높아진다. 클러스터드 키 하나만 잘 설계해 놓으면 이러한 부분들을 줄일 수 있다.

한번 읽어들인 데이터를 재활용해라

데이터베이스에서 가장 비용이 많이 들어가는 부분은 디스크를 읽는 단계이다. 디스크 기술이 비약적으로 발전하고 있지만, 아직까지도 메모리나 CPU에 비해서 디스크의 I/O 속도는 느리고, 병목 현상도 심하다. 디스크로 한번의 I/O가 일어나는 쿼리와 다섯 번의 I/O가 일어나는 쿼리를 비교할때, 일반적으로 첫 번째 쿼리가 비용상 우위에 있다고 볼 수 있다.

어떤 쿼리들에서는 동일한 작업을 수행하기 위해서 디스크를 여러 번 읽어들인다. 디스크에서 한번 읽어들인 데이터를 계속해서 이용할 수 있다면, 매우 효율적일 것이다. 이것이 바로 복제 기법을 이용한 쿼리이다. 디스크로의 I/O를 줄일려고 노력해야만 한다.

이러한 네가지 생각이 쿼리를 작성할 때, 도움이 되었으면 좋겠다. 이러한 기준들은 쿼리를 작성할 때 참고하면 좋은 일종의 가이드 라인이다. 필자는 세미나에서 쿼리라는 것의 의미에 대해서 상당히 오랜 시간 이야기하였다. ㅎㅎㅎ. 지루해하는 사람들도 많았을지 모르겠다. 하지만, 사람들이 SQL이나 날릴 줄 알지, 쿼리가 어떤 것일까에 대해서 그다지 고민해보지는 않은것 같다. 사실, 사는데 도움이 안되니깐, 그다지 중요하지 않을 것일 수도 있다. 하지만, 문제를 정리하고 자신만의 기준을 만들어서 지킬려고 노력할때, 발전이라는 것이 있다.

쿼리(Query)와 유사한 단어로 퀘스천(Question)이라는 말이 있다. 둘다, 질문이라는 말이다. 질문이라는 것은 어떤 것이 궁금해서 물어보는 것을 보통 일컫는다. 다만, 퀘스천(Question)의 경우에는 이런 것이라고 생각한다. "이 문제에 대한 답이 무엇인가?" 바로 "What ... ?"이다. 질문자가 잘 모르는 것을 물어보는 것이다. 이것이 무엇이냐? 난, 궁금하다. 모르는 것에 대한 이해를 요구하는게 퀘스천이다.

이에 반해서 쿼리라는 것은 일종의 "How to ... ?" 이다. "이 결과를 내기 위해서는 어떻게 해야하는가?"에 대한 답을 요구한다. 이것은 일종의 질문자의 요구되는 의도가 숨겨져 있다. 질문자는 결과를 알고 있고, 그 요구되는 결과를 내기 위해서 작업되어야 하는 방법을 알고자 하는 것이다.

이렇듯 쿼리를 작성하는 사람들은 요구되는 결과를 사전에 알고 있어야만 한다. 요구되는 결과가 어떤 형태가 될 것인지를 알지 못하고는 쿼리를 할 수 없다. 요구되는 결과를 미리 상상한다면, 그 다음은 이 요구되는 결과를 내기 위해서 수행해야할 방법을 선택해야 한다. 이것은 매우 단계적인 것이다.

최대한 조회 대상을 줄이면서, 가장 낮은 비용을 사용하여, 원하는 형태로 데이터를 가공하는 것. 그것이 바로 쿼리라고 정의할 수 있다.

또, 흔한 차이 중의 하나가 바로 프로그래밍(Programming)과 쿼리(Query)의 차이이다. 많은 사람들이 헷갈리지만, 쿼리와 프로그래밍은 근본 부터가 다르다. 프로그래밍이라는 것은 일전에도 이야기했듯이 "어떤 일을 하는 무엇인가를 만드는 작업"이다. 이것은 일종의 유기체적인 로봇과도 마찬가지이다. 이 로봇은 자신이 해야할 일이 무엇인지를 알고, 그 일을 수행하기 위해서 존재한다. 여기에는 시간상의 절차와 제어가 필요하다.

쿼리는 이러한 로봇에게 어떻게 일을 지시해서 효율적으로 처리할 지를 선택하는 것이다. 즉, 로봇이 일하게 만드는 것이지, 일하는 로봇을 만드는 작업은 아니라는 것이다.

<그림 5> 프로그램과 쿼리

Think Different

사고를 바꾸면 세상이 달라 보인다. 쿼리를 하는 사람들에도 발전의 단계가 있다면 아마도 이런 것일 것이다. 우선, 첫번째, SQL 문법대로 쿼리를 작성하는 수준이고, 두 번째는 쿼리 비용을 감안해서 효율적인 쿼리를 작성하는 수준일 것이고, 세 번째는 비지니스 모델에 맞추어 적절한 쿼리를 작성하는 수준일 것이다. 처음 단계에서는 쿼리 그 자체만을 볼 것이고, 두 번째 단계에서는 쿼리의 데이터를 볼 것이고, 세 번째에는 비지니스 환경을 고려하게 될 것이다.

자신이 어떤 생각을 가지고 있느냐에 따라서, 쿼리는 다르게 작성될 수 있다. 단순하게 처리할 수 있는 문제를 곧이곧대로 어렵게 처리할 수도 있고, 어려운 문제를 비지니스 자체를 다르게 해석함으로써 쉽게 처리할 수도 있다. 그것이 바로 쿼리가 가지는 매력이라고 할 수 있다.

하나의 벽을 깨기 위해서는 부단히 노력하고, 다른 세상을 맛보아야 된다. 그러지 못하면, 항상 그 나물에 그 밥인 세상을 살게 된다.

예를 한가지 들어보자. 그림 6과 같은 종류의 테이블이 있다고 가정해보자. 일종의 역정규화된 테이블이다. 이러한 테이블들을 실제로 많이 보았을 것이다. #SalesName 테이블에는 점포 아이디, 점포명, 그리고, 각 점포별 제고 개수가 저장되어 있다.

<그림 6> #SalesName 테이블

누군가 다음과 같은 질문을 여러분에게 던져본다고 가정하자.

"점포별로 물품 수량은 몇개나 되는가"

이건 쉬운 쿼리이다. 점포 아이디 별로 개수를 더하면 된다. 즉, 단순한 Group By와 Sum 함수를 이용하는 쿼리라는 것이다.

select stor_id, sum(qty) totalQty
from #SalesName
group by stor_id

<그림 7> 집계 쿼리 결과

하지만, 이것이 원하는 것은 아니다. 누구도 점포 아이디가 무엇을 의미하는지는 알지 못한다. 왜냐면, 점포 아이디라는 것은 오로지 각각의 사건들을 유일하게 구분하는 번호 이상의 의미가 아니기 때문이다. 우리가 이 결과를 해석하기 위해서는 각 점포 아이디가 무엇을 의미하는지 보여줘야만 알 수 있다.

이제야, 조금은 문제가 어렵게 느껴질 것이다. 해당 집계 데이터를 다시 해석하기 위해서, 우리는 해당 점포 아이디가 의미하는 점포 이름을 가져와야만 한다. 그러기 위해서, 우리는 상관 쿼리를 이용할 수도 있고, 외부 조인으로 점포 이름을 가지고 있는 테이블과 조인할 수도 있다.

select x.stor_id, y.stor_name, x.totalQty
from (
select stor_id, sum(qty) totalQty
from #SalesName
group by stor_id
)  x left outer join pubs.dbo.stores y
on x.stor_id = y.stor_id

이러한 작업들 많이 해보았을 것이다. 이런 경우는 숱하게 많았으니 말이다. 이것이 가장 정석적인 해결 방법이다. 혹은, 다음과 같은 방법도 이용할 수 있다.

select stor_id,stor_name,sum(qty)
from #SalesName
group by stor_id,stor_name

하지만, 두 번째 방법은 조금 더 쉽지만, 문제가 있을 수 있다. 만일 점포명 중에 동일한 아이디로 약간의 차이가 있는 데이터가 있다면 어쩔것인가? 두 번쨰는 아예, 집계키로써 점포명을 이용하였으므로, 별도의 데이터로 처리되어야 한다. 그럼, 점포 아이디가 7860인 데이터이면서 "(주)맥스무비" 점포는 198개, "맥스무비" 점포는 2개로 출력될 수도 있다. 사실은 "(주)맥스무비"로 200개의 재고인데 말이다.

어떻게, 외부 조인도 사용하지 않고, 집계키를 잘못 써서 데이터가 분리되어 출력되지 않도록 하는 방법은 없을까? 바로 있다. 다만, 이것을 알기 위해서는 생각이 유연해야만 하고, 단어를 다양하게 이해할 줄 알아야 한다. 아마, 쿼리를 보면 이게 뭐야? 라고 생각하는 독자들도 있을 수 있다.

짜잔... 정답은 MAX 함수를 이용하면 되는것이다. 훌륭하게 원하는 작업을 마무리짓게 한다.

select stor_id,max(stor_name) stor_name, sum(qty) totalQty
from #SalesName
group by stor_id

<그림 8> MAX 함수를 이용한 쿼리 질의

MAX 함수라는 것은 정말 쉬운 함수중에 하나이다. 이 함수 모르는 사람들은 없을 것이다. 어떤 의미를 가지는가? 아마도, 구간 범위에서 가장 큰 수치를 찾는 것일 것이다. 그렇게만, 이 함수의 범위를 한정한다면, 겉으로 들어난 단어의 뜻만을 해석하는 것이 될 것이다. 사실, MAX나 MIN 함수의 역할은 구간 범위에서 양 끝단의 노드를 선택하는 함수이다.

그림 9는 가장 일반적인 상황에서 MIN과 MAX 함수가 어떻게 노드를 선택하는 지를 보여주고 있다. MIN과 MAX 함수는 정렬된 데이터의 범위에서 양쪽 끝단을 각각 선택하고 이것은 숫자형식의 데이터 뿐만이 아니라, 문자 형식을 포함한 전체 스칼라 데이터 형식에 모두 반영되는 것이다.

<그림 9> 서로 다른 값의 범위를 가질떄의 MIN & MAX

그런데, 만일에 집계 범위의 데이터가 완전히 혹은 거의 동일하다고 인정된다면, MIN 혹은 MAX 함수는 양쪽 끝단의 데이터만을 선택하는 것이면서, 일종의 구간 안의 데이터를 선택해주는 역할을 수행할 수 있다. 그림 10이 그러한 경우의 범위 형태이다. 모든 값은 같다. 그렇다면 MIN과 MAX 함수가 선택한 값도 같다.

위의 개선된 쿼리에서 사용된 방법이 바로 이러한 MIN 혹은 MAX 함수를 이용하는 것이다. 역 정규화된 상태에서 점포명은 거의 동일할 것이다. 혹은 약간의 차이가 있더라도 크게 문제가 되지 않는다. 앞의 예에서 처럼 "(주)맥스무비"와 "맥스무비"는 의미상으로 동일한 것이니 말이다. "홍 길동"과 "홍길동"도 마찬가지이다. 그렇다면, 데이터 자체의 의미상으로는 동일한 구간값을 가진다고 볼 수 있다.

<그림 10> 동일 값 범위를 가지는 MIN & MAX 함수

동일한 구간값을 가지는 경우에 MAX 함수는 구간값을 보여줄 수 있다. 위의 쿼리에서 점포명은 어떤 것이 되어도 크게 문제없었다. 따라서 MAX 함수를 이용할 수 있었고, 이것은 성능면이나 쿼리 작성이나 모두 이득이다. 무엇이 필요했을까? 생각을 넓혔을 뿐이다.

다른 MAX 함수의 용도도 살펴보자. 그림 11에는 코끼리 옆에 개미가 한마리 붙어있다. 잘 보면, 엉덩이 쪽에 있다. 그림에서는 개미가 강조되어 상당히 크게 나왔지만, 실제라면 거의 점 하나 정도의 수준일 것이다. 개미가 이렇게 붙어있는 코끼리를 보았을때, 사람들은 이것을 "개미가 붙어있는 코끼리"라고 이야기하지는 않는다. 그냥 "코끼리"일 뿐이다. 개미는 눈에 들어오지 않는다.

그렇다고, 개미의 존재가 사라지는 것은 아니다. 다만, 무시될 뿐이다. 개미가 코끼리 엉덩이에서 떨어지면, 다시 개미는 개미이다. 비로소, 개미의 존재가 부각된다. 왠지, 너무 철학적인 이야기이지만, 이러한 개념을 쿼리에서 이용할 수 있다.

<그림 11> 코끼리와 개미

그림 12와 같은 문제를 해결한다고 보자. "각 도서 분류별로 연간 가장 많이 팔린 도서 번호"를 찾는 문제이다. 대상이 되는 테이블은 Pubs.dbo.titles 테이블이며, 이 테이블에는 도서별 연간 판매량이 기록되어있다.

<그림 12> 도서 분류별로 연간 가장 많이 팔린 도서?

일반적으로 이러한 쿼리는 Group By절로 풀이된다. 왜냐면, 집계가 있기 때문이다. 도서 분류별이라는 집계 기준이 명확하고, 이 중에서 가장 상위 노드(즉, 값이 가장 큰)것을 선택해야 하는 문제이다. 이까지 작업을 하면, 아마도 다음과 같은 쿼리가 될 것이다.

select Type,max(ytd_sales) max_ytd_sales
from pubs.dbo.titles
group by Type

<그림 13> 집계 연산

그러나, 이러한 방식으로는 가장 많이 판매된 양을 알 수는 있어도, 해당 판매량의 도서 번호는 알 수 없다. 굳이 원한다면, 외부 조인으로 다시 도서 번호를 확인해보는 수밖에는 없다.

select x.type,y.title_id,x.max_ytd_sales
from (
	select Type,max(ytd_sales) max_ytd_sales
	from pubs.dbo.titles
	group by Type
) as x left outer join pubs.dbo.titles as y
on x.type = y.type and x.max_ytd_sales = y.ytd_sales

   최종 수정일 : 2005년 3월 16일

하지만, 외부 조인을 사용하지 않고도 이를 해결할 수 있는 방법이 있다. 쿼리가 조금은 이해되지 않을 수도 있을 것이다. 이 쿼리는 MAX 함수가 메인이되어 사용되며, 후위 문자열을 잘라서 도서 번호를 찾고 있다.

select Type,
	substring(
		max(
			dbo.LPAD(ytd_sales,10,0) + title_id
		)
	,11,6) MaxSaledTitle,
	max(ytd_sales) Max_ytd_sales
from pubs.dbo.titles
group by type

우선, 여기에 사용된 LPad 함수에 대해서 보자. 이 함수는 우리가 흔히 주문 번호등을 따는 경우 숫자를 문자열로 만들어서 처리하는 경우가 많다. 1을 "00001"등으로 만드는 경우이다. 이러한 숫자열을 포맷화된 문자열로 만든다.

CREATE FUNCTION LPAD(
	@s varchar(255), @n int, @p varchar(255) 
) 
returns varchar(255)
as
BEGIN
return IsNULL(REPLICATE(@p,@n-LEN(@s)),'')+@s
END

해당 UDF에 대해서 이해하였다면, 다음의 쿼리를 수행한다. 숫자 형식인 판매량을 문자열로 변경하는 것이다. 숫치를 같은 자릿수의 문자열로 변경하여도 정렬 순서는 크게 변경되지 않는다. "10000가"와 "10001나"는 어떤 것이 MAX인가? 당연 "10001나"이다. 두 개의 크기 비교는 앞의 네 자리 "10000"과 "10001"이 중요한 팩터이지 "가"나 "나"가 중요한 역할을 하는 것이 아니다. 즉, 앞에 숫자가 코끼리고, 그 뒤에 붙는 것은 문자열은 개미라는 것이다.

select type,
	dbo.LPAD(ytd_sales,10,0) ytd_sales, 
	title_id 
from pubs.dbo.titles
order by type, ytd_sales desc, title_id

<그림 14> LPAD Order By

그래서, 아래 쿼리 처럼 양식화된 문자열에 도서 번호를 붙여서 정렬해도 정렬 순서는 바뀌지 않게 된다.

select type,
	dbo.LPAD(ytd_sales,10,0) + title_id  	ytd_sales_title_id
from pubs.dbo.titles
order by type, ytd_sales_title_id desc

<그림 15> 문자열 정렬

이제, 이렇게 붙어진 것에서 MAX 값을 취하고, 그 값에서 정해진 뒷 자리를 분리해놓으면, 원래의 값을 찾아내게 된다. 두 쿼리의 실행 계획을 살펴보자. 외부 조인을 사용하는 쿼리는 MAX 함수를 사용하는 쿼리에 비교해서 두 배 이상의 비용이 소모된다. 즉, 그만큼이나 MAX 함수를 사용하는 것이 좋을 수 있다는 것이다. 물론, 인덱스가 어떻게 고려되었느냐에 따라서 다르겠지만 말이다.

<그림 16> 두 쿼리의 실행 계획 비교

T-SQL 기본 테크닉

SQL을 공부하는 데에 여러 가지 방법들이 있다지만, 그래도 몇 가지 기본 적인 사항들을 알고 시작하는 것이 자기 발전에 도움이 된다. 이러한 방법들 중에는 정말 별것 아닌 내용들도 있고, 자신이 잘 모르고 있던 내용들도 있을 것이다. 중요한 것은 실무에 이러한 내용들을 적용해 본 적이 있는가 하는 것이다. 내용만 알고 써먹지 않는다면, 올바른 지식 활용 습관(?)이 아니다. 캬캬캬.

우선 첫번쨰로, 사전 테이블(Dictionary table)의 이용에 대해서 생각해보자. 데이터베이스는 집합을 처리하는 게 주목적이다. 집합이 없을 때는 만들어서 처리할 수도 없다. 테이블이 존재하지 않는다고, 그냥 있는 것만 쓰지 마라. 새로운 것을 만들어서 처리하는 능력이 필요하다.

그림 17과 같이 Date_sales라는 테이블이 있다고 가정하자. PDate 컬럼에는 일자가 문자열로 저장되어 있다.

<그림 17> Date_Sales 테이블

이 테이블에서 1998년에 요일별 판매량을 집계한다고 가정해보자. 그럼 보통 어떻게 처리할까? 아마도 문자열 컬럼인 PDate를 Datetime 형식으로 변경하고, 변형된 컬럼을 기준으로 요일을 뽑아서 집계해야 할것이다. PDate 컬럼에 인덱스가 있다고 하더라도 집계시에는 사용할 수 없고 재가공해야만 한다.

select  
datepart(
	weekday,
		cast(
			substring(pdate,1,4) + '-' + 
			substring(pdate,5,2) + '-' + 
			substring(pdate,7,2) 
		as smalldatetime)
) 요일,
avg(sales) 평균판매량
from date_sales
where pdate between '19980101' and '19981231'
group by datepart(weekday,cast(substring(pdate,1,4) + '-' + substring(pdate,5,2) + '-' + substring(pdate,7,2) as smalldatetime))
order by 1

이것을 있는 그대로만으로 해결할려고 하면 답이 없다. 하지만, 별도의 집합을 만들어서 두 집합 사이의 교집합을 구하면 쉽게 해결할 수 있다. 게다가, 컬럼의 변형이 없으므로 인덱스도 제대로 이용할 수 있다. 여기서 사용되는 별도의 집합은 날짜 사전 테이블이다. 이 테이블의 샘플 데이터가 그림 18에 나와있다.

<그림 18> DIC_Date 테이블

새로운 집합이 만들어졌으며로, 보다 편하게 쿼리할 수 있다. Dic_date 테이블에는 요일이 저장된 컬럼이 있으로로 굳이 Date_sales 테이블의 Pdate 컬럼을 변형할 필요가 없다.

select b.pweek 요일
,avg(a.sales) 평균판매량
from date_sales a inner join Dic_date b
on a.pdate = b.pdate
and a.pdate between '19980101' and '19981231'
group by b.pweek
order by 1

외부 조인을 사용한 쿼리와 별도 사전 테이블의 실행 계획을 비교해보면, 사전 테이블을 사용한 쿼리가 절반 이상 빠른 것을 알 수 있다. 모집단의 크기가 커질수록 이런 경향은 더 가속화된다.

<그림 19> 두 실행 계획 비교

두 번째, 쿼리 테크닉은 존재 하지 않는 데이터를 생성하는 인라인 메모리 뷰(Inline memory view)를 이용하는 것이다. 존재 하지 않는 데이터는 컬럼이 여러 개인 것과 여러 로우로 구성되는 경우 등이 있다. 여러 컬럼인 것은 "select '남자' c1, '여자' c2"과 같이 특정 값에 컬럼 명을 붙이는 경우이고, 여러 로우로 구성되는 경우는 UNION ALL을 사용하는 "select 1 num union all select 2 union all select 3"과 같은 쿼리이다. 물론, 이 두가지를 복합해서 여러 컬럼과 여러 로우로 구성되게 쿼리를 작성할 수도 있다.

세 번째는 CASE WHEN 문장을 쿼리에 삽입하는 것이다. 이 문장은 SQL 서버에서 쿼리의 동적인 힘을 불어넣어준다. 이 동적인 힘의 SQL의 여러 부분에서 사용되는 것이 가능하다. 컬럼에서도 가능하고, Where 조건절이나 Group by 절에서도 이용할 수 있다. 다음 쿼리는 컬럼에 CASE WHEN을 사용한 예이다.

select (
	case type 
	when 'UNDECIDED' then 'business' 
	else type end),
	sum(ytd_sales)
from pubs.dbo.titles
group by (
	case type 
	when 'UNDECIDED' then 'business' 
	else type end)

두번째와 세번째는 다른 기법들과 같이 사용되는 경우가 많다. 네 번쨰는 데이터를 복제하여 사용하는 방식이다. 집합 복제의 기능에는 대략 세 가지가 있다. 요소의 복제, 존재하지 않는 데이터를 발생, 집합을 여러 차원으로 복하는 기능이다.

<그림 20> 집합 복제의 주요 기능들

요소의 복사라는 것은 집합과 하나의 원소를 가지는 값간에 크로스 조인이 작용될때 발생하는 것이다. 집합 2의 원소는 집합 1에 모두 복제되게 된다.

<그림 21> 요소의 복사

예를 들어 다음과 같은 쿼리가 있다고 가정하자. 이 쿼리에서 동일한 역할을 수행하는 두 개의 서브 쿼리가 있다. 바로 Pubs.dbo.sales 테이블의 평균을 계산하는 쿼리이다. 이 값이 필요할 때마다, 이 값을 서브 쿼리해와야만 한다.

select title_id,sum(qty) title_qty,
  (select avg(qty) avgQty from pubs.dbo.sales) avgQty,
  (sum(qty)-(select avg(qty) avgQty from pubs.dbo.sales)) calcQty
from pubs.dbo.sales
group by title_id

하지만, 요소 복제를 이용하면 이것을 쉽게 해결할 수 있다. 밑의 쿼리가 이것이다. 하나의 원소를 가지는 서브 쿼리와의 크로스 조인으로 집합 1은 이 값을 원하는 만큼 사용할 수 있다.

select title_id,title_qty,avgQty,title_qty-avgQty as calcQty
from 
(
 select title_id,sum(qty) title_qty
 from pubs.dbo.sales
 group by title_id
) as a,
(
 select avg(qty) avgQty from pubs.dbo.sales
) as b

이것은 성능면에서도 매우 좋은 방식이라고 할 수 있다. 쿼리 1은 서브 쿼리를 사용할 때마다 그만큼의 I/O 비용이 들게 된다. 하지만, 두번째 방식은 그럴 필요가 없다. 비용면에서도 59.36% : 40.64%로 훨씬 이익이다.

<그림 22> 두 쿼리의 비교 - 요소복제

하나 이상의 로우들로 구성된 집합과의 크로스 조인은 로우수만큼 집합 1을 복제하게 된다. 이러한 집합 복제가 가지는 의미는 존재하지 않는 데이터를 발생시키는 것이 가능하다는 것이다.

<그림 23> 존재하지 않는 데이터의 발생

예를 들어서 다음과 같은 집계 결과가 있다고 보자. 지역별로 통계를 내는 것이다. 집계라는 작업은 기본적으로 사실만을 기준으로 하는 것이고, 존재하는 데이터만을 기준으로 하는 것이다. 하지만, 현실이라는 것은 그렇지 않다. 존재하지 않는 데이터도 있어야할 필요가 있다. 쿼리 결과엣 경기도에는 남자는 118명으로 집계되어 있지만, 여성에 대한 데이터는 없다. 실존하지 않기 때문이다. 하지만, 많은 경우 여성은 0명이라는 데이터라도 있었으면 한다. 이것을 어떻게 처리할 것이냐? 일반적으로 이런 경우에는 절차적인 방식을 사용하는 것이 기본이다. 하지만, 쿼리로도 이러한 작업이 가능하다.

select area,gender,sum(something) total
from CustomerArea
group by area,gender

area       gender total       
---------- ------ ----------- 
경기         남      118
서울         남      100
부산         여      150
서울         여      120
청주         여      200

다음의 결과를 보면, 존재하지 않는 데이터를 발생시켜서 집계를 하고 있다. 이것이 하나 이상의 로우들로 구성된 집합과의 크로스 조인 효과이다.

<그림 24> 존재하지 않는 데이터의 발생

그날 세미나에서도 이야기했지만, 필자 스스로에게 이런 세미나는 거의 2년만이었다. 세미나 장에서 많은 SQLER 여러분들을 만났고, 100%는 아니지만, 하고 싶은 이야기도 모두 다했다. 다만, 좀더 액션이 있었다면, 뒤에서 졸고 계신 분들을 줄일수 있었을텐데.ㅋㅋㅋ. 모쪼록 좋은 기회를 주셨던 모든 분들과, 날씨 과히 좋지 않았는데, 참여해주셨던 많은 분들께 정.말. 감.사.합.니.다.

끝으로 DEEP INSIDE SQL SERVER 2000에 관련되어 질문하고자 하시는 분이 있으면, 필자의 전자 메일 주소로 [SQLMAG]라는 말머리를 붙여서 메일을 보내기 바란다. 메일 주소는 역시 다음과 같다.

kind511@dreamwiz.com

   최종 수정일 : 2005년 3월 16일