조인 컬럼에 IS NOT NULL 조건 추가
- 조인 컬럼에 is not null 조건을 추가하면 필요시 불필요한 테이블 엑게스 조건 및
조인 시도를 줄일 수 있다.
- 조인시 is not null 조건이 없더라도 인덱스를 읽을 때는 null인 컬럼은 조인하지 않는다.
다만 full table scan일 시에는 모두 조인한다.
( 그러나 is not null 조건을 명시적으로 추가해 준다면 염려할 필요가 없다.
다만 full table scan 시 is not null 조건을 필터링 하기 위한 테이블 방문은 일어난다. )
- 다행히, 컬럼 통계를 수집하고 나면 옵티마이저가 그런 조건절을 자동으로 추가해 준다.
단, 조인 컬럼의 null 값 비중이 5% 이상일 때만 이 기능이 작동한다.
- 조인 컬럼에 is not null 조건을 추가해 주면 NL 조인뿐만 아니라 해시 조인, 소트 머지 조인
시에도 효과를 발휘한다.
- 우선, 해시 조인을 위해 Build Input을 읽어 해시 맵을 만들 때 더 적은 메모리를 사용한다.
Probe Input을 읽을 때도 Null 값인 레코드를 제외함으로써 해시 맵 탐색 횟수를 줄일 수 있다.
양쪽 모두 null 값 비중이 클수록 효과도 커진다.
- 소트 머지 조인할 때도 양쪽 테이블에서 조인 컬럼이 null인 레코드를 제외한다면 소트 및
비교 연산 횟수를 줄일 수 있다.
- 조인 컬럼에 대한 is not null 조건을 추가한다고 손해 볼 일은 전혀 없다.
- 그리고 조인 컬럼에 null 값 비중이 많을 때 임의의 default 값으로 채우는 방식으로 설계하면
조인 성능을 떨어뜨릴 수 있다는 사실도 기억하기 바란다.
필터 조건 추가
- 바인드 변수를 사용한 between 쿼리에서 두 값을 비교해서 최소 값이 최대값보다 클 때
쿼리를 읽을 필요가 없다. 그때는 9i부터는 이를 방지하기 위해 옵티마이저가 임의로 필터
조건식을 추가한다.
- 바인드 변수 대신 상수 값으로 조회할 때도 filter 조건이 추가되는데, 아래와 같은 9i 와 10g
에서 조금 다르게 처리하고 있다.
9i : filter (5000<=100)
10g 이상 : filter(NULL IS NOT NULL)
- 9i에서 오브젝트 통계가 없으면 RBO 모드로 작동해 위와 같은 쿼리 변환이 일어나지 않는다.
10g는 통계정보가 없어도 항상 CBO 모드로 작동하므로 쿼리 변환이 잘 일어난다.
* 테스트 시 확인 사항
테스트 시 확인한 것은 위와 같은 상황에서 블록 읽기가 발생하지 않게 하려면
해당 테이블들이 buffer cache에 있어야 한다는 점이다.
만약 해당 테이블들이 buffer cache에 없다면 consistent gets의 값이 full table scan을
할 때와 동일하게 발생하였다. 물론 이는 정확한 값을 입력했다고 해도 마찬가지였다.
이 부분은 매우 중요한 부분임으로 몇번 더 테스트를 해보아야 할 것이다.
조건절 비교 순서
- 옵티마이저는, 테이블 전체를 스캔하거나 인덱스를 수평적으로 스캔할 때의 Filter 조건식을
평가할 때 선택도가 낮은 컬럼을 먼저 처리하도록 순서를 조정한다.
- 이런 쿼리 변환이 작동하려면 9i, 10g를 불문하고 옵티마이저에게 시스템 통계를 제공함으로써
CPU Costing 모델을 활성화해야 한다. I/O Costing 모델에서는 where절에 기술된 순서대로
조건식 비교가 일어난다. RBO 모드에서는 where절에 기술된 반대 순서로 조건식 비교가
일어난다.
RBO : where절에 기술된 반대 순서
CBO(I/O Costing모드) : where절에 기술된 순서
CBO(CPU Costing모드) : 비교 연산해야 할 일량을 고려해 옵티마이저가 결정,
선택도가 낮은 조건식부터 평가
- ordered_predicates 힌트를 사용하면 CPU Costing 모드에서도 조건절 비교 순서를
제어할 수 있다. 이 힌트를 사용하면 where절에 기술도니 순서대로 비교가 일어난다.
ordered_predicates 힌트의 또 다른 용도
- 10g에서 OR 또는 IN-List 조건에 대한 OR-Expansion이 일어날 때 실행 순서를 제어할
목적으로 ordered_predicates 힌트를 사용할 수도 있다.
* 나의 생각
- 난 아직 이걸 어떨때 유용하게 사용할 수 있을지 모르겠다. 어차피 or 조건에 있는
2번 다 실행해야 할텐대 처리 순서가 왜 중요한 건지 모르겠다.
'ORACLE > SQLP' 카테고리의 다른 글
성능고도화 5-2. 소트를 발생시키는 오퍼레이션 (0) | 2016.12.26 |
---|---|
성능고도화 5-1. 소트 수행 원리 (0) | 2016.12.26 |
성능고도화 4-11. 집합 연산을 조인으로 변환 (0) | 2016.12.26 |
성능고도화 4-10. 실체화 뷰 쿼리로 작성 (0) | 2016.12.26 |
성능고도화 4-9 Outer 조인을 Inner 조인으로 변환 (0) | 2016.12.26 |