Oracle - SQL 재작성 기법

Oracle SQL 재작성 기법

비효율적인 SQL을 효율적으로 재작성하여 성능을 개선하는 다양한 기법을 다룹니다.

SQL 재작성은 동일한 결과를 얻으면서도 더 빠르게 실행되도록 쿼리를 변경하는 기법입니다. 옵티마이저가 최적화하기 어려운 경우 직접 SQL을 개선할 수 있습니다.

UNION ALL vs UNION

-- UNION ALL (중복 제거 불필요시)
SELECT employee_id, last_name FROM employees WHERE department_id = 10
UNION ALL
SELECT employee_id, last_name FROM employees WHERE department_id = 20;

-- UNION (중복 제거 필요시만)
SELECT employee_id FROM employees WHERE department_id = 10
UNION
SELECT employee_id FROM employees WHERE department_id = 20;

-- 성능 차이:
-- UNION ALL: 정렬 없음, 빠름
-- UNION: 정렬 + 중복 제거, 느림

COUNT 최적화

-- 나쁜 예: COUNT(*)에 불필요한 조건
SELECT COUNT(*) FROM employees WHERE salary > 0;
-- 문제: salary는 NOT NULL이므로 불필요한 조건

-- 좋은 예: 필요한 조건만
SELECT COUNT(*) FROM employees WHERE department_id = 10;

-- 인덱스 활용
SELECT COUNT(*) FROM employees;
-- 인덱스가 있으면 테이블 스캔 없이 빠르게 계산

-- 존재 여부만 확인할 때
-- 나쁜 예
SELECT COUNT(*) FROM employees WHERE department_id = 10;

-- 좋은 예
SELECT CASE WHEN EXISTS (
  SELECT 1 FROM employees WHERE department_id = 10
) THEN 1 ELSE 0 END FROM DUAL;

DISTINCT 최적화

-- 나쁜 예: 불필요한 DISTINCT
SELECT DISTINCT e.employee_id, e.last_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;
-- 문제: employee_id는 PK이므로 DISTINCT 불필요

-- 좋은 예: DISTINCT 제거
SELECT e.employee_id, e.last_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

-- EXISTS로 대체
-- 나쁜 예: DISTINCT로 중복 제거
SELECT DISTINCT e.employee_id, e.last_name
FROM employees e, job_history jh
WHERE e.employee_id = jh.employee_id;

-- 좋은 예: EXISTS 사용
SELECT e.employee_id, e.last_name
FROM employees e
WHERE EXISTS (
  SELECT 1 FROM job_history jh
  WHERE jh.employee_id = e.employee_id
);

OR 조건 최적화

-- 나쁜 예: OR 조건 (인덱스 비효율)
SELECT * FROM employees
WHERE department_id = 10 OR department_id = 20;

-- 좋은 예: IN 사용
SELECT * FROM employees
WHERE department_id IN (10, 20);

-- 또는 UNION ALL 사용 (인덱스 활용)
SELECT * FROM employees WHERE department_id = 10
UNION ALL
SELECT * FROM employees WHERE department_id = 20;

-- 다른 컬럼 OR 조건
-- 나쁜 예
SELECT * FROM employees
WHERE department_id = 10 OR job_id = 'SA_REP';

-- 좋은 예: UNION ALL로 분리
SELECT * FROM employees WHERE department_id = 10
UNION ALL
SELECT * FROM employees WHERE job_id = 'SA_REP'
  AND department_id != 10;  -- 중복 제거

LIKE 조건 최적화

-- 인덱스 사용 가능
SELECT * FROM employees
WHERE last_name LIKE 'K%';  -- 앞부분 고정

-- 인덱스 사용 불가능
SELECT * FROM employees
WHERE last_name LIKE '%ing';  -- 뒷부분만

SELECT * FROM employees
WHERE last_name LIKE '%in%';  -- 중간 부분

-- 대안: Full-Text Search 또는 함수 기반 인덱스
CREATE INDEX idx_last_name_reverse
ON employees(REVERSE(last_name));

SELECT * FROM employees
WHERE REVERSE(last_name) LIKE REVERSE('%ing');

범위 조건 최적화

-- 나쁜 예: 함수 사용
SELECT * FROM employees
WHERE TO_CHAR(hire_date, 'YYYY') = '2020';

-- 좋은 예: 범위 조건
SELECT * FROM employees
WHERE hire_date >= DATE '2020-01-01'
  AND hire_date < DATE '2021-01-01';

-- 나쁜 예: 계산식
SELECT * FROM employees
WHERE salary * 12 > 100000;

-- 좋은 예: 조건 변형
SELECT * FROM employees
WHERE salary > 100000 / 12;

NULL 처리 최적화

-- NVL vs COALESCE
-- NVL: 두 번째 인자도 항상 평가
SELECT NVL(commission_pct, 0) FROM employees;

-- COALESCE: 첫 번째 NULL이 아닌 값만 평가 (더 효율적)
SELECT COALESCE(commission_pct, 0) FROM employees;

-- NULL 비교
-- 나쁜 예
SELECT * FROM employees
WHERE NVL(commission_pct, 0) = 0;

-- 좋은 예
SELECT * FROM employees
WHERE commission_pct IS NULL OR commission_pct = 0;

CASE 문 최적화

-- 나쁜 예: 여러 번 CASE 실행
SELECT employee_id,
       CASE
         WHEN salary >= 10000 THEN 'HIGH'
         WHEN salary >= 5000 THEN 'MEDIUM'
         ELSE 'LOW'
       END as salary_grade,
       CASE
         WHEN salary >= 10000 THEN salary * 0.2
         WHEN salary >= 5000 THEN salary * 0.15
         ELSE salary * 0.1
       END as bonus
FROM employees;

-- 좋은 예: 한 번만 계산
SELECT employee_id,
       salary_grade,
       CASE salary_grade
         WHEN 'HIGH' THEN salary * 0.2
         WHEN 'MEDIUM' THEN salary * 0.15
         ELSE salary * 0.1
       END as bonus
FROM (
  SELECT employee_id, salary,
         CASE
           WHEN salary >= 10000 THEN 'HIGH'
           WHEN salary >= 5000 THEN 'MEDIUM'
           ELSE 'LOW'
         END as salary_grade
  FROM employees
);

집계 함수 최적화

-- 나쁜 예: 여러 번 테이블 스캔
SELECT
  (SELECT COUNT(*) FROM employees WHERE department_id = 10) as dept10_cnt,
  (SELECT COUNT(*) FROM employees WHERE department_id = 20) as dept20_cnt,
  (SELECT COUNT(*) FROM employees WHERE department_id = 30) as dept30_cnt
FROM DUAL;

-- 좋은 예: 한 번만 스캔
SELECT
  COUNT(CASE WHEN department_id = 10 THEN 1 END) as dept10_cnt,
  COUNT(CASE WHEN department_id = 20 THEN 1 END) as dept20_cnt,
  COUNT(CASE WHEN department_id = 30 THEN 1 END) as dept30_cnt
FROM employees;

-- 또는 GROUP BY 사용
SELECT department_id, COUNT(*) as cnt
FROM employees
WHERE department_id IN (10, 20, 30)
GROUP BY department_id;

페이징 최적화

-- 나쁜 예: OFFSET 사용 (모든 행 스캔)
SELECT *
FROM (
  SELECT e.*, ROW_NUMBER() OVER (ORDER BY employee_id) as rn
  FROM employees e
)
WHERE rn BETWEEN 101 AND 110;

-- 좋은 예: 키 기반 페이징
SELECT *
FROM employees
WHERE employee_id > :last_employee_id
ORDER BY employee_id
FETCH FIRST 10 ROWS ONLY;

-- TOP-N 쿼리
SELECT *
FROM (
  SELECT e.*
  FROM employees e
  ORDER BY salary DESC
)
WHERE ROWNUM <= 10;

-- 또는 (12c 이상)
SELECT *
FROM employees
ORDER BY salary DESC
FETCH FIRST 10 ROWS ONLY;

중복 제거 최적화

-- 나쁜 예: DISTINCT 남용
SELECT DISTINCT employee_id, last_name
FROM employees;  -- employee_id는 이미 unique

-- 좋은 예: DISTINCT 제거
SELECT employee_id, last_name
FROM employees;

-- 그룹핑으로 대체
-- 나쁜 예
SELECT DISTINCT department_id
FROM employees;

-- 좋은 예 (추가 집계가 필요한 경우)
SELECT department_id, COUNT(*) as emp_count
FROM employees
GROUP BY department_id;

데이터 타입 변환 최적화

-- 나쁜 예: 암시적 형변환
SELECT * FROM employees
WHERE employee_id = '100';  -- 숫자를 문자로 비교

-- 좋은 예: 명시적 형변환
SELECT * FROM employees
WHERE employee_id = 100;

-- 인덱스 컬럼 변환 방지
-- 나쁜 예
SELECT * FROM employees
WHERE TO_CHAR(employee_id) = '100';

-- 좋은 예
SELECT * FROM employees
WHERE employee_id = TO_NUMBER('100');

복잡한 조건 단순화

-- 나쁜 예: 복잡한 조건
SELECT * FROM employees
WHERE (department_id = 10 AND salary > 5000)
   OR (department_id = 20 AND salary > 6000)
   OR (department_id = 30 AND salary > 7000);

-- 좋은 예: CASE 활용
SELECT * FROM employees
WHERE salary > CASE department_id
                 WHEN 10 THEN 5000
                 WHEN 20 THEN 6000
                 WHEN 30 THEN 7000
               END;

SQL 재작성 체크리스트

• 불필요한 DISTINCT 제거
• UNION을 UNION ALL로 변경 가능 여부
• OR 조건을 IN 또는 UNION ALL로 변경
• 함수 적용된 컬럼을 조건 변경
• 중복 서브쿼리 제거
• COUNT(*)를 EXISTS로 변경 (존재 확인만 필요시)
• NULL 처리 최적화
• 암시적 형변환 제거

실전 예제

-- 예제 1: 복잡한 쿼리 재작성
-- Before
SELECT e.employee_id, e.last_name,
       (SELECT department_name FROM departments d
        WHERE d.department_id = e.department_id) as dept_name,
       (SELECT COUNT(*) FROM employees e2
        WHERE e2.department_id = e.department_id) as dept_count
FROM employees e
WHERE TO_CHAR(hire_date, 'YYYY') = '2020'
  OR TO_CHAR(hire_date, 'YYYY') = '2021';

-- After
SELECT e.employee_id, e.last_name,
       d.department_name,
       dc.dept_count
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
LEFT JOIN (
  SELECT department_id, COUNT(*) as dept_count
  FROM employees
  GROUP BY department_id
) dc ON e.department_id = dc.department_id
WHERE hire_date >= DATE '2020-01-01'
  AND hire_date < DATE '2022-01-01';

-- 예제 2: 집계 최적화
-- Before: 3번 테이블 스캔
SELECT
  (SELECT AVG(salary) FROM employees WHERE department_id = 10) as avg_10,
  (SELECT AVG(salary) FROM employees WHERE department_id = 20) as avg_20,
  (SELECT AVG(salary) FROM employees WHERE department_id = 30) as avg_30
FROM DUAL;

-- After: 1번 테이블 스캔
SELECT
  AVG(CASE WHEN department_id = 10 THEN salary END) as avg_10,
  AVG(CASE WHEN department_id = 20 THEN salary END) as avg_20,
  AVG(CASE WHEN department_id = 30 THEN salary END) as avg_30
FROM employees;

관련 문서

• 쿼리 튜닝 가이드
• 서브쿼리 최적화
• 인덱스 최적화