책뿌수기 - SQL 레벨업 8
8장. SQL의 순서 - 깨어나는 절차 지향 sql은 관계 모델의 이론에 기초하고 있기 때문에 순번을 다루기 위한 기능이 없다. 하지만 레코드에 순번을 붙여 처리하는 경우가 많아 관련기능(시퀀스 객체, ID 필드, 윈도우 함수)을 추가하고 있다. 23강. 레코드에 순번 붙이기 1. 기본키가 한 개의 필드일 경우 1) 윈도우 함수로 사용 ROW_NUMBER 함수를 사용 1 2 3 SELECT student_id, ROW_NUMBER() OVER (ORDER BY student_id) AS Seq FROM Weights; 2) 상관 서브쿼리 사용 1 2 3 4 5 SELECT student_id, (SELECT COUNT(*) FROM Weight W2 WHERE W2.student_id <= W1.student_id) AS Seq FROM Weights W1; 기능은 동일하지만 1) 방법의 성능이 좋다. 스캔 횟수 1):1회, 2):2회 2. 기본 키가 여러 개의 필드로 구성되는 경우 1) 윈도우 함수를 사용 1 2 3 SELECT class, student_id, ROW_NUMBER() OVER (ORBER BY class, stduent_id) AS Seq FROM Weight2; 2) 상관 서브쿼리를 사용 다중 필드 비교하기(문자, 숫자, 3개 비교도 가능) 1 2 3 4 5 6 SELECT class, student_id, (SELECT COUNT(*) FROM Weight W2 WHERE (W2.class, W2.student_id) <= (W1.class, W1.stduent_id) AS Seq FROM Weight W1; 3. 그룹마다 순번을 붙이는 경우 1) 윈도우 함수를 사용 class 필드에 PARTITION BY 적용 1 2 3 SELECT class, student_id, ROW_NUMBER() OVER(PARTITION BY class ORDER BY student_id) AS Seq FROM Weight2; 2) 상관서브쿼리를 사용 1 2 3 4 5 6 SELECT class, student_id, (SELECT COUNT(*) FROM COUNT(*) WHERE W2.class = W1.class AND W2.student_id <= W1.student_id) AS Seq FROM Weight2 W1; 4. 순번과 갱신 1) 윈도우 함수를 사용 셀렉트 쿼리를 SET에 넣으면 됨 1 2 3 4 5 6 7 8 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 = SeqTbls.class AND Weights3.student_id = SeqTbl.student_id) 2) 상관 서브쿼리를 사용 1 2 3 4 5 UPDATE Weight3 SET seq = (SELECT COUNT(*) FROM Weight3 W2 WHERE W2.class = Weights3.class AND W2.student_id <= Weight3.student_id) 24강. 레코드에 순번 붙이기 응용 1. 중앙값 구하기 1) 집합 지향적 방법 1 2 3 4 5 6 7 8 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) >= COUNT(*)/2 AND SUM(CASE WHEN W2.weight <= W1.weight THEN 1 ELSE 0 END) >= COUNT(*)/2) TMP; 코드가 복잡하다 성능이 나쁘다. w1과 w2간에 결합이 발생 2) 절차 지향적 방법 1 - 세계의 중심을 향해 sql에서 자연수의 특징을 활용하면 ‘양쪽 끝부터 숫자 세기’를 할 수 있다 1 2 3 4 5 6 SELECT AVG(weight) AS median FROM (SELECT weight, ROW_NUMBER() OVER (ORDER BY weight ASC, student_id ASC) AS hi, ROW_NUMBER() OVER (ORDER BY weight DESC, student_id DESC) AS lo FROM Weights) TMP WEHRE hi IN(lo, lo+1, lo+2); RANK 또는 DENSE_RANK를 사용해서는 안된다. 순위가 겹치거나 빌 수 있다. 테이블 접근 1회로 감소, 대신 정렬이 2회로 늘었다. ROW_NUMBER에서 사용하는 정렬이 오름/내림차순 2개라서 그렇다. 3) 절차 지향적 방법 2 - 2빼기 1은 1 성능적으로 개선하기 1 2 3 4 5 6 SELECT AVG(weight) FROM (SELECT weight, 2 * ROW_NUMBER() OVER(ORDER BY weight) - COUNT(*) OVER() AS diff FROM Weights) TMP WHERE diff BETWEEN 0 AND 2; 정렬리 1회로 줄어든다. 이 방법이 SQL 표준으로 중앙값을 구하는 가장 빠른 방법이다. 2. 순번을 사용한 테이블 분할 비어있는 자리 출력하기 1) 집합 지향적 방법 - 집합의 경계선 1 2 3 4 5 6 7 SELECT (N1.num + 1) AS gap_start, '~', (MIN(N2.min - 1) AS gap_end FROM Number N1 INNER JOIN Numbers N2 ON N2.num > N1.num GROUP BY N1.num HAVING (N1.num + 1) < MIN(N2.num); 코드도 간단하며 집합 지향적인 방식이라 좋다. 다만, 자기 결합을 사용해야 한다(Nested Loop). 2) 절차 지향적 방법 - 다음 레코드와 비교 컨셉 : 현재 레코드와 다음 레코드를 비교해 차이가 1이 아니면 1 2 3 4 5 6 7 8 9 10 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 FOLLOWING) - num FROM numbers) TMP (num, diff) WHERE diff <> 1 테이블 접근 1회, 정렬 1회로 안정적 성능 3. 테이블에 존재하는 시퀀스 찾기 친구 또는 가족 인원수에 맞게 자리를 예약하는 경우 활용됨 1) 집합 지향적 방법 - 다시, 집합의 경계선 1 2 3 4 5 6 7 8 9 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; 2) 집합 지향적 방법 - 다시, 다음 레코드 하나와 비교 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 SELECT low, high FROM(SELECT low, CASE WHEN high IS NULL THEN MIN(high) OVER (ORDER BY seq ROW 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 FOLLOWING AND 1 FOLLOWING) - num AS next_diff, 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; 25강. 시퀀스 객체, IDENTIFY 필드, 채번 테이블 표준 SQL에는 순번을 다루는 기능으로 시퀀셜 객체나 IDENTIFY 필드가 존재한다. 하지만 사용하는 것을 권장하지는 않고, 사용한다면 시퀀스 객체를 권한다. 1. 시퀀스 객체 테이블 또는 뷰처럼 스키마 내부에 존재하는 객체 중 하나 1 2 3 4 5 6 CREATE SEQUENCE testseq START WITH 1 INCREMENT BY 1 MAXVALUE 10000 MINVALUE 1 CYCLE; INSERT 구문에서 흔히 사용된다. 1) 시퀀스 객체의 문제점 표준화가 늦어서, 구현에 따라 구문이 달라 이식성이 없고, 사용할 수 없는 구현도 있다 시스템에서 자동으로 생성되는 값이므로 실제 엔티티 속성이 아니다. 성능적인 문제를 일으킨다 2) 시퀀스 객체로 발생하는 성능 문제 순서성(순번의 대소 관계가 유지됨), 유일성, 연속성 사용자 A가 시퀀스 객체에서 NEXT VALUE를 검색할 때의 처리 시퀀스 기개체에 배타 락을 적용 NEXT VALUEfmf rjator CURRENT VALUE를 1만큼 증가 시퀀스 객체에 배타 락을 해제 3) 시퀀스 객체로 발생하는 성능 문제의 대처 (1) CACHE 읽어들일 변수를 메모리에 설정하는 것, 다만 시스템 장애시 정상동작을 담보할 수 없다. (1) NOORDER 순서성을 담보하지 않음으로써 오버 헤드를 줄인다. 4) 순번을 키로 사용할 때의 성능 문제 Hot spot 과 관련된 문제임 DBMS는 비슷한 데이털르 연속적으로 INSERT하면 물리적으로 같은 영역에 저장한다. 이 때 특정 물리적 블록에만 I/O 부하가 커지므로 성능 악화가 발생 = Hot spot, Hot block 시퀀스 객체를 사용해 INSERT를 반복하는 경우 발생하고, 대처가 불가능 5) 순번을 키로 사용할 때의 성능 문제에 대처 (1) Oracle의 열 키 인덱스 연속된 값을 도입하는 경우라도 DBMS 내부에서 변화를 주어 제대로 분산할 수 있는 구조를 사용하는 것, 다만 SELECT 성능이 나빠질 수 있다. (2) 인덱스에 복잡한 필드를 추가해서 데이터의 분산도를 높인다. 논리적으로 좋은 설계가 아님 2. IDENTIFY 필드 ‘자동 순번 필드’라고도 한다. 테이블의 필드로 정의하고, INSERT 발생할때마다 자동을 순번을 붙여주는 기능이다. 시퀀스 객체에 비해 단점이 많다. 시퀀스 객체는 여러 테이블에서 사용가능하지만, IDENTIFY 필드는 특정 테이블에 국한된다. CACHE, NOORDER를 지정할 수도 없다. 이점이 거의 없다. 3. 채번 테이블 순번을 부여하기 위해 어플리케이션에서 채번 테이블이라는 것을 만들어 사용했었다. 구시대 유물이며 문제가 안생기기를 바라는 것이 최선(튜닝할 방법도 없다)