페이징과 대량 조회 성능

SELECT *
FROM (
  SELECT a.*, ROWNUM AS rnum
  FROM (
    SELECT order_id, order_dt, amount
    FROM   orders
    WHERE  status_cd = 'DONE'
    ORDER BY order_dt DESC
  ) a
  WHERE ROWNUM <= 30      -- 끝 지점: 여기서 미리 잘라 냅니다
)
WHERE rnum > 20;          -- 시작 지점

3중 구조인 이유가 있습니다. 가장 안쪽에서 정렬하고, 중간에서 ROWNUM으로 끝까지만 잘라 내고, 바깥에서 시작 지점을 거릅니다. 중간 단계에서 미리 자르는 것이 성능의 핵심이라 이 형태를 함부로 단순화하면 느려집니다.

SELECT order_id, order_dt, amount
FROM (
  SELECT order_id, order_dt, amount,
         ROW_NUMBER() OVER (ORDER BY order_dt DESC) AS rnum
  FROM   orders
  WHERE  status_cd = 'DONE'
)
WHERE rnum BETWEEN 21 AND 30;

읽기는 쉽지만 ROWNUM 방식처럼 중간에서 잘라 내는 최적화가 덜 걸릴 수 있습니다. 건수가 많으면 실행계획을 비교해 봅니다.

SELECT order_id, order_dt, amount
FROM   orders
WHERE  status_cd = 'DONE'
ORDER BY order_dt DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

가장 간결하고 MSSQL과도 같은 문법입니다. 다만 운영 DB가 11g 이하면 못 씁니다. 대상 버전을 먼저 확인하고, 레거시 시스템이면 1세대 형태를 읽을 줄 알아야 합니다.

-- 항상 0건 (오류가 아니라 결과가 없습니다)
SELECT * FROM orders WHERE ROWNUM > 10;

-- 정렬보다 ROWNUM이 먼저 적용됩니다
SELECT * FROM orders
WHERE  ROWNUM <= 10
ORDER BY amount DESC;
--> 금액 상위 10건이 아니라, 아무 10건을 뽑아 정렬한 결과입니다

-- 의도한 상위 10건
SELECT * FROM (
  SELECT * FROM orders ORDER BY amount DESC
)
WHERE ROWNUM <= 10;

두 번째 쿼리가 특히 위험합니다. 오류 없이 그럴듯한 결과를 주기 때문에 잘못을 알아채기 어렵습니다. 상위 N건은 반드시 정렬을 인라인뷰 안에 넣습니다.

-- 첫 페이지
SELECT order_id, order_dt, amount
FROM   orders
WHERE  status_cd = 'DONE'
ORDER BY order_dt DESC, order_id DESC
FETCH FIRST 10 ROWS ONLY;

-- 다음 페이지: 마지막으로 본 값을 조건으로
SELECT order_id, order_dt, amount
FROM   orders
WHERE  status_cd = 'DONE'
AND    (order_dt < :last_order_dt
        OR (order_dt = :last_order_dt AND order_id < :last_order_id))
ORDER BY order_dt DESC, order_id DESC
FETCH FIRST 10 ROWS ONLY;

정렬 키가 유일하지 않으면 동점 처리를 위해 보조 키를 함께 써야 합니다. 대신 특정 페이지로 바로 점프하는 것은 안 되므로, 페이지 번호가 필요한 화면에는 쓸 수 없습니다.

-- 나쁨: 값마다 다른 SQL이 됩니다
SELECT * FROM orders WHERE customer_id = 100;
SELECT * FROM orders WHERE customer_id = 101;
SELECT * FROM orders WHERE customer_id = 102;
--> 공유 풀에 세 개의 서로 다른 문장이 쌓입니다

-- 좋음: 문장 하나를 재사용합니다
SELECT * FROM orders WHERE customer_id = :customer_id;

MyBatis에서는 #{} 가 바인드 변수, ${} 는 문자열 치환입니다. 값에 ${} 를 쓰면 하드 파싱과 SQL 인젝션이 함께 따라옵니다. 컬럼명이나 정렬 방향처럼 구조가 바뀌는 곳에만 제한적으로 씁니다.