[DB Admin] Oracle 의 LOB 이란 ?


LOB Enhancement

Oracle 8.0버전부터 LOB이라는 데이터타입이 소개되었고 여러 가지 기능때문에

LONG 데이터타입보다는 LOB을 사용하도록 권장하고 있다. LONG과 LOB을 간단히 비교해 보면 다음과 같다.

LOB                                                                LONG

4 Gigabyte까지                                                2 Gigabyte
Random piecewise access                      Sequential access
Table당 여러 개 column가능                     Single Long
따로 Storage지정 가능                                        Table내에 저장

Aggregate data type attributes

Oracle9i에서 LOB을 사용하기 위해 추가된 기능은 다음과 같다.

  • Migrating LONG to LOB
  • LOB enhancements in SQL and PL/SQL
  • LONG API for LOBs

이 장에서는 OCI부분을 제외한 나머지 각각의 간단한 예제들을 테스트 해 보았다.

 

Oracle9i LONG to LOB Migration

Oracle8i에서는 CREATE TABLE AS, INSERT INTO AS구문에 TO_LOB()을 이용하여, LONG data를 LOB으로 복사하였다. 이 때에는 모든 Constraint, Trigger, Index를 다시 만들어 주어야 했다.

 

— Oracle 8i에서의 Migration

CREATE TABLE Long_tab (id NUMBER, long_col LONG);

CREATE TABLE Lob_tab (id NUMBER, clob_col CLOB);

INSERT INTO Lob_tab SELECT id, TO_LOB(long_col) FROM long_tab;

DROP TABLE Long_tab;

CREATE VIEW Long_tab (id, long_col) AS SELECT * from Lob_tab;

Oracle9i에서는 ALTER TABLE …. MODIFY를 써서 LONG을 CLOB으로, LONG_RAW를 BLOB으로 바꿀 수 있다.
모든 Constraint가 유지가 되고, Default값을 지정하지 않을 경우에는 Long 컬럼의 Default값을 그대로 가지고 온다. Trigger의 경우는

아래 Limitation에서 언급할 두 가지 타입을 제외하고는 유효하다. LONG컬럼에 해당하는 Index는 Migration전에 Drop되어야 하고,

Migration후 다른 Index들은 모두 Rebuilt되어야 한다.  또한, LONG을 LOB으로 복사하는 것이므로 LONG과 LOB 데이터를 위한 두 배의 space가 필요하다.

Migration시 테이블이 LOGGING모드이면 테이블에 대한 redo log가 남고, LOB의 Storage 지정시 LOGGING모드였으면 바뀐 컬럼의 redo log가 남는다.

 

— Oracle 9i에서의 Migration

CREATE TABLE print_media
(PRODUCT_ID NUMBER(6) NOT NULL,
AD_ID NUMBER(6) NOT NULL,
AD_COMPOSITE BLOB,
AD_SOURCETEXT CLOB,
PRESS_RELEASE LONG );

ALTER TABLE print_media
MODIFY PRESS_RELEASE CLOB;

–Migration시 Log를 남기고 싶지 않을 때

ALTER TABLE Long_tab NOLOGGING;
ALTER TABLE Long_tab MODIFY ( long_col CLOB [default <default_val>]) LOB (long_col) STORE AS (… NOLOGGING…);
ALTER TABLE Long_tab MODIFY LOB long_col STORE AS (…LOGGING…);
ALTER TABLE Long_tab LOGGING;
 

 

SQL & PL/SQL Support for LOB Migration

Argument로 VARCHAR를 지원하는 Function이나 Operator는 CLOB도 허용한다. 마찬가지로, RAW를 지원하면 BLOB도 가능하다.

LONG, LONG RAW 타입을 허용하는 SUBSTR, INSTR과 같은 Predefined function들도 CLOB, BLOB을 쓸 수 있다.

다음 예제는 SUBSTR function의 argument에 CLOB컬럼을 사용한 경우이다.

SELECT SUBSTR(PRESS_RELEASE, 10, 20)
FROM print_media;
 

Implicit Assignment and Parameter Passing to LOBs

Assignment시 LOB을 VARCHAR2나 RAW로 또는 반대로 VARCHAR2, RAW 가 LOB으로 Implicit conversion이 가능하다.

LONG컬럼의 %TYPE으로 선언된 변수는, 그 컬럼이 LOB으로 바뀌면 자동으로 변수도 LOB 데이터를 가질 수 있다. LONG컬럼 %TYPE의 Parameter의 경우도 LOB으로 바뀐 후 LOB, VARCHAR, RAW값을 갖는다.

SELECT INTO구문에서 CLOB 컬럼을 VARCHAR로, BLOB 컬럼 값을 RAW 변수로 저장할 수 있다. 모든 Built-in function이나 operator에 VARCHAR나 RAW외에 CLOB, BLOB데이터를 허용한다.

CREATE TABLE lobtab( lcol LONG);

DECLARE
var1 VARCHAR2(100);
var2 lobtab.lcol%type;

BEGIN
SELECT * INTO var2 FROM lobtab;
var1 := var2;     — varchar2 = long => varchar2 = clob
var2 := var1;     — long = varchar2 => clob = varchar2

END;
/

— 다음과 같이 clob으로 변경 후에도 앞의 anonymous block 컴파일됨
ALTER TABLE lobtab MODIFY lcol CLOB;
 

CREATE TABLE t (clob_col CLOB, blob_col BLOB);
INSERT INTO t VALUES(‘abcdefg’, ‘aaaaaa’);
DECLARE
var_buf VARCHAR2(100);
clob_buf CLOB;
raw_buf RAW(100);
blob_buf BLOB;
BEGIN
SELECT * INTO clob_buf, blob_buf FROM t;
var_buf := clob_buf;
clob_buf:= var_buf;
raw_buf := blob_buf;
blob_buf := raw_buf;
END;
/

 

CREATE PROCEDURE FOO ( a IN t.clob_col%type) IS
Begin
dbms_output.put_line (a);
end;
/

CREATE PROCEDURE BAR (b IN VARCHAR2) IS
Begin
dbms_output.put_line (b);
end;
/
DECLARE
a VARCHAR2(100);
b t.clob_col%type;
BEGIN
a := ‘abc’;
SELECT clob_col into b from t;
FOO(a); — Actual parameter is VARCHAR2, formal parameter is CLOB
BAR(b); — Actual parameter is CLOB, formal parameter is VARCHAR2
END;
/

abc
abcdefg

 

 

Support for LOB Migration in OCI Interface

CLOB, BLOB컬럼을 INSERT/UPDATE하기 위해서 VARCHAR2, RAW버퍼를 4GB까지 사용할 수 있다.

LOB데이터를 SELECT문으로 바로 버퍼에 저장하기 위해서 CLOB을 VARCHAR2로, BLOB을 RAW로 정의할 수 있다.

INSERT/UPDATE가 single piece, piecewise, array mode에서 가능하다.

Note : OCI문서 참조

 

Other LOB Enhancements

첫째로, Partitioned Index Organized Tables(IOTs)에 LOB 컬럼을 생성할 수 있다.

CREATE TABLE Multimedia_tab (
CLIP_ID    INTEGER PRIMARY KEY,
CLIP_DATE  DATE,
STORY      CLOB,
FLSUB      NCLOB,
PHOTO      BFILE,
FRAME      BLOB,
SOUND      BLOB,

)
ORGANIZATION INDEX
TABLESPACE TBS_IDX
OVERFLOW
TABLESPACE TBS_OVF
LOB (FRAME, S0UND) STORE AS (TABLESPACE TBS_LOB)
PARTITION BY RANGE (CLIP_DATE)
(PARTITION Jan_Multimedia_tab VALUES LESS THAN (01-FEB-2000)
LOB (STORY) STORE AS (TABLESPACE TBS_LOB),
PARTITION Feb_Multimedia_tab VALUES LESS THAN (01-MAR-2000)
LOB (FLSUB) STORE AS (TABLESPACE TBS_LOB
ENABLE STORAGE IN ROW)
);
 

둘째로, LOB 컬럼에 Functional Indexes를 걸 수 있다. 다음예제는 length 함수를 사용한 예이다.

length는 원래 CHAR, VARCHAR2, NCHAR, NVARCHAR2를 사용하는 함수인데, 9i에서는 Implicit Conversion이 가능하기 때문에 LOB을 쓸 수 있는 것이다.

CREATE INDEX test_idx ON print_media(length(press_release));

SELECT length(press_release) FROM print_media ;
LENGTH(PRESS_RELEASE)
————————————-
25

 

Limitations

  1. Clustered Table에는 LOB을 사용할 수 없다.
  2. LONG 컬럼은 Replication이 되지 않기 때문에, Replication전에 LOB으로 Migration시켜야 한다. 만약 Replication후에 Migration을 한 경우에는 Replica에 대해서 따로 LOB으로 Migration시켜 주어야 한다.
  3. 다음 두 가지 Trigger의 경우 LONG타입만 지원하고, LOB은 지원하지 않는다.
  • UPDATE OF clause
  • LOB을 가진 뷰에 INSTEAD OF trigger를 걸었을 경우, LOB컬럼에 INSERT/UPDATE 할 수 없다.
  1. LONG컬럼을 LOB으로 바꾸기 전에 LONG 컬럼에 대한 Index를 Drop시켜야 한다. 그리고 그 외의 Index는 Migration후 Rebuilt되어야 한다.

 

활용 가이드 및 결론

 

Oracle9i LOB enhancement는 LONG에서 LOB으로의 migration을 더 용이하게 해 준다. 기존의 function에 LOB을 그대로 사용할 수 있고,

이미 작성한 코드의 LONG을 LOB으로 대치시킬 수도 있다. 특히, 9i에서는 LOB이 VARCHAR, RAW로

내부적으로 Conversion이 가능해 졌기 때문에, VARCHAR, RAW와 같이 사용되어 질 수 있다.


Comments

comments

haisins

오라클 DBA 박용석 입니다. haisins@gmail.com 으로 문의 주세요.

댓글 남기기

이메일은 공개되지 않습니다. 필수 입력창은 * 로 표시되어 있습니다