SQL을 작성할 줄 아는 것과 잘 작성하는 것은 다르다. 같은 결과를 돌려주는 쿼리라도 실행 계획에 따라 성능이 수십 배 이상 차이 날 수 있다. Oracle에서 SQL 튜닝은 단순히 인덱스를 추가하는 게 아니라, 옵티마이저가 어떻게 쿼리를 해석하는지 이해하는 데서 시작한다. 이 글에서는 실무에서 자주 쓰는 튜닝 접근법과 쿼리 작성 노하우를 정리한다.
실행 계획(Execution Plan)부터 확인한다
튜닝의 첫 단계는 실행 계획 확인이다. 쿼리가 느리다고 느껴지면 바로 수정하기 전에 반드시 실행 계획을 먼저 본다.
EXPLAIN PLAN FOR
SELECT *
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > 5000;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
SQL
복사
실행 계획에서 확인해야 할 핵심 항목은 다음과 같다.
•
FULL TABLE SCAN: 인덱스를 타지 않고 테이블 전체를 읽는 것. 대용량 테이블에서 발생하면 위험하다.
•
INDEX RANGE SCAN: 인덱스를 범위로 탐색하는 것. 일반적으로 효율적이다.
•
NESTED LOOP / HASH JOIN / MERGE JOIN: 조인 방식. 데이터 크기와 인덱스 유무에 따라 최적 방식이 달라진다.
•
Cost / Cardinality: 옵티마이저가 예측한 비용과 예상 행 수. 실제 수치와 크게 다르면 통계 정보가 오래된 것일 수 있다.
인덱스를 제대로 이해하고 쓴다
인덱스는 만능이 아니다. 잘못 쓰면 오히려 성능이 떨어진다.
인덱스가 무력화되는 대표 패턴
-- 컬럼에 함수를 씌우면 인덱스를 타지 않는다
SELECT * FROM employees WHERE TO_CHAR(hire_date, 'YYYY') = '2023';
-- 개선: 범위 조건으로 변경
SELECT * FROM employees
WHERE hire_date >= TO_DATE('2023-01-01', 'YYYY-MM-DD')
AND hire_date < TO_DATE('2024-01-01', 'YYYY-MM-DD');
-- 묵시적 형변환도 인덱스를 죽인다
SELECT * FROM employees WHERE employee_id = '100'; -- 위험
-- 개선: 타입을 맞춰준다
SELECT * FROM employees WHERE employee_id = 100;
-- LIKE 앞쪽 와일드카드는 인덱스를 타지 않는다
SELECT * FROM employees WHERE last_name LIKE '%son';
-- 개선: 앞쪽 고정값이 있어야 인덱스가 동작한다
SELECT * FROM employees WHERE last_name LIKE 'John%';
SQL
복사
복합 인덱스는 선두 컬럼 순서가 중요하다
-- (department_id, salary) 복합 인덱스가 있다고 가정
-- 선두 컬럼(department_id)을 쓰면 인덱스 사용 가능
SELECT * FROM employees WHERE department_id = 10 AND salary > 5000;
-- 선두 컬럼 없이 salary만 조건으로 쓰면 인덱스가 동작하지 않는다
SELECT * FROM employees WHERE salary > 5000;
SQL
복사
조인 작성 시 주의할 점
드라이빙 테이블은 결과 행이 적은 쪽으로
옵티마이저가 자동으로 판단하지만, 힌트로 유도할 수도 있다. 일반적으로 WHERE 조건으로 행이 많이 걸러지는 테이블이 드라이빙 테이블이 되는 게 유리하다.
SELECT /*+ LEADING(d) USE_NL(e) */
e.employee_id,
e.last_name,
d.department_name
FROM departments d
JOIN employees e ON d.department_id = e.department_id
WHERE d.location_id = 1700;
SQL
복사
불필요한 DISTINCT, ORDER BY는 제거한다
-- DISTINCT는 정렬 비용이 발생한다. 중복이 없는 게 확실하면 제거한다
SELECT DISTINCT department_id FROM employees;
-- EXISTS로 대체하면 더 효율적인 경우가 많다
SELECT department_id
FROM departments d
WHERE EXISTS (
SELECT 1 FROM employees e WHERE e.department_id = d.department_id
);
SQL
복사
서브쿼리 vs 조인
서브쿼리와 조인 중 어느 게 빠른지는 데이터에 따라 다르지만, 일반적으로 서브쿼리보다 조인이 옵티마이저가 최적화하기 더 좋다.
-- 서브쿼리 방식
SELECT last_name, salary
FROM employees
WHERE department_id IN (
SELECT department_id FROM departments WHERE location_id = 1700
);
-- 조인 방식으로 변경 (대부분 더 효율적)
SELECT e.last_name, e.salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.location_id = 1700;
SQL
복사
단, 스칼라 서브쿼리에서 캐싱이 발생하는 경우엔 서브쿼리가 더 빠를 수도 있다.
WITH절(CTE)로 가독성과 성능을 동시에
WITH절은 복잡한 쿼리를 분리해서 읽기 쉽게 만들고, 동일한 결과를 여러 번 참조할 때 반복 연산을 줄일 수 있다.
WITH high_salary_emp AS (
SELECT employee_id, last_name, salary, department_id
FROM employees
WHERE salary >= 8000
),
dept_stats AS (
SELECT department_id,
AVG(salary) AS avg_salary,
COUNT(*) AS emp_count
FROM high_salary_emp
GROUP BY department_id
)
SELECT h.last_name,
h.salary,
d.avg_salary,
d.emp_count
FROM high_salary_emp h
JOIN dept_stats d ON h.department_id = d.department_id
ORDER BY h.salary DESC;
SQL
복사
페이징 쿼리는 ROWNUM보다 ROW_NUMBER()를 쓴다
Oracle 12c 이전에는 ROWNUM으로 페이징을 처리했지만, 가독성이 떨어지고 실수하기 쉽다. 12c 이상에서는 FETCH FIRST를 쓰는 게 훨씬 깔끔하다.
-- 구식 방법 (Oracle 11g 이하 호환)
SELECT *
FROM (
SELECT e.*, ROWNUM AS rn
FROM (
SELECT employee_id, last_name, salary
FROM employees
ORDER BY salary DESC
) e
WHERE ROWNUM <= 20
)
WHERE rn >= 11;
-- 권장 방법 (Oracle 12c 이상)
SELECT employee_id, last_name, salary
FROM employees
ORDER BY salary DESC
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
-- ROW_NUMBER()로 유연하게 처리
SELECT *
FROM (
SELECT employee_id,
last_name,
salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees
)
WHERE rn BETWEEN 11 AND 20;
SQL
복사
통계 정보는 주기적으로 갱신한다
옵티마이저는 통계 정보를 기반으로 실행 계획을 수립한다. 통계 정보가 오래되면 잘못된 실행 계획을 선택할 수 있다.
-- 특정 테이블 통계 수집
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');
-- 스키마 전체 통계 수집
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('HR');
-- 통계 정보 확인
SELECT table_name,
num_rows,
last_analyzed
FROM user_tables
WHERE table_name = 'EMPLOYEES';
SQL
복사
힌트(Hint)는 최후의 수단으로
힌트는 옵티마이저에게 실행 계획을 강제하는 방법이다. 하지만 데이터가 바뀌면 힌트가 오히려 독이 될 수 있으므로, 통계 수집이나 인덱스 조정으로 해결이 안 될 때 마지막으로 쓴다.
SELECT /*+ FULL(e) */
employee_id, last_name
FROM employees e;
SELECT /*+ INDEX(e emp_salary_idx) */
employee_id, last_name
FROM employees e
WHERE salary > 5000;
SELECT /*+ USE_HASH(e d) */
e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;
SELECT /*+ PARALLEL(e, 4) */
COUNT(*)
FROM employees e;
SQL
복사
실무에서 자주 쓰는 분석 함수
분석 함수(Window Function)는 GROUP BY 없이 집계를 할 수 있어 복잡한 리포팅 쿼리를 단순하게 만든다.
-- 부서 내 급여 순위
SELECT employee_id,
last_name,
department_id,
salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank_in_dept,
DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rank_in_dept,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num_in_dept
FROM employees;
-- 누적 합계와 이동 평균
SELECT employee_id,
hire_date,
salary,
SUM(salary) OVER (ORDER BY hire_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_salary,
AVG(salary) OVER (ORDER BY hire_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3
FROM employees
ORDER BY hire_date;
-- 부서 내 전체 평균과 개인 급여 비교
SELECT employee_id,
last_name,
department_id,
salary,
AVG(salary) OVER (PARTITION BY department_id) AS dept_avg,
salary - AVG(salary) OVER (PARTITION BY department_id) AS diff_from_avg
FROM employees;
SQL
복사
마치며
Oracle SQL 튜닝은 한 번에 완성되는 작업이 아니다. 실행 계획을 읽는 습관, 인덱스가 무력화되는 패턴 파악, 통계 정보 관리, 그리고 적절한 조인 방식 선택이 쌓여야 실력이 늘어난다. 쿼리를 작성할 때 "이 쿼리가 어떤 실행 계획을 탈까"를 먼저 생각하는 습관이 튜닝의 출발점이다.