CLOB #5 - ENABLE STORAGE IN ROW

CLOB 타입은 4000바이트 이상인 값만 LOB 세그먼트에 저장한다.

테스트를 위해 11.2 버전에서 아래와 같이 테이블을 생성하자. 데이터베이스 캐릭터 셋은 KO16MSWIN949다.

-- 1-1
DROP TABLE t1 PURGE;

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

LOB 세그먼트를 조회해보면 c2 칼럼은 64K, c3 칼럼은 80M를 사용하고 있다. 두 칼럼 모두 BASIC FILE로 생성되었다.

-- 1-2
SELECT a.column_name, a.securefile, b.segment_name, b.bytes, b.blocks
  FROM user_lobs a
     , user_segments b
 WHERE a.table_name = 'T1'
   AND b.segment_name = a.segment_name;

COLUMN_NAME SEGMENT_NAME              SECUREFILE    BYTES BLOCKS
----------- ------------------------- ---------- -------- ------
C2          SYS_LOB0000197636C00002$$ NO            65536      8
C3          SYS_LOB0000197636C00003$$ NO         83886080  10240

2 행이 선택되었습니다.

데이터 블록 덤프 내용에서 c2 칼럼은 실제 값, c3 칼럼은 LOB 로케이터가 저장되어 있는 것을 확인할 수 있다. c2 칼럼은 4000바이트를 사용하고 있다. 실제 값의 크기는 입력한 값의 2배(3964 = 1982*2)고^[각주:1], Inode 관련 16바이트, LOB 관련 20바이트의 부가 정보가 추가로 저장되는 것을 유추할 수 있다. c3 칼럼은 4002바이트(= (1983*2)+16+20)가 필요하므로 LOB 세그먼트에 저장되었다.

-- 1-3
ALTER SYSTEM DUMP DATAFILE 4 BLOCK 587;

block_row_dump:
tab 0, row 0, @0xfae
tl: 4050 fb: --H-FL-- lb: 0x0  cc: 3
col  0: [ 2]  c1 02
col  1: [4000]
 00 54 00 01 02 0c 80 00 00 02 00 00 00 01 00 00 00 c8 be d3 0f 8c 09 00 00
...
LOB
Locator:
  Length:        84(4000)
  Version:        1
  Byte Length:    2
  LobID: 00.00.00.01.00.00.00.c8.be.d3
  Flags[ 0x02 0x0c 0x80 0x00 ]:
    Type: CLOB 
    Storage: BasicFile
    Enable Storage in Row 
    Characterset Format: IMPLICIT
    Partitioned Table: No
    Options: VaringWidthReadWrite 
  Inode: 
    Size:     3980
    Flag:     0x09 [ Valid DataInRow ]
    Future:   0x00 (should be '0x00')
    Blocks:   0
    Bytes:    3964
    Version:  00000.0000000001
    Inline data[3964]
Dump of memory from 0x000000001C2C3457 to 0x000000001C2C43D3
01C2C3450          00010000 00580058 00580058      [....X.X.X.X.]
01C2C3460 00580058 00580058 00580058 00580058  [X.X.X.X.X.X.X.X.]
        Repeat 246 times
01C2C43D0 28580058                             [X.X(]            
col  2: [40]
 00 54 00 01 02 0c 80 00 00 02 00 00 00 01 00 00 00 c8 be d4 00 14 05 00 00
 00 00 00 0f 7e 00 00 00 00 00 02 01 00 02 2d
LOB
Locator:
  Length:        84(40)
  Version:        1
  Byte Length:    2
  LobID: 00.00.00.01.00.00.00.c8.be.d4
  Flags[ 0x02 0x0c 0x80 0x00 ]:
    Type: CLOB 
    Storage: BasicFile
    Enable Storage in Row 
    Characterset Format: IMPLICIT
    Partitioned Table: No
    Options: VaringWidthReadWrite 
  Inode: 
    Size:     20
    Flag:     0x05 [ Valid InodeInRow(ESIR) ]
    Future:   0x00 (should be '0x00')
    Blocks:   0
    Bytes:    3966
    Version:  00000.0000000002
    DBA Array[1]:
      0x0100022d
end_of_block_dump

12.2 버전에서 동일한 테스트를 진행해보자. CLOB 칼럼의 길이를 각각 1984, 1985로 변경했다.

-- 2-1
DROP TABLE t1 PURGE;

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

LOB 세그먼트를 조회해보면 c2 칼럼은 128K, c3 칼럼은 96M를 사용하고 있는 것을 확인할 수 있다. BASIC FILE과 SECURE FILE의 차이로 인한 결과다. 

-- 2-2
SELECT a.column_name, a.segment_name, a.securefile, b.bytes, b.blocks
  FROM user_lobs a
     , user_segments b
 WHERE a.table_name = 'T1'
   AND b.segment_name = a.segment_name;

COLUMN_NAME SEGMENT_NAME              SECUREFILE     BYTES BLOCKS
----------- ------------------------- ---------- --------- ------
C2          SYS_LOB0000281769C00002$$ YES           131072     16
C3          SYS_LOB0000281769C00003$$ YES        100859904  12312

2 행이 선택되었습니다.

데이터 블록 덤프를 확인해보면 11.2 버전과 유사한 내용을 확인할 수 있다. c2 칼럼이 3999바이트를 사용했으므로 SECURE FILE은 36바이트가 아닌 31바이트의 부가 정보를 저장하는 것으로 보인다.   

-- 2-3
ALTER SYSTEM DUMP DATAFILE 7 BLOCK 563;

block_row_dump:
tab 0, row 0, @0xfb1
tl: 4047 fb: --H-FL-- lb: 0x0  cc: 3
col  0: [ 2]  c1 02
col  1: [3999]
 00 70 00 01 02 0c 80 80 00 02 00 00 00 01 00 00 01 4d 98 65 0f 8b 48 90 0f
...
col  2: [38]
 00 70 00 01 02 0c 80 80 00 02 00 00 00 01 00 00 01 4d 98 66 00 12 40 90 00
 0c 21 00 0f 82 01 00 01 01 c0 02 1b 01
end_of_block_dump

CLOB 타입의 저장 방식은 [ENABLE | DISABLE] STORAGE IN ROW 옵션을 통해 제어할 수 있다. 아래 쿼리는 c2 칼럼에 DISABLE STORAGE IN ROW 옵션을 사용했다.

-- 3-1
DROP TABLE t1 PURGE;

CREATE TABLE t1
LOB (c2) STORE AS (DISABLE STORAGE IN ROW)
LOB (c3) STORE AS (ENABLE  STORAGE IN ROW)
AS
SELECT ROWNUM AS c1
     , TO_CLOB (LPAD ('X', 1984, 'X')) AS c2
     , TO_CLOB (LPAD ('X', 1985, 'X')) AS c3
  FROM XMLTABLE ('1 to 10000');

LOB 세그먼트를 조회해보면 c2, c3 칼럼은 모두 96M를 사용하고 있는 것을 확인할 수 있다.

-- 3-2
SELECT a.column_name, a.segment_name, a.securefile, b.bytes, b.blocks
  FROM user_lobs a
     , user_segments b
 WHERE a.table_name = 'T1'
   AND b.segment_name = a.segment_name;

COLUMN_NAME SEGMENT_NAME              SECUREFILE     BYTES BLOCKS
----------- ------------------------- ---------- --------- ------
C2          SYS_LOB0000282050C00002$$ YES        100859904  12312
C3          SYS_LOB0000282050C00003$$ YES        100859904  12312

2 행이 선택되었습니다.

CLOB 데이터가 행에 저장되면 부가적인 I/O가 발생하지 않으므로 쿼리의 성능이 향상될 수 있지만, 테이블 세그먼트 크기가 커지므로 CLOB 값을 조회하지 않을 경우 쿼리의 성능이 저하될 수도 있다. 조회 유형에 따라 적절한 옵션을 사용해야 할 필요가 있다.

1. Byte Length가 2로, 4000바이트 미만의 문자열을 CLOB에 저장하면 공간 낭비가 발생하는 것을 알 수 있다. [본문으로]