MAX_EXECUTION_TIME(10)
최대 실행 시간
# ms 단위
SELECT /*+ MAX_EXECUTION_TIME(500) */ *
FROM temp
SET_VAR(optimizer_switch='index_merge=on')
시스템 변수 제어
SELECT /*+ SET_VAR(optimizer_switch='index_merge=on') */ *
FROM temp
INDEX(table_A idx_a)
USE|IGNORE INDEX 대체
USE|IGNORE INDEX FOR GROUP BY 대체
USE|IGNORE INDEX FOR ORDER BY 대체
WHERE 절에서 사용/미사용할 인덱스
SELECT /*+ INDEX(t PRIMARY) */ emp_no
FROM employees t
SELECT /*+ NO_INDEX(t PRIMARY) */ emp_no
FROM employees t
STRAIGHT_JOIN 동일
JOIN_ORDER(table_a, table_b, table_c)
조인 순서 선택
JOIN_PREFIX(table_a, table_b)
시작(드라이빙) 테이블
JOIN_SUFFIX(table_c, table_d)
종료(드리븐) 테이블
SELECT /*+ JOIN_FIXED_ORDER() */ *
FROM salaries s
INNER JOIN employees e
ON s.emp_no = e.emp_no
SELECT /*+ JOIN_ORDER(e, s) */ *
FROM salaries s
INNER JOIN employees e
ON s.emp_no = e.emp_no
SELECT /*+ JOIN_PREFIX(e, s) */ *
FROM salaries s
INNER JOIN employees e
ON s.emp_no = e.emp_no
INNER JOIN employee_name en
ON en.emp_no = e.emp_no
서브쿼리 이름 설정
SUBQUERY(DUPSWEEDOUT|FIRSTMATCH|LOOSESCAN|MATERIALIZATION)
서브쿼리에 직접 사용함
QB_NAME을 이용해서 SUBQUERY에 힌트 사용함
SEMIJOIN(@subq1, DUPSWEEDOUT|FIRSTMATCH|LOOSESCAN|MATERIALIZATION)
SELECT /*+
SEMIJOIN(@dept MATERIALIZATION)
JOIN_SUFFIX(e)
*/e.*
FROM employees e
WHERE e.emp_no IN (
SELECT /*+
QB_NAME(dept)
*/e.emp_no
FROM departments d
INNER JOIN dept_emp e
ON d.dept_no = e.dept_no
WHERE dept_name = 'Sales'
)
Batched Key Access Join 사용 여부
SELECT /*+
SET_VAR(optimizer_switch='batched_key_access=on')
JOIN_ORDER(e, de)
BKA(e, de)
*/ *
FROM dept_emp de
INNER JOIN employees e
ON de.emp_no = e.emp_no
Block Nested-Loop Join
8.0.20부터는 hash Join 사용 여부
SELECT /*+ NO_BNL() */ *
FROM employees e
INNER JOIN employees e2
ON e.birth_date = e2.birth_date
derived table condition pushdown
Hash join 사용 여부
8.0.19부터 BNL로 사용함
index merge 사용여부
SELECT /*+ INDEX_MERGE(e) */ e.*
FROM employees e
WHERE e.first_name = 'Aamer'
AND e.hire_date = '1996-02-15'
서브쿼리, 뷰 병합 활성화 여부
Multi Range Read 사용여부
SELECT /*+ MRR(e) */ e.*
FROM employees e
WHERE e.first_name BETWEEN 'Aamer' AND 'Abdelaziz'
Index condition pushdown 사용여부
Index range scan 사용여부
Index skip scan 사용여부