데이터가 많을 때, 쿼리 향상 방법
인덱스와 힌트를 사용하여, 쿼리 성능을 향상 시킬 수 있다.
인덱스는 데이터가 많고, 성능이 중요한 상황에서 쿼리 성능을 최적화하기 위한 도구로, 테이블 내의 데이터를 빠르게 검색하기 위해 사용된다.
또한, 힌트는 옵티마이저의 검색 조건보다 개발자가 직접 쿼리 실행 방식을 제어함으로써 쿼리 성능을 향상 시킬 수 있다.
📌 1. 인덱스는 언제 사용할까?
인덱스는 테이블 내의 데이터를 빠르게 검색하기 위해 사용되는 검색 도우미이다.
예를 들어, 도서관에서 책을 찾을 때 책 전체를 일일이 확인하는 것보다 목차를 보고 바로 해당 페이지로 이동하는 방식과 같다.
- 특히 데이터량이 수만 건 이상이 되는 테이블에서
WHERE,JOIN,ORDER BY,GROUP BY같은 연산이 많을 때 인덱스를 사용하면 데이터 처리 속도를 높일 수 있다. - 오라클에서는 기본적으로 B-tree 인덱스를 많이 사용하고, 조건에 따라 비트맵 인덱스나 함수 기반 인덱스를 적용하기도 한다.
예를 들어, 고객 테이블에서 이름으로 검색을 자주 한다면 이름 컬럼에 인덱스를 생성하면 쿼리 응답 시간 줄일 수 있다.
하지만, 인덱스 또한 데이터를 추가하거나 수정할 때에도 관리 비용이 발생하기 때문에, 자주 변경되는 컬럼에는 인덱스를 신중하게 적용해야 하며, 불필요한 인덱스는 성능을 떨어뜨릴 수 있음에 주의해야 한다.
📌 2. 힌트는 어떻게 사용할까?
힌트는 옵티마이저의 검색 방식 보다, 개발자가 직접 쿼리 실행 방식을 제어함으로써 쿼리를 최적화 하는 방식이다.
주로, 오라클이 특정 인덱스를 건너뛰거나, 조인 순서를 비효율적으로 선택해서 성능이 저하되는 경우가 있을 경우 사용된다.
이럴 때 /*+ INDEX */, /*+ USE_NL */, /*+ ORDERED */ 같은 힌트를 통해 명시적인 실행 전략을 지정함으로써 성능을 안정화시킬 수 있다.
📌 정리
| 구분 | 사용 목적 | 사용 시점 | 주의할 점 |
|---|---|---|---|
| 인덱스 | 데이터 검색 속도 향상 | 대량의 데이터에서 자주 조회되는 컬럼 | 너무 많거나 잘못된 인덱스는 오히려 성능 저하 |
| 힌트 | 옵티마이저 실행 계획 제어 | 실행 계획이 예상과 다르거나 성능이 떨어질 때 | 구조 변경 시 유지보수 어려움 가능성 |
오라클 힌트는 SQL 문에 주석 형태로 작성하여 오라클 옵티마이저에게 특정 실행 계획을 따르도록 제어한다.
SELECT 문 바로 뒤에 /*+ 힌트내용 */ 형식으로 작성하며, 쿼리의 실행 계획을 미세하게 조정
코드 예시
SELECT /*+ 힌트이름(테이블명 또는 인덱스명) */ 컬럼명
FROM 테이블명
WHERE 조건;✅ 1. INDEX – 특정 인덱스를 사용하도록 강제
SELECT /*+ INDEX(회원 idx_회원_이름) */ *
FROM 회원
WHERE 이름 = '홍길동';→ 옵티마이저가 자동으로 인덱스를 선택하지 않을 경우, 내가 지정한 인덱스(idx_회원_이름)를 반드시 사용하게 함.
✅ 2. FULL – 전체 테이블 스캔을 강제
SELECT /*+ FULL(회원) */ *
FROM 회원
WHERE 등록일 >= TO_DATE('2024-01-01', 'YYYY-MM-DD');→ 인덱스를 쓰는 것보다 전체 스캔이 더 빠를 경우 사용
특히 대량 데이터 중 80~90% 이상을 읽을 경우, FULL 스캔이 오히려 효율적일 수 있다.
✅ 3. NO_INDEX – 인덱스 사용하지 말라고 지시
SELECT /*+ NO_INDEX(회원 idx_회원_이름) */ *
FROM 회원
WHERE 이름 = '홍길동';→ 테스트나 특정 상황에서 인덱스를 강제로 배제하고 성능을 확인하고자 할 때 유용
✅ 4. ORDERED – FROM 절 순서대로 조인하도록 강제
SELECT /*+ ORDERED */ *
FROM 부서 d, 직원 e
WHERE d.부서번호 = e.부서번호;→ 옵티마이저는 FROM 순서를 무시하고 효율적인 순서로 조인하지만,
ORDERED힌트를 사용하면 FROM 절 순서대로 조인이 수행조인 순서에 따라 성능이 크게 달라질 때 사용
✅ 5. USE_NL, USE_HASH, USE_MERGE – 조인 방식 지정
-- Nested Loop Join 사용 강제
SELECT /*+ USE_NL(직원 부서) */ *
FROM 직원
JOIN 부서 ON 직원.부서번호 = 부서.부서번호;
-- Hash Join 사용 강제
SELECT /*+ USE_HASH(직원 부서) */ *
FROM 직원
JOIN 부서 ON 직원.부서번호 = 부서.부서번호;→ 옵티마이저가 비효율적인 조인 방식을 선택할 경우,
정해진 조인 방식(NL, HASH, MERGE)을 강제로 적용
✅ 6. PARALLEL – 병렬 처리 지정 (대량 데이터 처리에 유용)
SELECT /*+ PARALLEL(주문, 4) */ *
FROM 주문
WHERE 주문일 >= SYSDATE - 30;→ 4개의 프로세스를 병렬로 사용하여 데이터 조회 속도를 높임.
대용량 배치, 리포트 쿼리에서 유용하게 사용
📌 정리
| 힌트 | 역할 |
|---|---|
| INDEX | 특정 인덱스 사용 |
| FULL | 전체 테이블 스캔 |
| NO_INDEX | 인덱스 사용 안 함 |
| ORDERED | 조인 순서 강제 |
| USE_NL, USE_HASH, USE_MERGE | 조인 방식 강제 |
| PARALLEL | 병렬 처리 실행 |
