정렬하기 전에 몇 건만 가져오라고 하면, 데이터베이스는 정말 아무 몇 건이나 가져다 줍니다.
풀고 시작
문제 1. 급여가 가장 높은 사원 다섯 명을 뽑으려 합니다. 행 번호를 제한하는 조건과 정렬을 함께 쓸 때 반드시 지켜야 할 것은?
행 번호는 정렬 전에 매겨지므로 조건을 그대로 쓰면 아무 다섯 건이 잡힙니다. 인라인 뷰로 먼저 정렬한 뒤 그 결과에 번호를 매기거나, 정렬 다음에 행 수를 제한하는 표준 구문을 써야 합니다.
Top N 쿼리, 순서가 전부입니다
"매출이 가장 큰 상위 10개 상품"처럼 정렬한 결과의 앞부분만 필요한 질의를 Top N 쿼리라 부릅니다. 개념은 단순한데 구현에서 사고가 자주 납니다. 이유는 하나입니다. 행을 제한하는 시점과 정렬하는 시점의 순서입니다.
전통적인 방식은 결과 행에 자동으로 붙는 행 번호를 조건으로 걸어 제한하는 것입니다. 문제는 이 번호가 정렬 전에 매겨진다는 점입니다. 정렬 조건과 번호 조건을 같은 질의에 나란히 쓰면, 데이터베이스는 먼저 아무 행 다섯 개를 잡고 그 다섯 개만 정렬해 줍니다. 상위 다섯이 아니라 임의의 다섯입니다. 결과가 그럴듯해 보여서 더 위험하죠.
해법은 두 가지입니다. 인라인 뷰로 먼저 정렬한 뒤 바깥에서 행을 제한하는 것, 또는 11강의 순위 함수로 등수를 매긴 뒤 바깥에서 등수 조건을 거는 것입니다. 후자는 공동 순위를 어떻게 처리할지 고를 수 있어 더 정확합니다. 상위 5등을 뽑는데 공동 5등이 셋이면 여섯 명이 나오는 것이 맞는지 다섯 명이 맞는지를 우리가 결정할 수 있으니까요.
표준 SQL에는 정렬 뒤에 행 수를 제한하는 구문이 있습니다. OFFSET과 FETCH FIRST입니다. ORDER BY 다음에 오므로 순서 문제가 애초에 생기지 않고, 페이지 나누기에도 그대로 쓰입니다. 제품에 따라 지원 여부가 다르지만 최근 버전에서는 대체로 쓸 수 있습니다.
페이지 나누기의 함정
목록을 페이지로 나눌 때도 같은 문제가 다른 얼굴로 나옵니다. 정렬 기준이 유일하지 않으면 페이지마다 같은 행이 반복되거나 빠집니다.
이유는 5강에서 말한 그대로입니다. 정렬 기준 값이 같은 행들 사이의 순서는 보장되지 않습니다. 등록일자로만 정렬한 목록에서 같은 날짜의 글이 여러 개면, 1페이지를 뽑을 때와 2페이지를 뽑을 때 그 안의 순서가 달라질 수 있습니다. 그러면 어떤 글은 두 페이지에 모두 나오고 어떤 글은 어디에도 나오지 않습니다.
해법은 단순합니다. 정렬 기준에 유일한 컬럼을 마지막으로 하나 더 붙이세요. 등록일자로 정렬하고 그다음 기준으로 게시글 번호를 주면 순서가 확정됩니다. 이 한 줄이 원인 모를 목록 버그를 대부분 없앱니다.
계층형 질의, 자기 자신을 참조하는 데이터
조직도를 생각해 보죠. 사원 테이블에 관리자 사번 컬럼이 있어 자기 테이블의 다른 행을 가리킵니다. 이런 구조에서 특정 사원의 부하 전체를 몇 단계든 끝까지 따라 내려가야 할 때 쓰는 것이 계층형 질의입니다.
원리는 반복입니다. 먼저 시작점이 되는 행을 찾고, 그 행의 키를 부모로 갖는 행들을 찾고, 다시 그 행들을 부모로 갖는 행들을 찾는 일을 더 나올 것이 없을 때까지 되풀이합니다. 그래서 계층형 질의에는 언제나 두 부분이 있습니다. 어디서 시작할지를 정하는 조건과 어떻게 이어 갈지를 정하는 조건입니다.
구현 방식은 크게 두 갈래입니다. 하나는 제품 고유의 계층 질의 구문을 쓰는 방식이고, 다른 하나는 표준 SQL의 재귀 공통 테이블 식을 쓰는 방식입니다. 후자는 시작 질의와 반복 질의를 합집합으로 묶어 스스로를 참조하게 만듭니다. 표준이라 이식성이 좋습니다.
계층형 질의에서 함께 얻고 싶은 정보가 몇 가지 있습니다. 지금 몇 단계 깊이인지, 뿌리에서 여기까지의 경로가 무엇인지, 아래에 자식이 있는지입니다. 제품마다 이를 위한 의사 컬럼이나 함수를 제공합니다. 그리고 순환 참조에 주의해야 합니다. A가 B의 부모이고 B가 A의 부모인 데이터가 들어가면 질의가 끝나지 않습니다. 제품들이 순환을 감지해 멈추는 장치를 제공하지만, 애초에 그런 데이터가 들어오지 않게 막는 것이 옳습니다.
셀프 조인, 계층의 얕은 사촌
계층이 정해진 깊이만큼이라면 계층형 질의까지 갈 필요가 없습니다. 6강에서 언급한 셀프 조인으로 충분합니다.
사원과 그 사원의 직속 관리자 이름을 함께 보여 주는 일이 그렇습니다. 사원 테이블을 두 번 불러 서로 다른 별칭을 주고, 한쪽의 관리자 사번과 다른 쪽의 사번을 이으면 됩니다. 한 단계면 조인 한 번, 두 단계면 조인 두 번입니다.
문제는 깊이가 정해져 있지 않을 때입니다. 조직이 몇 단계인지 모른다면 조인을 몇 번 써야 할지도 알 수 없죠. 깊이가 고정이면 셀프 조인, 깊이를 모르면 계층형 질의라고 기준을 잡아 두면 판단이 빠릅니다.
한 가지 덧붙이면, 셀프 조인에서 최상위 관리자는 관리자 사번이 비어 있습니다. 내부 조인으로 이으면 그 행이 사라지니, 사장까지 목록에 남기려면 7강의 외부 조인을 써야 합니다. 여기서도 모델의 선택성이 조인의 종류를 정하는 셈입니다.
인출 문제
문제 1. 정렬과 행 제한을 함께 쓸 때 상위 N건이 아니라 임의의 N건이 나오는 이유는?
번호가 정렬 전에 매겨지므로 조건을 같은 질의에 그대로 쓰면 임의의 행이 먼저 잡히고 그것만 정렬됩니다. 인라인 뷰로 정렬을 먼저 끝내거나 정렬 뒤에 행 수를 제한하는 구문을 써야 합니다.
문제 2. 목록을 페이지로 나눌 때 같은 행이 두 페이지에 나오거나 빠지는 현상의 원인은?
정렬 기준 값이 같은 행들의 순서는 보장되지 않으므로 조회 시점마다 달라질 수 있습니다. 정렬 기준 끝에 유일한 컬럼을 하나 더 붙여 순서를 확정하면 해결됩니다.
문제 3. 계층형 질의가 반드시 갖추어야 하는 두 요소는?
어디서 출발하고 어떻게 이어 갈지가 정해져야 반복이 성립합니다. 재귀 공통 테이블 식에서도 시작 질의와 반복 질의가 이 두 요소에 각각 대응합니다.
문제 4. 셀프 조인과 계층형 질의를 고르는 기준으로 가장 적절한 것은?
깊이가 정해져 있으면 그만큼 조인을 쓰면 되지만 깊이를 모르면 조인 횟수를 정할 수 없습니다. 이때 반복 구조가 필요하고 그것이 계층형 질의입니다.
더 풀기
출제기준의 세세항목을 따라 이 강의 범위에서 새로 낸 문제입니다. 모의고사도 여기에서 뽑습니다.
묶음 1
문제 1. Top N 쿼리를 가장 잘 설명한 것은?
순서가 먼저 정해져야 앞쪽이라는 말이 성립합니다. 그래서 Top N 쿼리는 정렬과 떼어 놓을 수 없습니다.
문제 2. 정렬 없이 상위 몇 건만 가져오면 어떤 문제가 있나요?
집합에는 순서가 없습니다. 정렬을 명시하지 않으면 우연히 읽힌 순서대로 잘라 내는 셈이 됩니다.
문제 3. 오라클에서 행 번호를 매기는 의사 컬럼을 조건에 쓸 때 주의할 점으로 옳은 것은?
이 번호는 조건을 통과한 순서대로 붙습니다. 정렬은 그 뒤에 일어나므로 정렬 결과의 상위와는 다릅니다.
문제 4. 그 의사 컬럼에 세 번째 행이라는 조건을 직접 걸면 어떻게 되나요?
번호는 행이 결과에 담길 때마다 하나씩 올라갑니다. 첫 행이 일이 되지 못하고 탈락하면 다음 행도 다시 일에 도전하므로 영원히 삼이 되지 못합니다.
문제 5. 그 의사 컬럼으로 상위 다섯 건을 얻는 조건으로 알맞은 것은?
이하 조건은 첫 행부터 차례로 통과하므로 성립합니다. 같음이나 초과 조건은 앞의 이유로 결과가 비게 됩니다.
문제 6. 표준 SQL에서 상위 몇 건을 제한하는 절로 알맞은 것은?
표준은 FETCH 절입니다. LIMIT과 TOP은 제품 고유 문법이고 ROWNUM은 오라클의 의사 컬럼입니다.
문제 7. 표준 문법에서 앞의 몇 건을 건너뛰고 그다음부터 가져오려면 어떤 절을 함께 쓰나요?
OFFSET으로 건너뛸 행 수를 지정하고 FETCH로 가져올 행 수를 지정합니다.
문제 8. 스무 건씩 보여 주는 목록의 세 번째 페이지를 가져오려면 몇 건을 건너뛰어야 하나요?
첫 페이지와 두 번째 페이지의 마흔 건을 건너뛰고 그다음 스무 건을 가져옵니다.
문제 9. 페이지 번호가 커질수록 OFFSET 방식이 느려지는 이유로 가장 알맞은 것은?
건너뛴다고 해서 읽지 않는 것이 아닙니다. 백 페이지쯤 가면 앞의 이천 건을 매번 읽습니다.
문제 10. 깊은 페이지를 빠르게 가져오는 대안으로 가장 알맞은 것은?
이 방식은 매번 시작 위치를 조건으로 좁히므로 페이지가 깊어져도 읽는 양이 늘지 않습니다. 대신 임의의 페이지로 건너뛰기는 어렵습니다.
묶음 2
문제 1. 페이지 나누기에서 정렬 기준에 동점이 많으면 생기는 문제는?
순서가 결정적이지 않으면 자르는 위치도 흔들립니다. 유일한 컬럼을 정렬 기준 끝에 하나 더해야 합니다.
문제 2. 페이지를 나눌 때 정렬 기준 마지막에 기본키를 추가하는 이유는?
순서가 실행할 때마다 같아야 페이지 나누기가 신뢰할 만해집니다.
문제 3. 계층형 질의가 필요한 데이터의 특징으로 가장 알맞은 것은?
조직도나 부품 구성처럼 자기 자신을 참조하는 구조가 계층형 질의의 대상입니다.
문제 4. 계층형 질의에서 시작 지점을 지정하는 절은?
START WITH가 루트를 정하고 CONNECT BY가 이어 가는 규칙을 정합니다.
문제 5. 계층형 질의에서 부모와 자식을 잇는 규칙을 적는 절은?
CONNECT BY에 이전 행과 현재 행의 관계를 적습니다. PRIOR 키워드가 어느 쪽이 이전 행인지 표시합니다.
문제 6. PRIOR 키워드가 하는 일로 가장 알맞은 것은?
PRIOR가 붙은 쪽이 부모 행의 값입니다. 어느 컬럼에 붙이느냐에 따라 위로 올라갈지 아래로 내려갈지가 정해집니다.
문제 7. 계층형 질의에서 현재 행의 깊이를 알려 주는 값은?
LEVEL은 최상위가 일이고 한 단계 내려갈 때마다 하나씩 늘어납니다.
문제 8. 최상위 사장 아래 부장이 있고 그 아래 과장이 있다면 과장의 LEVEL은?
사장이 일, 부장이 이, 과장이 삼입니다.
문제 9. 계층 구조를 들여쓰기로 보여 주려면 무엇을 활용하나요?
깊이만큼 왼쪽을 채우면 조직도처럼 보입니다.
문제 10. 같은 부모를 가진 형제 행들의 순서를 정하는 절은?
일반 정렬을 쓰면 계층 구조가 흐트러집니다. 형제 안에서만 정렬하는 전용 절이 필요합니다.
묶음 3
문제 1. 계층형 질의에서 순환 참조가 생기면 어떤 일이 벌어지나요?
사원이 자기 자신을 관리자로 가리키거나 두 사원이 서로를 가리키면 순환이 생깁니다. 순환을 감지해 끊는 옵션이 제품마다 있습니다.
문제 2. 최상위 행을 판별하는 조건으로 가장 흔히 쓰이는 것은?
부모가 없다는 사실을 NULL로 표시하는 것이 일반적입니다. 그 조건이 곧 시작 지점이 됩니다.
문제 3. 계층형 질의의 WHERE 절 조건은 언제 적용되나요?
그래서 중간 노드를 WHERE로 걸러도 그 아래 자식은 사라지지 않습니다. 가지치기를 하려면 CONNECT BY 쪽에 조건을 붙여야 합니다.
문제 4. 특정 부서 아래의 조직만 잘라 내고 싶다면 조건을 어디에 두어야 하나요?
따라가는 규칙 자체를 바꾸어야 가지가 잘립니다. 결과를 나중에 거르는 것과는 다릅니다.
문제 5. 재귀 공통 테이블 표현식으로 계층을 처리할 때의 구조로 옳은 것은?
앵커 부분과 재귀 부분을 UNION ALL로 잇는 것이 표준 구조입니다. 제품 고유 문법 없이 계층을 다룰 수 있습니다.
문제 6. 셀프 조인으로 계층을 처리할 때의 한계로 가장 알맞은 것은?
조인을 한 번 더 붙일 때마다 한 단계씩 내려갑니다. 깊이가 가변이면 조인 수를 정할 수 없습니다.
문제 7. 사원과 그 사원의 직속 관리자 이름만 함께 보면 되는 경우에 알맞은 방법은?
한 단계만 필요하다면 셀프 조인이 간단하고 읽기 쉽습니다. 계층 전체를 훑을 필요가 없습니다.
문제 8. 관리자가 없는 최상위 사원도 함께 보이게 하려면 셀프 조인을 어떻게 해야 하나요?
관리자 컬럼이 NULL이면 짝을 찾지 못해 내부 조인에서 빠집니다. 외부 조인으로 남겨야 합니다.
문제 9. 상위 몇 건을 뽑을 때 정렬 순서와 같은 인덱스가 있으면 유리한 이유는?
인덱스는 이미 정렬되어 있습니다. 앞에서 몇 건만 읽으면 끝나므로 대상이 아무리 커도 비용이 일정합니다.
문제 10. 다음 중 계층형 질의에 대한 설명으로 옳지 않은 것은?
WHERE는 계층을 다 만든 뒤에 적용됩니다. 가지를 자르려면 따라가는 규칙 쪽에 조건을 붙여야 합니다.