| LOB 타입 | 설명 | ||
|---|---|---|---|
| {code:none | borderStyle=solid}CLOB{code} | {code:none | borderStyle=none}문자 LOB, 대량 문자 정보 저장, 기본 캐릭터셋{code} |
| {code:none | borderStyle=solid}NCLOB{code} | {code:none | borderStyle=none}CLOB 과 같으나, 내셔널 캐릭터셋{code} |
| {code:none | borderStyle=solid}BLOB{code} | {code:none | borderStyle=none}이진 LOB, 대량 이진 정보 저장(이미지등){code} |
| {code:none | borderStyle=solid}BFILE{code} | {code:none | borderStyle=none}이진 파일 LOB, 파일에 대한 포인터, 읽기 모드{code} |
| Demo#1 (LOB) | |
|---|---|
| {code:sql | borderStyle=solid} SQL> CREATE TABLE T 2 ( ID INT PRIMARY KEY, 3 TXT CLOB 4 ) SEGMENT CREATION IMMEDIATE; |
Table created.
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE', 'T') FROM DUAL;
DBMS_METADATA.GET_DDL('TABLE', 'T')
-- 주요속성: TABLESPACE, STORAGE IN ROW, CHUNK 8192, RETENTION, NOCACHE, STORAGE 절
-- 멀티세그먼트 객체 형식
23:58:08 SCOTT@ORCL> @seg T
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME SIZE_MB
– LOBINDEX : LOB 데이터 찾기용 (마스터)
– LOBSEGMENT : LOB 데이터 저장용 (디테일)
– LOB컬럼 → LOBINDEX → LOBSEGMENT
|
* LOB 테이블스페이스
** 테이블/LOB의 테이블스페이스는 다를 수 있음 : 관리/성능상 분리
** LOB은 버퍼 캐시에 못올라가며 DIRECT READ/WRITE 함 (기본 설정)
||Demo#2 (IN ROW 절)||
|{code:sql|borderStyle=solid}
-- ENABLE STORAGE IN ROW : 4000 바이트 이내 LOB 데이터는 테이블 내 컬럼에 저장, 4000 바이트 초과 LOB 데이터는 LOBSEGMENT에 저장
-- DISABLE STORAGE IN ROW : LOB 데이터는 LOBSEGMENT에 저장
00:13:20 SCOTT@ORCL> CREATE TABLE T
00:13:21 2 ( ID INT PRIMARY KEY,
00:13:21 3 IN_ROW CLOB,
00:13:21 4 OUT_ROW CLOB
00:13:21 5 )
00:13:21 6 LOB (IN_ROW) STORE AS ( ENABLE STORAGE IN ROW)
00:13:21 7 LOB (OUT_ROW) STORE AS (DISABLE STORAGE IN ROW);
Table created.
Elapsed: 00:00:00.12
00:13:22 SCOTT@ORCL> INSERT INTO T
00:14:11 2 SELECT ROWNUM
00:14:11 3 , OWNER || ' ' || OBJECT_NAME || ' ' || OBJECT_TYPE || ' ' || STATUS
00:14:11 4 , OWNER || ' ' || OBJECT_NAME || ' ' || OBJECT_TYPE || ' ' || STATUS
00:14:11 5 FROM ALL_OBJECTS;
101434 rows created.
Elapsed: 00:00:52.44
00:15:04 SCOTT@ORCL> COMMIT;
Commit complete.
Elapsed: 00:00:00.00
00:23:16 SCOTT@ORCL> DECLARE
00:23:20 2 L_CNT NUMBER;
00:23:20 3 L_DATA VARCHAR2(32765);
00:23:20 4 BEGIN
00:23:20 5
00:23:20 6 SELECT COUNT(*)
00:23:20 7 INTO L_CNT
00:23:20 8 FROM T;
00:23:20 9
00:23:20 10 DBMS_MONITOR.SESSION_TRACE_ENABLE;
00:23:20 11 FOR I IN 1 .. L_CNT
00:23:20 12 LOOP
00:23:20 13 SELECT IN_ROW INTO L_DATA FROM T WHERE ID = I;
00:23:20 14 SELECT OUT_ROW INTO L_DATA FROM T WHERE ID = I;
00:23:20 15 END LOOP;
00:23:20 16 END;
00:23:21 17 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:24.62
-- TRACE/TKPROF
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 101434 0.07 1.37 0 0 0 0
Fetch 101434 0.04 1.48 0 304302 0 101434
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 202869 0.11 2.86 0 304302 0 101434
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 101434 0.07 1.22 0 0 0 0
Fetch 101434 13.32 7.70 101434 710038 0 101434
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 202869 13.39 8.92 101434 710038 0 101434
-- IN_ROW 에 비해서 OUT_ROW 는 LOBSEGMENT 접근(disk:101434) 및 LOBINDEX 접근(query:710038-304302) 자원을 더 필요로 함
00:30:48 SCOTT@ORCL> CREATE SEQUENCE S START WITH 200000;
Sequence created.
Elapsed: 00:00:00.02
00:31:32 SCOTT@ORCL> DECLARE
00:31:35 2 L_CNT NUMBER;
00:31:35 3 L_DATA VARCHAR2(32765);
00:31:35 4 BEGIN
00:31:35 5 DBMS_MONITOR.SESSION_TRACE_ENABLE;
00:31:35 6 FOR I IN 1 .. 100
00:31:35 7 LOOP
00:31:35 8 UPDATE T SET IN_ROW = TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') WHERE ID = I;
00:31:35 9 UPDATE T SET OUT_ROW = TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') WHERE ID = I;
00:31:35 10 INSERT INTO T (ID, IN_ROW) VALUES (S.NEXTVAL, 'Hello World');
00:31:35 11 INSERT INTO T (ID, OUT_ROW) VALUES (S.NEXTVAL, 'Hello World');
00:31:35 12 END LOOP;
00:31:35 13 END;
00:31:36 14 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.21
-- TRACE/TKPROF
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.02 0.01 0 200 206 100
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 101 0.02 0.01 0 200 206 100
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.05 0.07 0 1509 2252 100
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 101 0.05 0.07 0 1509 2252 100
INSERT INTO T (ID, IN_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.00 0.01 0 2 322 100
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 101 0.00 0.01 0 2 322 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.02 0.04 0 1007 1551 100
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 101 0.02 0.04 0 1007 1551 100
-- IN_ROW 에 비해서 OUT_ROW 는 읽기/쓰기 모두 자원을 더 필요로 함, ENABLE STORAGE IN ROW 추천
|
| Demo#3 (RETENTION 절) | |
|---|---|
| {code:sql | borderStyle=solid} – UNDO 안쓰고 읽기 일관성 제공 (LOBINDEX 는 UNDO 사용) -- 변경 정보를 LOBSEGMENT 자체 보관 : LOB 데이터가 수정되면 새로운 CHUNK 를 할당하고, 이전 CHUNK 를 남겨둠) |
00:57:11 SCOTT@ORCL> CREATE TABLE T
00:57:16 2 ( ID INT PRIMARY KEY,
00:57:16 3 TXT CLOB
00:57:16 4 ) LOB (TXT) STORE AS ( DISABLE STORAGE IN ROW );
Table created.
00:57:18 SCOTT@ORCL> INSERT INTO T VALUES (1, 'Hello World');
1 row created.
00:57:21 SCOTT@ORCL> COMMIT;
Commit complete.
00:57:24 SCOTT@ORCL> DECLARE
00:57:28 2 L_CLOB CLOB;
00:57:28 3
00:57:28 4 CURSOR C IS SELECT ID FROM T;
00:57:28 5 L_ID NUMBER;
00:57:28 6 BEGIN
00:57:28 7 SELECT TXT INTO L_CLOB FROM T;
00:57:28 8 OPEN C;
00:57:28 9
00:57:28 10 UPDATE T SET ID = 2, TXT = 'GoodBye';
00:57:28 11 COMMIT;
00:57:28 12
00:57:28 13 DBMS_OUTPUT.PUT_LINE( DBMS_LOB.SUBSTR(L_CLOB, 100, 1));
00:57:28 14 FETCH C INTO L_ID;
00:57:28 15 DBMS_OUTPUT.PUT_LINE( 'ID = ' || L_ID );
00:57:28 16 CLOSE C;
00:57:28 17 END;
00:57:28 18 /
Hello World
ID = 1
PL/SQL procedure successfully completed.
00:57:29 SCOTT@ORCL> SELECT * FROM T;
ID TXT
---
-- 커서 C의 읽기 일관성 : UNDO SEGMENT
– LOB의 읽기 일관성 : LOBSEGMENT
– RETENTION : UNDO_RETENTION 설정에 맞추어 변경된 LOB 데이터를 LOBSEGMENT에 보유 하도록 함 (LOB SEGMENT 가 자꾸 증가 한 원인이 혹시...)
– PCTVERSION : 읽기 일관성을 위해 사용할 LOBSEGMENT 공간 크기를 지정 (기본값 : LOBSEGMENT 크기의 10%)
|
* CACHE 절
** NOCACHE(기본값), CACHE(읽기쓰기캐시), CACHE READS(읽기캐시)
** ALTER TABLE tabname MODIFY LOB (lobname) (CACHE | NOCACHE);
* LOB STORAGE 절
** LOBSEGMENT 와 LOBINDEX 에 동일 하게 적용
||Demo#4 (SECUREFILE)||
|{code:sql|borderStyle=solid}
23:11:39 SCOTT@ORCL> CREATE TABLE T1BF
23:12:16 2 (
23:12:16 3 C1 NUMBER,
23:12:16 4 C2 CLOB
23:12:16 5 ) TABLESPACE TSUSER01 NOLOGGING LOB (C2) STORE AS BASICFILE;
Table created.
Elapsed: 00:00:00.17
23:12:16 SCOTT@ORCL> CREATE TABLE T1SF
23:12:20 2 (
23:12:20 3 C1 NUMBER,
23:12:20 4 C2 CLOB
23:12:20 5 ) TABLESPACE TSUSER01 NOLOGGING LOB (C2) STORE AS SECUREFILE;
Table created.
Elapsed: 00:00:00.10
23:12:21 SCOTT@ORCL> CREATE TABLE T1SF_CM
(23:12:25 2
23:12:25 3 C1 NUMBER,
23:12:25 4 C2 CLOB
23:12:25 5 ) TABLESPACE TSUSER01 NOLOGGING LOB (C2) STORE AS SECUREFILE CM (COMPRESS MEDIUM);
Table created.
Elapsed: 00:00:00.02
23:12:26 SCOTT@ORCL> CREATE TABLE T1SF_CH
23:12:29 2 (
23:12:29 3 C1 NUMBER,
23:12:29 4 C2 CLOB
23:12:29 5 ) TABLESPACE TSUSER01 NOLOGGING LOB (C2) STORE AS SECUREFILE CH (COMPRESS HIGH);
Table created.
Elapsed: 00:00:00.01
23:12:30 SCOTT@ORCL> CREATE TABLE T1SF_DD
23:12:34 2 (
23:12:34 3 C1 NUMBER,
23:12:34 4 C2 CLOB
23:12:34 5 ) TABLESPACE TSUSER01 NOLOGGING LOB (C2) STORE AS SECUREFILE DD (DEDUPLICATE);
Table created.
Elapsed: 00:00:00.01
23:16:30 SCOTT@ORCL> INSERT /*+ APPEND NOLOGGING */ INTO T1BF SELECT C1, C2 FROM SAMPLE;
11184 rows created.
Elapsed: 00:00:03.00
23:16:37 SCOTT@ORCL> COMMIT;
Commit complete.
Elapsed: 00:00:00.00
23:17:21 SCOTT@ORCL> INSERT /*+ APPEND NOLOGGING */ INTO T1SF SELECT * FROM T1BF UNION ALL SELECT * FROM T1BF;
22368 rows created.
Elapsed: 00:00:17.32
23:18:10 SCOTT@ORCL> INSERT /*+ APPEND NOLOGGING */ INTO T1SF_CM SELECT * FROM T1BF UNION ALL SELECT * FROM T1BF;
22368 rows created.
Elapsed: 00:00:06.37
23:18:44 SCOTT@ORCL> INSERT /*+ APPEND NOLOGGING */ INTO T1SF_CH SELECT * FROM T1BF UNION ALL SELECT * FROM T1BF;
22368 rows created.
Elapsed: 00:00:08.55
23:18:54 SCOTT@ORCL> INSERT /*+ APPEND NOLOGGING */ INTO T1SF_DD SELECT * FROM T1BF UNION ALL SELECT * FROM T1BF;
22368 rows created.
Elapsed: 00:00:21.86
23:19:17 SCOTT@ORCL> INSERT /*+ APPEND NOLOGGING */ INTO T1BF SELECT * FROM T1BF;
11184 rows created.
Elapsed: 00:00:02.83
23:19:21 SCOTT@ORCL> COMMIT;
Commit complete.
23:21:48 SCOTT@ORCL> @seg T1BF
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME SIZE_MB
---------- --------------- ---------------- --------------- --------------- -------
SCOTT T1BF TABLE TSUSER01 21
SCOTT SYS_IL000019428 LOBINDEX TSUSER01 0
7C00002$$
SCOTT SYS_LOB00001942 LOBSEGMENT TSUSER01 200
87C00002$$
23:22:00 SCOTT@ORCL> @seg T1SF
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME SIZE_MB
---------- --------------- ---------------- --------------- --------------- -------
SCOTT T1SF TABLE TSUSER01 21
SCOTT SYS_IL000019429 LOBINDEX TSUSER01 0
0C00002$$
SCOTT SYS_LOB00001942 LOBSEGMENT TSUSER01 224
90C00002$$
23:22:04 SCOTT@ORCL> @seg T1SF_CM
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME SIZE_MB
---------- --------------- ---------------- --------------- --------------- -------
SCOTT T1SF_CM TABLE TSUSER01 19
SCOTT CM LOBSEGMENT TSUSER01 4
SCOTT SYS_IL000019429 LOBINDEX TSUSER01 0
3C00002$$
23:22:07 SCOTT@ORCL> @seg T1SF_CH
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME SIZE_MB
---------- --------------- ---------------- --------------- --------------- -------
SCOTT T1SF_CH TABLE TSUSER01 15
SCOTT CH LOBSEGMENT TSUSER01 4
SCOTT SYS_IL000019429 LOBINDEX TSUSER01 0
6C00002$$
23:22:12 SCOTT@ORCL> @seg T1SF_DD
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME SIZE_MB
---------- --------------- ---------------- --------------- --------------- -------
SCOTT T1SF_DD TABLE TSUSER01 21
SCOTT DD LOBSEGMENT TSUSER01 113
SCOTT SYS_IL000019429 LOBINDEX TSUSER01 3
9C00002$$
|
| Demo#5 (BFILE) | |
|---|---|
| {code:sql | borderStyle=solid} – OS 파일 포인터, 파일 읽기 모드 접근 가능, 읽기 일관성 없음 – SIZE : DIRECTORY 객체 이름, 파일 이름 + 오버헤드(20 바이트) SQL> CREATE TABLE T 2 ( ID INT PRIMARY KEY, 3 OS_FILE BFILE 4 ); |
Table created.
SQL> CREATE OR REPLACE DIRECTORY MY_DIR AS '/tmp/';
Directory created.
SQL> INSERT INTO T VALUES (1, BFILENAME('MY_DIR', 'nofile'));
1 row 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
No such file or directory
ORA-06512: at "SYS.DBMS_LOB", line 850
SQL> UPDATE T SET OS_FILE = BFILENAME('MY_DIR', 'testfile');
1 row updated.
SQL> SELECT DBMS_LOB.GETLENGTH(OS_FILE) FROM T;
DBMS_LOB.GETLENGTH(OS_FILE)
SQL> !du -sk /tmp/testfile
601268 /tmp/testfile
|