주 콘텐츠로 건너뛰기

검색

Welcome to Tibero GTS!

INDEX 정렬을 활용한 조회 예제

문서유형ㅣ기술정보

분야ㅣ튜닝

적용제품버전ㅣTibero 7.2.4

문서번호ㅣTTUTI038

 

개요

인덱스가 값의 정렬 상태를 유지한다는 특징을 이용하여 Min, Max 값 추출 시 활용하는 예제입니다.
 
 

방법

1. 테스트 오브젝트 및 데이터 생성

INDEX 정렬에 따른 sort 연산 생략 예제 수행을 위해 아래 구문을 먼저 수행합니다.
 
-- 테스트 테이블 생성
DROP TABLE T1;
CREATE TABLE "T1"
( "C1" NUMBER,
 "C2" NUMBER,
 "C3" NUMBER,
 "C4" VARCHAR2(10)
);
-- 2000 만건 생성
INSERT /*+ append */ INTO T1
SELECT 1
 , 1
 , 1
 , 'TIBERO'
 FROM DUAL CONNECT BY LEVEL<=20000000;
COMMIT;
-- 인덱스 생성
CREATE INDEX IDX_T1_C1C2 ON T1(C1,C2);
-- T1 테이블의 통계정보 생성
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'TIBERO', TABNAME=>'T1');

 

2. 인덱스 없이 실행 하기

인덱스 없이 최대 값을 구하는 경우 실행 계획을 확인합니다.
 
SQL> SET TIMING ON
SQL> ALTER SESSION SET GATHER_SQL_PLAN_STAT=Y;

Session altered.

SQL> SELECT MAX(C3) FROM T1 WHERE C3 > 0 AND C1 = 1;

   MAX(C3)
----------
         1

1 row selected.

Total elapsed time 00:00:06.869296

SQL> set linesize 180
SQL> set pagesize 0
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL BUFGETS'));

SQL ID         : fa94ybmrnh3yy
CHILD NUMBER   : 501
HASH VALUE     : 4014477278
PLAN HASH VALUE: 1804130916
EXECUTIONS     : 2
FETCHES        : 2
LOADED AT      : 2025/12/08 16:15:26
TOT ELAPSED TIME: 00:00:14.1772
AVG ELAPSED TIME: 00:00:07.0886
TOT BUFFER GETS: 119498
AVG BUFFER GETS: 59749

-----------------------------------------------------------------------------------------------------------------
| ID  | Operation              | Name | Cost (%CPU)  | Cards    | Rows     | Elaps. Time  | BUFGETS  | Starts   |
-----------------------------------------------------------------------------------------------------------------
|   1 | COLUMN PROJECTION      |      |26335   (6.44)|        1 |        1 |00:00:00.0000 |       0  |       1  |
|   2 |  SORT AGGR             |      |26335   (6.44)|        1 |        1 |00:00:00.4022 |       0  |       1  |
|   3 |   TABLE ACCESS (FULL)  | T1   |25588   (3.71)| 19987446 | 20000000 |00:00:06.4635 |   59749  |       1  |
-----------------------------------------------------------------------------------------------------------------

LAST ELAPSED TIME: 00:00:06.8657
LAST BUFFER GET: 59749

Predicate Information
-----------------------------------------------------------------------------------------------------------------
   3 - filter: ("T1"."C3" > 0) AND ("T1"."C1" = 1) (1.000 * 1.000)

참고

  • T1 테이블의 C3 컬럼에는 Index가 존재하지 않으므로 Full Scan을 수행한 후 Sort 작업을 통해 MAX 값을 조회합니다.

  • 읽기 일관성 모드에서의 블록 요청 횟수(consistent gets) 값은 59,749로 확인됩니다.

 

3. INDEX_DESC 사용

INDEX_DESC 힌트를 사용해 인덱스 스캔을 내림차순으로 하도록 지시하여 최대값을 구하는 경우 실행 계획을 확인합니다. 아래 예제를 통해 IDX_T1_C1C2 인덱스의 정렬을 활용하여 C2 컬럼 최대 값을 구하고, 인덱스의 정렬 상태(현재 작은 값 부터 큰 값으로)에 대한 역방향 스캔(큰 값부터 스캔)하여 수행하는 것을 알 수 있습니다.
 
SQL> 
SELECT /*+ INDEX_DESC(T1 IDX_T1_C1C2)*/
       MAX(C2)
  FROM T1
 WHERE C1 = 1
   AND C2 > 0    
/

   MAX(C2)
----------
         1

1 row selected.

Total elapsed time 00:00:00.000233

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL BUFGETS'));

SQL ID         : 4k1ak2xnmj3td
CHILD NUMBER   : 507
HASH VALUE     : 1765314349
PLAN HASH VALUE: 472743196
EXECUTIONS     : 3
FETCHES        : 3
LOADED AT      : 2025/12/08 16:22:39
TOT ELAPSED TIME: 00:00:00.0007
AVG ELAPSED TIME: 00:00:00.0002
TOT BUFFER GETS: 18
AVG BUFFER GETS: 6

------------------------------------------------------------------------------------------------------------------------------------
| ID  | Operation                           | Name        | Cost (%CPU)  | Cards    | Rows    | Elaps. Time  | BUFGETS  | Starts   |
------------------------------------------------------------------------------------------------------------------------------------
|   1 | COLUMN PROJECTION                   |             |48126    (.75)|        1 |       1 |00:00:00.0000 |       0  |       1  |
|   2 |  SORT AGGR                          |             |48126    (.75)|        1 |       1 |00:00:00.0000 |       0  |       1  |
|   3 |   COUNT (STOP NODE) (STOP LIMIT 2)  |             |48126    (.75)|        1 |       1 |00:00:00.0000 |       0  |       1  |
|   4 |    INDEX (RANGE SCAN) DESCENDING    | IDX_T1_C1C2 |47764      (0)| 19987446 |     263 |00:00:00.0000 |       6  |       1  |
------------------------------------------------------------------------------------------------------------------------------------

LAST ELAPSED TIME: 00:00:00.0000
LAST BUFFER GET: 6

Predicate Information
------------------------------------------------------------------------------------------------------------------------------------
   3 - filter: ("T1"."C2" IS NOT NULL) (1.000)
   4 - access: ("T1"."C1" = 1) AND ("T1"."C2" > 0) (1.000 * 1.000)

참고

테이블 Full Scan보다 적은 블록을 읽으며, 정렬 작업이 발생하지 않으므로 부하가 적습니다.

INDEX_DESC 힌트를 적용하여 실행하였으며, 약 0.0002초가 소요되었습니다.

읽기 일관성 모드에서의 블록 요청 횟수(consistent gets) 값은 6으로 확인됩니다.

 

4. INDEX_ASC 사용

INDEX_ASC 힌트를 사용해 인덱스 스캔을 오름차순으로 하도록 지시하여 최대 값을 구하는 경우 실행 계획을 확인합니다. 아래 예제를 통해 IDX_T1_C1C2 인덱스 스캔 힌트를 적용하여, 인덱스의 정렬 상태(현재 작은 값 부터 큰 값으로)에 대한 순방향 스캔(작은 값부터 스캔)하여 수행하는 것을 알 수 있습니다.
 
SQL> 
SELECT /*+ INDEX_ASC(T1 IDX_T1_C1C2)*/
       MAX(C2)
  FROM T1
 WHERE C1 = 1
   AND C2 > 0    
/

   MAX(C2)
----------
         1

1 row selected.

Total elapsed time 00:00:00.474051

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL BUFGETS'));

SQL ID         : b5kz6gzxqam8p
CHILD NUMBER   : 510
HASH VALUE     : 4217720085
PLAN HASH VALUE: 3306013127
EXECUTIONS     : 3
FETCHES        : 3
LOADED AT      : 2025/12/08 16:30:53
TOT ELAPSED TIME: 00:00:12.6722
AVG ELAPSED TIME: 00:00:04.2241
TOT BUFFER GETS: 143901
AVG BUFFER GETS: 47967

-----------------------------------------------------------------------------------------------------------------------
| ID  | Operation             | Name        | Cost (%CPU)  | Cards    | Rows     | Elaps. Time  | BUFGETS  | Starts   |
-----------------------------------------------------------------------------------------------------------------------
|   1 | COLUMN PROJECTION     |             |48510   (1.54)|        1 |        1 |00:00:00.0000 |       0  |       1  |
|   2 |  SORT AGGR            |             |48510   (1.54)|        1 |        1 |00:00:00.3712 |       0  |       1  |
|   3 |   INDEX (RANGE SCAN)  | IDX_T1_C1C2 |47764      (0)| 19987446 | 20000000 |00:00:00.0959 |   47967  |       1  |
-----------------------------------------------------------------------------------------------------------------------

LAST ELAPSED TIME: 00:00:00.4671
LAST BUFFER GET: 47967

Predicate Information
-----------------------------------------------------------------------------------------------------------------------
   3 - access: ("T1"."C1" = 1) AND ("T1"."C2" > 0) (1.000 * 1.000)

참고

  • 조건절을 만족하는 값을 찾기 위해 Index Scan을 수행한 후 정렬 작업을 통해 MAX 값을 조회합니다.

  • 읽기 일관성 모드에서의 블록 요청 횟수(consistent gets) 값은 47,967로 확인됩니다.

 

5. INDEX_DESC, ORDER BY 구문 사용

T1 테이블 C2 최대 값을 구할때, 3번 예제와 달리 MAX 함수 사용하지 않고 ORDER BY 구문 사용하는 경우 실행 계획을 확인합니다. IDX_T1_C1C2 인덱스의 정렬을 활용하여 C2 컬럼 최대값을 구하고, 인덱스의 정렬 상태(현재 작은 값 부터 큰 값으로)에 대한 역방향 스캔(큰 값부터 스캔) 수행하는 것을 알 수 있습니다.

 
SQL> 
SELECT C2
FROM ( SELECT /*+ INDEX_DESC(T1 IDX_T1_C1C2)*/
           C2
      FROM T1
     WHERE C1 = 1
       AND C2 > 0
     ORDER BY C1 DESC, C2 DESC
)
WHERE ROWNUM <= 1
/

        C2
----------
         1

1 row selected.

Total elapsed time 00:00:00.000222

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL BUFGETS'));

SQL ID         : bs671x3mca6xs
CHILD NUMBER   : 511
HASH VALUE     : 3871677368
PLAN HASH VALUE: 872264694
EXECUTIONS     : 3
FETCHES        : 3
LOADED AT      : 2025/12/08 16:39:45
TOT ELAPSED TIME: 00:00:00.0290
AVG ELAPSED TIME: 00:00:00.0097
TOT BUFFER GETS: 18
AVG BUFFER GETS: 6

----------------------------------------------------------------------------------------------------------------------------------
| ID  | Operation                         | Name        | Cost (%CPU)  | Cards    | Rows    | Elaps. Time  | BUFGETS  | Starts   |
----------------------------------------------------------------------------------------------------------------------------------
|   1 | COUNT (STOP NODE) (STOP LIMIT 2)  |             |47764      (0)|        1 |       1 |00:00:00.0000 |       0  |       1  |
|   2 |  INDEX (RANGE SCAN) DESCENDING    | IDX_T1_C1C2 |47764      (0)| 19987446 |     263 |00:00:00.0000 |       6  |       1  |
----------------------------------------------------------------------------------------------------------------------------------

LAST ELAPSED TIME: 00:00:00.0000
LAST BUFFER GET: 6

Predicate Information
----------------------------------------------------------------------------------------------------------------------------------
   2 - access: ("T1"."C1" = 1) AND ("T1"."C2" > 0) (1.000 * 1.000)

참고

  • 테이블 Full Scan보다 적은 블록을 읽으며, 정렬 작업이 발생하지 않으므로 부하가 적습니다.

  • INDEX_DESC 힌트를 적용하여 실행한 결과, 약 0.0002초가 소요되었습니다.

  • 읽기 일관성 모드에서의 블록 요청 횟수(consistent gets) 값은 6으로 확인됩니다.

 

관련 대상