데이터가 많을 때, 쿼리를 최적화하는 방법은 무엇인가요?

[daumi125]

🤔

[daumi125]

인덱스와 힌트를 사용하여, 쿼리 성능을 향상 시킬 수 있습니다.
데이터가 많고, 성능이 중요한 상황에서 쿼리 성능을 최적화하기 위한 도구로, 테이블 내의 데이터를 빠르게 검색하기 위해 사용되는 인덱스를 사용할 수 있습니다. 또한, 옵티마이저의 검색 조건보다 개발자가 직접 쿼리 실행 방식을 제어함으로써 쿼리 성능을 향상 시킬 수 있습니다.

📌 1. 인덱스는 언제 사용하나요?

인덱스는 테이블 내의 데이터를 빠르게 검색하기 위해 사용되는 검색 도우미 역할을 합니다. 예를 들어, 도서관에서 책을 찾을 때 책 전체를 일일이 확인하는 것보다 목차를 보고 바로 해당 페이지로 이동하는 방식과 같습니다.

• 특히 데이터량이 수만 건 이상이 되는 테이블에서 WHERE, JOIN, ORDER BY, GROUP BY 같은 연산이 많을 때 인덱스는 성능에 큰 차이를 만들어냅니다.
• 오라클에서는 기본적으로 B-tree 인덱스를 많이 사용하고, 조건에 따라 비트맵 인덱스나 함수 기반 인덱스를 적용하기도 합니다.

예를 들어, 고객 테이블에서 이름으로 검색을 자주 한다면 이름 컬럼에 인덱스를 생성하여 쿼리 응답 시간을 수초에서 수밀리초로 줄일 수 있습니다.

하지만, 인덱스는 데이터를 추가하거나 수정할 때에도 관리 비용이 발생하기 때문에, 자주 변경되는 컬럼에는 인덱스를 신중하게 적용해야 하며, 불필요한 인덱스는 성능을 떨어뜨릴 수 있습니다.

---

📌 2. 힌트는 어떤 경우에 사용하나요?

오라클 힌트는 오라클의 옵티마이저에게 **“이 쿼리는 이렇게 실행해줘”**라고 직접 알려주는 방식입니다. 일반적으로는 옵티마이저가 쿼리를 분석해서 최적의 실행 계획을 선택하지만, 실제 운영 환경에서는 옵티마이저의 판단이 항상 정확하다고 볼 수는 없습니다.

예를 들어, 특정 인덱스를 오라클이 무시하거나, 조인 순서를 비효율적으로 선택해서 성능이 저하되는 경우가 있습니다.

이럴 때 /*+ INDEX */, /*+ USE_NL */, /*+ ORDERED */ 같은 힌트를 통해 명시적인 실행 전략을 지정함으로써 성능을 안정화시킬 수 있습니다.

---

📌 정리하면

구분	사용 목적	사용 시점	주의할 점
인덱스	데이터 검색 속도 향상	대량의 데이터에서 자주 조회되는 컬럼	너무 많거나 잘못된 인덱스는 오히려 성능 저하
힌트	옵티마이저 실행 계획 제어	실행 계획이 예상과 다르거나 성능이 떨어질 때	구조 변경 시 유지보수 어려움 가능성

오라클 힌트는 SQL 문에 주석 형태로 작성하여, 오라클 옵티마이저에게 특정 실행 계획을 따르도록 유도할 수 있습니다.

힌트는 SELECT 문 바로 뒤에 /*+ 힌트내용 */ 형식으로 작성하며, 쿼리의 실행 계획을 미세하게 조정할 수 있습니다.

---

📌 힌트 기본 문법

sql
복사편집
SELECT /*+ 힌트이름(테이블명 또는 인덱스명) */ 컬럼명
FROM 테이블명
WHERE 조건;

---

🔍 대표적인 힌트 문법 & 예시

---

✅ 1. INDEX – 특정 인덱스를 사용하도록 강제

sql
복사편집
SELECT /*+ INDEX(회원 idx_회원_이름) */ *
FROM 회원
WHERE 이름 = '홍길동';

→ 옵티마이저가 자동으로 인덱스를 선택하지 않을 경우, 내가 지정한 인덱스(idx_회원_이름)를 반드시 사용하게 합니다.

---

✅ 2. FULL – 전체 테이블 스캔을 강제

sql
복사편집
SELECT /*+ FULL(회원) */ *
FROM 회원
WHERE 등록일 >= TO_DATE('2024-01-01', 'YYYY-MM-DD');

→ 인덱스를 쓰는 것보다 전체 스캔이 더 빠를 경우 사용합니다.

특히 대량 데이터 중 80~90% 이상을 읽을 경우, FULL 스캔이 오히려 효율적일 수 있습니다.

---

✅ 3. NO_INDEX – 인덱스 사용하지 말라고 지시

sql
복사편집
SELECT /*+ NO_INDEX(회원 idx_회원_이름) */ *
FROM 회원
WHERE 이름 = '홍길동';

→ 테스트나 특정 상황에서 인덱스를 강제로 배제하고 성능을 확인하고자 할 때 유용합니다.

---

✅ 4. ORDERED – FROM 절 순서대로 조인하도록 강제

sql
복사편집
SELECT /*+ ORDERED */ *
FROM 부서 d, 직원 e
WHERE d.부서번호 = e.부서번호;

→ 옵티마이저는 FROM 순서를 무시하고 효율적인 순서로 조인하지만,

ORDERED 힌트를 사용하면 FROM 절 순서대로 조인이 수행되도록 합니다.

조인 순서에 따라 성능이 크게 달라질 때 사용합니다.

---

✅ 5. USE_NL, USE_HASH, USE_MERGE – 조인 방식 지정

sql
복사편집
-- Nested Loop Join 사용 강제
SELECT /*+ USE_NL(직원 부서) */ *
FROM 직원
JOIN 부서 ON 직원.부서번호 = 부서.부서번호;
-- Hash Join 사용 강제

SELECT /*+ USE_HASH(직원 부서) */ *

FROM 직원

JOIN 부서 ON 직원.부서번호 = 부서.부서번호;

→ 옵티마이저가 비효율적인 조인 방식을 선택할 경우,

정해진 조인 방식(NL, HASH, MERGE)을 강제로 적용할 수 있습니다.

---

✅ 6. PARALLEL – 병렬 처리 지정 (대량 데이터 처리에 유용)

sql
복사편집
SELECT /*+ PARALLEL(주문, 4) */ *
FROM 주문
WHERE 주문일 >= SYSDATE - 30;

→ 4개의 프로세스를 병렬로 사용하여 데이터 조회 속도를 높입니다.

대용량 배치, 리포트 쿼리에서 유용하게 사용됩니다.

---

📌 정리

힌트	역할
INDEX	특정 인덱스 사용
FULL	전체 테이블 스캔
NO_INDEX	인덱스 사용 안 함
ORDERED	조인 순서 강제
USE_NL, USE_HASH, USE_MERGE	조인 방식 강제
PARALLEL	병렬 처리 실행