SQL의 순서

레코드에 순번 붙이기

기본 키가 한 개의 필드일 경우

• 윈도우 함수를 사용

  SELECT student_id,
             ROW_NUMBER() OVER (ORDER BY student_id) AS seq
  FROM Weights
  

• 상관 서브쿼리를 사용

  SELECT student_id,
             (SELECT COUNT(*)
    			FROM Weights W2
     		 WHERE W2.student_id <= W1.student_id) AS seq
  FROM Weights W1
  

  • 윈도우 함수는 인덱스 스캔 1회, 상관 서브쿼리는 테이블 풀 스캔 2회
  • 윈도우 함수를 사용하는 것이 성능 측면에서 좋음

    기본 키가 여러 개의 필드로 구성되는 경우

• 윈도우 함수를 사용

  SELECT student_id,
             ROW_NUMBER() OVER (ORDER BY class, student_id) AS seq
  FROM Weights2
  

• 상관 서브쿼리를 사용

  SELECT class, student_id,
             (SELECT COUNT(*)
    			FROM Weights2 W2
     		 WHERE (W2.class, W2.student_id)
        					<= (W1.class, W1.student_id) ) AS seq
  FROM Weights2 W1
  

  • 다중 필드 비교 사용
  • 암묵적인 자료형 변환이 발생하지 않으므로 기본 키 인덱스도 사용 가능
  • 필드가 3개 이상일 때도 간단하게 확장 가능

    그룹마다 순번을 붙이는 경우

• 윈도우 함수를 사용

  SELECT class, student_id,
             ROW_NUMBER() OVER (PARTITION BY class ORDER BY student_id) AS seq
  FROM Weights2
  

• 상관 서브쿼리를 사용

  SELECT class, student_id,
             (SELECT COUNT(*)
    			FROM Weights2 W2
     		 WHERE W2.class = W1.class
        	 AND W2.student_id = W1.student_id) AS seq
  FROM Weights2 W1
  

  순번과 갱신

  • 테이블에 순번 필드(seq)를 생성
  • 윈도우 함수를 사용

    UPDATE Weights3
     SET seq = (SELECT seq
     							FROM (SELECT class, student_id,
          										ROW_NUMBER() 
                    						OVER (PARTITION BY class
                          								ORDER BY student_id) AS seq)
                        FROM Weights3) SeqTbl			 
                           WHERE Weights3.class = SeqTbl.class
         				 AND Weights3.student_id = SeqTbl.student_id)
    

• 상관 서브쿼리를 사용

  UPDATE Weights3
     SET seq = (SELECT COUNT(*)
  							FROM Weights3 W2
         			 WHERE W2.class = Weights3.class
             		 AND W2.student_id <= Weights3.student_id)
  

  레코드에 순번 붙이기 응용

  중앙값 구하기

• 집합 지향적 방법

  SELECT AVG(weight)
    FROM (SELECT W1.weight
   					FROM Weights W1, Weights W2
      	 GROUP BY W1.weight
        HAVING SUM(CASE WHEN W2.weight >= W1.weight THEN 1 ELSE 0 END) >= COOUNT(*) /2
        	 AND SUM(CASE WHEN W2.weight <= W1.weight THEN 1 ELSE 0 END) >= COOUNT(*) /2) TMP
  

  • CASE식에 표현한 두 개의 특성 함수로 모집합 Weights를 상위 집합과 하위 집합으로 분할
  • 코드가 복잡해서 무엇을 하고 있는지 한 번에 이해하기 어려움
  • 자기 결합을 수행하므로 성능이 나쁨
• 절차 지향적 방법 1 - 세계의 중심을 향해

  SELECT AVG(weight)
   FROM (SELECT W1.weight
   							 ROW_NUMBER() OVER (ORDER BY weight ASC, student_id ASC) hi,
        			 ROW_NUMBER() OVER (ORDER BY weight DESC, student_id DESC) lo,
         FROM Weights) TMP
   WHERE hi IN (lo, lo+1, lo-1)
  

• IN 구문으로 홀수인 경우, 짝수인 경우를 한꺼번에 처리
• 비슷한 기능의 RANK 또는 DENSE_RANK는 중복이 발생할 수 있으므로 연속성과 유일성을 갖게 만들 수 없음
• Weights 테이블에 대한 접근이 1회로 감소, 정렬 2회
• ORDER BY의 정렬키에 기본 키인 student_id를 포함해야 함
  • 포함하지 않을 경우 체중이 같은 학생이 여러 명 있을 때 NULL이 발생할 가능성이 있음
• 절차 지향적 방법 2 - 2 빼기 1은 1

  SELECT AVG(weight) AS median
    FROM (SELECT weight,
   							 2 * ROW_NUMBER() OVER (ORDER BY weight)
         			 		 - COUNT(*) OVER() AS diff
          FROM Weights) TMP
   WHERE diff BETWEEN 0 AND 2
  

  • ROW_NUMBER 함수로 구한 순번을 2배하고 거기서 COUNT(*)를 빼서 diff를 구함
  • 중간값에 해당하는 diff는 0부터 2 사이의 값을 가짐
  • 이전 방법에 비해 정렬이 1회 감소하므로 성능 향상

    순번을 사용한 테이블 분할

• 문제. 단절 구간 찾기
  • 집합 지향적 방법 - 집합의 경계선

    SELECT (N1.num + 1) AS gap_start,
             '~',
    	 (MIN(N2.num) - 1) AS gap_end
    FROM Numbers N1 INNER JOIN Numbers N2
     		ON N2.num > N1.num
     GROUP BY N1.num
    HAVING (N1.num + 1) < MIN(N2.num)
    

    • 간단하지만 자기 결합을 사용하여 비용이 높고 실행 계획이 불안정함
  • 절차 지향적 방법 - ‘다음 레코드’와 비교

    SELECT num + 1 AS gap_start,
             '~',
    	 (num + diff - 1) AS gap_end
    FROM (SELECT num,
     							 MAX(num)
         				OVER(ORDER BY num
             					ROWS BETWEEN 1 FOLLOWING
                  						 AND 1 FOLLWING) - num)
         FROM numbers) TMP(num, diff)
     WHERE diff <> 1
    

    • 윈도우 함수로 ‘현재 레코드의 다음 레코드’를 구하고 두 레코드의 숫자 차이를 diff 필드에 저장
    • 결합을 사용하지 않으므로 성능이 안정적

테이블에 존재하는 시퀀스 구하기

• 집합 지향적 방법 - 다시, 집합의 경계선

  SELECT MIN(num) AS low,
             '~',
    	 MAX(num) AS high
    FROM (SELECT N1.num,
   							 COUNT(N2.num) - N1.num
          FROM Numbers N1 INNER JOIN Numbers N2
   						ON N2.num <= N1.num
      	 GROUP BY N1.num) N(num, gp)
   GROUP BY gp
  

• 절차 지향적 방법 - 다시 ‘다음 레코드 하나’와 비교

  SELECT low, high
    FROM (SELECT low,
   							 CASE WHEN high IS NULL
                    THEN MIN(high)
                    			OVER (ORDER BY seq
                       					ROWS BETWEEN CURRENT ROW
                            										AND UNBOUNDED FOLLOWING)
   							 			ELSE high END AS high
      	  FROM (SELECT CASE WHEN COALESCE(prev_diff, 0) <> 1
         										THEN num ELSE NULL END AS low,
                   		 CASE WHEN COALESCE(next_diff, 0) <> 1
         										THEN num ELSE NULL END AS high,
                   		 seq
                	FROM (SELECT num,
                 							 MAX(num)
                         			 OVER(ORDER BY num
                             				 ROWS BETWEEN 1 PRECEDING
                                  						AND 1 PRECEDING) AS prev_diff,
                               ROW_NUMBER() OVER (ORDER BY num) AS seq
                          FROM Numbers) TMP1 ) TMP2) TMP3
   WHERE low IS NOT NULL
  

  • TMP1은 현재 레코드와 전후의 레코드에 있는 num 값의 차이를 구함
  • max_diff는 다음 레코드의 num에서 현재 레코드의 num을 뺀 값이고, prev_diff는 현재 레코드의 num에서 이전 레코드의 num을 뺀 값
    • next_diff나 prev_diff가 1보다 크다는 것은 단절된 부분이 있음을 의미
  • TMP3에서 동일한 레코드에 low와 high 필드가 존재하지 않는 경우를 정리
  • WHERE low IS NOT NULL로 불필요한 레코드 제거
  • 서브쿼리 스캔의 비용은 테이블의 크기에 비례하므로 성능 측면에서 집합 지향 쿼리에 비해 좋다고 단언할 수는 없음

    시퀀스 객체, IDENTITY 필드, 채번 테이블

• 시퀀스 객체, IDENTITY 필드는 최대한 사용하지 않고 꼭 필요한 부분에만 사용
• IDENTITY 필드보다는 시퀀스 객체를 활용
• 시퀀스 객체는 MySQL이 지원하지 않고, IDENTITY 필드는 Orcale이 지원하지 않음

  시퀀스 객체

  ```sql CREATE SEQUENCE testseq START WITH 1 INCREMENT BY 1 MAXVALUE 1000 MINVALUE 1 CYCLE

INSERT INTO HogeTbl VALUES(NEXT VALUE FOR nextval, ‘a’, ‘b’, …) ```

• 시퀀스 객체의 문제점
  • 표준화가 늦어서 구현에 따라 구문이 달라 이식성이 없고, 사용할 수 없는 구현도 있음
  • 시스템에서 자동으로 생성되는 값이므로 실제 엔티티 속성이 아님
  • 성능적인 문제 발생
• 시퀀스 객체로 발생하는 성능 문제
  • 시퀀스 객체의 로직에 의존하는 문제
    • 시퀀스 객체는 유일성, 연속성, 순서성을 만족
    • 동시 실행을 제어하기 위하여 락을 사용하므로 성능 저하 및 오버헤드
• 시퀀스 객체로 발생하는 성능 문제의 대처
  • CACHE: 새로운 값이 필요할 때마다 메모리에 읽어들일 필요가 있는 값의 수를 설정
    • 시스템 장애 시 연속성 보장 불가능
  • NOORDER: 순서성을 담보하지 않으므로 오버헤드 감소
    • 순서성이 필요한 경우 사용할 수 없음
• 순번을 키로 사용할 때의 성능 문제
  • 순번처럼 비슷한 데이터를 연속적으로 INSERT하면 물리적으로 같은 영역에 저장됨
  • 저장소의 특정 물리적 블록에만 I/O 부하가 커지므로 성능 악화 발생 (Hot Spot, Hot Block)
  • RDB 설계 특성상 이러한 물리적인 계층의 접근 패턴을 사용자가 바꿀 수 없음
• 순번을 키로 사용할 때의 성능 문제에 대처
  • DBMS 내부에서 변화를 주어 제대로 분산할 수 있는 구조(일종의 해시)를 사용
    • INSERT 구문 자체는 빨라지지만 SELECT 구문의 성능이 나빠질 위험이 있음
  • 인덱스에 복잡한 필드를 추가하여 데이터의 분산도를 높임
    • 인덱스에 불필요한 필드를 키로 추가하는 것은 좋은 설계가 아님

      IDENTITY 필드

• 테이블의 필드로 정의하고, 테이블에 INSERT가 발생할 때마다 자동으로 순번을 붙여주는 기능
• 테이블에 종속적이므로 CACHE나 NOORDER를 지정할 수 없어 성능상 이점 없음

  채번 테이블

• 시퀀스 객체나 IDENTITY 필드를 모두 지원하지 않을 경우 사용
• 개선 방법이 없으므로 사용할 이유가 없음