막장개발자의 블로그

Update문을 Static SQL로 구성하는 것도 쉬운 일은 아니다. 아이디어를 알고 보면 별 거 아니지만, 아무 것도 없는 상황에서 그런 아이디어를 생각하는 것은 솔직히 어렵다. 이 글도 물론-_- 필자의 아이디어는 아니다.

Update문을 Static SQL로 구성하기 어려운 것은 Parameter Binding을 사용하기 어렵기 때문이다. 보통 Update는 전체 Column에 이루어지지 않고 달라진 하나 또는 두개의 Column에만 이루어 지기 때문이다.
즉, 아래와 같은 방식은 통하지 않는다.

그렇다고 Dynamic SQL이 정당화 될 수는 없지 않겠는가? 바로 앞 포스트에서 알아 본 Case When문과 약간의 Idea를 이용하면 간단하게 해결할 수 있다.

• Update 할 내용이 없는 경우는 원래 값으로 Update를 해준다.
• Update할 내용이 있는지 없는지 여부는 Varchar2계열이라면 Length를 Check하면 되고, 숫자라면 전혀 올 수 없는 값을 주면 될 것이다.
• 미리 정해진 값이 있다면 입력값에 따라 처리할 수 있다.(ex : sysdate)
  

이제 Idea를 조합해보자.

알고 보면 간단한 Update문의 Static SQL 처리였다.

Case When 문의 의미
Case When문이나 Decode함수를 사용하여 Select절의 결과물을 원하는 대로 변경하는 SQL은 여러 번 본 적이 있다.
하지만 주변 사람들과 이야기해보면 의외로 Select절 뿐만 아니라 Where절, Order By절, Group By절 등에 다 사용할 수 있다는 사실을 모르는 경우가 있었는데 오늘은 이런 사실에 대해 이야기해보고자 한다.

Case When문의 활용범위는 무궁무진하며, 이 것을 사용하지 않고 Static한 SQL을 사용한다는 것이 필자로서는 거의 불가능하게 느껴진다. 앞으로의 포스트에서도 줄기차게 사용할 것이므로 관심이 있으신 분들은 여러 활용방법을 제대로 알아두면 좋을 것 같다.


Case When문
간단하게는 Case When문은 Decode함수의 확장이다.
Decode함수는 Oracle에서 제공되는 SQL 내의 if/else의 조건문 역할을 하는 함수이나 = 연산만 가능하다. Case When문은 이를 확장한 것이며, 논리연산, 산술연산, 관계 연산을 다 지원하며 Oracle 뿐만 아니라 MS-SQL에서도 지원한다.
(예전에 듣기로는 My-SQL에서도 지원한다고 들은 적이 있다. Test해보지는 않았지만.)
복잡한 조건일 수록 Case When문은 Decode문에 비해 가독성이 높고, 수행속도도 빠르다고 할 수 있겠다.

사용하는 문법은 다음과 같다.

• case when [조건1] then [결과1] when [조건2] then?[결과2] ...else [결과3] end
• case [컬럼1] when [값1] then [결과1] when [값2] then [결과2] …  else [결과 3] end

Select 절에서의 활용
실제로 가장 많이 사용되는 활용은 Select절에서일 것이다. 실제 우리가 가지고 있는 Data로부터 우리가 원한 Data를 뽑아내는데 자주 사용된다.

이 SQL은 당연히 다음과 같이도 사용가능하다.

지역이라는 Column이 없기 때문에 수도권인지 아닌지를 가져오기 위해 위와 같은 SQL을 사용하였다.
(왜 수도권에 '인천'은 없는지에 대해서 궁금해하는 사람은 없기 바란다. PT하다보면 가끔 그런 사람을 만날 수 있는데 정말 한숨만 나온다-_- 집중해야 할 곳에 집중하자.)


Group By 절에서의 활용
사례를 살펴보기 위해서 Cartesian Product(카테시안 곱) 항목에서 예로 들었던 SQL을 다시 가져와 보자.

복제된 Data에서 no1이 1인 것은 품목으로 Group by를 하였고, 2인 것은 "합계" 즉 상수로 Group by를 하였다. 따라서, 1인 경우는 품목별로 합계를 구하게 되고, 나머지 항목들은 전체 Group by가 되게 될 것이다.


Order By 절에서의 활용
회사 직원을 이름 순으로 뽑아오는 쿼리가 있다고 하자. 어느 날 직원 시스템을 한 번도 써보지 않았던 사장님이 시스템에 들어가보니 본인 이름이 중간에 있는 것을 발견하고 명색이 사장님인데 직원들 순서 중에서 내가 제일 앞에 나와야 하지 않는가 하는 의문을 제기하였다.

그런-_- 이유로 고쳐야할 시스템의 요구사항은 다음과 같다.
사장님의 이름만 맨 앞으로 뽑아내고 나머지는 가나다 순으로 정렬하면 되는 것이다. 물론 일단 Data를 Client로 다 가져온 다음 프로그램 상에서 처리할 수도 있겠으나, 나중에 이 사실을 알게 된 이사님도 이의를 제기한다면? 다른 관리자들은? 이런 요구사항들을 다 Client에서 처리한다면 프로그램은 갈수록 복잡해지게 될 것이다.

따라서, 그런 요구사항의 변경은 SQL에서 적용하는 것이 가장 간단할 것이며, 이 것이 우리가 추구하고자 하는 Static SQL의 본질이다. (Static SQL이란 원하는 집합을 하나의 SQL로 뽑아내는 것을 말하며 이 문제영역에서는 사장님이 맨 앞에 나오고 다른 직원들은 이름순으로 정렬된 집합이 우리가 원하는 집합이다. 따라서, 그것을 하나의 SQL로 얻어와야 한다.)

Order by절에서는 오름차순이건, 내림차순이건 사장님의 이름만 맨 앞으로 뽑아올 수 없으나, Case When문을 사용하면 간단하다.



Where절에서의 활용
외부에서 들어온 조건(:v1)에 따라 서로 다른 동작을 정의하고자 할 때 사용할 수 있다.

검색할 이름에 대한 입력이 :v1에 들어오면 이름을 검색하여 결과를 보여주고 입력이 없으면 전체 직원의 이름을 Return하는 SQL이다. Where절을 동적으로 조합하지 않고 SQL 내부에서도 이러한 방법을 통해 서로 다른 결과값을 Return할 수 있다.


결론
SQL을 작성하는 여러가지 방법을 배우는 것은 문제에 따라 다양한 Idea를 내기 위한 필요조건이다. Client에서 혹은 PL/SQL에서의 if/else를 상당부분 SQL로 옮겨올 수 있는 Case When문을 익혀두는 것은 그 중에서도 첫 걸음이 될 수 있을 것이다.

Cartesian Product란
Query의 From절에 2개 이상의 Table이 있고, 두 Table 사이의 유효한 Join 조건이 기술되어 있지 않은 경우에는 두 Table의 모든 행들이 무조건 결합하여 Table들에 존재하는 행 갯수를 곱한 만큼의 결과값이 반환되는 것을 Cartesian Product(카테시안 곱)라고 한다.

말로 써놓으니 어려운 듯 하지만, 실제로는 크게 어려운 개념이 아니다.
아래의 예를 보자. 각 품목별 수량의 합계와 전체합계를 한꺼번에 구하는 Static SQL이다.


tbl1의 결과값과 tbl2의 결과값 사이에 유효한 Join 조건이 기술되지 않았으므로 Cartesian Product가 일어나게 되며 위 그림은 이 과정을 거친 중간 결과집합을 보여주고 있다. 이 중간집합을 Group By하여 품목별 합계와 전체합계를 한 번에 구할 수 있는 것이다. 결과가 궁금하신 분들은 실제로 한 번 Test해보셔도 좋겠다.
(한가지 부언하자면, Oracle도 이전 Version에서는 Sort Group By를 채택하여 전체 합계가 맨 밑에 나왔지만, Hash Group By 실행계획으로 풀린다면 품목별 합계와 전체 합계의 순서가 뒤섞여 나오게 될 것이다. 이런 경우에는 Order By절을 추가하여 원하는 순서대로 바로 잡아주도록 하자^[각주:1])

이러한 Cartesian Product는 Static한 SQL을 사용하기 위해 우리가 사용할 수 있는 중요한 무기 중 하나이며, 이전 포스트에서도 사용자의 입력을 여러 개로 구분하기 위해 Cartesian Product를 사용한 바 있다. 개념을 잘 이해하여 중요한 순간에 쓸 수 있도록 우리의 무기창고에 잘 넣어두도록 하자.

다음 포스트에서는 오늘 Query에서도 쓰였던 Case When문에 대해 써보도록 하겠다.

원래 Static SQL이란 카테고리가 만들어 진 것보다 이 포스트를 작성한 게 먼저였으나, 쓰고보니 달랑 이 글만 올려놓기 보다는 Static SQL에 대해서 한 번 정리해보고자 하는 생각이 들어 한동안 비공개로 잠들어있던 비운의(?) 포스트이다.

Static한 SQL을 구사하는 데에 가끔 까다롭게 느껴지는 것이 사용자 선택이나 입력사항을 Static하게 구성하는 것이다.
예를 들어, 사용자가 A,B,C 항목을 선택했다고 할 때

Select column1, column2, column3
From Table1
Where column1 in ('A', 'B', 'C')
와 같은 SQL을 작성해야 한다고 하자.

사용자가 몇 개를 선택할지 알 수 없기 때문에 아래와 같은 Binding Parameter처리가 불가능한 것은 당연하다.
Select column1, column2, column3
From Table1
Where column1 in (:v1, :v2, :v3)


이런 경우 Dynamic SQL을 작성하는 경우가 많았고, 뭔가 '이건 아닌데..' 하면서도 다른 대안이 없었다.  
그러던 중,  데브피아에서 박정진(bleujin)님의 글을 읽고 방법을 찾았는데 Cartesian Product와 사용자 정의 함수를 이용하여 멋지게 문제를 해결한 방법이었다. 이 자리를 빌어 다시 한번 감사드린다.
시간이 되시는 분들은 링크를 타고 원문을 한번 읽어보시기 바라며, 강좌와 팁란에서 이름으로 검색하시면 주옥같은 글들을 더 보실 수 있을 것이다.

그동안 잘 써먹었었는데, 최근 oracle용으로 이 함수가 필요해 찾아보니 예전에 작성했던 건 Backup을 안해놨고 어디 있는지 찾기도 귀찮아 그냥 다시 작성했고 나중에 또 못찾을까봐-_- 여기 올려두기로 했다.
v_FullString : 전체 문자열(‘a|b|c|d|’)
v_DivString : 구분자(’|’)
v_Index : 몇 번째 내용(항목)을 가지고 올 것인가

oracle에서 getIndexString 함수를 이용하여 변하는 사용자 입력값을 Static SQL로 변환하는 방법은 다음와 같다.

간단히 설명하자면, copy_t는 복제용 Table이며, 보통 1~31까지의 값을 갖는 Table이다.
In 절 내의 SQL에서 Where 조건에 유효한 Join 조건이 기술되지 않았으므로 tbl1과 tbl2의 곱만큼 복제(Cartesian Product)가 일어나게 되고 이 복제된 Row들이 getIndexString을 거치면서 원하는 값들을 가진 Row로 가공되어 In 절에 공급되게 된다.

Where tbl2.no1 <=  (Length(tbl1.fullStr) - Length(replace(tbl1.fullStr, tbl1.divStr, ‘’)) / Length(tbl1.divStr)) 조건은
Where tbl2.no1 <=  (Length(‘a|b|c|d|e|f|’) –Legnth(‘abcdef’) / Length(‘|’)) 과 같으므로
결국 tbl2.no1 <= 6(들어온 입력값의 갯수)가 되고, 따라서 들어온 입력값만큼 복제가 일어나게 된다.

getIndexString 사용자정의 함수는 no1의 값에 따라 전체문제열에서 구분된 항목을 뽑아오는 함수이므로 a, b, c, d, e, f가 In절에 공급되게 된다.

너무 깔끔하지 않은가? Static한 SQL에 한번 맛을 들이고 나면 실력이 모자라 어쩔 수 없이 Dynamic SQL을 사용하게 되는 경우 웬지모를 죄책감을 가지게 되며, 끊임없이 해결방법을 찾아 헤메이게 될 것이다.

Static SQL에 대한 연재를 하자면 왜 Static SQL을 사용하여야 하는가에 대한 설명이 있어야하지 않겠는가?
어쩔 수 없이 글을 길게 쓰게 됬지만 긴 글을 읽기 싫은 분들은 결론만 읽으셔도 되겠다.

Static SQL과 Dynamic SQL
DBMS는(정확히 말해서는 Optimizer는) 다음과 같은 SQL을 다 다른 SQL로 처리하며 이를 Dynamic SQL이라고 한다. 
반면, Static SQL은 위와 같은 SQL을 다음과 같이 작성한다.
Select * From Table1 Where Column1 = :v1
:v1과 같은 방식을 Parameter Binding이라고 하며 변하는 부분을 변수처리한 것이라고 생각하면 되겠다.
Parameter Binding == Static SQL은 아니지만 오늘 이야기할 내용은 그렇게 이해해도 충분하리라 생각한다.


SQL의 수행과정
SQL은 어떤 단계를 거쳐 수행되는 것일까?
물론 SQL문은 원하는 집합을 규정하는 언어일 뿐, 처리 방법을 기술하는 언어는 아니다. 하지만 실제로는 수행되는 절차가 있는데 이에 대해서 한번 알아보자.

Oracle기준이며 DML문과 DDL문은 수행방법이 약간 다른데 Select문과 같은 DML문에 대해서만 언급하도록 하겠다.
1. 파싱(구문/의미검사)
    1) 구문검사 : SQL 문법에 맞는가? 예를 들어 Select를 Selct라고 오타가 난 경우 여기서 Error를 발생한다.
    2) 의미검사 : SQL이 실행가능한가? 컬럼명이나 테이블명이 명확하고 애매함이 없는가?
    3) 공유풀검사 : 이미 실행계획이 세워져 수행된 Query인지 공유풀(Shared Pool)을 검사하고 세워진 실행계획이 있다면 
                         이를 재사용하여 실행전단계까지 건너뛴다.(소프트파스)

2. 실행계획 수립
실행계획이란 SQL을 수행할 순서를 말한다. Table1과 Table2가 Join을 하고 있다면 Nested Loop Join방식으로 처리할 것인지 Sort Merge Join으로 처리할지 Hash Join일지를 결정한다.
또한, Nested Loop Join이라면 Table1을 먼저 Driving(읽을)할 것인지 어떤 Index를 사용할 것인지... 등등을 결정하게 된다.
실행계획은 Optimizer가 세우게 되는데 그 Optimizer가 RBO(Rule Based Optimizer)인지 CBO(Cost Based Optimizer)인지에 따라 실행계획을 세우는 방법은 다르지만 어쨋든 실행계획은 이 단계에서 수립된다.

잘 이해가 안된다면 Optimizer가 실행계획이란 것을 세우고, 그 계획에 따라 SQL이 처리된다는 것만 이해하고 넘어가자. 여기서 설명하고자 하는 요점은 바로 그 것이다.

3.  Parameter Binding 및 실행
Parameter 처리된 실제 값을 적용하고 SQL을 실행한다.


실행계획 수립의 비용
이 실행계획 수립은 비용이 많이 드는 연산이다. 이 단계를 건너뛸 수 있다면 우리는 상당한 시간을 절약할 수 있을 것이다.
또한 하나의 실행계획을 세우기 위해서는 공유풀에 접근하여 현재 사용가능한 Index가 어떤 것이 있는지 Column의 분포도가 어떠한지를 살펴보아야 한다. 하지만 이러한 구조을 살펴보기 위해서는 이 구조에 대한 다른 접근을 막아야 한다. Index를 타는 것이 타당한지 살펴보고 있는데 다른 사용자가 Index를 지운다고 생각해보자. 당연히 문제가 발생하지 않겠는가.
따라서 이런 구조에 대한 접근은 직렬화되며 따라서 실행계획을 수립하기 위한 SQL이 많다면 자신의 차례가 올때까지 기다려야 한다.


실행계획 확인의 비용
어느날 DBA가 어떤 Table에 하나의 Index를 더 만들겠다고 선언했다. Optimizer는 새로운 Index가 생겼으므로 기존의 실행계획을 다시 검토해볼 수 있다. 따라서, 우리는 그 Index가 그 Table을 접근하는 SQL의 실행계획에 영향을 주지 않는지 검토해보아야 할 것이다. 하지만, Dynamic SQL을 사용한 경우에는 확인 자체도 그리 만만하지 않다.
예를 들어 Where절을 동적으로 결합하기 위한 if/else 문이 2개가 있다고 하면 그 경우의 수는 4가지이며 그 4가지 경로에 대한 실행계획의 변경을 재검토해보아야 할 것이다.(3개라면 경우의 수는 8개이다.)
하지만 Static SQL은 하나의 SQL로 모든 것을 처리하므로 하나의 SQL에 대한 실행계획만 검토해보면 된다.


Parameter Binding과 SQL Injection
Parameter Binding 방식은 SQL Injection을 방지하는 한가지 방법이다. SQL Injection이란 Query에 SQL을 주입하여 원하지 않는 행동(Admin 권한 획득 혹은 Table Drop 등)을 할 수 있는 악의적인 보안 Issue 중 하나이다.
SQL Injection만으로도 엄청난 주제이니 관련 사항은 인터넷을 찾아보기 바란다.


왜 Static SQL인가
공유풀을 적절히 활용하고, 보안문제도 손쉽게 해결할 수 있으며 Debugging 혹은 실행계획 검토도 쉽게 만들어줄 수 있는 방식이다. 이 방법을 몰랐다면 모르지만 알면서도 사용하지 않는다면 꿈에 Optimizer가 나타나서 괴롭힐지도 모른다.-_-
Dynamic SQL은 Toad와 같은 DB 개발 Tool에서는 사용해도 무방한 방식이지만 프로그램에 의해 자주 사용되는 SQL이 Dynamic SQL이어서는 안될 것이다.

물론 단점도 있다.
처음 예로 든 SQL은 누가 봐도 이해할 만하지만 알다시피 현실세계의 문제들은 그리 간단하지 않다. SQL이 복잡해지면 질수록 Static한 SQL은 더더욱 복잡해진다. 따라서 일부 사람들이 가독성의 문제를 제기하기도 한다. (필자의 경우에는 if/then/else를 사용한 SQL이 더 가독성이 없다고 생각한다.) 가독성에 대한 문제제기를 받았다면 이 포스트를 참고하라.
또, CBO는 Parameter Binding된 변수에 대해 통계정보를 활용하지 못한다. 이에 대해서는 이후에 다시 설명하게 될 것이다.

하나 하나의 주제만 해도 수십페이지는 될 내용을 한 번에  요약하였으니, 잘 이해가 안 갈만도 하다. 이해가 안가는 부분이나 용어는 인터넷에 많은 자료들이 있으니 한번 찾아보기 바란다.
무책임해서 미안하다.-_-

다음 포스트부터는 이제 본격적으로 Static SQL을 뒤집어보자.

Static한 SQL란 무엇일까?
Static SQL란 Dynamic SQL에 반대되는 개념으로서 원하는 집합^[각주:1]을 얻어오는 데 필요한 Query를 하나의 SQL문으로 작성하는 것을 말한다. 즉, 프로그래밍 언어나 PL/SQL을 이용해 동적으로 생성하지 않는 것이며, if/then/else 와 문자열 합성을 사용하지 않는 것을 말한다.

개념은 간단하나 알아야 할 내용은 생각보다 많다. 하지만 조금씩 알아갈수록 뭔가 가슴 뿌듯해지는 주제이기도 하다.
요즘 사용하고 있는 DB가 Oracle이라 Oracle 위주로 적어나가겠지만, 타 DB에서도 개념적으로 크게 다른 부분은 없으리라 생각한다.

그럴 듯하게 적어놨지만, 필자도 쌩초보에서 그냥 초보 사이를 왔다갔다 하는 수준이라 삽질이 좀 필요할 것이다. 워낙 천성이 게으른지라 연재의 마무리가 언제일지는 아무도 모른다.

어쨋든!!
이 카테고리에서는 Static Query를 위한 개념, 기법, 사례를 중심으로 기술할 예정이다.