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
- Clustered Table에는 LOB을 사용할 수 없다.
- LONG 컬럼은 Replication이 되지 않기 때문에, Replication전에 LOB으로 Migration시켜야 한다. 만약 Replication후에 Migration을 한 경우에는 Replica에 대해서 따로 LOB으로 Migration시켜 주어야 한다.
- 다음 두 가지 Trigger의 경우 LONG타입만 지원하고, LOB은 지원하지 않는다.
- UPDATE OF clause
- LOB을 가진 뷰에 INSTEAD OF trigger를 걸었을 경우, LOB컬럼에 INSERT/UPDATE 할 수 없다.
- 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와 같이 사용되어 질 수 있다.