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

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

학생 때 기껏해야 select * from  으로 시작하는 쿼리만 짜보다 처음 회사에 입사해서 업무를 맡아 짜보았던 쿼리는 어느덧 union all이 아니어도 기본 천줄이 넘어갈 때가 종종 생기고는 했습니다. 내가 쿼리를 너무 못해서 이렇게 길어지는 건지 데이터 구조를 파악을 못해서 이렇게 길어지는 건지 헷갈리는 부분도 있었지만 시간에 쫒겨 업무마감에 쫒겨 그럭저럭 굴러가기만 하면 된다는 생각에 엉망진창의 쿼리를 그대로 두고는 했었습니다. 하지만 이러한 쿼리는 데이터가 쌓이다보면 결국 잘못된 조인방식으로 인해 나중에는 무한정 기다려야하는 엄청난 네거티브적 쿼리가 될수도 있는데도 불구하고 그런 위험을 두고 하루 하루 하루만 살면 된다는 식으로 살아왔던 것입니다. 이 시간에는 이런 저를 반성하는 의미로 옵티마이저 수행절차와 쿼리를 짤 때 많이 쓰는 힌트 종류의 사용법에 대해 알아보려합니다.

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

그 후 옵티마이저는 데이터 딕셔너리 정보와 통계정보 비교연산자를 통하여 각 실행 계획의 비용을 계산해 보게 됩니다. 아무래도 아무리 좋은 실행계획이라 하더라도 남는게 없는 장사라면 안해야 수지타산이 맞으니깐 말입니다. 그 바탕이 되는 딕셔너리 정보에는 테이블 저장구조,인덱스 구조,파티션 형태가 있으며 통계정보에는 저장 건수와 데이터의 분포같은 정보를 사용하고 있습니다.

 

그러면 옵티마이저는 이러한 실행계획들의 산출된 비용을 비교해보고 가장 최소비용을 갖고 있는 실행계획을 선택하게 됩니다. 물론 이러한 옵티마이저가 택한 최저가 입찰 방식은 늘 최적의 결정이라고 단정 할수는 없습니다. 따라서 옵티마이저가 선택 한 것이라 하더라도 실행계획에 대해 다시한번 살펴보는 것도 좋은 쿼리를 짜는 하나의 훌륭한 자세라 할 수 있겠습니다.

 

다음은 질의 변환에 대해 알아보겠습니다. 예시로 한번 살펴보도록 하겠습니다.

먼저 수식연산의 경우 사용자가 작성한 쿼리로 수식 표현을 이해하기 쉬운 형태로 변환해 본 것입니다.

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

☆ 힌트 종류와 사용법

 

일단 힌트는 select/insert/update/delete 의 sql 문에서 사용할 수 있습니다. 힌트의 사용은 SQL 전체가 아닌 쓰여진 SQL 블럭에서만 적용이 가능하며 사용법은 대체로 /* HINT[TEXT] [HINT[TEXT]] */ 이런식의 표현식을 필요로 하고 있습니다.

 

다음은 가장 많이 사용하고 있는 오라클 힌트의 종류를 정리한 것입니다. 참조하면 좋을 것 같습니다.

1. INDEX ACCESS OPERATION

 

a. INDEX 
- 인덱스를 순차적으로 스캔하는 힌트이며 INDEX(table명,INDEX명) 로 사용하면 됩니다. 이때 table명 에는 주로 AS 이하로 준 별칭을 사용하면 됩니다.

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

 

a.USE_MERGE 
- SORT MERGE JOIN 힌트로 USE_MERGE(TABLE1,TABLE2) 로 사용합니다.

 

b.USE_HASH 
- HASH JOIN 힌트로 USE_HASH(TABLE1,TABLE2) 로 사용합니다. 

 

c.USE_NL
- NESTED LOOP JOIN 힌트로 USE_NL(TABLE1,TABLE2) 로 사용합니다.

3. JOIN 시 DRIVING 순서결정 HINT

 

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

 

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

4. 기타 HINT

 

a. PARALLEL 
- select나 insert 시 여러개의 프로세스를 수행해야할 때 사용하면 되며 PARALLEL(table명,개수) 입니다. 

 

b. APPEND
- insert 시 direct loading 이 필요할 경우 사용하면 됩니다.

 

c. PUSH_SUBQ
- sub query를 먼저 수행할 때 사용하면 됩니다.

 

이 시간에는 옵티마이저 수행절차와 오라클 힌트종류와 사용법에 대해 알아보았습니다. 힌트 하나만으로도 실행계획이  어마어마하게 달라질 수 있는 쿼리의 세계는 단순하면서도 단순하지 않고 복잡하면서도 복잡하지 않은 참 알수없는 신비의 세계같습니다. 그래도 힌트 하나로 계속해서 속도가 현저하게 차이나는만큼 잘 사용해야 하는 것 같습니다. 어찌됬든 간에 DB나 쿼리 이쪽은 공부를 해도 해도 끝이 없는 것 같습니다. 기본적인 사항이지만 잘 참고해서 업무나 공부에 조금이나마 도움되었으면 합니다.