옵티마이저 수행절차 및 힌트종류와 사용법 확인

[옵티마이저 수행절차 및 힌트종류와 사용법 편]

예전 사원일 때 정말 5천줄이 되는 쿼리를 짠 적이 있었습니다. 성과 기본 베이스로 급여를 산정하는 쿼리였는데 어떻게 지금 생각해도 5천줄이 넘었었는지 기가 막히고 코가 막힐 쿼리였습니다. 조금만 데이터의 흐름이나 구조를 생각해보고 풀어가면 됬을 거였는데도 말이지요. 오늘은 이런 저를 반성하는 의미로 옵티마이저 수행절차와 쿼리를 짤때 많이 쓰는 힌트 종류와 사용법에 대해 알아볼까 합니다.

| 옵티마이저 수행절차 및 힌트종류와 사용법 확인

| 옵티마이저 수행절차 

옵티마이저 수행절차에 대해 알아보겠습니다. 사용자가 실행한 sql문은 데이터 딕셔너리를 참조하여 우선 파싱 작업을 수행하게 됩니다. 옵티마이저는 이러한 파싱 결과를 이용하여 논리적으로 적용 가능한 실행계획을 선택하게 되고 힌트를 감안하여 일차적으로 잠정적인 실행계획을 생성하게 됩니다.

다음으로 데이터 딕셔너리 정보와 통계정보 비교연산자 등을 통해 각 실행 계획의 비용을 계산하게 됩니다. 딕셔너리 정보에는 테이블 저장구조,인덱스 구조,파티션 형태 등이 있으며 통계정보는 저장 건수와 데이터의 분포도 등을 주로 사용하고 있습니다.

옵티마이저는 실행계획들의 산출된 비용을 비교해보고 가장 최소의 비용을 가진 실행계획을 선택하게 됩니다. 하지만 최저가 입찰 방식을 택한 것이므로 항상 최적의 결정이라고 할수는 없습니다.

다음은 질의 변환의 예입니다.

수식연산의 경우 사용자 쿼리 기준으로 미리 수식 표현을 이해 쉬운 형태로 변환한 것입니다.

그럼 이런 것들을 기본으로 한 힌트 종류와 사용법에 대해 알아보도록 하겠습니다.

| 힌트 종류 및 사용법

일단 힌트는 select/insert/update/delete 4가지 문에서 사용할 수 있습니다. 힌트의 사용은 SQL 전체가 아닌 쓰여진 SQL 블럭에서만 적용이 가능합니다. 또한 /* HINT[TEXT] [HINT[TEXT]] */ 이런식으로 사용을 하면 됩니다.

다음은 오라클 힌트의 종류 정리입니다.

1. INDEX ACCESS OPERATION 관련 HINT

a. INDEX 

- 인덱스를 순차적으로 스캔하라는 힌트입니다. 사용법은 INDEX(table명,INDEX명) 입니다. 이때 table명 에는 주로 별칭을 주고 있습니다.

b. INDEX_DESC

- 인덱스를 역순으로 스캔하라는 힌트입니다. 사용법은 INDEX_DESC(table명,INDEX명) 입니다. 

c. INDEX_FFS

- 인덱스를 FAST FULL 스캔하라는 힌트입니다. 사용법은 INDEX_FFS(table명,INDEX명) 입니다. 

d. PARALLEL_INDEX

- 인덱스를 PARALLEL 스캔하라는 힌트입니다. 사용법은 PARALLEL_INDEX(table명,INDEX명) 입니다.

e. AND_EQUALS

- INDEX MERGE 수행하라는 힌트입니다. 사용법은 AND_EQUALS(INDEX명,INDEX명) 입니다. 

f. FULL

- FULL 스캔하라는 힌트입니다. 사용법은 FULL(TABLE명) 입니다.

2. JOIN ACCESS OPERAATION 관련 HINT

a.USE_NL

- NESTED LOOP JOIN 힌트입니다. 사용법은 USE_NL(TABLE1,TABLE2) 입니다.

b.USE_MERGE

- SORT MERGE JOIN 힌트입니다. 사용법은 USE_MERGE(TABLE1,TABLE2) 입니다.

b.USE_HASH

- HASH JOIN 힌트입니다. 사용법은 USE_HASH(TABLE1,TABLE2) 입니다.

3. JOIN 시 DRIVING 순서결정 HINT

a. ORDERED

- from 절의 앞에서부터 driving 하라는 힌트입니다.

b. DRIVING

- 해당 테이블을 먼저  driving 하라는 힌트입니다.

4. 기타 HINT

a. APPEND

- insert 시 direct loading 하는 힌트입니다.

b. PARALLEL

- select,insert 시 여러개의 프로세스로 수행하는 힌트입니다. PARALLEL(table명,개수) 사용법입니다.

c. PUSH_SUBQ

- sub query를 먼저 수행하라는 힌트입니다.

이상 지금까지 옵티마이저 수행절차와 오라클 힌트종류 및 사용법에 대해 알아보았습니다. 간단한 방법으로 실행계획이 달라지고 속도가 현저하게 차이나는만큼 잘 사용해야 하는 것 같습니다. 이쪽은 공부를 해도 해도 끝이 없는 것 같습니다. 기본적인 사항이지만 잘 참고하시기 바랍니다. 이만 오늘의 옵티마이저 수행절차 및 힌트종류와 사용법 확인에 대한 포스팅을 마칩니다.