반복문
반복문 의존증
- SQL에는 반복문이 없음
- SQL을 적용하기 힘든 작업에 무리하게 SQL을 사용할 필요는 없음
- 미들웨어 또는 O/R 맵퍼 등의 프레임워크가 내부적으로 반복계 코드를 사용하기도 함
- 반복문 의존증 예시
- SQL 실행의 오버헤드
- 전처리
- SQL 구문을 네트워크로 전송
- 데이터베이스 연결
- SQL 구문 파스
- SQL 구문의 실행 계획 생성 또는 평가
- 후처리
- 결과 집합을 네트워크로 전송
- 1, 5는 오버헤드 중 영향이 적음
- 2는 커넥션 풀을 사용하여 오버헤드를 줄임
- 3, 4는 오버헤드 중 가장 영향이 큼
- 반복계를 사용할 경우 3이 반복되기 때문에 오버헤드가 높아짐
- 전처리
- 병렬 분산이 어려움
- 반복계에서 실행하는 SQL 구문은 대부분 단순해서 1회의 구문이 접근하는 데이터양이 적음 → I/O 병렬화가 어려움
- 데이터베이스의 진화로 인한 혜택을 받을 수 없음
- 반복계를 포장계로 다시 작성 → 실제 상황에서는 사용하기 어려움
- 각각의 SQL 을 빠르게 수정 → 각각의 구문이 간단하여 튜닝의 여지가 없음
- 다중화 처리 → 데이터를 분할할 수 있는 명확한 키가 없거나, 순서가 중요하거나, 병렬화했을 때 물리 리소스가 부족하면 사용할 수 없음
반복계의 장점
- 실행 계획의 안정성 → 실행 계획의 변동 위험이 거의 없음
- 예상 처리 시간의 정밀도
- 트랜잭션 제어가 편리 → 중간에 오류 발생 시 발생 구간에서 다시 처리 가능
SQL에서는 반복을 어떻게 표현할까?
포인트는 CASE 식과 윈도우 함수
- SQL에서는 CASE 식과 윈도우 함수로 반복을 대신함
INSERT INTO Sales2 SELECT company, year, sale, CASE SIGN(sale - MAX(sale)) OVER ( PARTITION 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옵션을 사용하여 레코드 범위를 제한 - 인접한 우편번호 찾기: 입력받은 우편번호(4130033)와 가장 가까운 우편번호 검색 ```sql CREATE TABLE PostalCode (pcode CHAR(7), district_name VARCHAR(256), CONSTRAINT pk_pcode PRIMARY KEY(pcode));
INSERT INTO PostalCode VALUES (‘4130001’, ‘시즈오카 아타미 이즈미’) INSERT INTO PostalCode VALUES (‘4130002’, ‘시즈오카 아타미 이즈산’) INSERT INTO PostalCode VALUES (‘4130103’, ‘시즈오카 아타미 아지로’) INSERT INTO PostalCode VALUES (‘4130041’, ‘시즈오카 아타미 아오바초’) INSERT INTO PostalCode VALUES (‘4103213’, ‘시즈오카 이즈 아오바네’) INSERT INTO PostalCode VALUES (‘4380824’, ‘시즈오카 이와타 아카’)
- 결국, 순위 붙이기 문제
- 우편번호 순위를 매기는 쿼리
```sql
SELECT pcode,
district_name,
CASE WHEN pcode = '4130033' THEN 0
WHEN pcode LIKE '413003%' THEN 1
WHEN pcode LIKE '41300%' THEN 2
WHEN pcode LIKE '4130%' THEN 3
WHEN pcode LIKE '413%' THEN 4
WHEN pcode LIKE '41%' THEN 5
WHEN pcode LIKE '4%' THEN 6
ELSE NULL END AS rank
FROM PostalCode;
- 가까운 우편번호를 구하는 쿼리
SELECT pcode, district_name, FROM PostalCode; WHERE CASE WHEN pcode = '4130033' THEN 0 WHEN pcode LIKE '413003%' THEN 1 WHEN pcode LIKE '41300%' THEN 2 WHEN pcode LIKE '4130%' THEN 3 WHEN pcode LIKE '413%' THEN 4 WHEN pcode LIKE '41%' THEN 5 WHEN pcode LIKE '4%' THEN 6 ELSE NULL END = (SELECT MIN(CASE WHEN pcode = '4130033' THEN 0 WHEN pcode LIKE '413003%' THEN 1 WHEN pcode LIKE '41300%' THEN 2 WHEN pcode LIKE '4130%' THEN 3 WHEN pcode LIKE '413%' THEN 4 WHEN pcode LIKE '41%' THEN 5 WHEN pcode LIKE '4%' THEN 6 ELSE NULL END) FROM PostalCode); - 테이블 풀 스캔 2회
- 윈도우 함수를 사용한 스캔 횟수 감소
SELECT pcode, district_name, FROM (SELECT pcode, district_name, CASE WHEN pcode = '4130033' THEN 0 WHEN pcode LIKE '413003%' THEN 1 WHEN pcode LIKE '41300%' THEN 2 WHEN pcode LIKE '4130%' THEN 3 WHEN pcode LIKE '413%' THEN 4 WHEN pcode LIKE '41%' THEN 5 WHEN pcode LIKE '4%' THEN 6 ELSE NULL END AS hit_code, MIN(CASE WHEN pcode = '4130033' THEN 0 WHEN pcode LIKE '413003%' THEN 1 WHEN pcode LIKE '41300%' THEN 2 WHEN pcode LIKE '4130%' THEN 3 WHEN pcode LIKE '413%' THEN 4 WHEN pcode LIKE '41%' THEN 5 WHEN pcode LIKE '4%' THEN 6 ELSE NULL END) OVER(ORDER BY WHEN pcode = '4130033' THEN 0 WHEN pcode LIKE '413003%' THEN 1 WHEN pcode LIKE '41300%' THEN 2 WHEN pcode LIKE '4130%' THEN 3 WHEN pcode LIKE '413%' THEN 4 WHEN pcode LIKE '41%' THEN 5 WHEN pcode LIKE '4%' THEN 6 ELSE NULL END) AS min_code FROM PostalCode) Foo WHERE hit_code = min_code; - 인접 리스트 모델과 재귀 쿼리: 현재 주소 뿐만 아니라 과거 주소까지 관리하는 문제 ```sql CREATE TABLE PostalHistory (name CHAR(1), pcode CHAR(7), new_pcode CHAR(7), CONSTRAINT pk_name_pcode PRIMARY KEY(name, pcode));
CREATE INDEX idx_new_pcode ON PostalHistory(new_pcode);
INSERT INTO PostalHistory VALUES (‘A’, ‘4130001’, ‘4130002’); INSERT INTO PostalHistory VALUES (‘A’, ‘4130002’, ‘4130103’); INSERT INTO PostalHistory VALUES (‘A’, ‘4130001’, NULL); INSERT INTO PostalHistory VALUES (‘B’, ‘4130041’, NULL); INSERT INTO PostalHistory VALUES (‘B’, ‘4103213’, ‘4380824’); INSERT INTO PostalHistory VALUES (‘C’, ‘4380824’, NULL);
- 가장 오래된 주소 검색: 재귀 공통 테이블 식(recursion common table expression)
```sql
WITH RECURSIVE Explosion (name, pcode, new_pcode, depth)
AS
(SELECT name, pcode, new_pcode, 1
FROM PostalHistory
WHERE name = 'A'
AND new_pcode IS NULL -- 검색시작
UNION
SELECT Child.name, Child.pcode, Child.new_pcode, depth + 1
FROM Explosion AS Parent, PostalHistory AS Child
WHERE Parent.pcode = Child.new_pcode
AND Parent.name = Child.name)
-- 메인 SELECT 구문
SELECT name, pcode, new_pcode
FROM Explosion
WHERE depth = (SELECT MAX(depth)
FROM Explosion);
- 표준 SQL에 포함되어 있는 내용으로 구현에 의존적이지 않음
- 재귀 공통 테이블은 비교적 최근에 만들어진 기능으로 구현되지 않았거나, 실행 계획이 최적화되지 않은 DBMS에서는 다른 방법을 사용해야 함
중첩 집합 모델: 각 레코드의 데이터를 집합(원)으로 보고, 계층 구조를 집합의 중첩 관계로 나타냄 ```sql CREATE TABLE PostalHistory2 (name CHAR(1), pcode CHAR(7), lft REAL NOT NULL, rgt REAL NOT NULL
CONSTRAINT pk_name_pcode2 PRIMARY KEY(name, pcode), CONSTRAINT uq_name_lft UNIQUE (name, lft), CONSTRAINT uq_name_rgt UNIQUE (name, rgt), CHECK(lft<rgt));
INSERT INTO PostalHistory2 VALUES(‘A’, ‘4130001’, 0, 27); INSERT INTO PostalHistory2 VALUES(‘A’, ‘4130002’, 9, 18); INSERT INTO PostalHistory2 VALUES(‘A’, ‘4130103’, 12, 15); INSERT INTO PostalHistory2 VALUES(‘B’, ‘4130041’, 0, 27); INSERT INTO PostalHistory2 VALUES(‘B’, ‘4103213’, 0, 27); INSERT INTO PostalHistory2 VALUES(‘C’, ‘4380824’, 9, 18);
- 중첩 집합 모델에서는 우편번호의 데이터를 수치선 상에 존재하는 원으로 가정
- `lgt`, `rgt`는 원의 왼쪽 끝과 오른쪽 끝에 위치하는 좌표를 나타냄
- 이러한 모델의 테이블에서 A의 가장 오래된 주소는 가장 바깥쪽에 있는 원 → `NOT EXISTS`를 사용하여 구함
```sql
SELECT name, pcode
FROM PostalHistory2 PH1
WHERE name = 'A'
AND NOT EXISTS
(SELECT *
FROM PostalHistory2 PH2
WHERE PH2.name = 'A'
AND PH1.lft > PH2.lft);
바이어스의 공죄
- SQL은 ‘절차 지향형에서의 탈출’을 목표로 설계된 언어
- DBMS도 내부적으로는 절차 지향형 언어로 만들어져, 물리 데이터 접근 등은 절차 지향적인 방법으로 수행됨
- 이러한 절차적 계층을 은폐하는 것이 SQL의 이념
- RDB에서 고성능을 실현하려면 절차 지향적인 바이어스를 떼어내고 집합 지향의 사고 방식을 가져야 함
- 반복계와 포장계의 장단점을 고려하고 어떤 처리 방식을 채택할지 판단해야 함