대용량 대이터베이스 솔루션 2권 #02 SQL 활용의 당위성

SQL 활용의 당위성

1. SQL 수행 횟수의 차이 (DBMS Call)
2. Random Access 발생량 차이
3. 실행계획 최적화 차이
4. Client/Server 환경에서의 SQL 역할 (Network 부하 최소로)
5. 처리경로 개선의 용이성
6. 병렬처리에서의 SQL의 역할
7. 처리과정의 값에 대한 활용
   (DECLARE CURSER ... FETCH 로 쓰는 이유는 처리과정에서 값을 변환해서 전달하는 방법을 몰라서이다.)
8. 단순성, 유지보수성, 생산성

위의 사항들에 대해서 하나하나씩 살펴 보자.

1. SQL 수행 횟수의 차이 (DBMS Call)

- JOIN : 한번의 DBMS Call로 최종결과를 얻어낸다.

- DECLARE CURSER : 한번의 DBMS Call로 전체 List를 알아온다.
   FETCH : 할때마다 SQL문이 하나씩 DBMS Call로 Server로 전달된다.

2. Random Access 발생량 차이

- 왼쪽의 경우 1번의 INDEX RANGE SCAN 이후 Table에 대해서 Random-Single Block Access가 발생
- 오른쪽은 INDEX에 대해서 각각의 :ITEM에 대해서 Random Access 이후, Table로 Random-Single Block Access가 발생

3. 처리경로 최적화의 차이

 1만건의 Data를 처리하는 경우

* 절차형으로 작성된 프로그램

- SELECT 1,2,3, INSERT ,UPDATE 각각에 대해서 SQL 최적화 (최적화는 무슨 기껏해야 PK Access ?)
- 각각의 SQL문이 최적화 된다고 하여 전체 처리가 최적화 될까 ?
- Optimizer는 단순노무만 하게 된다.
- 절차형 처리에서 최적화하는 Optimizer는 있을 수가 없다.



* SQL 위주 프로그램

- Optimizer는 Table 3개를 최적화하여 JOIN하고 그 결과를 다중처리로 Array INSERT, Array UPDATE 를 수행한다.

4. Client/Server 환경에서의 SQL 역할

10만건의 Data가 Server에 있는 경우

* 절차형으로 작성된 프로그램

DECLARE CURSOR ...
SELECT * FROM TAB1 WHERE SALE_DATE LIKE '199810%'; -- 10만건의 Data를 일단 Server가 가지고 있고...

FETCH -- 일단 1건을 Network로 가져오고

UPDATE TAB1 x
   SET COL1 = DECODE(...),
       COL2 = 100, ...
 WHERE KEY = :KEY; -- 이렇게 10만건의 실행과 거기에 따른 결과가 Network를 타고...

* SQL문 하나로 처리

UPDATE TAB1 x
   SET (COL1, COL2, ...) = 
       (SELECT DECODE(...), 100, ...
          FROM TAB2 y
         WHERE x.KEY = y.KEY ...)
       COL2 = 100, ...
 WHERE SALE_DATE LIKE '199810%'; -- 한번의 실행으로 얼마 안되는 SQL문 Network로 전송

* 예제
   제품이 'A로 시작하는 Row(1만건)를 읽어서 'A1,A2'는 '제품1' , 'A3,A5,A6'는 '제품2', 'A4'는 '제품3', 나머지는 '제품4'
   제품별 생산일은 '1~5일' 은 1주기, '6~15일'은 2주기, '16~25일'은 3주기, '26~말일'은 4주기
   1 ~ 6월까지의 월별 평균 생산량 추이를 분석하라.

SELECT '제품'||ITEM
       DECODE(FLOOR(DD/6), 0, 1,
       DECODE(FLOOR(DD/16),0, 2,
       DECODE(FLOOR(DD/26),0, 3, 4))),
       SUM(DECODE(MM, '01', QTY)) * 100 / SUM(DCODE(MM, '01', 1)),
       ...
       SUM(DECODE(MM, '06', QTY)) * 100 / SUM(DCODE(MM, '06', 1)),
       SUM(QTY) / COUNT(*)
 FROM (SELECT DECODE(SUBSTR(제품,2,1), '1', '1', '2', '1',
                  '3', '2', '5', '2', '6', '2', '4', '3', '4') AS ITEM,
              SUBSTR(생산일,3,2) AS MM,
              SUBSTR(생산일,5,2) AS DD,
              SUM(수량) AS QTY
         FROM 생산테이블
        WHERE 생상일 BETWEEN '20140101' AND '20140630'
          AND 제품 LIKE 'A%'
        GROUP BY DECODE(SUBSTR(제품,2,1), '1', '1', '2', '1',
                  '3', '2', '5', '2', '6', '2', '4', '3', '4'),
              SUBSTR(생산일,3,2),
              SUBSTR(생산일,5,2)
       )
 GROUP BY ITEM,
          DECODE(FLOOR(DD/6), 0, 1,
          DECODE(FLOOR(DD/16),0, 2,
          DECODE(FLOOR(DD/26),0, 3, 4)))

5. 처리경로 개선의 용의성

- 절차형으로 작성된 프로그램은 처리절차를 함부로 못바꾼다.
  이거 지금 안쓰는 건데 빼버릴까 ? 그랬다간 무슨 일이 발생할지 모른다.
  완전 전체 다를 알고 있지 않는 이상 함부로 손보기가 힘들다.

- SQL 위주로 작성된 프로그램은
  SQL문 수정, INDEX 구조 조정, HINT 사용 만으로도 전혀 다른 처리결과를 얻을수 있다.

* 예제
   어느 통신회사의 월 청구 작업이다.

- 월 사용로는 정액제로 계산
- 사유에 따라 다양한 감액 요인발생
- 장애인, 연체에 의한 사용 중지, 분실에 의한 사용 중지, 본인 요청에 의한 사용 중지
- 감액요인이 중첩되더라도 한가지만 적용. 감액율 90%
- 장애인 감액은 20%, 다른 감액과 중첩시 감액계산 후 20%
- 고객수는 500만명

 예를 들어 어느 한 고객의 경우

  4.05 ~ 5.21 : 고객요청 사용정지,
  5.25 ~ 5.28 : 분실정지
  5.27 ~ 영원히 : 장애인 할인

  이라고 가정했을 때 빌링 범위는 5.1 ~ 5.31 이다.
  위의 범위를 5월 빌링 범위안으로 수정시 아래와 같이 될 것이다.

  5.01 ~ 5.21 : 고객요청 사용정지
  5.22 ~ 5.24 : 정상
  5.25 ~ 5.26 : 분실정지
  5.27 ~ 5.28 : 분실정지 + 장애인감액
  5.29 ~ 5.31 : 장애인감액

- 개선전
  1. 고객 정보를 하나씩 읽는다. : DECLARE CURSOR ... FETCH
  2. 고객상태 변경 정보를 읽어 지속기간을 확인 : SELECT ... FROM 고객상태변경 WHERE ...
  3. 일자만큼 반복해서 각 일자의 감액방법 결정 : LOOP 1일 ~ 30일 ...
  4. 감액 종류별로 집계, 감액율을 곱하여 더함 : 감액적용 -> 임시테이블 저장 -> 장애인 감액 계산
  5. 계산후 결과를 테이블에 저장
  6. 전체 고객이 끝날 때까지 반복

  6개의 C 프로그램으로 이틀이 걸렸었다.
  어떻게 수정을 해야 할까 ?
  고객은 500만명이라도 빌링 범위 기간에 감액이 발생한 고객은 얼마나 될까 ? 기껏 1만명 이내이다.
  그런데 고객 Table을 Driving 하는거 자체가 엄청난 실수이다.
  고객상태변경 Table을 Driving 하는게 휠씬 더 효율적이다.
  그리고 아래 처리과정의 Parameter 활용에 관계된 이야기지만,
  처리과정 중에 값을 이용하거나 변경하는게 SQL로 불가능해서 DECLARE CURSOR 를 사용한다고 ?
  얼마든지 SQL로도 가능하다.
  값을 어떻게 잘게 나눠서 다시 GROUP BY로 합치면서 계산을 해야 내가 원하는 식으로 바뀔까 ?
  그것을 생각할 수 있는 능력이 필요하다.
  개선된 SQL문은 아래와 같다. 참고로 아래 SQL로 변경 후 처리시간은 3분안에 끝났다.

SELECT cust_id,
       SUM(950 * NVL(sale1,1) * NVL(sale2,1)) 감액금액,
       COUNT(sale1 || sale2) 감액일수
  FROM (
       SELECT x.cust_id,
              AVG(DECODE(status, '분실', 0.9, '정지', 0.9, '일시', 0.9)) sale1,
              MIN(DECODE(status, '장애', 0.2)) sale2
         FROM (
              SELECT cust_id, status,
                     GREATEST('19980501', begin_date) begin_date, -- Billing 범위로 변경
                     LEAST('19980531', end_date) end_date
                FROM 고객상태변경
               WHERE status IN ('분실','일시','장애','정지')
                 AND end_date >= '19980501'   -- Driving 범위
                 AND begin_date <= '19980531'
              ) x, COPY_T y -- 01 ~ 31 문자를 저장한 Table
        WHERE y.num BETWEEN SUBSTR(x.begin_date, 7, 2)
                        AND SUBSTR(x.end_date, 7, 2)
        GROUP BY x.cust_no, y.num)
 GROUP BY cust_no;

 6. 병렬처리에서 SQL의 역할

- 처리Logic의 얼마나 많은 부분을 병렬처리로 끌어들였냐는게 중요
  병렬처리는 보통 JOIN 을 Parallel로 하여 그 결과를 Hash를 이용하여 각 CPU 별로 처리한다.
  그런뒤 INSERT, UPDATE 등의 처리는 Array Processing을 이용한다.

- SORT_AREA_SIZE, HASH_AREA_SIZE와 Parellel Processor 개수의 조화가 중요하다.
  적절량 이상으로 많이 잡아봐야 별 효과도 없고, 해당 DBMS를 이용하는 다른 사용자에게 피해만 주게 된다.

7. 처리과정의 Parameter 이용

- SQL은 '요구'와 '결과'만 있을 뿐 처리중 그 값을 '이용' 하거나 '가공' 할수가 없다고 ?
  그래서 어쩔수 없이 DECLARE CURSOR ... FETCH 한다고 ?
  그건 그렇게 생각할 수 있는 능력이 안되는거고...
  여기서 필요한 능력은 수학의 인수분해 능력. 예제로는 바로위에 있는 SQL문을 보면 될 것이다.

- JOIN 과 INLINE VIEW를 적절히 활용하여 SQL 내부에서 서로 값을 참조하여 가공하는 것이 얼마든지 가능하다.

- 이 능력을 키우는 것이 우리가 가야할 길이다.

8. 생산성, 단순성, 유지보수성의 향상

* SQL 위주로 작성된 프로그램은...
- 단순, 명료하다.
- 처리과정은 없고, 결과만 요구한 문장으로 이해가 쉽다. (처리과정은 Optimizer가 알아서)
- 약간의 수정 (SQL, INDEX, HINT) 만으로 처리과정이 달라지므로 개선이 쉽다.
- Data Model이 변경되더라도 수정이 적다. (일반 Program은 아에 뒤엎고 첨부터 다시 할 상황이 자주 ...)
- 고급 SQL 생성 능력을 키우기 힘들지만 수준에 도달하면 대단한 생산성 보장

* SQL은...
- 원리는 쉽지만 응용은 어렵다.
- 쉬운거 100만개 푸느니 어려운 문제 하나 붙잡고 몇날 며칠 고민하는게 더 효과적이다.
- 설명듣고 만들어진것을 보고 이해하는건 쉽지만, 직접 작성하는건 어렵다.
- 암기가 아니라 이해를 해야한다.
- 암기로 해결가능한게 다른분야에 비해 훨씬 더 얼마 안된다. 무조건 이해하도록 노력하자.
  

대용량 데이터베이스솔루션 2

작가

이화식

출판

엔코아컨설팅

발매

1999.11.20

평점

리뷰보기