🗃️

Oracle DB 기초와 쿼리 튜닝 완전 정복

생성 일시

2026/04/08 11:32

태그

Database

Oracle

Knowledge

게시일

2026/04/08

최종 편집 일시

2026/04/08 11:33

들어가며

RDBMS의 대명사 Oracle. 엔터프라이즈 환경에서 여전히 압도적인 점유율을 자랑하는 이 DB를 제대로 이해하려면 단순히 쿼리 문법을 넘어 실행 계획(Execution Plan) 과 인덱스 전략까지 알아야 한다. 이 글에서는 Oracle의 기본 개념부터 실전 튜닝 기법까지 단계적으로 정리한다.

1. Oracle DB 기초

1-1. Oracle 아키텍처 개요

Oracle은 크게 Instance와 Database 두 가지 구성 요소로 나뉜다.

구성 요소	설명
Instance	SGA(System Global Area) + Background Process의 조합
Database	실제 데이터가 저장되는 물리적 파일(Datafile, Redo Log 등)
SGA	Shared Pool, Buffer Cache, Redo Log Buffer 등 포함
PGA	각 세션마다 독립적으로 할당되는 개인 메모리 영역

1-2. 데이터 타입

Oracle에서 자주 사용하는 데이터 타입은 아래와 같다.

-- 문자형
VARCHAR2(100)   -- 가변 길이 문자열 (Oracle 권장)
CHAR(10)        -- 고정 길이 문자열
CLOB            -- 대용량 텍스트

-- 숫자형
NUMBER(10, 2)   -- 전체 10자리, 소수점 2자리

-- 날짜형
DATE            -- 날짜 + 시간 (초 단위)
TIMESTAMP       -- 날짜 + 시간 (마이크로초 단위)
SQL
복사

1-3. 기본 SELECT 문법

-- 기본 조회
SELECT
    EMP_ID,
    EMP_NAME,
    SALARY,
    DEPT_ID
FROM EMPLOYEES
WHERE DEPT_ID = 10
  AND SALARY >= 3000
ORDER BY SALARY DESC;
SQL
복사

1-4. Oracle 전용 함수

-- NVL: NULL 처리 (COALESCE와 유사)
SELECT NVL(COMMISSION, 0) FROM EMPLOYEES;

-- DECODE: 조건부 값 반환 (CASE와 유사)
SELECT DECODE(DEPT_ID, 10, '개발팀', 20, '인사팀', '기타') AS DEPT_NAME
FROM EMPLOYEES;

-- ROWNUM: 결과 행 번호
SELECT *
FROM EMPLOYEES
WHERE ROWNUM <= 10;  -- 상위 10건

-- ROWID: 물리적 행 주소
SELECT ROWID, EMP_ID FROM EMPLOYEES;
SQL
복사

1-5. JOIN 문법

-- INNER JOIN
SELECT
    E.EMP_NAME,
    D.DEPT_NAME
FROM EMPLOYEES E
INNER JOIN DEPARTMENTS D
    ON E.DEPT_ID = D.DEPT_ID;

-- LEFT OUTER JOIN
SELECT
    E.EMP_NAME,
    D.DEPT_NAME
FROM EMPLOYEES E
LEFT OUTER JOIN DEPARTMENTS D
    ON E.DEPT_ID = D.DEPT_ID;

-- Oracle 전통 조인 문법 (+)
SELECT E.EMP_NAME, D.DEPT_NAME
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPT_ID = D.DEPT_ID(+);  -- LEFT OUTER JOIN과 동일
SQL
복사

2. 서브쿼리와 분석 함수

2-1. 서브쿼리 종류

-- 인라인 뷰 (FROM절 서브쿼리)
SELECT *
FROM (
    SELECT
        EMP_NAME,
        SALARY,
        ROW_NUMBER() OVER (ORDER BY SALARY DESC) AS RN
    FROM EMPLOYEES
)
WHERE RN <= 5;  -- 급여 상위 5명

-- 스칼라 서브쿼리 (SELECT절)
SELECT
    E.EMP_NAME,
    (SELECT D.DEPT_NAME FROM DEPARTMENTS D WHERE D.DEPT_ID = E.DEPT_ID) AS DEPT_NAME
FROM EMPLOYEES E;

-- 상관 서브쿼리 (WHERE절)
SELECT EMP_NAME, SALARY
FROM EMPLOYEES E
WHERE SALARY > (
    SELECT AVG(SALARY)
    FROM EMPLOYEES
    WHERE DEPT_ID = E.DEPT_ID
);
SQL
복사

2-2. 분석 함수 (Window Function)

-- ROW_NUMBER, RANK, DENSE_RANK
SELECT
    EMP_NAME,
    DEPT_ID,
    SALARY,
    ROW_NUMBER() OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC) AS ROW_NUM,
    RANK()       OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC) AS RANK_NUM,
    DENSE_RANK() OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC) AS DENSE_RANK_NUM
FROM EMPLOYEES;

-- 누적 합계, 이동 평균
SELECT
    SALE_DATE,
    AMOUNT,
    SUM(AMOUNT) OVER (ORDER BY SALE_DATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CUMULATIVE_SUM,
    AVG(AMOUNT) OVER (ORDER BY SALE_DATE ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MOVING_AVG_3
FROM SALES;
SQL
복사

2-3. WITH절 (CTE)

-- 공통 테이블 표현식으로 가독성 향상
WITH
DEPT_STATS AS (
    SELECT
        DEPT_ID,
        AVG(SALARY) AS AVG_SAL,
        COUNT(*)    AS EMP_CNT
    FROM EMPLOYEES
    GROUP BY DEPT_ID
),
HIGH_SAL_EMP AS (
    SELECT E.EMP_NAME, E.SALARY, E.DEPT_ID
    FROM EMPLOYEES E
    JOIN DEPT_STATS D ON E.DEPT_ID = D.DEPT_ID
    WHERE E.SALARY > D.AVG_SAL
)
SELECT * FROM HIGH_SAL_EMP
ORDER BY DEPT_ID, SALARY DESC;
SQL
복사

3. 인덱스 (Index)

3-1. 인덱스 기본 개념

Oracle의 기본 인덱스는 B-Tree 구조로, 대용량 테이블에서 특정 데이터를 빠르게 찾기 위한 자료구조다.

-- 단일 컬럼 인덱스
CREATE INDEX IDX_EMP_NAME ON EMPLOYEES(EMP_NAME);

-- 복합 인덱스 (컬럼 순서가 매우 중요!)
CREATE INDEX IDX_EMP_DEPT_SAL ON EMPLOYEES(DEPT_ID, SALARY);

-- 함수 기반 인덱스
CREATE INDEX IDX_EMP_UPPER_NAME ON EMPLOYEES(UPPER(EMP_NAME));

-- 인덱스 삭제
DROP INDEX IDX_EMP_NAME;
SQL
복사

3-2. 인덱스 선택 기준

상황	권장 여부
WHERE절에 자주 등장하는 컬럼	권장
Cardinality(선택도)가 높은 컬럼	권장
JOIN 조건 컬럼	권장
DML(INSERT/UPDATE/DELETE)이 매우 빈번한 컬럼	신중
데이터 분포가 편향된 컬럼	신중
전체 데이터의 10~15% 이상 조회 시	Full Scan이 유리

3-3. 인덱스가 무력화되는 패턴

-- ❌ 컬럼에 함수 적용 → 인덱스 무효
WHERE TO_CHAR(REG_DATE, 'YYYYMMDD') = '20260408'

-- ✅ 올바른 방법
WHERE REG_DATE >= TO_DATE('20260408', 'YYYYMMDD')
  AND REG_DATE <  TO_DATE('20260409', 'YYYYMMDD')

-- ❌ 묵시적 형변환 → 인덱스 무효
WHERE EMP_ID = 1000    -- EMP_ID가 VARCHAR2인 경우

-- ✅ 올바른 방법
WHERE EMP_ID = '1000'

-- ❌ NOT 조건, NULL 비교
WHERE DEPT_ID IS NOT NULL
WHERE SALARY != 3000

-- ❌ LIKE 앞부분 와일드카드
WHERE EMP_NAME LIKE '%홍길동'  -- 인덱스 무효

-- ✅ 뒷부분 와일드카드는 가능
WHERE EMP_NAME LIKE '홍길동%'  -- 인덱스 사용 가능
SQL
복사

4. 실행 계획 (Execution Plan)

4-1. 실행 계획 확인 방법

-- 방법 1: EXPLAIN PLAN
EXPLAIN PLAN FOR
SELECT * FROM EMPLOYEES WHERE DEPT_ID = 10;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

-- 방법 2: 실행 후 실제 계획 확인 (더 정확)
SELECT /*+ GATHER_PLAN_STATISTICS */ *
FROM EMPLOYEES
WHERE DEPT_ID = 10;

SELECT * FROM TABLE(
    DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST')
);
SQL
복사

4-2. 실행 계획 읽는 방법

실행 계획은 안쪽(들여쓰기 깊은 것)부터 위쪽으로 읽는다.

주요 Operation	의미
TABLE ACCESS FULL	풀 테이블 스캔 (비용 높음)
INDEX RANGE SCAN	인덱스 범위 스캔
INDEX UNIQUE SCAN	인덱스 유니크 스캔 (가장 빠름)
NESTED LOOPS	중첩 루프 조인
HASH JOIN	해시 조인 (대용량 조인 시 유리)
MERGE JOIN	정렬 병합 조인

5. 쿼리 튜닝 기법

5-1. 힌트(Hint) 사용

-- 인덱스 강제 사용
SELECT /*+ INDEX(E IDX_EMP_DEPT_SAL) */
    EMP_NAME, SALARY
FROM EMPLOYEES E
WHERE DEPT_ID = 10;

-- Full Table Scan 강제
SELECT /*+ FULL(E) */
    EMP_NAME
FROM EMPLOYEES E
WHERE DEPT_ID = 10;

-- 조인 방식 힌트
SELECT /*+ USE_HASH(E D) */
    E.EMP_NAME, D.DEPT_NAME
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPT_ID = D.DEPT_ID;

-- Leading: 조인 순서 지정
SELECT /*+ LEADING(D E) USE_NL(E) */
    E.EMP_NAME, D.DEPT_NAME
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPT_ID = D.DEPT_ID;
SQL
복사

5-2. 페이징 처리 최적화

-- ❌ 안티패턴: ROWNUM을 직접 비교
SELECT * FROM EMPLOYEES
WHERE ROWNUM BETWEEN 11 AND 20;  -- 의도대로 동작 안 함

-- ✅ 올바른 페이징 (Oracle 12c 미만)
SELECT *
FROM (
    SELECT E.*, ROWNUM AS RN
    FROM (
        SELECT EMP_ID, EMP_NAME, SALARY
        FROM EMPLOYEES
        ORDER BY SALARY DESC
    ) E
    WHERE ROWNUM <= 20  -- 상위 N건 먼저 자름
)
WHERE RN >= 11;

-- ✅ Oracle 12c 이상: FETCH FIRST 문법
SELECT EMP_ID, EMP_NAME, SALARY
FROM EMPLOYEES
ORDER BY SALARY DESC
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
SQL
복사

5-3. DML 성능 최적화

-- BULK COLLECT + FORALL: 대량 DML 처리 시 Context Switch 최소화
DECLARE
    TYPE EMP_TAB IS TABLE OF EMPLOYEES%ROWTYPE;
    V_EMPS EMP_TAB;
BEGIN
    SELECT * BULK COLLECT INTO V_EMPS
    FROM EMPLOYEES
    WHERE DEPT_ID = 10;

    FORALL I IN 1..V_EMPS.COUNT
        UPDATE EMPLOYEES
        SET SALARY = SALARY * 1.1
        WHERE EMP_ID = V_EMPS(I).EMP_ID;

    COMMIT;
END;
/

-- 직접 경로 INSERT로 Redo 최소화
INSERT /*+ APPEND */ INTO EMPLOYEES_BACKUP
SELECT * FROM EMPLOYEES;
COMMIT;
SQL
복사

5-4. 파티션 테이블 활용

-- 범위 파티션 (날짜 기반)
CREATE TABLE SALES (
    SALE_ID   NUMBER,
    SALE_DATE DATE,
    AMOUNT    NUMBER
)
PARTITION BY RANGE (SALE_DATE) (
    PARTITION P_2024 VALUES LESS THAN (DATE '2025-01-01'),
    PARTITION P_2025 VALUES LESS THAN (DATE '2026-01-01'),
    PARTITION P_2026 VALUES LESS THAN (DATE '2027-01-01'),
    PARTITION P_MAX  VALUES LESS THAN (MAXVALUE)
);

-- 파티션 조회 (Partition Pruning)
SELECT * FROM SALES
WHERE SALE_DATE >= DATE '2026-01-01'
  AND SALE_DATE <  DATE '2027-01-01';
-- → P_2026 파티션만 스캔 (나머지 파티션 Skip)
SQL
복사

6. 통계 정보와 옵티마이저

6-1. 통계 정보 수집

Oracle의 CBO(Cost Based Optimizer) 는 통계 정보를 기반으로 최적의 실행 계획을 선택한다. 통계가 오래되면 잘못된 실행 계획을 생성할 수 있다.

-- 테이블 통계 수집
BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(
        ownname   => 'HR',
        tabname   => 'EMPLOYEES',
        cascade   => TRUE,   -- 인덱스 통계도 함께 수집
        estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE
    );
END;
/

-- 현재 통계 정보 확인
SELECT
    TABLE_NAME,
    NUM_ROWS,
    LAST_ANALYZED
FROM USER_TABLES
WHERE TABLE_NAME = 'EMPLOYEES';

-- 컬럼별 통계
SELECT
    COLUMN_NAME,
    NUM_DISTINCT,    -- 유니크 값 수 (Cardinality)
    NUM_NULLS,
    DENSITY
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'EMPLOYEES';
SQL
복사

7. 실전 튜닝 체크리스트

 튜닝 전 확인 사항

마치며

DB 튜닝은 문제를 발견하는 눈을 기르는 것이 핵심이다. 실행 계획을 읽는 습관을 들이고, 인덱스 설계 원칙을 이해하면 대부분의 성능 이슈를 해결할 수 있다. Oracle은 강력한 힌트 시스템과 분석 함수를 제공하므로 이를 적극 활용하자.

 핵심 원칙: 옵티마이저를 이기려 하기보다, 옵티마이저가 올바른 선택을 할 수 있도록 정확한 통계 정보와 적절한 인덱스를 제공하는 것이 먼저다.