최소값/최대값 구하기
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
EXPLAIN PLAN FOR
SELECT MAX(A.customAge) customAge
FROM Ex4_Custom A
WHERE A.customApplyDate > '20010101'
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT |
------------------------------------------------------------------------------------+
Plan hash value: 3022444818 |
|
------------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
------------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 1 | 12 | 11 (0)| 00:00:01 ||
| 1 | SORT AGGREGATE | | 1 | 12 | | ||
|* 2 | INDEX RANGE SCAN| EX4_CUSTOM_IDX | 1862 | 22344 | 11 (0)| 00:00:01 ||
------------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
2 - access("A"."CUSTOMAPPLYDATE">'20010101' AND "A"."CUSTOMAPPLYDATE" IS |
NOT NULL) |
최솟값 최댓값을 구하는 SQL 실행계획을 보면 SORT AGGREGATE 오퍼레이션이 나타난다 SORT AGGREGATE 은 전체 데이터를 정렬하진 않지만 전체 데이터를 읽으면서 값을 비교한다 이때 인덱스는 정렬되어 있으므로 이를 이용하면 전체 데이터를 읽지 않고도 최소 또는 최댓값을 쉽게 찾을 수 있다
지금처럼 MAX 값을 찾는 경우 인덱스 맨 오른쪽으로 가서 첫 번째 값이 MAX 값이고 MIN 경우 제일 왼쪽으로 가서 첫 번째 읽는 값이 최솟값이 된다
이처럼 전체 데이터를 읽지 않고 인덱스를 이용해 최소 또는 최댓값을 구하려면 조건절 컬럼과 MIN/MAX 함수 인자 컬럼이 모두 인덱스에 포함이 되어 있어야 한다 즉 인덱스 테이블 스캔만으로 최대 최솟값을 찾으려고 할 때 사용되는 방법이다
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
CREATE INDEX emp_idx1 ON EMP(DEPTNO , MGR , SAL)
EXPLAIN PLAN FOR
SELECT MAX(SAL)
FROM EMP
WHERE DEPTNO = 30
AND MGR = 7698
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT |
-----------------------------------------------------------------------------------------+
Plan hash value: 2317044335 |
|
-----------------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
-----------------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 1 | 11 | 1 (0)| 00:00:01 ||
| 1 | SORT AGGREGATE | | 1 | 11 | | ||
| 2 | FIRST ROW | | 1 | 11 | 1 (0)| 00:00:01 ||
|* 3 | INDEX RANGE SCAN (MIN/MAX)| EMP_IDX1 | 1 | 11 | 1 (0)| 00:00:01 ||
-----------------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
3 - access("DEPTNO"=30 AND "MGR"=7698) |
조건절 컬럼과 MAX 컬럼이 인덱스에 포함이 되어 있다 이때 두 조건을 모두 만족하는 범위 가장 오른쪽 첫 번째 값을 찾아서 이때 오퍼레이션은 FIRST ROW 가 나오게 되는데 앞에서 보았듯이 STOPKEY처럼 하나만 찾으면 바로 멈추게 된다 그야 당연하게도 MAX 값은 하나일 수밖에 없기 때문이다
하지만 FIRST ROW는 반드시 INDEX RAGNE SCAN 이 발생할 때만 나오는 것은 아니다
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
CREATE INDEX emp_idx2 ON EMP(SAL , DEPTNO , MGR)
EXPLAIN PLAN FOR
SELECT /*+ INDEX(EMP emp_idx2)*/MAX(SAL)
FROM EMP
WHERE DEPTNO = 30
AND MGR = 7698
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT |
----------------------------------------------------------------------------------------+
Plan hash value: 2883667987 |
|
----------------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
----------------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 1 | 11 | 1 (0)| 00:00:01 ||
| 1 | SORT AGGREGATE | | 1 | 11 | | ||
| 2 | FIRST ROW | | 1 | 11 | 1 (0)| 00:00:01 ||
|* 3 | INDEX FULL SCAN (MIN/MAX)| EMP_IDX2 | 1 | 11 | 1 (0)| 00:00:01 ||
----------------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
3 - filter("MGR"=7698 AND "DEPTNO"=30) |
선두 칼럼이 아닌 인덱스를 만들어서 힌트를 줘서 하게 되면 이렇게 인덱스 테이블을 FULL 스캔 하긴 하지만 만족하는 레코드를 하나 찾았을 때 FIRST ROW 가 동작해서 더 이상 찾지 않는 알고리즘은 동일하다 위 쿼리와 다른 것은 인덱스 테이블 범위로 찾으냐 아니면 인덱스 테이블 전체를 찾느냐 문제인 것이다
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
CREATE INDEX emp_idx3 ON EMP(DEPTNO , SAL)
EXPLAIN PLAN FOR
SELECT /*+ INDEX(EMP emp_idx3)*/MAX(SAL)
FROM EMP
WHERE DEPTNO = 30
AND MGR = 7698
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT |
-------------------------------------------------------------------------------------------------+
Plan hash value: 3102304397 |
|
-------------------------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
-------------------------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 1 | 11 | 2 (0)| 00:00:01 ||
| 1 | SORT AGGREGATE | | 1 | 11 | | ||
|* 2 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 1 | 11 | 2 (0)| 00:00:01 ||
|* 3 | INDEX RANGE SCAN | EMP_IDX3 | 6 | | 1 (0)| 00:00:01 ||
-------------------------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
2 - filter("MGR"=7698) |
3 - access("DEPTNO"=30) |
이때 인덱스 선두 칼럼이 없으면 First ROW 오퍼레이션도 동작하지 않고 본 테이블에서 해당 조건을 만족하는 데이터를 찾게 됩니다 그렇기에 성능 차이가 발생하는 것을 볼 수 있습니다
TOP - N 쿼리로 MAX 값 찾기
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
EXPLAIN PLAN FOR
SELECT SAL
FROM (
SELECT SAL,
ROW_NUMBER() OVER(ORDER BY SAL DESC) SAL_ROWNUMBER
FROM EMP
WHERE DEPTNO = 30
AND MGR = 7698
)
WHERE SAL_ROWNUMBER = 1
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT |
-----------------------------------------------------------------------------------------+
Plan hash value: 4183394237 |
|
-----------------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
-----------------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 1 | 26 | 1 (0)| 00:00:01 ||
|* 1 | VIEW | | 1 | 26 | 1 (0)| 00:00:01 ||
|* 2 | WINDOW NOSORT STOPKEY | | 1 | 11 | 1 (0)| 00:00:01 ||
|* 3 | INDEX SKIP SCAN DESCENDING| EMP_IDX2 | 1 | 11 | 1 (0)| 00:00:01 ||
-----------------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
1 - filter("SAL_ROWNUMBER"=1) |
2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("SAL") DESC )<=1) |
3 - access("DEPTNO"=30 AND "MGR"=7698) |
filter("MGR"=7698 AND "DEPTNO"=30) |
이렇게 작성을 해도 STOPKEY 가 동작을 해서 전체 테이블을 조회하지 않고 부분 조회를 하게 됩니다