[Oracle]3️⃣ 고급 단계 (PL/SQL 및 성능 튜닝)

3️⃣ 고급 단계 (PL/SQL 및 성능 튜닝)

고급 단계에서는 PL/SQL(절차적 SQL), 트리거, 커서, 성능 최적화, 인덱스 튜닝, 파티셔닝 등을 학습합니다.


---

✅ 1. PL/SQL 개요 및 기본 구조

PL/SQL(Procedural Language/SQL)은 SQL을 절차적으로 확장한 프로그래밍 언어입니다.

변수 선언 가능

반복문 및 조건문 사용 가능

예외 처리 가능


DECLARE
    v_name VARCHAR2(50);
BEGIN
    SELECT first_name INTO v_name FROM employees WHERE employee_id = 100;
    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_name);
END;
/

📌 설명: employee_id = 100인 직원의 first_name을 조회하여 출력

✅ 출력 예시:

Employee Name: Steven


---

✅ 2. 조건문 및 반복문

PL/SQL에서는 IF, CASE, LOOP, FOR, WHILE 등을 사용할 수 있습니다.

(1) 조건문 (IF, CASE)

DECLARE
    v_salary NUMBER;
BEGIN
    SELECT salary INTO v_salary FROM employees WHERE employee_id = 100;

    IF v_salary > 5000 THEN
        DBMS_OUTPUT.PUT_LINE('High Salary');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Low Salary');
    END IF;
END;
/

✅ CASE 문 활용

DECLARE
    v_salary NUMBER;
    v_category VARCHAR2(20);
BEGIN
    SELECT salary INTO v_salary FROM employees WHERE employee_id = 100;

    v_category := CASE
                     WHEN v_salary > 7000 THEN 'High'
                     WHEN v_salary > 5000 THEN 'Medium'
                     ELSE 'Low'
                 END;

    DBMS_OUTPUT.PUT_LINE('Salary Category: ' || v_category);
END;
/


---

✅ 3. 커서(Cursor) 활용

커서는 SQL 쿼리의 결과를 행 단위로 처리하는 기능입니다.

(1) 명시적 커서 사용

DECLARE
    CURSOR emp_cursor IS SELECT first_name, last_name FROM employees;
    v_first employees.first_name%TYPE;
    v_last employees.last_name%TYPE;
BEGIN
    OPEN emp_cursor;
    LOOP
        FETCH emp_cursor INTO v_first, v_last;
        EXIT WHEN emp_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_first || ' ' || v_last);
    END LOOP;
    CLOSE emp_cursor;
END;
/

📌 설명: employees 테이블의 모든 직원 이름을 한 줄씩 출력


---

✅ 4. 트리거(Trigger)

트리거는 특정 이벤트(INSERT, UPDATE, DELETE)가 발생할 때 자동으로 실행되는 PL/SQL 블록입니다.

(1) 행 단위 트리거 (BEFORE INSERT)

CREATE OR REPLACE TRIGGER trg_before_insert  
BEFORE INSERT ON employees  
FOR EACH ROW  
BEGIN  
    :NEW.salary := :NEW.salary * 1.1;  -- 삽입되는 급여를 10% 증가  
END;  
/

📌 설명: 새로운 직원의 급여(salary)가 자동으로 10% 증가


---

(2) 감사(Audit) 트리거 (AFTER UPDATE)

CREATE OR REPLACE TRIGGER trg_audit_salary  
AFTER UPDATE ON employees  
FOR EACH ROW  
BEGIN  
    INSERT INTO salary_audit (employee_id, old_salary, new_salary, change_date)  
    VALUES (:OLD.employee_id, :OLD.salary, :NEW.salary, SYSDATE);
END;  
/

📌 설명: 급여가 변경될 때 salary_audit 테이블에 기록을 남김


---

✅ 5. 성능 최적화 및 인덱스 튜닝

(1) 실행 계획 분석 (EXPLAIN PLAN)

SQL 실행 계획을 확인하여 비효율적인 쿼리를 최적화할 수 있습니다.

EXPLAIN PLAN FOR  
SELECT * FROM employees WHERE last_name = 'Smith';  

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

📌 설명: 실행 계획을 확인하여 Full Table Scan(테이블 전체 검색) 발생 여부 체크


---

(2) 인덱스(Index) 활용

인덱스는 데이터 검색 속도를 높이기 위한 자료구조입니다.

✅ 단일 컬럼 인덱스 생성

CREATE INDEX idx_emp_lastname ON employees(last_name);

📌 설명: last_name 컬럼에 대한 인덱스를 생성하여 검색 속도 개선

✅ 복합 인덱스 생성

CREATE INDEX idx_emp_dept_salary ON employees(department_id, salary);

📌 설명: department_id와 salary를 함께 검색할 때 성능 향상

✅ 인덱스 삭제

DROP INDEX idx_emp_lastname;


---

✅ 6. 파티셔닝(Partitioning) 개념

대용량 테이블을 여러 개의 파티션으로 나누어 성능을 최적화하는 기법입니다.

✅ 범위(RANGE) 파티셔닝 예제

CREATE TABLE sales (
    sale_id NUMBER,
    sale_date DATE,
    amount NUMBER
)
PARTITION BY RANGE (sale_date) (
    PARTITION sales_2024 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD')),
    PARTITION sales_2025 VALUES LESS THAN (TO_DATE('2026-01-01', 'YYYY-MM-DD'))
);

📌 설명: sales 데이터를 연도별(2024, 2025)로 분할

✅ 파티션 확인

SELECT table_name, partition_name, high_value FROM user_tab_partitions WHERE table_name = 'SALES';


---

✅ 7. 병렬 처리 및 성능 향상 기법

대량의 데이터를 빠르게 처리하기 위한 최적화 기법입니다.

✅ 병렬 쿼리 실행

SELECT /*+ PARALLEL(employees, 4) */ * FROM employees;

📌 설명: employees 테이블을 4개의 프로세스로 병렬 실행

✅ Materialized View(물리적 뷰) 활용

CREATE MATERIALIZED VIEW mv_emp_avg_salary AS  
SELECT department_id, AVG(salary) avg_salary FROM employees GROUP BY department_id;

📌 설명: 부서별 평균 급여를 미리 저장하여 성능을 개선

✅ 실제 데이터 갱신 (REFRESH)

BEGIN
    DBMS_MVIEW.REFRESH('mv_emp_avg_salary');
END;
/


---

📌 고급 단계 핵심 요약

✅ PL/SQL → 변수, 조건문, 반복문, 예외 처리
✅ 커서(Cursor) → SQL 결과를 행 단위로 처리
✅ 트리거(Trigger) → 데이터 변경 시 자동 실행
✅ 성능 최적화 → 실행 계획 분석(EXPLAIN PLAN), 인덱스 활용
✅ 파티셔닝 → 대용량 데이터 성능 최적화
✅ 병렬 처리 및 Materialized View → 속도 향상

PL/SQL과 성능 튜닝을 학습하면 실제 프로젝트에서 데이터베이스 성능 최적화 및 유지보수 능력을 향상할 수 있습니다.
추가 설명이 필요하면 말씀해주세요!