Note 001_01 # Range Scan이 불가능한 경우 |
다음과 같이 단일 인덱스 컬럼을 가공하면 Index Range Scan은 불가능하다.
단, Index Full Scan 또는 Fast Index Full Scan은 가능하다.
- 인덱스 컬럼에 함수를 사용한 경우
- 인덱스 컬럼에 결합연산을 사용한 경우
- 인덱스 컬럼에 사칙연산을 사용한 경우
- 묵시적 형변환이 발생한 경우
- HAVING 절에서 필터링할 경우
- Not-Nullable 인덱스의 어떤 컬럼에 IS NULL 연산자를 사용한 경우
Note 001_02 # 인덱스 스캔이 불가능한 경우 |
- Nullable 인덱스의 어떤 컬럼에 IS NULL 연산자를 사용한 경우
Note 001_03 # 스캔이 아예 일어나지 않는 경우 |
- Not-Nullable 인덱스의 모든 컬럼에 IS NULL 연산자를 사용한 경우
Plus Note 001_01 # Range Scan이 불가능한 경우 # 묵시적 형변환이 발생한 경우 |
12c-R2 벤더에서 묵시적 형변환으로 인한 비효율적인 실행계획을 재현할 수 없었다.
옵티마이저가 스스로 묵시적 형변환을 처리하도록 개선 된 것 같다.
다음 쿼리를 살펴보자.
쿼리에서 1을 명시적으로 char 타입으로 캐스팅 했음에도 불구하고
옵티마이저가 이를 무시하고 number 타입으로 캐스팅 한 것에 주목하길 바란다.
-- 인덱스 X_LINEITEM_02 : [L_PARTKEY + L_SUPPKEY] SELECT COUNT(*) FROM LINEITEM WHERE L_PARTKEY = to_char(1);
----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5 | 3 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 5 | | | |* 2 | INDEX RANGE SCAN| X_LINEITEM_02 | 30 | 150 | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("L_PARTKEY"=1)
만약 이전 버전의 벤더였다면 다음과 비슷한 실행계획이 출력될 것 이다.
--------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5 | 4646 (2)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 5 | | | |* 2 | INDEX FAST FULL SCAN| X_LINEITEM_02 | 30 | 150 | 4646 (2)| 00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access(TO_CHAR("L_PARTKEY")=TO_CHAR(1))
DECODE 함수에서 사용된 묵시적 형변환도 같은 결과를 보였다.
12c부터는 묵시적 형변환으로 인한 비효율은 걱정하지 않아도 될 것이다.
Plus Note 001_02 # Range Scan이 불가능한 경우 # HAVING 절에서 필터링할 경우 |
SELECT 문장에서 각 절의 실행순서를 간략하게 설명하면
FROM ㅡ WHERE ㅡ GROUP BY ㅡ HAVING ㅡ ORDER BY 순서이다.
따라서 WHERE 절이 아닌 HAVING 절에서 필터링을 수행하면
인덱스를 사용하지 못한 채 GROUP BY를 수행하는 꼴이 된다.
다음 계획에서 필터링 조건을 수행하는 필터 계획이
GROUP BY 상단에 위치하는 것에 주목하기 바란다.
-- 인덱스 X_LINEITEM_02 : [L_PARTKEY + L_SUPPKEY] SELECT L_PARTKEY, COUNT(*) FROM LINEITEM GROUP BY L_PARTKEY HAVING L_PARTKEY < 50 ORDER BY L_PARTKEY;
---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 50 | 250 | 4927 (7)| 00:00:01 | |* 1 | FILTER | | | | | | | 2 | SORT GROUP BY | | 50 | 250 | 4927 (7)| 00:00:01 | | 3 | INDEX FAST FULL SCAN| X_LINEITEM_02 | 6001K| 28M| 4631 (1)| 00:00:01 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("L_PARTKEY"<50)
GROUP BY를 수행했기 때문에 L_PARTKEY<50 조건을 필터링할 때에도
인덱스를 사용하지 못한것을 알 수 있다.
반면 WHERE 절에서 필터링을 처리한다면
Range Scan 후 GROUP BY를 수행하는 실행계획이 수립될 것 이다.
다음 계획에서 필터링 조건을 수행하는 액세스 계획이
GROUP BY 하단에 위치하는 것에 주목하기 바란다.
-- 인덱스 X_LINEITEM_02 : [L_PARTKEY + L_SUPPKEY] SELECT L_PARTKEY, COUNT(*) FROM LINEITEM WHERE L_PARTKEY < 50 GROUP BY L_PARTKEY ORDER BY L_PARTKEY;
-------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 49 | 245 | 7 (0)| 00:00:01 | | 1 | SORT GROUP BY NOSORT| | 49 | 245 | 7 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | X_LINEITEM_02 | 1470 | 7350 | 7 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("L_PARTKEY"<50)
Plus Note 001_03 # 스캔이 아예 일어나지 않는 경우 # Not-Nullable 인덱스의 모든 컬럼에 IS NULL 연산자를 사용한 경우 |
이러한 경우에는 굳이 실제로 수행하지 않아도
결과집합이 없음을 쉽게 예측할 수 있다.
이런 경우에는 오라클이 자동적으로 NULL IS NOT NULL 조건절을 추가하여
불필요한 스캔계획이 실제로 수행하지 않도록 한다.
아래 실행계획에서 INDEX FAST FULL SCAN이 발생했지만,
오라클이 추가한 FILTER 계획에 의하여 전체비용이 0이 된것에 주목하기 바란다.
-- 인덱스 X_LINEITEM_02 : [L_PARTKEY + L_SUPPKEY] SELECT COUNT(*) FROM LINEITEM WHERE L_PARTKEY IS NULL AND L_SUPPKEY IS NULL;
---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 9 | 0 (0)| | | 1 | SORT AGGREGATE | | 1 | 9 | | | |* 2 | FILTER | | | | | | | 3 | INDEX FAST FULL SCAN| X_LINEITEM_02 | 6001K| 51M| 4631 (1)| 00:00:01 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(NULL IS NOT NULL AND NULL IS NOT NULL)
