전문가를 위한 오라클 데이터베이스 아키텍처 (2014년)
LOB 타입 0 0 86,717

by 구루비스터디 데이터타입 DATATYPE LOB 타입 BFILE [2018.09.27]


LOB 타입

오라클에서 네 가지 LOB타입을 지원
  • CLOB : 문자 LOB, XML또는 일반문장과 같은 문자 정보를 대량으로 저장
  • NCLOB: 문자 LOB, CLOB과 같으나 내셔널 케릭터 셋
  • BLOB : 이진 LOB, 워드, 이미지 이진정보를 저장
  • BFILE : 이진 파일 LOB, 파일에 대한 포인터 일기모드만 접근 가능


내부 LOB


SQL> DROP TABLE T;

Table dropped.

SQL>
SQL> CREATE TABLE T
  2  (
  3     ID INT PRIMARY KEY,
  4     TXT CLOB
  5  );

Table created.

SQL> SELECT DBMS_METADATA.GET_DLL('TABLE','T');
SELECT DBMS_METADATA.GET_DLL('TABLE','T')
                                        *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected


SQL> SELECT DBMS_METADATA.GET_DLL('TABLE','T') FROM DUAL;
SELECT DBMS_METADATA.GET_DLL('TABLE','T') FROM DUAL
       *
ERROR at line 1:
ORA-00904: "DBMS_METADATA"."GET_DLL": invalid identifier


SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','T') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','T')
--------------------------------------------------------------------------------


  'CREATE TABLE "BSHMAN"."T" 
   (	"ID" NUMBER(*,0), 
	"TXT" CLOB, 
	 PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE "USERS"  ENABLE
   ) SEGMENT CREATION DEFERRED 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE "USERS" 
 LOB ("TXT") STORE AS BASICFILE (
  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION 
  NOCACHE LOGGING )'

--LOB의 주요속성(테이블스페이스, STORAGE IN ROW, CHUNK 8192, RETENTION, NOCACHE, STORAGE 절)

-- 멀티 세크먼트 
SQL> SELECT SEGMENT_NAME, SEGMENT_TYPE
  2  FROM USER_SEGMENTS;

SEGMENT_NAME                    SEGMENT_TYPE
------------------------------  ------------------------------------
A                                 TABLE
BIN$V0XPJ2kWRXeXkMio4Pes/Q==$0    TABLE
SOURCE                            TABLE
DR$SOURCE_IDX$I                   TABLE
DR$SOURCE_IDX$R                   TABLE
SYS_IL0000075319C00006$$          LOBINDEX
SYS_IOT_TOP_75322                 INDEX
SYS_IL0000075324C00002$$          LOBINDEX
SYS_IOT_TOP_75327                 INDEX
DR$SOURCE_IDX$X                   INDEX
SYS_LOB0000075319C00006$$         LOBSEGMENT
SYS_LOB0000075324C00002$$         LOBSEGMENT

12 rows selected.

-- LOBINDEX : LOB을 찾기위해 사용
-- LOBSEGMENT : 실제 데이터가 저장되는 공간



LOB 테이블스페이스
  • 실제 테이블 데이터를 저장하는 테이블스페이스와 관리적인 측면과 성능적인 측면으로 다를 수 있다.
  • 버퍼 캐쉬에 캐쉬되지 않는다.
  • LOB 액세스는 디스크에서 DIRECT READ나 디스크에 DIRECT WRITE 등 물리적 I/O가 발생


IN ROW 절


SQL> DROP TABLE T;

Table dropped.

SQL>
SQL> CREATE TABLE T
  2  (
  3      ID INT PRIMARY KEY,
  4      IN_ROW CLOB,
  5      OUT_ROW CLOB
  6  )
  7  LOB (IN_ROW) STORE AS (ENABLE STORAGE IN ROW)
  8  LOB (OUT_ROW) STORE AS (DISABLE STORAGE IN ROW)
  9  ;

Table created.


SQL> DROP TABLE T;

Table dropped.

Elapsed: 00:00:00.37

-- 테이블에 길이가 4,000바이트 미만인 문자열 데이터를 입력
SQL> CREATE TABLE T
  2  (
  3      ID INT PRIMARY KEY,
  4      IN_ROW CLOB,
  5      OUT_ROW CLOB
  6  )
  7  LOB (IN_ROW) STORE AS (ENABLE STORAGE IN ROW)
  8  LOB (OUT_ROW) STORE AS (DISABLE STORAGE IN ROW);

Table created.

Elapsed: 00:00:00.12
SQL>
SQL> INSERT INTO T
  2   SELECT ROWNUM
  3              , OWNER || ' ' || OBJECT_NAME || ' ' || OBJECT_TYPE || ' ' || STATUS
  4              , OWNER || ' ' || OBJECT_NAME || ' ' || OBJECT_TYPE || ' ' || STATUS
  5   FROM ALL_OBJECTS;

71641 rows created.

Elapsed: 00:01:24.61
SQL> DECLARE
  2      L_CNT   NUMBER;
  3      L_DATA  VARCHAR2(32765);
  4    BEGIN
  5
  6      SELECT COUNT(*)
  7        INTO L_CNT
  8        FROM T;
  9
 10      DBMS_MONITOR.SESSION_TRACE_ENABLE;
 11      FOR I IN 1 .. L_CNT
 12      LOOP
 13        SELECT IN_ROW INTO L_DATA FROM T WHERE ID = I;
 14        SELECT OUT_ROW INTO L_DATA FROM T WHERE ID = I;
 15      END LOOP;
 16    END;
 17    /

PL/SQL procedure successfully completed.

Elapsed: 00:01:24.83

SELECT IN_ROW 
FROM
 T WHERE ID = :B1 


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  71638      1.57       1.53          0          0          0           0
Fetch    71638      1.21       1.36          0     214914          0       71638
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   143277      2.79       2.89          0     214914          0       71638


SELECT OUT_ROW 
FROM
 T WHERE ID = :B1 


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  71638      1.26       1.16          0          0          0           0
Fetch    71638     15.49      63.37      71638     501469          0       71638
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   143277     16.75      64.53      71638     501469          0       71638

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY INDEX ROWID T (cr=3 pr=0 pw=0 time=0 us cost=1 size=253 card=1)
      1   INDEX UNIQUE SCAN SYS_C0011335 (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 75339)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  direct path read                            71638        0.14         51.95
  asynch descriptor resize                    71638        0.00          0.14

-- IN_ROW 보다 OUT_ROW 더 많은 논리 I/O를 발생 

SQL> CREATE SEQUENCE S START WITH 200000;

Sequence created.

Elapsed: 00:00:00.06
SQL> DECLARE
  2    L_CNT NUMBER;
  3    L_DATA VARCHAR2(32765);
  4  BEGIN
  5    DBMS_MONITOR.SESSION_TRACE_ENABLE;
  6    FOR I IN 1 .. 100
  7    LOOP
  8      UPDATE T SET IN_ROW = TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') WHERE
ID = I;
  9      UPDATE T SET OUT_ROW = TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') WHERE
 ID = I;
 10       INSERT INTO T (ID, IN_ROW) VALUES (S.NEXTVAL, 'Hello World');
 11       INSERT INTO T (ID, OUT_ROW) VALUES (S.NEXTVAL, 'Hello World');
 12     END LOOP;
 13   END;
 14  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.29

UPDATE T SET IN_ROW = TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') 
WHERE
 ID = :B1 


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute    100      0.00       0.00          0        200        206         100
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      101      0.00       0.00          0        200        206         100

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  UPDATE  T (cr=2 pr=0 pw=0 time=0 us)
      1   INDEX UNIQUE SCAN SYS_C0011339 (cr=2 pr=0 pw=0 time=0 us cost=1 size=2015 card=1)(object id 75437)


UPDATE T SET OUT_ROW = TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') 
WHERE
 ID = :B1 


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute    100      0.03       0.13          0       1499       2244         100
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      101      0.03       0.13          0       1499       2244         100

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  UPDATE  T (cr=12 pr=0 pw=1 time=0 us)
      1   INDEX UNIQUE SCAN SYS_C0011339 (cr=2 pr=0 pw=0 time=0 us cost=1 size=253 card=1)(object id 75437)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  Disk file operations I/O                        1        0.00          0.00
  direct path write                              91        0.01          0.04
  asynch descriptor resize                      175        0.00          0.00
********************************************************************************

INSERT INTO T (ID, IN_ROW) 
VALUES
 (S.NEXTVAL, 'Hello World')


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute    100      0.01       0.00          0          2        321         100
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      101      0.03       0.01          0          2        321         100


INSERT INTO T (ID, OUT_ROW) 
VALUES
 (S.NEXTVAL, 'Hello World')


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute    100      0.01       0.07          0       1007       1531         100
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      101      0.01       0.07          0       1007       1531         100


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  direct path write                             100        0.00          0.04
  asynch descriptor resize                      192        0.00          0.00
********************************************************************************

-- 동일한 현상 발생 기본적으로 ENABLE STORAGE IN ROW를 사용하는것이 좋음


CHUNK
  • 일반적으로 가장 작은 할당 단위가 블록인데, LOB에서는 청크가 가장 작은 단위이다.
  • LOB인스턴스는 적어도 하나의 청크를 사용
  • LOB인스턴스당 청크의 수를 최소화할 때 주의(공간낭비를 최소화하여 적당한 크기지정)


RETENTION절

SQL> DROP TABLE T;

Table dropped.

SQL> CREATE TABLE T
  2  (
  3      ID INT PRIMARY KEY,
  4      TXT CLOB
  5  );

Table created.


SQL> INSERT INTO T VALUES(1,'HELLO WORLD');

1 row created.


SQL> COMMIT;

Commit complete.

SQL> DECLARE
  2      L_CLOB CLOB;
  3
  4      CURSOR C IS SELECT ID FROM T;
  5      L_ID NUMBER;
  6    BEGIN
  7      SELECT TXT INTO L_CLOB FROM T;
  8      OPEN C;
  9
 10      UPDATE T SET ID = 2, TXT = 'GoodBye';
 11      COMMIT;
 12
 13      DBMS_OUTPUT.PUT_LINE( DBMS_LOB.SUBSTR(L_CLOB, 100, 1));
 14      FETCH C INTO L_ID;
 15      DBMS_OUTPUT.PUT_LINE( 'ID = ' || L_ID );
 16      CLOSE C;
 17    END;
 18    /

PL/SQL procedure successfully completed.

SQL> SELECT * FROM T;

        ID TXT
----------  ----------------------------
         2 GOOBBYE

-- 커서 C 읽기 일관성 이미지 UNDO SEGMENT 사용
-- LOB의 읽기 일관성 이미지는 LOBSEGMENT 사용
-- RETENTION은 UNDO_RETENTION 설정에 따라 데이터베이스가 변경된 LOG 세그먼트 데이터를 LOG 세그먼트에 보유
-- LOB 세그먼트의 재사용 가능한 빈 공간이 계속적인 INSERT , UPDATE에 의해 즉시 재사용도는 것이 아니다("왜 LOB세그먼트는 자꾸 증가하는가?")
-- 보유기간이 만료되지 않았기 때문에 정보를 재적하면서 대량 삭제로 인해 LOB세그먼가 커지는 경향이 존재
-- RETENTION 옵션대신 LOG STORAGE절에 PCTVERSION을 사용
-- 다른세션이 LOB을 수정하는 동안 LOB을 자주 읽기에는 10% 기본값은 너무 작을 수 있다.
-- 그렇다면 언두 테이블스페이스를 늘리거나, 언두 RETENTION을 늘리거나, RBS 공간을 추가


CACHE절
  • NOCACHE 대신 CACHE나 CACHE READS를 선택가능
  • 기본값은 NOCACHE
  • CACHE READ는 LOB데이터를 디스크에서 버퍼에 캐시하지만, 쓰기는 캐시하지않고 디스크에 직접씀
  • CACHE는 읽기와 쓰기 모두 LOB 데이터를 캐시


문법
  • ALTER TABLE TABNAME MODIFY LOB(LOBNAME) (CACHE);
  • ALTER TABLE TABNAME MODIFY LOB(LOBNAME) (NOCACHE);


LOB STORAGE절
  • LOBSEGMENT 와 LOBINDEX 에 동일 하게 적용


BFILE


-- BFILE은 운영체제에서 파일의 포인터다
-- 읽기모드로 접근할 수 있다.
-- BFILE데이터는 읽기 일관성을 제공하지 않는다.

SQL> DROP TABLE T;

Table dropped.

SQL>
SQL> CREATE TABLE T
  2  (
  3      ID INT PRIMARY KEY,
  4      OS_FILE BFILE
  5  );

Table created.

SQL>
SQL> CREATE OR REPLACE DIRECTORY MY_DIR AS '/TMP';

Directory created.

SQL> INSERT INTO T VALUES(1, BFILENAME('MY_DIR', 'test.dbf'));

1 row created.

SQL>
SQL> SELECT DBMS_LOB.GETLENGTH(OS_FILE) FROM T;
SELECT DBMS_LOB.GETLENGTH(OS_FILE) FROM T
       *
ERROR at line 1:
ORA-22288: file or LOB operation GETLENGTH failed
?????????? ??? ?? ???????
ORA-06512: at "SYS.DBMS_LOB", line 787


SQL> COMMIT;

Commit complete.


SQL> UPDATE T
  2  SET OS_FILE = BFILENAME('my_dir','test.dbf');

1 row updated.

SQL> SELECT DBMS_LOB.GETLENGTH(OS_FILE) FROM T;
SELECT DBMS_LOB.GETLENGTH(OS_FILE) FROM T
       *
ERROR at line 1:
ORA-22285: non-existent directory or file for
GETLENGTH operation
ORA-06512: at "SYS.DBMS_LOB", line 787


SQL> CREATE OR REPLACE DIRECTORY "my_dir" AS '/tmp';

Directory created.

SQL> SELECT DBMS_LOB.GETLENGTH(OS_FILE) FROM T;
SELECT DBMS_LOB.GETLENGTH(OS_FILE) FROM T
       *
ERROR at line 1:
ORA-22288: file or LOB operation GETLENGTH failed
?????????? ??? ?? ???????
ORA-06512: at "SYS.DBMS_LOB", line 787

"데이터베이스 스터디모임" 에서 2014년에 "전문가를 위한 오라클 데이터베이스 아키텍처 " 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/4055

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입