Optimizer & Hint

💡 Optimizer

 

SQL을 가장 빠르고 효율적으로 수행할 최적의 처리경로(최저비용)를 생성해주는 DBMS 내부의 핵심 엔진이다.

사용자가 쿼리문으로 결과를 요청하면,
이를 생성하는데 필요한 처리경로는 DBMS에 내장된 옵티마이저가 자동으로 생성한다.

옵티마이저가 생성한 SQL 처리 경로를 실행계획(Execution Plan)이라고 한다.

 

---

Optimizer의 SQL 최적화 과정

사용자가 작성한 쿼리 수행을 위해, 실행될 만한 실행 계획을 찾는다.

Data Dictionary에 미리 수집해놓은 오브젝트 통계 및 시스템 통계 정보를 이용해서,

각 실행 계획의 예상 비용을 산정하며 각 실행 계획을 비교해서 최저 비용을 갖는 하나를 선택 및 실행한다.

 

Parser	Optimizer	Row_Source	SQL_Engine
문법 오류 검사, 코드로 변경	총 비용 계산, 실행 계획 생성	Generator, 실행 가능 코드로 변경	실행

 

---

Optimizer의 종류

• 규칙 기반(Role Based Optimizer), 휴리스틱(Heuristic) 옵티마이저
  • 미리 정해진 규칙에 따라 실행
  • Oracle 10g 버전부터 RBO에 대한 지원 중단
• 비용 기반(Cost Based Optimizer) 옵티마이저
  • 비용이 가능 낮은 실행 계획(Execution Plan)을 선택

규칙 기반 옵티마이저는 Cost가 더 낮은 실행 계획이 있더라도,
미리 정해놓은 규칙에 따라 더 높은 Cost의 실행 계획을 실행하기도 한다.

이 방식이 비효율적이라서 Oracle 10g 버전부터는 RBO에 대한 지원을 중단한 것임.

 

---

💡 Hint

Select 문에 실행하고 싶은 계획을 전달할 때 사용하는 문법이다.

잘못 작성되어도 실행할 때에는 무시되며 별도의 오류는 발생하지 않는다.

/*+ 로 시작되어 */로 마치며, 뒤에 컬렴명을 작성할 때 ","를 사용하지 않는다.

 

SELECT /*+ INDEX_DESC(테이블 PK)*/ 컬럼명1, 컬럼명2, ... FROM 테이블;  // 내림차순
SELECT /*+ INDEX(테이블 PK)*/ 컬럼명1, 컬럼명2, ... FROM 테이블;  // 오름차순

SELECT * FROM USER_IND_COLUMNS I
WHERE TABLE_NAME = 'PLAYER';

Hint는 테이블의 인덱스를 가져와서 정렬을 시켜준다.

이 쿼리문을 쓰면 해당 테이블의 인덱스명을 알 수 있다.

 

정렬을 해주는 ORDER BY 함수가 있지만 ORDER BY는 정렬을 시키고자 하는 데이터가 많아진다면,
SELECT를 하고 정렬을 시키느라 속도가 많이 느려진다.

 

반면 Hint는 SELECT전에 인덱스 번호로 접근하여 정렬을 시키기 때문에 데이터가 많아도 빠르다.