Intra-block row chaining

오라클 데이터베이스는 테이블 칼럼 개수가 255개 이상인 경우 1행을 동일한 블록에 2행으로 나눠 저장한 후 행을 서로 연결한다. 이런 동작을 Intra-block row chaining이라고 한다.^[각주:1] Intra-block row chaining이 발생하면 1행을 조회하기 위해 추가적인 블록 I/O가 발생하므로 쿼리의 성능이 저하될 수 있다.

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

-- 1
DROP TABLE t1 PURGE;

DECLARE
    l_sql_text    VARCHAR2(32767);
BEGIN
    l_sql_text := 'CREATE TABLE T1 (c1 NUMBER';
    FOR i IN 2 .. 300 LOOP l_sql_text := l_sql_text || ', c' || i || ' NUMBER'; END LOOP;
    l_sql_text := l_sql_text || ')';
    EXECUTE IMMEDIATE l_sql_text;

    l_sql_text := 'INSERT INTO t1 VALUES (1';
    FOR i IN 2 .. 300 LOOP l_sql_text := l_sql_text || ', 2'; END LOOP;
    l_sql_text := l_sql_text || ')';
    EXECUTE IMMEDIATE l_sql_text;

    COMMIT;
END;
/

CREATE INDEX t1_x1 ON t1 (c1);

Intra-block row chaining이 발생한 테이블을 조회하는 칼럼에 따라 블록 I/O가 달라진다. 전체 칼럼을 조회한 쿼리 2-1은 3개, 45번째 칼럼을 조회한 쿼리 2-2는 2개, 46번째 칼럼을 조회한 쿼리 2-3은 3개의 블록 I/O가 발생했다. 칼럼을 뒤쪽부터 46~255번째를 1행, 나머지 1~45번째를 1행으로 저장하기 때문이다.

-- 2-1
SELECT * FROM t1 WHERE c1 = 1;

-------------------------------------------------------
| Id  | Operation                   | Name  | Buffers |
-------------------------------------------------------
|   0 | SELECT STATEMENT            |       |       3 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |       3 |
|*  2 |   INDEX RANGE SCAN          | T1_X1 |       1 |
-------------------------------------------------------

-- 2-2
SELECT c45 FROM t1 WHERE c1 = 1;

-------------------------------------------------------
| Id  | Operation                   | Name  | Buffers |
-------------------------------------------------------
|   0 | SELECT STATEMENT            |       |       2 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |       2 |
|*  2 |   INDEX RANGE SCAN          | T1_X1 |       1 |
-------------------------------------------------------

-- 2-3
SELECT c46 FROM t1 WHERE c1 = 1;

-------------------------------------------------------
| Id  | Operation                   | Name  | Buffers |
-------------------------------------------------------
|   0 | SELECT STATEMENT            |       |       3 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |       3 |
|*  2 |   INDEX RANGE SCAN          | T1_X1 |       1 |
-------------------------------------------------------

블록 덤프를 확인해보면 칼럼이 뒤쪽부터 저장된 것을 확인할 수 있다. tab 0, row 1, @0x1bf0 부분에 46~300번째, tab 0, row 1, @0x1bf0 부분에 1~45번째 칼럼이 저장되어 있다.

-- 3-1
SELECT DBMS_ROWID.ROWID_RELATIVE_FNO (ROWID) AS fno
     , DBMS_ROWID.ROWID_BLOCK_NUMBER (ROWID) AS bno
  FROM t1;

FNO     BNO
--- -------
  4 1126476

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

-- 3-2
ALTER SYSTEM DUMP DATAFILE 4 BLOCK 1126476;

block_row_dump:

tab 0, row 0, @0x1c80
tl: 768 fb: -----L-- lb: 0x1  cc: 255
col   0: [ 2]  c1 03
…
col 254: [ 2]  c1 03

tab 0, row 1, @0x1bf0
tl: 144 fb: --H-F--- lb: 0x1  cc: 45
nrid:  0x0111304c.0
col  0: [ 2]  c1 02
…
col 44: [ 2]  c1 03

end_of_block_dump

테이블을 재구성한 후 테이블을 조회해보면, 255번째 칼럼을 조회한 쿼리 4-2는 2개, 256번째 칼럼을 조회한 쿼리 4-3은 3개의 블록 I/O가 발생하는 것을 확인할 수 있다. 테이블을 재구성할 경우 앞쪽부터 1~255번째 열을 1행, 나머지 256~300번째 열을 1행으로 저장하기 때문이다. 이후 입력되는 행은 뒤쪽부터 저장된다.

-- 4-1
ALTER TABLE t1 MOVE;
ALTER INDEX t1_x1 REBUILD;

-- 4-2
SELECT c255 FROM t1 WHERE c1 = 1;

-------------------------------------------------------
| Id  | Operation                   | Name  | Buffers |
-------------------------------------------------------
|   0 | SELECT STATEMENT            |       |       2 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |       2 |
|*  2 |   INDEX RANGE SCAN          | T1_X1 |       1 |
-------------------------------------------------------

-- 4-3
SELECT c256 FROM t1 WHERE c1 = 1;

-------------------------------------------------------
| Id  | Operation                   | Name  | Buffers |
-------------------------------------------------------
|   0 | SELECT STATEMENT            |       |       3 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |       3 |
|*  2 |   INDEX RANGE SCAN          | T1_X1 |       1 |
-------------------------------------------------------

블록 덤프를 확인해보면 칼럼이 앞쪽부터 저장된 것을 확인할 수 있다. tab 0, row 0, @0x1c7a 부분에 1~255번째, tab 0, row 1, @0x1bf0 부분에 256~300번째 칼럼이 저장되어 있다.

-- 5-1
SELECT DBMS_ROWID.ROWID_RELATIVE_FNO (ROWID) AS fno
     , DBMS_ROWID.ROWID_BLOCK_NUMBER (ROWID) AS bno
  FROM t1;

FNO     BNO
--- -------
  4 1928835

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

-- 5-2
ALTER SYSTEM DUMP DATAFILE 4 BLOCK 1928835;

block_row_dump:

tab 0, row 0, @0x1c7a
tl: 774 fb: --H-F--- lb: 0x0  cc: 255
nrid:  0x011d6e83.1
col   0: [ 2]  c1 02
...
col 254: [ 2]  c1 03

tab 0, row 1, @0x1bf0
tl: 138 fb: -----L-- lb: 0x0  cc: 45
col  0: [ 2]  c1 03
...
col 44: [ 2]  c1 03

end_of_block_dump

Intra-block row chaining이 발생한 테이블을 NL 조인 이너 테이블로 사용하면 과도한 블록 I/O가 발생할 수 있다. 아래에서 255번째 칼럼을 조회한 쿼리 6-1은 140개, 256번째 칼럼을 조회한 쿼리 6-2는 1140개의 블록 I/O가 발생했다. 아우터 테이블의 건수만큼 추가적인 블록 I/O가 발생하는 것을 확인할 수 있다.

-- 6-1
SELECT /*+ ORDERED USE_NL(B) INDEX(B) */ b.c255 FROM XMLTABLE ('1 to 1000') a, t1 b WHERE b.c1 = 2;

------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                  | Starts | A-Rows | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                       |      1 |   1000 |     140 |
|   1 |  NESTED LOOPS                      |                       |      1 |   1000 |     140 |
|   2 |   COLLECTION ITERATOR PICKLER FETCH| XQSEQUENCEFROMXMLTYPE |      1 |   1000 |       3 |
|   3 |   TABLE ACCESS BY INDEX ROWID      | T1                    |   1000 |   1000 |     137 |
|*  4 |    INDEX RANGE SCAN                | T1_X1                 |   1000 |   1000 |      69 |
------------------------------------------------------------------------------------------------

-- 6-2
SELECT /*+ ORDERED USE_NL(B) INDEX(B) */ b.c256 FROM XMLTABLE ('1 to 1000') a, t1 b WHERE b.c1 = 2;

------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                  | Starts | A-Rows | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                       |      1 |   1000 |    1140 |
|   1 |  NESTED LOOPS                      |                       |      1 |   1000 |    1140 |
|   2 |   COLLECTION ITERATOR PICKLER FETCH| XQSEQUENCEFROMXMLTYPE |      1 |   1000 |       3 |
|   3 |   TABLE ACCESS BY INDEX ROWID      | T1                    |   1000 |   1000 |    1137 |
|*  4 |    INDEX RANGE SCAN                | T1_X1                 |   1000 |   1000 |      69 |
------------------------------------------------------------------------------------------------

Intra-block row chaining은 테이블을 수직 분할하거나, 자주 사용되는 칼럼을 255개 안쪽으로 배치하고 정기적으로 테이블을 재구성함으로써 성능 저하를 방지할 수 있다. 현실적인 방안은 후자에 가깝다.

1. 반대로 여러 블록에 걸쳐 발생한 row chining을 Inter-block row chaining이라고 한다. [본문으로]