SQL INDEX & INDEX HINT

SELECT *
  FROM T;
  
  
CREATE INDEX T_X01 ON T(DEPTNO, NO);  
CREATE INDEX T_X02 ON T(DEPTNO, JOB, NO);


--통계 정보 수집
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T');


--CTRL + E
SELECT *
  FROM T
 WHERE DEPTNO = 10
   AND NO = 1
     ;
     

--INDEX 힌트 지정
SELECT /*+ INDEX(T T_X02) */ * 
  FROM T
 WHERE DEPTNO = 10
   AND NO = 1
     ;
     
     
--FULL SCAN
SELECT /*+ FULL(T) */ * 
  FROM T
 WHERE DEPTNO = 10
   AND NO = 1
     ;

 

 

◎ 주의사항

1. ','(콤마) 사용 방법

-- 힌트 안에 인자를 나열할 땐 사용 가능
/*+ INDEX(A A_X01) INDEX(B, B_X03) */    -- 모두 유효

-- 힌트와 힌트 사이에 사용불가
/*+ INDEX(C), FULL(D) */                 -- 앞에만 유효

 

2. 스키마명

--테이블 지정시 스키마명까지 명시하면 안 된다
SELECT /*+ FULL(SCOTT.EMP) */ *          -- 무효
  FROM EMP;

 

3. ALIAS

-- FROM 절 테이블명 앞에 ALIAS 지정시 힌트에도 반드시 ALIAS를 사용
SELECT /*+ FULL(EMP) */ *          -- 무효
  FROM EMP E;

 

---

 

◎ 자율과 강제

 

--하나만 설정하고 나머지 옵티마이저 자율성 부여  
SELECT /*+ INDEX(A (주문일자)) */
       A.주문번호
     , A.주문금액
     , B.고객명
     , B.연락처
     , B.주소
  FROM 주문 A
     , 고객 B
 WHERE A.주문일자 = :ORD_DT
   AND A.고객ID = B.고객ID;
   
   
--경로 전부 설정
SELECT /*+ LEADING(A) USE_NL(B) INDEX(A (주문일자)) INDEX(B 고객_PK)*/
       A.주문번호
     , A.주문금액
     , B.고객명
     , B.연락처
     , B.주소
  FROM 주문 A
     , 고객 B
 WHERE A.주문일자 = :ORD_DT
   AND A.고객ID = B.고객ID;

 

---

 

◎ 자주 사용하는 힌트 목록

분류	힌트	설명
최적화 목표	ALL_ROWS	전체  처리속도 최적화
	FIRST_ROWS(N)	최초 N건 응답속도 최적화
액세스 방식	FULL	Table Full Scan으로 유도
	INDEX	Index Scan으로 유도
	INDEX_DESC	Index를 역순으로 스캔하도록 유도
	INDEX_FFS	Index Fast Full Scan으로 유도
	INDEX_SS	Index Skip Scan으로 유도
조인순서	ORDERED	FROM 절에 나열된 순서대로 조인
	LEADING	LEADING 힌트 괄호에 기술한 순서대로 조인 (예) LEADING(T1 T2)
	SWAP_JOIN_INPUTS	해시 조인 시, BUILD INPUT을 명시적으로 선택 (예) SWAP_JOIN_INPUTS(T1)
조인방식	USE_NL	NL 조인으로 유도
	USE_MERGE	소트 머지 조인으로 유도
	USE_HASH	해시 조인으로 유도
	NL_SJ	NL 세미조인으로 유도
	MERGE_SJ	소트 머지 세미조인으로 유도
	HASH_SJ	해시 세미조인으로 유도
서브쿼리 팩토링	MATERIALIZE	WITH문으로 정의한 집합을 물리적으로 생성하도록 유도 예) WITH /*+ MATERIALIZE */ T AS (SELECT ...)
	INLINE	WITH문으로 정의한 집합을 물리적으로 생성하지 않고 INLINE 처리하도록 유도 예) WITH /*+ INLINE */ T AS (SELECT ...)
쿼리 변환	MERGE	뷰 머징 유도
	NO_MERGE	뷰 머징 방지
	UNNEST	서브쿼리 Unnesting 유도
	NO_UNNEST	서브쿼리 Unnesting 방지
	PUSH_PRED	조인조건 Pushdown 유도
	NO_PUSH_PRED	조인조건 Pushdown 방시
	USE_CONCAT	OR 또는 IN-List 조건을 OR-Expansion으로 유도
	NO_EXPAND	OR 또는 IN-List 조건에 대한 OR-Expansion 방지
병렬 처리	PARALLEL	테이블 스캔 또는 DML을 병렬방식으로 처리하도록 유도 예) PARALLEL(T1 2)  PARALLEL(T2 2)
	PARALLEL_INDEX	인덱스 스캔을 병렬방식으로 처리하도록 유도
	PQ_DISTRIBUTE	병렬 수행 시 데이터 분배 방식 결정 예) PQ_DISTRIBUTE(T1 HASH HASH)
기타	APPEND	Direct-Path Insert로 유도
	DRIVING_SITE	DB Link Remote 쿼리에 대한 최적화 및 실행 주체 지적 (Local 또는 Remote)
	PUSH_SUBQ	서브쿼리를 가급적 빨리 필터링하도록 유도
	NO_PUSH_SUBQ	서브쿼리를 가급적 늦게 필터링하도록 유도