SQL 5장 - 반복문

14강 반복문 의존증

SQL에는 반복문이 없다.

- 일부러 반복문을 언어 설계에서 제외한 것, 처음부터 '반복문을 제외'하고 만들어진 언어

관계 조작은 관계 전체를 모두 조작의 대상으로 삼는다. 이러한 것의 목적은 반복을 제외하는 것이다. 최종 사용자의 생산성을 생각하면 이러한 조건을 만족해야 한다. 그래야만 응용 프로그래머의 생산성에도 기여할 수 있을 것이다.

1. 내부적으로는 반복문 사용

   반복문이 없어 당황하는 사용자들은 일단 하나의 레코드마다 작은 SQL을 사용해 접근하고, 비즈니스 로직은 호스트 언어(절차형 언어)에서 반복 처리를 구현하는 것이 일반적

 

15강 반복계의 공포

1. 반복계의 단점

   '성능'

   같은 기능을 구현한다고 가정하면, 반복계로 구현한 코드는 포장계로 구현한 코드에 성능적으로 이길 수가 없음

   처리하는 레코드 수가 많아지면 많아지수록 차이는 점점 더 벌어짐

 

   반복계의 처리시간: 처리횟수 * 한 회에 걸리는 처리 시간

   포장계의 처리시간: 인덱스를 사용한 접근이고, 실행계획 변동이 없다고 한다면 대부분 그래프에서 완만한 커브를 그리     게 됨

   

<반복계가 포장계에 성능적으로 질 수밖에 없는 주요 이유>

-SQL 실행의 오버 헤드

 

SQL 처리

 

전처리

1. SQL 구문을 네트워크로 전송

2. 데이터베이스 연결

3. SQL 구문 파스

4. SQL 구문의 실행 계획 생성 또는 평가

 

후처리

5. 결과 집합을 네트워크로 전송

 

--> 3,4 가 오버헤드 중에서 가장 영향이 큼

작은 SQL을 여러 번 반복하는 반복계에서는 오버헤드가 높아질 수 밖에 없음

 

-병렬 분산이 힘들다

반복계는 반복 1회마다의 처리를 굉장히 단순화하므로, 리소스를 분산해서 병렬 처리하는 최적화가 안됨.

 

-데이터베이스의 진화로 인한 혜택을 받을 수 없다.

데이터베이스가 처리해야 하는 데이터양은 최근 급격히 증가하고 있으며, 그에 따라 DBMS 벤터는 어떻게 해야 SQL을 빠르게 할 수 있을지 계속 연구하고 있음 -> 이러한 모든 노력의 중심은 '대규모 데이터를 다루는 복잡한 SQL 구문'을 빠르게 하려는데 있지, 단순한 SQL 구문과 같은 가벼운 처리를 빠르게 만드는 것은 안중에도 없음

따라서 반복계는 미들웨어 또는 하드웨어의 진화에 따른 혜택을 받을 수 없음.

 

2. 반복계를 빠르게 만드는 방법

- 반복계를 포장계로 다시 작성

-각각의 SQL을 빠르게 수정

-다중화 처리

 

3. 반복계의 장점

-실행계획의 안정성

-예상 처리 시간의 정밀도

   <처리시간> = <한 번의 실행시간> x <실행 횟수>

-트랜잭션 제어가 편리

 

16강 SQL에서는 반복을 어떻게 표현할까?

1. 포인트는 CASE식과 윈도우 함수

CASE식은 절차 지향형 언어에서 말하는 IF-THEN-ELSE 구문에 대응하는 기능

절차 지향형 언어에서 반복의 내부에 대부분 IF 조건문을 세트로 사용하는 것처럼, SQL에서도 CASE식과 윈도우 함수를 함께 사용하는 세트   

 

윈도우 함수를 사용한 방법

INSERT INTO Sales2
SELECT company,
	 year,
         sale,
       CASE SIGN(sale-MAX(sale)
       			    OVER (PARTITON BY company
                            	  ORDER BY year
                                  ROWS BETWEEN 1 PRECEDING
                                           AND 1 PRECEDING) )
        WHEN 0 THEN '='
        WHEN 1 THEN '+'
        WHEN -1 THEN '-'
        ELSE NULL END AS var
FROM Sales;

포인트는 SIGN 함수: 숫자 자료형을 매개변수로 받아 음수라면 -1, 양수라면 1, 0이라면 0리턴

 

ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING은 '현재 레코드에서 1개 이전부터 1개 이전까지의 레코드 범위'를 나타냄

 

2. 최대 반복 횟수가 정해진 경우

 

윈도우 함수를 사용한 방법

SELECT pcode,
       district_name
   FROM (SELECT pcode,
                district_name,
                    CASE WHEN pcode = '4130033' THEN 0
                         WHEN pcdoe LIKE '413003%' THEN 1
                         WHEN pcdoe LIKE '41300%'  THEN 2    
                         WHEN pcdoe LIKE '4130%'   THEN 3
                         WHEN pcdoe LIKE '413%'    THEN 4
                         WHEN pcdoe LIKE '41%'     THEN 5
                         WHEN pcdoe LIKE '4%'      THEN 6 
                         ELSE NULL END AS hit_code,
                     MIN(CASE WHEN pcode = '4130033' THEN 0
                              WHEN pcdoe LIKE '413003%' THEN 1
                              WHEN pcdoe LIKE '41300%'  THEN 2    
                              WHEN pcdoe LIKE '4130%'   THEN 3
                              WHEN pcdoe LIKE '413%'    THEN 4
                              WHEN pcdoe LIKE '41%'     THEN 5
                              WHEN pcdoe LIKE '4%'      THEN 6
                              ELSE NULL END)
                     OVER(ORDER BY CASE WHEN pcode = '4130033' THEN 0
                                        WHEN pcode = '4130033' THEN 0
                                        WHEN pcdoe LIKE '413003%' THEN 1
                                        WHEN pcdoe LIKE '41300%'  THEN 2    
                                        WHEN pcdoe LIKE '4130%'   THEN 3
                                        WHEN pcdoe LIKE '413%'    THEN 4
                                        WHEN pcdoe LIKE '41%'     THEN 5
                                        WHEN pcdoe LIKE '4%'      THEN 6
                                        ELSE NULL END)AS min_code
            FROM PostalCode) Foo
   WHERE hit_code = min_code;

 

3. 반복 횟수가 정해지지 않은 경우

- 인접 리스트 모델과 재귀 쿼리

-중첩 집합 모델

  1) 인접 리스트 모델

  2) 중첩 집합 모델

  3) 경로 열거 모델