CLUSTER_BY_ROWID 힌트

12.1 버전부터 CLUSTER_BY_ROWID 힌트를 사용하면 CF 향상을 통해 Buffer Pinning 효과를 높일 수 있다. 관련 내용을 살펴보자.

테스트를 위해 아래와 같이 테이블을 생성하자. t1_x1 인덱스는 CF가 매우 나쁘다.

-- 1
DROP TABLE t1 PURGE;

CREATE TABLE t1 AS
SELECT ROWNUM AS c1, MOD (ROWNUM, 100) AS c2, LPAD ('X', 100, 'X') AS c3 FROM XMLTABLE ('1 to 10000');

CREATE INDEX t1_x1 ON t1 (c2);

SELECT clustering_factor, num_rows FROM user_indexes WHERE index_name = 'T1_X1';

CLUSTERING_FACTOR NUM_ROWS
----------------- --------
            10000    10000

1개의 행이 선택되었습니다.

아래 쿼리는 실행 계획 2번에서 행 1000개를 읽기 위해 블록 I/O가 1004개가 발생했다.

-- 2
SELECT /*+ INDEX(T1) */ COUNT (c1) FROM t1 WHERE c2 BETWEEN 1 AND 10;

-------------------------------------------------------------------------
| Id  | Operation                            | Name  | A-Rows | Buffers |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |       |      1 |    1004 |
|   1 |  SORT AGGREGATE                      |       |      1 |    1004 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1    |   1000 |    1004 |
|*  3 |    INDEX RANGE SCAN                  | T1_X1 |   1000 |       4 |
-------------------------------------------------------------------------

아래는 CLUSTER_BY_ROWID 힌트를 사용한 쿼리다. 인덱스 스캔 결과를 ROWID 순서로 정렬(SORT CLUSTER BY ROWID 오퍼레이션)한 후 테이블을 액세스한다.

-- 3
SELECT /*+ INDEX(T1) CLUSTER_BY_ROWID(T1) */ COUNT (c1) FROM t1 WHERE c2 BETWEEN 1 AND 10;

---------------------------------------------------------------------------------------------
| Id  | Operation                            | Name  | Starts | A-Rows | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |       |      1 |      1 |     116 |          |
|   1 |  SORT AGGREGATE                      |       |      1 |      1 |     116 |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1    |      1 |   1000 |     116 |          |
|   3 |    SORT CLUSTER BY ROWID             |       |      1 |   1000 |       4 |79872  (0)| -- !
|*  4 |     INDEX RANGE SCAN                 | T1_X1 |      1 |   1000 |       4 |          |
---------------------------------------------------------------------------------------------

12.1 이번 버전은 아래처럼 인라인 뷰를 통해 유사한 효과를 얻을 수 있다.

-- 4
SELECT /*+ ORDERED USE_NL(B) ROWID(B) */
       COUNT (c1)
  FROM (SELECT   /*+ NO_MERGE NO_ELIMINATE_OBY */
                 ROWID AS rid
            FROM t1
           WHERE c2 BETWEEN 1 AND 10
        ORDER BY ROWID) a
     , t1 b
 WHERE b.ROWID = a.rid;

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Starts | A-Rows | Buffers | Used-Mem |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |      1 |      1 |     116 |          |
|   1 |  SORT AGGREGATE              |       |      1 |      1 |     116 |          |
|   2 |   NESTED LOOPS               |       |      1 |   1000 |     116 |          |
|   3 |    VIEW                      |       |      1 |   1000 |       4 |          |
|   4 |     SORT ORDER BY            |       |      1 |   1000 |       4 |79872  (0)|
|*  5 |      INDEX RANGE SCAN        | T1_X1 |      1 |   1000 |       4 |          |
|   6 |    TABLE ACCESS BY USER ROWID| T1    |   1000 |   1000 |     112 |          |
-------------------------------------------------------------------------------------

관련 힌트와 파라미터는 아래와 같다.

-- 5
SELECT name, inverse, version FROM v$sql_hint WHERE name = 'CLUSTER_BY_ROWID';

NAME             INVERSE             VERSION
---------------- ------------------- --------
CLUSTER_BY_ROWID NO_CLUSTER_BY_ROWID 12.1.0.1

1개의 행이 선택되었습니다.

NAME                                   DEFAULT_VALUE DESCRIPTION
-------------------------------------- ------------- ----------------------------------------------------
_optimizer_cluster_by_rowid            TRUE          enable/disable the cluster by rowid feature
_optimizer_cluster_by_rowid_control    3             internal control for cluster by rowid feature mode
_optimizer_cluster_by_rowid_batched    TRUE          enable/disable the cluster by rowid batching feature
_optimizer_cluster_by_rowid_batch_size 100           Sorting batch size for cluster by rowid feature