스칼라 서브 쿼리의 버퍼 Pinning 효과

11.2 버전부터 스칼라 서브 쿼리도 버퍼 Pinning 효과가 발생한다.

테스트를 위해 아래와 같이 테이블을 생성하자.

-- 1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;

CREATE TABLE t1 AS SELECT ROWNUM AS c1, NVL (NULLIF (MOD (ROWNUM, 100), 0), 100) AS c2 FROM XMLTABLE ('1 to 10000');
CREATE TABLE t2 AS SELECT ROWNUM AS c1, ROWNUM AS c2 FROM XMLTABLE ('1 to 10000');

CREATE INDEX t1_x1 ON t1 (c1, c2);
CREATE INDEX t1_x2 ON t1 (c2, c1);
CREATE INDEX t2_x1 ON t2 (c1);
CREATE INDEX t2_x2 ON t2 (c2);

아래 쿼리는 서브 쿼리에 1에서 10000까지의 순번이 입력된다. 서브 쿼리 Caching 효과가 발생하지 않았지만(Starts = 10000), 버퍼 Pinning 효과에 의해 166개의 블록 I/O가 발생했다.

-- 2
SELECT /*+ INDEX(A) */
       COUNT ((SELECT /*+ INDEX(X) */
                      MAX (x.c2)
                 FROM t2 x
                WHERE x.c1 = a.c1))
  FROM t1 a
 WHERE a.c1 > 0;

--------------------------------------------------------------------------
| Id  | Operation                    | Name  | Starts | A-Rows | Buffers |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |      1 |      1 |     193 |
|   1 |  SORT AGGREGATE              |       |  10000 |  10000 |     166 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T2    |  10000 |  10000 |     166 | -- !
|*  3 |    INDEX RANGE SCAN          | T2_X1 |  10000 |  10000 |     148 |
|   4 |  SORT AGGREGATE              |       |      1 |      1 |     193 |
|*  5 |   INDEX RANGE SCAN           | T1_X1 |      1 |  10000 |      27 |
--------------------------------------------------------------------------

아래 쿼리는 서브 쿼리에 1, 101, 201 순서의 값이 100회 반복 입력된다. 서브 쿼리 Caching 효과가 발생하지 않았고, 버퍼 Pinning 효과도 발생하지 않아 13,421개의 블록 I/O가 발생했다.

-- 3
SELECT /*+ INDEX(A) */
       COUNT ((SELECT /*+ INDEX(X) */
                      MAX (x.c2)
                 FROM t2 x
                WHERE x.c1 = a.c1))
  FROM t1 a
 WHERE a.c2 > 0;

--------------------------------------------------------------------------
| Id  | Operation                    | Name  | Starts | A-Rows | Buffers |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |      1 |      1 |   13448 |
|   1 |  SORT AGGREGATE              |       |  10000 |  10000 |   13421 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T2    |  10000 |  10000 |   13421 | -- !
|*  3 |    INDEX RANGE SCAN          | T2_X1 |  10000 |  10000 |   11621 |
|   4 |  SORT AGGREGATE              |       |      1 |      1 |   13448 |
|*  5 |   INDEX RANGE SCAN           | T1_X2 |      1 |  10000 |      27 |
--------------------------------------------------------------------------

아래 쿼리는 서브 쿼리에 1에서 100까지의 값이 100회씩 입력된다. 서브 쿼리 Caching 효과와 버퍼 Pinning 효과에 의해 9개의 블록 I/O가 발생했다.

-- 4
SELECT /*+ INDEX(A) */
       COUNT ((SELECT /*+ INDEX(X) */
                      MAX (x.c1)
                 FROM t2 x
                WHERE x.c2 = a.c2))
  FROM t1 a
 WHERE a.c2 > 0;

--------------------------------------------------------------------------
| Id  | Operation                    | Name  | Starts | A-Rows | Buffers |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |      1 |      1 |      36 |
|   1 |  SORT AGGREGATE              |       |    100 |    100 |       9 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T2    |    100 |    100 |       9 | -- !
|*  3 |    INDEX RANGE SCAN          | T2_X2 |    100 |    100 |       8 |
|   4 |  SORT AGGREGATE              |       |      1 |      1 |      36 |
|*  5 |   INDEX RANGE SCAN           | T1_X2 |      1 |  10000 |      27 |
--------------------------------------------------------------------------