Science of Database

▶ 더미 테이블을 사용해서 장애를 만나는 경우

▶ 더미 테이블을 사용하는 이유

▶ 더미 테이블을 사용하지 않는 방법

 

포장마차에서 지인에게 재미있는 이야기를 들었다. 물론 공장 이야기 이다. 나는 이야기를 재미있게 들었지만, 지인의 입장에서는 머리가 쭈뼛쭈뼛 서는 심각한 일이었다. 사건은 2011년 겨울에 시작된다.

 

2011년 1월 1일 이른 아침, 갑자기 잘 돌아가던 시스템에 몇몇 프로그램들이 작동하지 않는 장애를 만났다. Y2K 버그도 아니고 2011년 1월 1일에 장애라니? 서버와 네트워크 그리고 Database는 정상이므로 관심의 화살은 개발팀으로 집중되었다. 개발팀에서 장애 프로그램을 조사해보니 지난 한 달간 프로그램 수정이 없다고 하였다. 결국 모든 것이 정상인데 프로그램만 돌아가지 않는 상황이다. 귀신이 곡할 노릇이 아닌가? 빨리 정상적인 서비스를 해야 하므로 1분, 1초가 아쉬운 시점이었다. 모두들 땀을 흘리며 원인을 찾고 있었다. 프로그램 담당자는 장애를 일으킨 사람을 찾으면 죽여버리겠다고 소리쳤다.

 

여러분은 이런 장애에서 안전한가?

다행히 오래 걸리지 않고 원인을 찾았다. 돌아가지 않는 프로그램들의 공통점은 더미테이블을 사용한다는 것이었다. 즉 Copy_ymd를 사용한 것이다. 그 테이블을 조사해보니 일자가 2010년 까지만 들어가 있었다. 그래서 2011년이 되자마자 장애가 발생한 것이었다. 다시 말해, Copy_ymd 테이블에 2011년 데이터가 없으므로, 이 테이블과 조인하면 한 건도 나오지 않는 것이다. 생각해보니, 모든 시스템에 이런 일이 발생할 수 있다. 이야기를 듣는 필자의 간담이 갑자기 서늘해진다.

 

시스템을 구축한 업체에게 항의하려고 문서를 찾아보니 2001년에 Open한 시스템으로 2001년 기준으로 미래의 일자를 10년치 넣어 놓았다. 소프트웨어의 라이프 사이클을 고려한다면, 10년이면 충분하다고 생각했을 것이다. 하지만 운이 없게도 차세대 프로젝트를 하지 않고 10년간 유지보수를 하면서 사용한 것이다. 그리고 인수인계서에 2011년이 되기 전에 몇 년치의 데이터를 더 넣어놓으라고 명시되어 있었다. 시스템을 구축한 업체에게 항의할 수 도 없는 일이었다. 인수인계서를 보는 사람이 한 명이라도 있었을까?

 

왜 더미 테이블을 사용할까?

데이터베이스에 관심이 있는 개발자라면 Copy_ymd, Copy_ym, Copy_y, Copy_t 등 네 개의 더미테이블을 알 것이다. 많은 시스템에 이런 더미 테이블들이 있다. 과거에는 이런 테이블들을 사용해야만 했다. 하지만 2011년의 시점에서 새로운 프로젝트를 할 때 이런 테이블들이 필요할까? 필요한지 아닌지를 알려면 먼저 더미테이블의 용도를 알아야 한다. 이 테이블들의 용도 중에서 대표적인 것은 아래와 같이 세 가지로 볼 수 있다.

 

1. Copy: 같은 집합을 여러 번 복제하여 원하는 결과집합을 구한다.

2. 데이터 체크: 일자의 경우 입력된 값이 올바른지 확인한다. 예를 들면, 2월 30일은 잘못된 일자이다.

3. 인덱스의 효율적 사용: 인덱스의 첫 번째 컬럼 혹은 중간 컬럼이 Where 조건에 사용되지 않을 때 더미 테이블을 이용하여 IN으로 공급해주면 인덱스를 효율적으로 사용할 수 있다.

 

물론, 다른 용도로 더미테이블을 사용할 수 도 있지만, 대부분은 위의 세가지 경우 때문에 더미테이블이 필요하다. 가끔 기준일자를 관리하는 테이블을 볼 수 있는데, 이것은 더미테이블이 아니라 business에 필요한 것이다. 더미테이블은 업무적인 것이 아니라, 성능적인 관점, 혹은 관리적인 목적으로 사용되는 것이다. 업무적인 데이터가 없으므로 차세대 시스템을 구축할 때 더미 테이블은 분석 대상에서 빠져도 된다. 이런 이유 때문에 모델러들도 더미테이블을 중요하게 생각하지 않는다.

 

더미 테이블의 단점

위의 세 가지를 더미 테이블을 사용하지 않고 처리할 수 있다면 굳이 사용할 필요는 없다. 왜냐하면 아래와 같은 단점이 있기 때문이다.

 

첫 번째, 더미 테이블이라고 해도 시스템 속성을 추가해야만 한다. 시스템 속성이란 입력자, 입력일시, 수정자, 수정일시 등을 의미한다. 모든 테이블에 이런 컬럼들이 4개 ~ 6개 정도 존재한다. 많은 기업들이 메타시스템을 사용하고 있다. 메타시스템에 테이블에 시스템 속성이 없으면 등록할 수가 없는 경우가 많다. 심지어 자동으로 시스템속성을 추가하는 메타시스템도 있다.

 

그런데 더미테이블은 튜닝의 목적이 있으므로 매우 가벼워야 한다. 생각해보라. Copy_t에 존재하는 숫자컬럼의 length는 3 byte에 불과한데 시스템 속성 네 개가 48 byte를 차지한다. 3 byte를 위해서 건건이 48 byte를 낭비해야 한다. 테이블이 무거워 질 수 밖에 없다. 더미 테이블은 메타시스템으로 관리하지 말고 엑셀로 관리하면 된다고? 왜 추가적인 관리를 해야만 하는가?

 

두 번째, 누가 더미 테이블을 중요하게 생각하는가? 더미 테이블을 인수인계 시 중요항목으로 관리되고 있는가? 2011년이 가까이 다가와도, Copy_ymd에 데이터를 넣어줄 생각을 하는 사람은 아무도 없었다. 왜냐하면 10년간 담당자가 세 번이나 바뀌었고, 더미테이블은 인수인계 시 중요관심사가 아니었기 때문이다. 결국 더미테이블을 신경 쓰는 사람은 아무도 없을 수 있다. 시스템은 이렇게 중요 테이블이 아니더라도 조그만 블랙홀이 생기면 장애를 맞는다. 이런 일이 발생할 수 밖에 없는 걸까?

 

세 번째, 관리해야 할 DB 서버가 많다면 위험이 증가한다. DB 팀이 관리하는 DB가 30개라고 가정하자. 지금 30개의 DB에 대해서 더미테이블을 관리하고 있는가? Copy_ymd에 추가적인 데이터를 insert 해야 하는 시기를 알고 있는가? 관리하고 있지 않다면 장애를 맞을 가능성이 높다. 그렇다면, 신경 쓰지 않아도 되도록, 시간이 되면 자동으로 insert되는 프로그램을 고려해 보아야 하는가? 아니면 시스템마다 더미테이블 들을 뒤져서 안전하게 100년치를 넣을 것인가? 왜 그래야 하는가? 아예 더미테이블을 사용하지 않으면 될 것을

 

지금은 운영 중이기 때문에 SQL을 바꾸는 것이 어렵다고 하더라도, 차세대 시스템을 구축할 때는 테이블을 관리할 필요도 없고, 장애도 일으키지 않는 방법이 무엇인지 고려하기 바란다. 방법은 얼마든지 있다. 이미 똑똑한 개발자들은 아래의 방법을 사용하고 하고 있다.

 

1. Copy

Copy_t 대신에 Rollup, Cube, Grouping Sets를 활용하면 원하는 집합을 만들 수 있다. 사용방법은 해당 글을 참고하라. 물론 내부적으로 쿼리변환이 발생되어 UNION ALL로 풀릴 수도 있으므로 성능이 저하되는지 실행계획의 확인은 필요하다. 이런 경우에도 COPY_T는 필요 없으며 DUAL + CONNECT BY LEVEL을 사용하면 된다. 또한 LEAD/LAG를 사용한다면 복제하지 않고도 전/후의 데이터를 비교할 수 있다.

 

2. 데이터 체크

데이터를 Insert 하기 전에 일자 컬럼을 체크하려고, DBMS Call을 해야만 하나? 다시 말해, 무슨 이유 때문에 DB에 불필요한 부하를 주어야 하는가? 비슷한 노력을 들이고도 DBMS Call을 하지 않을 수 있다. 화면 단에서 Java Script로 처리하던지, 아니면 Constraint를 걸면 Insert할 때에 자동으로 체크 되므로 별도의 DBMS Call은 필요 없다. Constraint에 대해서는 관련 글을 참조하라.

 

3. 인덱스의 효율적 사용

INDEX SKIP SCAN 기능이 추가되었기 때문에 IN 서브쿼리를 사용해야 되는 경우는 많이 한정 되었다. 또한 IN 서브쿼리를 사용한다고 하더라도 Copy_t, Copy_ymd 대신에 Dual + Connect By를 사용하면, Pseudo 컬럼인 Level을 사용할 수 있다. 물론 주의사항은 있다. 해당 글을 참조하라.

 

3번에 대해서 어느 개발자가 다음과 같이 질문한다.

 

질문1

개발자: 인덱스가 거래일자 + 고객번호 입니다. 거래일자에 Between 조건이 들어오고 고객번호에 = 조건이 들어온다고 칩시다. 인덱스의 선두 컬럼이 Range 조건이므로 똑똑한 고객번호를 인덱스로 액세스 할 수 없습니다. 이럴 때, Copy_ymd가 있어서 거래일자를 IN 서브쿼리로 공급할 수 있었습니다. 그런데 Copy_ymd 테이블 없이 Dual + Connect By + Level로 처리가 가능 한가요? Copy_t는 Level로 처리가 가능하지만 일자는 Range 조건으로 만들기 힘들 것 같은데요.

필자: 됩니다.

개발자: 어떻게요?

 

질문2

개발자: INDEX SKIP SCAN은 인덱스가 A+B+C 로 되어있고, A 혹은 B가 Where 조건에서 생략될 때만 사용할 수 있는 것 아닙니까? 즉, A 컬럼에 Range 조건이 오고 B에 = 조건이 오면 INDEX SKIP SCAN을 사용할 수 없는 걸로 알고 있습니다만.

필자: 꼭 그런 것은 아닙니다. A 컬럼에 조건이 Between이나 LIKE 조건이 오고 B 컬럼에 = 조건이 오더라도 INDEX SKIP SCAN이 발생합니다. 즉, 선두나 중간 컬럼의 조건이 생략될 때만 INDEX SKIP SCAN이 발생하는 것은 아니며, 선두나 중간 컬럼에 조건이 Range로 들어올 때도 발생합니다.  

개발자: 그럴 리가요?

 

이제부터 두 가지 질문에 대해 대답해보자. 먼저 Sales 테이블에 인덱스를 하나 만들고 Copy_ymd를 만들자.

 

CREATE INDEX IDX_SALES_01 ON SALES (time_id, cust_id, prod_id) ;

 

CREATE TABLE COPY_YMD AS

SELECT TO_CHAR(ROWNUM + TO_DATE('19800101', 'YYYYMMDD'), 'YYYYMMDD') AS YMD_CHAR,

       ROWNUM + TO_DATE('19800101', 'YYYYMMDD') AS YMD_DT

  FROM SALES

WHERE ROWNUM <= 14600;

 

ALTER TABLE COPY_YMD ADD CONSTRAINT PK_COPY_YMD

PRIMARY KEY (YMD_CHAR) USING INDEX; 

 

CREATE UNIQUE INDEX IDX_COPY_YMD_01 ON COPY_YMD(YMD_DT);

 

Sales 테이블의 인덱스는 Time_id _+ cust_id + Prod_id 이다. 해당 매출테이블의 transaction이 많아서 인덱스를 변경할 수도, 생성할 수도 없는 상황이라고 가정한다. 이제 테스트를 시작해보자.

 

참고로 아래의 힌트는 INDEX SKIP SCAN을 방지할 목적으로 사용한 것이다. INDEX SKIP SCAN이 나오기 전에는 이렇게 INDEX RANGE SCAN으로 수행되었다.

 

SELECT /*+ NO_INDEX_SS(S IDX_SALES_01) INDEX_RS_ASC(S IDX_SALES_01) */ s.*

  FROM sales s

 WHERE time_id BETWEEN TO_DATE('20011001', 'YYYYMMDD')

                   AND TO_DATE('20011130', 'YYYYMMDD')

   AND cust_id = 53;

 

----------------------------------------------------------------------------------------------------

| Id  | Operation                          | Name         | Starts | A-Rows |   A-Time   | Buffers |

----------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                   |              |      1 |      6 |00:00:00.01 |     209 |

|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| SALES        |      1 |      6 |00:00:00.01 |     209 |

|*  2 |   INDEX RANGE SCAN                 | IDX_SALES_01 |      1 |      6 |00:00:00.01 |     203 |

----------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("TIME_ID">=TO_DATE(' 2001-10-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "CUST_ID"=53

              AND "TIME_ID"<=TO_DATE(' 2001-11-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

       filter("CUST_ID"=53)

 

과거에는 선두컬럼이 Between이나 Like등의 Range 조건이 들어오면 위의 실행통계에서 볼 수 있듯이 비효율이 심했다. 고작 6건을 출력하기 위해 209 블록이나 Scan했다. 왜냐하면, 똑똑한 조건인 고객번호가 선두컬럼의 Range 조건 때문에 Access 조건이 못되고 Filter로 빠졌기 때문이다. 이런 비효율을 없애기 위해 예전에는 아래와 같이 더미테이블을 이용한 서브쿼리를 사용하였다.

 

SELECT /*+ LEADING(C@SUB) USE_NL(S) */ s.*

  FROM sales s

 WHERE time_id IN ( SELECT /*+ QB_NAME(SUB) */ ymd_dt

                      FROM copy_ymd c

                     WHERE ymd_dt BETWEEN TO_DATE('20011001', 'YYYYMMDD')

                                      AND TO_DATE('20011130', 'YYYYMMDD') )

   AND cust_id = 53;

 

--------------------------------------------------------------------------------------------------------

| Id  | Operation                           | Name            | Starts | A-Rows |   A-Time   | Buffers |

--------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                    |                 |      1 |      6 |00:00:00.01 |     136 |

|   1 |  NESTED LOOPS                       |                 |      1 |      6 |00:00:00.01 |     136 |

|   2 |   NESTED LOOPS                      |                 |      1 |      6 |00:00:00.01 |     130 |

|*  3 |    INDEX RANGE SCAN                 | IDX_COPY_YMD_01 |      1 |     61 |00:00:00.01 |       4 |

|*  4 |    INDEX RANGE SCAN                 | IDX_SALES_01    |     61 |      6 |00:00:00.01 |     126 |

|   5 |   TABLE ACCESS BY GLOBAL INDEX ROWID| SALES           |      6 |      6 |00:00:00.01 |       6 |

--------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - access("YMD_DT">=TO_DATE(' 2001-10-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND

              "YMD_DT"<=TO_DATE(' 2001-11-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

   4 - access("TIME_ID"="YMD_DT" AND "CUST_ID"=53)

       filter(("TIME_ID"<=TO_DATE(' 2001-11-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND

              "TIME_ID">=TO_DATE(' 2001-10-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

 

서브쿼리를 사용하자 고객번호를 Access 조건으로 사용할 수 있게 되었다. 이에 따라 서브쿼리를 사용하지 않은 경우(209 블럭)보다는 Scan량이 줄어 136 블록이 되었지만 약간의 비효율이 있다. Copy_ymd 때문에 4블럭을 Scan 하였다. 이것을 해결하려면 아래처럼 Dual + Connect By Level을 사용하면 된다. 위의 SQL과 아래의 SQL의 답은 같으며 아래의 SQL은 질문1의 답변에 해당한다.  

 

SELECT s.*

  FROM sales s,

      ( SELECT TO_DATE('20011001', 'YYYYMMDD') + LEVEL - 1 AS time_id

          FROM dual

       CONNECT BY LEVEL <= TO_DATE('20011130', 'YYYYMMDD') - TO_DATE('20011001', 'YYYYMMDD') + 1) d

 WHERE s.time_id = d.time_id

   AND s.cust_id = 53; 

 

-----------------------------------------------------------------------------------------------------

| Id  | Operation                           | Name         | Starts | A-Rows |   A-Time   | Buffers |

-----------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                    |              |      1 |      6 |00:00:00.01 |     132 |

|   1 |  NESTED LOOPS                       |              |      1 |      6 |00:00:00.01 |     132 |

|   2 |   NESTED LOOPS                      |              |      1 |      6 |00:00:00.01 |     126 |

|   3 |    VIEW                             |              |      1 |     61 |00:00:00.01 |       0 |

|   4 |     CONNECT BY WITHOUT FILTERING    |              |      1 |     61 |00:00:00.01 |       0 |

|   5 |      FAST DUAL                      |              |      1 |      1 |00:00:00.01 |       0 |

|*  6 |    INDEX RANGE SCAN                 | IDX_SALES_01 |     61 |      6 |00:00:00.01 |     126 |

|   7 |   TABLE ACCESS BY GLOBAL INDEX ROWID| SALES        |      6 |      6 |00:00:00.01 |       6 |

-----------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   6 - access("S"."TIME_ID"=INTERNAL_FUNCTION("D"."TIME_ID") AND "S"."CUST_ID"=53)

 

Dual을 사용했기 때문에 Block I/O가 없어졌다. 하지만 여기서 만족하면 안 된다. 왜냐하면 쓸모 없는 조인이 61번이나 시도되었고 이에 따라 126블록을 Scan하였기 때문이다. 따라서 SQL을 아래처럼 바꾸어야 한다.

 

SELECT /*+ INDEX_SS(S IDX_SALES_01) */ s.*

  FROM sales s

 WHERE time_id BETWEEN TO_DATE('20011001', 'YYYYMMDD')

                   AND TO_DATE('20011130', 'YYYYMMDD')

   AND cust_id = 53;

 

----------------------------------------------------------------------------------------------------

| Id  | Operation                          | Name         | Starts | A-Rows |   A-Time   | Buffers |

----------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                   |              |      1 |      6 |00:00:00.01 |      70 |

|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| SALES        |      1 |      6 |00:00:00.01 |      70 |

|*  2 |   INDEX SKIP SCAN                  | IDX_SALES_01 |      1 |      6 |00:00:00.01 |      64 |

----------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("TIME_ID">=TO_DATE(' 2001-10-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "CUST_ID"=53

              AND "TIME_ID"<=TO_DATE(' 2001-11-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

       filter("CUST_ID"=53)

 

불필요한 조인도 없어졌으며 Block I/O도 서브쿼리를 사용할 때에 비해서 약 절반으로 줄어들었다. 이것이 질문 2에 대한 대답이다.

 

참고사항

위의 SQL들을 보면 인덱스가 cust_id + time_id로 되어 있는 것이 최적이지만 막상 튜너가 현장에 투입되면 인덱스를 변경/생성/삭제 하기는 대단히 어려우므로 위의 방법을 잘 알아놓아야 한다.

 

결론

Copy_ymd, Copy_ym, Copy_y, Copy_t는 구시대의 유물이다. 성능에도 좋지 않으며, 코드가 길어지고, 장애가 발생할 수 있음에도 여러 가지 이유를 대어 차세대 시스템에 더미 테이블들이 또 포함될 수 있다. 안타깝게도 관행이나 표준으로 생각하는 사람이 많기 때문이다. 이제는 바뀔 때가 되었다. 지금 운영되는 모든 시스템에서 더미테이블을 사용하는 SQL을 모조리 조사해서 고치라는 이야기가 아니다. 그렇게 하기는 힘들 것이다. 다만 모든 더미테이블을 찾아서 미래의 데이터를 미리 그리고 넉넉히 넣자는 이야기 이다. 그리고 앞으로 시작될 프로젝트에서 더미테이블을 사용하지 않았으면 하는 것이 나의 바램이다. 당신이 발 뻗고 잘 수 있도록

SQL 포기하지마라. 라는 글에서 Model 절을 언급한바 있다.
독자중에 좀더 자세히 알고 싶어 하는 분들이 있어서 개인적으로 정리한 자료를 올리게 되었다.
하지만 배포시에는 출처를 꼭 밝혀주기 바란다.
아래파일은 upload 시에 200 KB 한계가 있으므로 3개로 나누어 올리지만
3개를 모두 다운로드 받아 압축을 풀면 하나의 파일로 생성되므로 문제가 없을 것이다.

SQL 은 방정식을 풀기에 적합한 언어인가?
  우연히 방정식을 푸는 요건이 있었는데 상황은 아래와 같았다.
요건은 "정수 X 를 정수 Y 로 계속 나누되 N 번 나눈 결과와 N-1 번 나눈 결과의 차이가 0.25 보다 작으면
멈추고 그때까지의  나눈값과 LOOP 횟수( N )를 출력하라" 였다. 물론 X 와 Y 는 변수이다.
이해를 쉽게 하기 위하여 왼쪽의 그림을 보자.

왼쪽의 그림을 설명하면  50을 2 로 8번 나누면 0.1953 인데 이것을 7번 나눈값으로 빼면 결과는 0.25 이하이다.

따라서 원하는 출력값은 0.1953 과 8 번(LOOP 횟수) 인 것이다.

이요건을 들은 개발자들은 저마다 자바나 PL/SQL 로 개발 하면 된다고 생각했다.

하지만 이기능을 요구한 사람의 말에 따르면 SELECT 쿼리결과로 나오는 것이 가장 좋다.

하지만 불가능하면 자바나 PL/SQL 등으로 구현해도 된다는 것이었다.

여러분이 만약 이러한 요구사항을 해결해야 한다면?

퀴즈라고 생각하고 머릿속에서 해법을 생각해보라.


포기하지마라
  사실 필자는 이문제가 성능이슈가 아니었고 SQL을 제법 잘사용하는 개발자들과 DBA 가 회의에 참석하였으므로 직접 나서지 않았는데 한마디 말 때문에 흥분하기 시작했다.

개발자1 : 이것은 SQL 로는 안됩니다. Loop 도 돌려야 하고 Loop 내에서 빠져나가는 로직도 추가해야 되기 때문입니다.
필자 : SQL 로 하는것을 포기 하지마십시오.
개발자1 : 네?
개발자2 : 5분이면 PL/SQL 함수로 구현이 가능할거 같습니다.
             그함수를 SQL 에서 call 하면 됩니다. 괜히 고생하지 마시죠.
필자 : 5분이라구요?....그렇다면 두분이서 작성한 코드를 10분뒤에 저에게 보여주세요.

  이렇게 해서 회의가 잠시중단이 되었다.
사실 필자는 자주 흥분하는편이 아닌데 언제부터인지 SQL 로는 불가능하며 비효율적이라는 소리를 들을때면 이상하게 자극이 된다. 어쩔수 없는 엔지니어 출신의 비애인것 같다.

It's Time to overhaul!
시간이 되어 개발자들이 작성한 코드를 검토해 보았다.


위의 함수는 약간 손볼곳이 있지만 그래도 결과에 충실하다. 위의 함수를 SQL 에서 사용하면 아래와 같다.


결과 : .1953125  , Loop 횟수 : 8

  함수의 길이도 길지않고 이정도면 괜찮다고 말할수 있다. 하지만 또다른 비효율이 숨어 있었다.
결과를 comma (,) 기준으로 잘라서 2 컬럼으로 보여줘야 하는 문제가 여전히 남는다.
그렇다면 필자가 작성한 아래의 SQL 로 바꾸는 것은 어떤가?



결과              Loop 횟수
----------- -----------
   0.1953125                 8

Model 절 문법이 어렵다고 들었다.
  결과가 2컬럼으로 분리되어 정확히 나왔다. 코딩의 시간은 단 1분 이며 코드의 길이는 어떤가? 이정도면 충분히 짧다.
개발자들이 유지보수가 어렵다는 문제를 들고 나왔다. 1분만 투자하면 문법을 익힐수 있음에도 문법이 어렵다는 것은 미신이다.
현재 오라클이 11g 까지 나왔지만 각각의 문법들은 몇분만 투자하면 다 알수 있는 정도이다.
물론 성능면에서 신기능의 실행계획을 분석하는것은 다른 이야기 이다.
간단히 문법을 설명하면 아래와 같다.



주의사항 :
  여기서 dimension by 는 아무런 뜻이 없다. 하지만 숫자 1 대신에 다른값을 사용할 경우 SQL 이 종료되지 않으므로 주의해야 한다.

Model 절로 무엇을 할수 있나?
 Model 절과 관련된 미신중의 하나는 Time series, 수요예측, 판매예측, 기상예측 등등 주로 이전실적에 여러가지 factor 를 추가하여 미래를 예측하는데 사용하거나 SQL 에서 축이동(Pivot/Unpivot) 등의 용도로만 사용된다고 알고 있다는 것이다. 하지만 위의 SQL 은 이것들중 어디에도 포함되지 않는다. 다시말하면 복잡한 연립방정식을 푸는데 제일좋은 것이 Model 절이다.  
       
결론:
  MODEL 절은 오래전(6년전)에 나온 SQL 문법이지만 아직 늦지 않았다. 필자가 확인한 결과 아직도 모르는 사람들이 많이 있으므로 지금 익힌다면 선구자가 될것이다. SQL 은 여러분이 생각하는것보다 훨씬더 강력하고 효율적임을 알수있다. 위의 SQL과 같이 간단하지만 강력한 기능들이 많이 준비되어 있다. 예를들면 X-Query, Regular Expression, Pivot/UnPivot 등이 그것이다. 무기의 종류(SQL)를 여러개 준비한 사람과 기관총 하나만 가지고 있는 사람의 차이는 어마어마 하다. 준비된 사람은 요구조건에 따라서 단검을 사용할것인지 탱크를 사용할것인지 그것도 아니라면 F16 전투기를 사용할것인지 상황에 따라서 맞는 무기를 선택할수 있다.  SQL 공부 여기서 포기할것인가?

SQL 작성시 응용력을 필요로 하는경우가 많이 있다.
아래의 예제도 그런상황중의 하나이다.
일별매출실적 테이블에서 sysdate 기준으로 현재월의 첫번째 월요일부터 현재월의 마지막 금요일 까지의 실적을 구해야 되는 요구사항이 있다.
문제의 핵심은 해당월의  첫번째 월요일과 마지막 금요일을 구하는 것이다.
2009년 3월 기준으로 생각하면 첫번째 월요일은 2009년 3월 2일 마지막 금요일은 2009 년 3월 27일 이다. 

아래의 SQL 을 보자.

위 SQL 은 변수를 2개 사용하고 있다.
:현재월의 첫번째 월요일과 :현재월의 마지막 금요일을 구하는 로직은 개발자가 자바 프로그램에서 작성할 예정이라고 한다.
그렇게 작성 할수는 있지만 로직이 길어지고  유지보수도 어려워지므로 Oracle DBMS 기능을 이용하여 쉽게 구하는 방법이 있는지 필자에게 물어보러 온 것이다.
아래의 SQL 을 보자

FIRST_MONDAY LAST_MONDAY
------------ -----------
2009-03-02   2009-03-27

last_day, next_day 함수와 trunc 함수를 적절히 활용하면 문제를 쉽게 풀수 있다.
간단히 설명 하면 첫번째 월요일은 trunc(sysdate,'mm')-1 로 전달의 마지막 일자를 구하고 next_day 함수를 사용하여 다음 월요일을 구하면 된다.
마지막 금요일은 last_day 함수를 사용하여 해당월의 마지막 일자에서 7일을 뺀 결과를 next_day 함수를 사용하여 다음 금요일을 구하면 되는 것이다.

이렇게 하여 아래와 같은 simple 한 SQL 이 완성되었다.

결론:
문제의 핵심을 분석하고 DBMS 의 기능을 최대한 활용하여 상황을 빨리 해결 하는 능력이 필요하다.
문제해결 능력은 분석력 + 지식 + 응용력 이다.  
해당 SQL 의 경우 분석이나 지식보다는 응용력을 잘 발휘 해야되는 상황인 것 이다.