Oracle 쿼리 실행 계획 확인 및 인덱스 최적화 실습

실행 계획 확인 방법

EXPLAIN PLAN FOR
-- 여기에 실행할 쿼리 입력

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

---

1. 고객과 주문 정보 조회

SELECT c.CUSTOMER_ID, c.NAME, o.ORDER_ID, o.ORDER_DATE, o.TOTAL_AMOUNT
FROM CUSTOMERS c
LEFT JOIN ORDERS o ON c.CUSTOMER_ID = o.CUSTOMER_ID;

필요한 인덱스

CREATE INDEX idx_customers_customer_id ON CUSTOMERS(CUSTOMER_ID);
CREATE INDEX idx_orders_customer_id ON ORDERS(CUSTOMER_ID);

---

2. 모든 고객과 해당 주문 정보 조회 (LEFT JOIN)

쿼리 동일. 위 인덱스와 동일하게 적용.

---

3. 모든 직원과 그들이 처리한 주문 조회

SELECT e.EMPLOYEE_ID, e.NAME, o.ORDER_ID, o.ORDER_DATE
FROM EMPLOYEES e
LEFT JOIN ORDERS o ON e.EMPLOYEE_ID = o.EMPLOYEE_ID;

필요한 인덱스

CREATE INDEX idx_employees_employee_id ON EMPLOYEES(EMPLOYEE_ID);
CREATE INDEX idx_orders_employee_id ON ORDERS(EMPLOYEE_ID);

---

4. 모든 고객과 리뷰 정보 조회

SELECT c.CUSTOMER_ID, c.NAME, r.REVIEW_ID, r.RATING, r.REVIEW_TEXT
FROM CUSTOMERS c
LEFT JOIN PRODUCT_REVIEWS r ON c.CUSTOMER_ID = r.CUSTOMER_ID;

필요한 인덱스

CREATE INDEX idx_customers_customer_id ON CUSTOMERS(CUSTOMER_ID);
CREATE INDEX idx_reviews_customer_id ON PRODUCT_REVIEWS(CUSTOMER_ID);

---

5. 직원과 관리자 정보 조회 (자기 조인)

SELECT e.EMPLOYEE_ID, e.NAME, m.EMPLOYEE_ID AS MANAGER_ID, m.NAME AS MANAGER_NAME
FROM EMPLOYEES e
LEFT JOIN EMPLOYEES m ON e.MANAGER_ID = m.EMPLOYEE_ID;

필요한 인덱스

CREATE INDEX idx_employees_manager_id ON EMPLOYEES(MANAGER_ID);

---

6. 주문 + 고객 + 직원 + 부서 정보 통합 조회

SELECT o.ORDER_ID, o.ORDER_DATE, c.NAME AS CUSTOMER_NAME, e.NAME AS EMPLOYEE_NAME, d.DEPARTMENT_NAME
FROM ORDERS o
JOIN CUSTOMERS c ON o.CUSTOMER_ID = c.CUSTOMER_ID
JOIN EMPLOYEES e ON o.EMPLOYEE_ID = e.EMPLOYEE_ID
JOIN DEPARTMENTS d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID;

필요한 인덱스

CREATE INDEX idx_orders_customer_id ON ORDERS(CUSTOMER_ID);
CREATE INDEX idx_orders_employee_id ON ORDERS(EMPLOYEE_ID);
CREATE INDEX idx_employees_department_id ON EMPLOYEES(DEPARTMENT_ID);
CREATE INDEX idx_employees_employee_id ON EMPLOYEES(EMPLOYEE_ID);
CREATE INDEX idx_departments_department_id ON DEPARTMENTS(DEPARTMENT_ID);

---

7. 카테고리별 주문 총액 조회

SELECT c.CATEGORY_NAME, SUM(o.TOTAL_AMOUNT) AS TOTAL_SALES
FROM CATEGORIES c
JOIN PRODUCTS p ON c.CATEGORY_ID = p.CATEGORY_ID
JOIN ORDER_ITEMS oi ON p.PRODUCT_ID = oi.PRODUCT_ID
JOIN ORDERS o ON oi.ORDER_ID = o.ORDER_ID
GROUP BY c.CATEGORY_NAME;

필요한 인덱스

CREATE INDEX idx_categories_category_id ON CATEGORIES(CATEGORY_ID);
CREATE INDEX idx_products_category_id ON PRODUCTS(CATEGORY_ID);
CREATE INDEX idx_order_items_product_id ON ORDER_ITEMS(PRODUCT_ID);
CREATE INDEX idx_orders_order_id ON ORDERS(ORDER_ID);

---

8. 평균 이상 리뷰 점수를 받은 제품 조회

SELECT p.PRODUCT_NAME, AVG(r.RATING) AS AVG_RATING
FROM PRODUCTS p
JOIN PRODUCT_REVIEWS r ON p.PRODUCT_ID = r.PRODUCT_ID
GROUP BY p.PRODUCT_NAME
HAVING AVG(r.RATING) >= 4;

필요한 인덱스

CREATE INDEX idx_products_product_id ON PRODUCTS(PRODUCT_ID);
CREATE INDEX idx_reviews_product_id ON PRODUCT_REVIEWS(PRODUCT_ID);

---

9. 리뷰가 없는 제품 조회

SELECT p.PRODUCT_NAME
FROM PRODUCTS p
LEFT JOIN PRODUCT_REVIEWS r ON p.PRODUCT_ID = r.PRODUCT_ID
WHERE r.REVIEW_ID IS NULL;

필요한 인덱스

CREATE INDEX idx_products_product_id ON PRODUCTS(PRODUCT_ID);
CREATE INDEX idx_reviews_product_id ON PRODUCT_REVIEWS(PRODUCT_ID);

---

10. 주문이 있는 고객만 조회

SELECT c.NAME
FROM CUSTOMERS c
WHERE EXISTS (
    SELECT 1
    FROM ORDERS o
    WHERE o.CUSTOMER_ID = c.CUSTOMER_ID
);

필요한 인덱스

CREATE INDEX idx_customers_customer_id ON CUSTOMERS(CUSTOMER_ID);
CREATE INDEX idx_orders_customer_id ON ORDERS(CUSTOMER_ID);

---

정리 요약

• 자주 조인되는 컬럼에 대해 적절한 인덱스 추가
• 성능 문제 발생 시, FULL TABLE SCAN 여부 확인
• 인덱스 생성 후 실행 계획 재확인 필요