파티션 인덱스와 소트에 대한 내용을 살펴보자.
테스트를 위해 아래와 같이 테이블과 인덱스를 생성하자. t1_x1 인덱스는 prefix, t1_x2 인덱스는 nonprefix 인덱스다.
-- 1
DROP TABLE t1 PURGE;
CREATE TABLE t1 (c1 NUMBER, c2 NUMBER)
PARTITION BY RANGE (c1) (
PARTITION p1 VALUES LESS THAN (2)
, PARTITION p2 VALUES LESS THAN (3)
, PARTITION p3 VALUES LESS THAN (MAXVALUE));
CREATE INDEX t1_x1 ON t1 (c1, c2) LOCAL;
CREATE INDEX t1_x2 ON t1 (c2, c1) LOCAL;
INSERT INTO t1 VALUES (1, 1);
INSERT INTO t1 VALUES (1, 2);
INSERT INTO t1 VALUES (2, 1);
INSERT INTO t1 VALUES (2, 2);
INSERT INTO t1 VALUES (3, 1);
INSERT INTO t1 VALUES (3, 2);
COMMIT;
아래 쿼리는 t1_x1 인덱스를 사용했기 때문에 소트가 발생하지 않았다.
-- 2-1 SELECT c1, c2 FROM t1 WHERE c1 > 1 ORDER BY c1, c2; C1 C2 --- -- 2 1 2 2 3 1 3 2 4 행이 선택되었습니다. ----------------------------------------------------- | Id | Operation | Name | Pstart| Pstop | ----------------------------------------------------- | 0 | SELECT STATEMENT | | | | | 1 | PARTITION RANGE ALL| | 1 | 3 | |* 2 | INDEX RANGE SCAN | T1_X1 | 1 | 3 | -----------------------------------------------------
아래 쿼리는 c2 열을 DESC로 정렬했기 때문에 파티션 단위로 소트가 발생했다.
-- 2-2 SELECT c1, c2 FROM t1 WHERE c1 > 1 ORDER BY c1, c2 DESC; C1 C2 -- -- 2 2 2 1 3 2 3 1 4 행이 선택되었습니다. ----------------------------------------------------- | Id | Operation | Name | Pstart| Pstop | ----------------------------------------------------- | 0 | SELECT STATEMENT | | | | | 1 | PARTITION RANGE ALL| | 1 | 3 | | 2 | SORT ORDER BY | | | | -- ! |* 3 | INDEX RANGE SCAN | T1_X1 | 1 | 3 | -----------------------------------------------------
아래 쿼리는 c1 열도 DESC로 정렬했다. 파티션을 뒤에서부터 읽었고, 인덱스를 DESC로 스캔했다. 소트가 발생하지 않는 것을 확인할 수 있다.
-- 2-3 SELECT c1, c2 FROM t1 WHERE c1 > 1 ORDER BY c1 DESC, c2 DESC; C1 C2 -- -- 3 2 3 1 2 2 2 1 4 행이 선택되었습니다. -------------------------------------------------------------- | Id | Operation | Name | Pstart| Pstop | -------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | 1 | PARTITION RANGE ALL | | 3 | 1 | |* 2 | INDEX RANGE SCAN DESCENDING| T1_X1 | 3 | 1 | --------------------------------------------------------------
아래 쿼리는 c1 열만 DESC로 정렬했다. 파티션을 뒤에서부터 읽었고, 인덱스를 ASC로 스캔했다. 소트가 발생하는 것을 확인할 수 있다.
-- 2-4 SELECT c1, c2 FROM t1 WHERE c1 > 1 ORDER BY c1 DESC, c2; C1 C2 -- -- 3 1 3 2 2 1 2 2 4 행이 선택되었습니다. ----------------------------------------------------- | Id | Operation | Name | Pstart| Pstop | ----------------------------------------------------- | 0 | SELECT STATEMENT | | | | | 1 | PARTITION RANGE ALL| | 3 | 1 | | 2 | SORT ORDER BY | | | | -- ! |* 3 | INDEX RANGE SCAN | T1_X1 | 3 | 1 | -----------------------------------------------------
아래 쿼리는 t1_x2 인덱스를 사용한다. nonprefix 인덱스이므로 소트가 발생한다. 모든 파티션을 읽은 후 정렬해야 하기 때문에 PARTITION RANGE ALL 후 소트를 수행한다.
-- 3-1 SELECT c2, c1 FROM t1 WHERE c2 > 1 ORDER BY c2, c1; C2 C1 -- -- 2 1 2 2 3 1 3 2 4 행이 선택되었습니다. ------------------------------------------------------ | Id | Operation | Name | Pstart| Pstop | ------------------------------------------------------ | 0 | SELECT STATEMENT | | | | | 1 | SORT ORDER BY | | | | -- ! | 2 | PARTITION RANGE ALL| | 1 | 3 | |* 3 | INDEX RANGE SCAN | T1_X2 | 1 | 3 | ------------------------------------------------------
ORDER BY 절을 지우면 소트가 사라지지만 의도한 결과를 얻을 수 없다. 결과가 파티션 내에서 정렬된 것을 확인할 수 있다.
-- 3-2 SELECT c2, c1 FROM t1 WHERE c2 > 1; C2 C1 -- -- 2 1 3 1 2 2 3 2 4 행이 선택되었습니다. ----------------------------------------------------- | Id | Operation | Name | Pstart| Pstop | ----------------------------------------------------- | 0 | SELECT STATEMENT | | | | | 1 | PARTITION RANGE ALL| | 1 | 3 | |* 2 | INDEX RANGE SCAN | T1_X2 | 1 | 3 | -----------------------------------------------------
조건을 c2 = 2로 변경하면 소트가 발생하지 않는다. ORDER BY c1 과 동일하게 동작하기 때문에 파티션을 순서대로 읽으면 소트할 필요가 없기 때문이다.
-- 3-3 SELECT c2, c1 FROM t1 WHERE c2 = 2 ORDER BY c2, c1; C2 C1 -- -- 2 1 2 2 2 행이 선택되었습니다. ----------------------------------------------------- | Id | Operation | Name | Pstart| Pstop | ----------------------------------------------------- | 0 | SELECT STATEMENT | | | | | 1 | PARTITION RANGE ALL| | 1 | 3 | |* 2 | INDEX RANGE SCAN | T1_X2 | 1 | 3 | -----------------------------------------------------
조건을 c1 = 2로 변경해도 소트가 발생하지 않는다. 한 파티션만 읽었기 때문에 소트할 필요가 없다.
-- 3-4 SELECT c2, c1 FROM t1 WHERE c1 = 2 ORDER BY c2, c1; C2 C1 -- -- 1 2 2 2 2 행이 선택되었습니다. -------------------------------------------------------- | Id | Operation | Name | Pstart| Pstop | -------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | 1 | PARTITION RANGE SINGLE| | 2 | 2 | |* 2 | INDEX RANGE SCAN | T1_X1 | 2 | 2 | --------------------------------------------------------
c2 열의 값이 고정적이라면 아래와 같이 UNION ALL로 정렬을 제거할 수 있다.
-- 3-5 SELECT * FROM (SELECT c2, c1 FROM t1 WHERE c2 = 2 ORDER BY c1) UNION ALL SELECT * FROM (SELECT c2, c1 FROM t1 WHERE c2 = 3 ORDER BY c1); C2 C1 -- -- 2 1 2 2 3 1 3 2 4 행이 선택되었습니다. ------------------------------------------------------- | Id | Operation | Name | Pstart| Pstop | ------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | 1 | UNION-ALL | | | | | 2 | VIEW | | | | | 3 | PARTITION RANGE ALL| | 1 | 3 | |* 4 | INDEX RANGE SCAN | T1_X2 | 1 | 3 | | 5 | VIEW | | | | | 6 | PARTITION RANGE ALL| | 1 | 3 | |* 7 | INDEX RANGE SCAN | T1_X2 | 1 | 3 | -------------------------------------------------------
-- 4-1 DROP INDEX t1_x2; CREATE INDEX t1_x2 ON t1 (c2, c1);
-- 4-2 SELECT c2, c1 FROM t1 WHERE c2 > 1 ORDER BY c2, c1; C2 C1 -- -- 2 1 2 2 3 1 3 2 4 행이 선택되었습니다. ---------------------------------- | Id | Operation | Name | ---------------------------------- | 0 | SELECT STATEMENT | | |* 1 | INDEX RANGE SCAN| T1_X2 | ----------------------------------
'Oracle > Tuning' 카테고리의 다른 글
| SQL Profile (0) | 2019.01.09 |
|---|---|
| 다중 행 다중 열 스칼라 서브 쿼리의 부분 범위 처리 (0) | 2019.01.09 |
| 조인이 포함된 Top-N 쿼리 (0) | 2019.01.07 |
| 고유 값 구하기 (0) | 2019.01.04 |
| UNION ALL 연산자의 Top-N 동작 (0) | 2018.12.31 |
