쿼리 테크닉 - 2부

글 손호성

하지만, 외부 조인을 사용하지 않고도 이를 해결할 수 있는 방법이 있다. 쿼리가 조금은 이해되지 않을 수도 있을 것이다. 이 쿼리는 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일