[DB] 힌트 예제 정리

 

 

힌트에 대한 정의와 사용법 일부를 이전 포스트에 기록을 진행했었다.

https://devnoong.tistory.com/4

[DB] 힌트에 대해서

 

이번 포스트에는 사용법과 예제에 대해 조금 더 알아보고자한다.

 

https://livesql.oracle.com/

Oracle Live SQL

 

테스트는 위의 사이트에서 테스트를 진행 해보도록 하겠습니다.

 

---

1.  최적화 목표

/*+ ALL_LOWS  */  : 전체 처리속도 최적화

 

 CBO(Cost Based Optimization)에서의  default로 전체 데이터를 조회하는데 최적의 경로를 찾아준다.

전체 RESOURCE 소비를 최소화시키기 위해  Cost-Based 접근방식으로 Full Table Scan을 선호한다.

 

테이블안에 있는 전체 데이터를 검색해서 전체의 합이나 평균을 구하는 용도인 통계를 위해 사용한다.

Batch성 업무에 적합하다.

 

사용전

 

EXPLAIN PLAN FOR select sum(e.sal)
from scott.emp e , scott.dept d
where e.deptno = d.deptno;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

 

 

사용후

 

EXPLAIN PLAN FOR select /*+ ALL_LOWS  */ sum(e.sal)
from scott.emp e , scott.dept d
where e.deptno = d.deptno;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

 

 

테스트 데이터 건수가 많지 않아서 그런지 사용전이나 사용 후나 Cost 비용이 크게 차이가 나지는 않는 것 같다 ㅠ.ㅠ

 

/*+FIRST_ROWS */ : 최초 N건 응답속도 최적화

 

조건에 맞는 첫 번째 row를 리턴하기 위한 Resource 소비를 최소화시키기 위한 힌트이며 Cost-Based 접근방식을 사용.

부분범위처리를 해서 첫부분을 빠르게 조회한다.

 

최초 응답을 빠르게 처리하기 위해서 사용하는것으로 그 후 응답은 동일 하다.

 

 

• Index Scan 이 가능하다면 Optimizer가 Full Table Scan 대신 Index Scan을 선택한다
• Index Scan 이 가능하다면 Optimizer가 Sort-Merge 보다 Nested Loop을 선택한다.
• Order By절에 의해 Index Scan이 가능하면, Sort과정을 피하기 위해 Index Scan을 선택한다.
• Delete/Update Block에서는 무시된다.
• 다음을 포함한 Select 문에서도 제외된다.
             집합 연산자 (UNION, INTERSECT, MINUS, UNION ALL)
             Group By
             For UpDate
             Group 함수
             Distinct
• Full Table Scan보다는 index scan을 선호하며 Interactive Application인 경우 best response time을 제공한다
• sort merge join보다는 nested loop join을 선호한다.

 

사용전

 

EXPLAIN PLAN FO
select  * from 	HR.EMPLOYEES where department_id = '50';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

사용 후

 

EXPLAIN PLAN FOR
select  /*+ FIRST_ROWS */ * from HR.EMPLOYEES where department_id = '50';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

Table Access Full에서 Index Range Scan으로 변경된 것을 확인 해 볼 수 있다.

 

 

2.  액세스 방식

① Table Full 스캔

                     사용법 : /*+FULL */

EXPLAIN PLAN FOR
select  /*+ FULL*/ * from 	HR.EMPLOYEES where department_id = '50';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

 

② Index 오름차순 스캔

                     사용법 : /*+INDEX(별칭 , 인덱스 이름)*/  or /*+INDEX_ASC(별칭 , 인덱스 이름)*/

EXPLAIN PLAN FOR
select  /*+ INDEX_ASC(EMPLOYEES,EMP_DEPARTMENT_IX)*/ * from HR.EMPLOYEES where department_id = '50';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

 

③ Index 내림차순 스캔

                     사용법 : /*+INDEX(별칭 , 인덱스 이름)*/  or /*+INDEX_ASC(별칭 , 인덱스 이름)*/

EXPLAIN PLAN FOR
select  /*+ INDEX_DESC(EMPLOYEES,EMP_EMP_ID_PK)*/ * from 	HR.EMPLOYEES where department_id = '50';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

④ 빠른 Index 스캔

              사용법 : /*+INDEX_FFS(인덱스 이름)*/ 

EXPLAIN PLAN FOR
select  /*+ INDEX_FFS(EMP_EMP_ID_PK)*/ EMPLOYEE_ID from HR.EMPLOYEES where department_id = '50';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

 

인덱스를 FAST FULL SCAN 요청하는 의미로, 보통 인덱스에 대한 스캔은 단일 블록 스캔인데 반해 인덱스 패스트 풀 스캔은 Multi Block Scan 이다.

 

대신 SELECT절에 나타나는 컬럼 들이 INDEX_FFS의 인자로 사용된 인덱스의 컬럼 이어야 한다.

 

⑤ INDEX_SS스캔

              사용법 : /*+INDEX_SS(테이블명 인덱스명)*/ 

 

A,B 컬럼과 같이 복합 인덱스로 구성되어 있는 상태에서 WHERE 절에 B컬럼절로만 조건으로 조회할때 강제적으로 사용하도록 해준다. 

 

 즉, 첫 번째 컬럼이 WHERE 조건에 존재하지 않아도 인덱스를 이용할 수 있는 인덱스 액세스 방식이다. 

A컬럼값에 대해 종류가 적을 경우 좋은 퍼포먼스를 나타내지만 상황에 따라 안좋은 퍼포먼스를 나타날수도 있기때문에 주의해서 사용해야한다.

 

---

참고

https://gurume.tistory.com/entry/%EC%98%A4%EB%9D%BC%ED%81%B4-%EC%9E%90%EC%A3%BC%EC%82%AC%EC%9A%A9%ED%95%98%EB%8A%94-%ED%9E%8C%ED%8A%B8%EB%AA%A9%EB%A1%9D-%EC%A0%95%EB%A6%AC%EC%B9%9C%EC%A0%88%ED%95%9C-sql-%ED%8A%9C%EB%8B%9D

https://devuna.tistory.com/35