오라클 19c varchar2 컬럼에서 clob 으로 변경 가이드

OS 환경 : Oracle Linux 7.6 (64bit)

 

DB 환경 : Oracle Database 19.3.0.0

 

방법 : 오라클 19c varchar2 컬럼에서 clob 으로 변경 방법

오라클 db 환경에서 varchar2 타입 컬럼을 clob 로 변경하는 방법을 설명함
11g 이전 기본적으로는 varchar2 타입은 4000이 최대값이어서 clob 타입으로 변경하는 경우가 가끔 있음
*참고로 12c 이상부터는 varchar2 타입의 최대 값이 MAX_STRING_SIZE 파라미터를 수정하면 32767까지 늘어남
(https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/MAX_STRING_SIZE.html)

 

 

테스트
방법1. 컬럼 추가 후 컬럼 rename
방법2. 테이블 생성 후 rename

 

 

테스트
방법1. 컬럼 추가 후 컬럼 rename
샘플 테이블 생성

1 2 3 4 5 6 7 8

SQL> drop table sample_t purge; CREATE TABLE sample_t ( id1 NUMBER, id2 NUMBER, name VARCHAR2(10), date1 VARCHAR2(8),  date2 VARCHAR2(8), date3 DATE, phone VARCHAR2(13), price NUMBER,  extra1 VARCHAR2(1000), extra2 VARCHAR2(1000), extra3 VARCHAR2(1000), extra4 VARCHAR2(4000) );

 

 

샘플 데이터 삽입

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

SQL> set timing on DECLARE   TYPE tbl_ins IS TABLE OF sample_t%ROWTYPE INDEX BY BINARY_INTEGER;   w_ins tbl_ins; BEGIN   FOR i IN 1..150000 LOOP      w_ins(i).id1 := i;     w_ins(i).id2 := i||ceil(dbms_random.value(1, 10000000));     w_ins(i).name := dbms_random.string('x', 5);     w_ins(i).date1 := round(dbms_random.value(2010, 2021))||to_char(round(dbms_random.value(1, 12)), 'FM09')||to_char(round(dbms_random.value(1, 28)), 'FM09');     w_ins(i).date2 := '2021'||to_char(round(dbms_random.value(1, 12)), 'FM09')||to_char(round(dbms_random.value(1, 28)), 'FM09');     w_ins(i).date3 := to_date(round(dbms_random.value(2010, 2021))||'-'||round(dbms_random.value(1, 12))||'-'||round(dbms_random.value(1, 28))||' '||round(dbms_random.value(1, 23))||':'||round(dbms_random.value(0, 59))||':'||round(dbms_random.value(0, 59)), 'YYYY-MM-DD HH24:MI:SS');     w_ins(i).phone := '010-'||ceil(dbms_random.value(1000, 9999))||'-'||ceil(dbms_random.value(1000, 9999));     w_ins(i).price := ceil(dbms_random.value(1, 10)) * 1000;     w_ins(i).extra1 := dbms_random.string('a', 1000);     w_ins(i).extra2 := dbms_random.string('a', 1000);     w_ins(i).extra3 := dbms_random.string('a', 1000);     w_ins(i).extra4 := dbms_random.string('a', 4000);   END LOOP;      FORALL i IN 1..150000      INSERT INTO sample_t VALUES w_ins(i);      COMMIT; END; /   PL/SQL procedure successfully completed. Elapsed: 00:08:40.36

 

 

인덱스 생성

1 2 3 4

SQL> create index idx_sample_t_id1_name on sample_t (id1, name); create index idx_sample_t_dates on sample_t (date1, date2, date3); create index idx_sample_t_extras on sample_t (extra4, extra1, extra2);

 

 

용량 확인

1 2 3 4 5 6 7 8 9 10 11 12 13

SQL> set lines 200 pages 1000 col segment_name for a30 select segment_name, blocks, bytes/1024/1024 mb  from dba_segments  where segment_name like '%SAMPLE_T%';   SEGMENT_NAME                       BLOCKS         MB ------------------------------ ---------- ---------- IDX_SAMPLE_T_ID1_NAME                 512          4 IDX_SAMPLE_T_DATES                    896          7 IDX_SAMPLE_T_EXTRAS                155648       1216 SAMPLE_T                           155648       1216

 

 

인덱스 상태 확인

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37

SQL> set lines 200 pages 1000 col owner for a15 col index_type for a10 col index_name for a30 col table_name for a30 col columns for a50 SELECT     ind.owner AS "OWNER",     ind.table_name AS "TABLE_NAME",     ind.index_name AS "INDEX_NAME",     ind.index_type AS "INDEX_TYPE",     LISTAGG(ind_col.column_name, ', ') WITHIN GROUP (ORDER BY ind_col.column_position) AS "COLUMNS", ind.status FROM     dba_indexes ind LEFT OUTER JOIN     dba_ind_columns ind_col     ON ind.owner = ind_col.index_owner     AND ind.index_name = ind_col.index_name WHERE     ind.table_name = 'SAMPLE_T' GROUP BY     ind.owner,     ind.index_name,     ind.index_type,     ind.table_name, ind.status ORDER BY     ind.owner,     ind.index_name;   OWNER           TABLE_NAME                     INDEX_NAME                     INDEX_TYPE COLUMNS STATUS --------------- ------------------------------ ------------------------------ ---------- ------------------------------ -------   IMSI            SAMPLE_T                       IDX_SAMPLE_T_DATES             NORMAL     DATE1, DATE2, DATE3 VALID IMSI            SAMPLE_T                       IDX_SAMPLE_T_EXTRAS            NORMAL     EXTRA4, EXTRA1, EXTRA2 VALID IMSI            SAMPLE_T                       IDX_SAMPLE_T_ID1_NAME          NORMAL     ID1, NAME VALID

 

 

컬럼 타입 확인

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

SQL> desc sample_t  Name                 Null?    Type  -------------------- -------- ----------------------  ID1                           NUMBER  ID2                           NUMBER  NAME                          VARCHAR2(10)  DATE1                         VARCHAR2(8)  DATE2                         VARCHAR2(8)  DATE3                         DATE  PHONE                         VARCHAR2(13)  PRICE                         NUMBER  EXTRA1                        VARCHAR2(1000)  EXTRA2                        VARCHAR2(1000)  EXTRA3                        VARCHAR2(1000)  EXTRA4                        VARCHAR2(4000)

EXTRA4 컬럼을 CLOB으로 변경 예정

 

 

update 시
set temp_clob1 = to_clob(extra4) 방식이 빠른지
set temp_clob2 = extra4 방식이 빠른지도 같이 비교해보기 위해 temp 컬럼을 2개 만듬

 

 

시간 확인용 timing 설정

1	SQL> set timing on

 

 

임시 컬럼 추가1

1 2 3

SQL> alter table sample_t add temp_clob1 clob;   Table altered.

 

 

임시 컬럼 추가2

1 2 3

SQL> alter table sample_t add temp_clob2 clob;   Table altered.

 

 

데이터 변환 및 복제1

1 2 3 4

SQL> update sample_t set temp_clob1 = to_clob(extra4);   150000 rows updated. Elapsed: 00:00:37.84

37초 소요됨

 

 

update 중 락 확인

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

SQL> set lines 200 pages 1000 col sid for 999999 col usn for a20 col object_name for a20 col subobject_name for a20 select sid, type, o.object_name, id1, id2, lmode, request, block, decode(block,1,'HOLDER',0,'WAITING','UNKNOWN') gubun , to_char(trunc(id1/power(2,16))) USN , bitand(id1, to_number('ffff', 'xxxx')) + 0 SLOT from v$lock v, dba_objects o where 1=1 and TYPE in ('TX','TM') and v.id1 = o.object_id(+) order by sid, type;       SID TY OBJECT_NAME                 ID1        ID2      LMODE    REQUEST      BLOCK GUBUN   USN                        SLOT ------- -- -------------------- ---------- ---------- ---------- ---------- ---------- ------- -------------------- ----------     391 TM SAMPLE_T                  68433          0          3          0          0 WAITING 1                          2897     391 TX                        13172755      29160          6          0          0 WAITING 201                          19

lmode 3 인 tm lock 이 걸리고, lmode 6인 tx lock 이 걸림
이 상태에서 다른 update 구문이 들어오면 해당 구문은 이 391 세션이 commit 또는 rollback 을 하기 전까지 대기하게됨

 

 

update 완료 후 커밋

1 2 3

SQL> commit;   Commit complete.

 

 

데이터 변환 및 복제2

1 2 3

SQL> update sample_t set temp_clob2 = extra4;   150000 rows updated.

33초 소요됨

 

 

update 완료 후 커밋

1 2 3

SQL> commit;   Commit complete.

 

 

기존 컬럼 삭제

1 2 3 4

SQL> alter table sample_t drop column extra4;   Table altered. Elapsed: 00:00:28.39

28초 소요됨

 

 

삭제 중 락 확인

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

SQL> set lines 200 pages 1000 col sid for 999999 col usn for a20 col object_name for a20 col subobject_name for a30 select sid, type, o.object_name, id1, id2, lmode, request, block, decode(block,1,'HOLDER',0,'WAITING','UNKNOWN') gubun , to_char(trunc(id1/power(2,16))) USN , bitand(id1, to_number('ffff', 'xxxx')) + 0 SLOT from v$lock v, dba_objects o where 1=1 and TYPE in ('TX','TM') and v.id1 = o.object_id(+) order by sid, type;       SID TY OBJECT_NAME                            ID1        ID2      LMODE    REQUEST      BLOCK GUBUN   USN                        SLOT ------- -- ------------------------------- ---------- ---------- ---------- ---------- ---------- ------- -------------------- ----------     391 TM WRI$_OPTSTAT_HISTHEAD_HISTORY        14861          0          3          0          0 WAITING 0                         14861     391 TM WRI$_OPTSTAT_HISTHEAD_HISTORY        14870          0          3          0          0 WAITING 0                         14870     391 TM SAMPLE_T                             68433          0          6          0          0 WAITING 1                          2897     391 TX                                   13107203      29337          6          0          0 WAITING 200                           3

SAMPLE_T 테이블에 대해 lmode 6 으로 tm, tx lock 이 걸림(WRI$~ 테이블은 컬럼 삭제로 인한 통계정보 관련 테이블로 보여 락에 대해선 따로 언급하지 않겠음)
이 상태에서 다른 update 구문이 들어오면 해당 구문은 이 391 세션이 commit 또는 rollback 을 하기 전까지 대기하게됨

 

 

컬럼 리네임

1 2 3 4

SQL> alter table sample_t rename column temp_clob1 to extra4;   Table altered. Elapsed: 00:00:00.02

1초 미만 소요됨

 

 

컬럼 확인

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

SQL> desc sample_t  Name                 Null?    Type  -------------------- -------- ----------------------  ID1                           NUMBER  ID2                           NUMBER  NAME                          VARCHAR2(10)  DATE1                         VARCHAR2(8)  DATE2                         VARCHAR2(8)  DATE3                         DATE  PHONE                         VARCHAR2(13)  PRICE                         NUMBER  EXTRA1                        VARCHAR2(1000)  EXTRA2                        VARCHAR2(1000)  EXTRA3                        VARCHAR2(1000)  EXTRA4                        CLOB  TEMP_CLOB2                    CLOB

EXTRA4 컬럼이 정상적으로 CLOB로 변경됨

 

 

lob 크기 확인

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

SQL> set lines 200 pages 1000  col owner for a20 col table_name for a20 select owner, table_name, sum(table_size) seg_mb, sum(lob_size) lob_mb from ( select a.owner, a.segment_name as table_name, a.bytes/1024/1024 as table_size,  (select sum(bytes)/1024/1024 from dba_segments where segment_name= b.segment_name) as lob_size from dba_segments a, (select owner, segment_name, table_name from dba_lobs) b where a.segment_name like '%SAMPLE_T%' and a.segment_name = b.table_name  and a.owner = b.owner)  group by owner, table_name;   OWNER                TABLE_NAME               SEG_MB     LOB_MB -------------------- -------------------- ---------- ---------- IMSI                 SAMPLE_T                   2432   2688.375

테이블 크기는 2432MB이고 LOB 크기는 2688MB임

 

 

인덱스 상태 확인

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38

SQL> set lines 200 pages 1000 col owner for a15 col index_type for a10 col index_name for a30 col table_name for a30 col columns for a50 SELECT     ind.owner AS "OWNER",     ind.table_name AS "TABLE_NAME",     ind.index_name AS "INDEX_NAME",     ind.index_type AS "INDEX_TYPE",     LISTAGG(ind_col.column_name, ', ') WITHIN GROUP (ORDER BY ind_col.column_position) AS "COLUMNS", ind.status FROM     dba_indexes ind LEFT OUTER JOIN     dba_ind_columns ind_col     ON ind.owner = ind_col.index_owner     AND ind.index_name = ind_col.index_name WHERE     ind.table_name = 'SAMPLE_T' GROUP BY     ind.owner,     ind.index_name,     ind.index_type,     ind.table_name, ind.status ORDER BY     ind.owner,     ind.index_name;   OWNER           TABLE_NAME                     INDEX_NAME                     INDEX_TYPE COLUMNS                 STATUS --------------- ------------------------------ ------------------------------ ---------- ----------------------- -------- IMSI            SAMPLE_T                       IDX_SAMPLE_T_DATES             NORMAL     DATE1, DATE2, DATE3     VALID IMSI            SAMPLE_T                       IDX_SAMPLE_T_ID1_NAME          NORMAL     ID1, NAME               VALID IMSI            SAMPLE_T                       SYS_IL0000067899C00012$$       LOB                                VALID IMSI            SAMPLE_T                       SYS_IL0000067899C00013$$       LOB                                VALID

extra4 컬럼이 있던 인덱스는 자동으로 삭제됨

 

 

extra4 컬럼 포함된 인덱스 재생성 시도

1 2 3 4 5

SQL> create index idx_sample_t_extras on sample_t (extra4, extra1, extra2); create index idx_sample_t_extras on sample_t (extra4, extra1, extra2)                                               * ERROR at line 1: ORA-02327: cannot create index on expression with datatype LOB

lob 타입의 컬럼이 존재하는 경우 인덱스 생성이 불가함

 

 

방법2. 테이블 생성 후 rename
기존 테이블 삭제 및 재생성

1 2 3 4 5 6 7 8

SQL> drop table sample_t purge; CREATE TABLE sample_t ( id1 NUMBER, id2 NUMBER, name VARCHAR2(10), date1 VARCHAR2(8),  date2 VARCHAR2(8), date3 DATE, phone VARCHAR2(13), price NUMBER,  extra1 VARCHAR2(1000), extra2 VARCHAR2(1000), extra3 VARCHAR2(1000), extra4 VARCHAR2(4000) );

 

 

샘플 데이터 삽입

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

SQL> set timing on DECLARE   TYPE tbl_ins IS TABLE OF sample_t%ROWTYPE INDEX BY BINARY_INTEGER;   w_ins tbl_ins; BEGIN   FOR i IN 1..150000 LOOP      w_ins(i).id1 := i;     w_ins(i).id2 := i||ceil(dbms_random.value(1, 10000000));     w_ins(i).name := dbms_random.string('x', 5);     w_ins(i).date1 := round(dbms_random.value(2010, 2021))||to_char(round(dbms_random.value(1, 12)), 'FM09')||to_char(round(dbms_random.value(1, 28)), 'FM09');     w_ins(i).date2 := '2021'||to_char(round(dbms_random.value(1, 12)), 'FM09')||to_char(round(dbms_random.value(1, 28)), 'FM09');     w_ins(i).date3 := to_date(round(dbms_random.value(2010, 2021))||'-'||round(dbms_random.value(1, 12))||'-'||round(dbms_random.value(1, 28))||' '||round(dbms_random.value(1, 23))||':'||round(dbms_random.value(0, 59))||':'||round(dbms_random.value(0, 59)), 'YYYY-MM-DD HH24:MI:SS');     w_ins(i).phone := '010-'||ceil(dbms_random.value(1000, 9999))||'-'||ceil(dbms_random.value(1000, 9999));     w_ins(i).price := ceil(dbms_random.value(1, 10)) * 1000;     w_ins(i).extra1 := dbms_random.string('a', 1000);     w_ins(i).extra2 := dbms_random.string('a', 1000);     w_ins(i).extra3 := dbms_random.string('a', 1000);     w_ins(i).extra4 := dbms_random.string('a', 4000);   END LOOP;      FORALL i IN 1..150000      INSERT INTO sample_t VALUES w_ins(i);      COMMIT; END; /   PL/SQL procedure successfully completed. Elapsed: 00:08:40.36

 

 

인덱스 생성

1 2 3 4

SQL> create index idx_sample_t_id1_name on sample_t (id1, name); create index idx_sample_t_dates on sample_t (date1, date2, date3); create index idx_sample_t_extras on sample_t (extra4, extra1, extra2);

 

 

용량 확인

1 2 3 4 5 6 7 8 9 10 11 12 13

SQL> set lines 200 pages 1000 col segment_name for a30 select segment_name, blocks, bytes/1024/1024 mb  from dba_segments  where segment_name like '%SAMPLE_T%';   SEGMENT_NAME                       BLOCKS         MB ------------------------------ ---------- ---------- IDX_SAMPLE_T_ID1_NAME                 512          4 IDX_SAMPLE_T_DATES                    896          7 IDX_SAMPLE_T_EXTRAS                155648       1216 SAMPLE_T                           155648       1216

 

 

인덱스 상태 확인

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37

SQL> set lines 200 pages 1000 col owner for a15 col index_type for a10 col index_name for a30 col table_name for a30 col columns for a50 SELECT     ind.owner AS "OWNER",     ind.table_name AS "TABLE_NAME",     ind.index_name AS "INDEX_NAME",     ind.index_type AS "INDEX_TYPE",     LISTAGG(ind_col.column_name, ', ') WITHIN GROUP (ORDER BY ind_col.column_position) AS "COLUMNS", ind.status FROM     dba_indexes ind LEFT OUTER JOIN     dba_ind_columns ind_col     ON ind.owner = ind_col.index_owner     AND ind.index_name = ind_col.index_name WHERE     ind.table_name = 'SAMPLE_T' GROUP BY     ind.owner,     ind.index_name,     ind.index_type,     ind.table_name, ind.status ORDER BY     ind.owner,     ind.index_name;   OWNER           TABLE_NAME                     INDEX_NAME                     INDEX_TYPE COLUMNS STATUS --------------- ------------------------------ ------------------------------ ---------- ------------------------------ -------   IMSI            SAMPLE_T                       IDX_SAMPLE_T_DATES             NORMAL     DATE1, DATE2, DATE3 VALID IMSI            SAMPLE_T                       IDX_SAMPLE_T_EXTRAS            NORMAL     EXTRA4, EXTRA1, EXTRA2 VALID IMSI            SAMPLE_T                       IDX_SAMPLE_T_ID1_NAME          NORMAL     ID1, NAME VALID

 

 

컬럼 타입 확인

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

SQL> desc sample_t  Name                 Null?    Type  -------------------- -------- ----------------------  ID1                           NUMBER  ID2                           NUMBER  NAME                          VARCHAR2(10)  DATE1                         VARCHAR2(8)  DATE2                         VARCHAR2(8)  DATE3                         DATE  PHONE                         VARCHAR2(13)  PRICE                         NUMBER  EXTRA1                        VARCHAR2(1000)  EXTRA2                        VARCHAR2(1000)  EXTRA3                        VARCHAR2(1000)  EXTRA4                        VARCHAR2(4000)

EXTRA4 컬럼을 CLOB으로 변경 예정

 

 

시간 확인용 timing 설정

1	SQL> set timing on

 

 

임시 테이블 생성

1 2 3 4 5 6 7

SQL>  CREATE TABLE sample_temp ( id1 NUMBER, id2 NUMBER, name VARCHAR2(10), date1 VARCHAR2(8),  date2 VARCHAR2(8), date3 DATE, phone VARCHAR2(13), price NUMBER,  extra1 VARCHAR2(1000), extra2 VARCHAR2(1000), extra3 VARCHAR2(1000), extra4 clob );

 

 

데이터 복제

1 2 3 4 5 6

SQL>  insert /*+ append */ into sample_temp select * from sample_t;   150000 rows created. Elapsed: 00:00:37.35

37초 소요됨

 

 

커밋

1 2 3

SQL> commit;   Commit complete.

 

 

이제 인덱스 작업을 해줘야하는데, 방식을 2가지로 구분할 수 있음
첫번째는 인덱스 이름을 동일하게 하려는 경우임
이 경우 기존 인덱스를 삭제하고 신규 테이블에 인덱스를 동일한 이름으로 생성 해줘야해서
기존 인덱스 삭제시점부터 기존 테이블에 들어오는 sql에 대해 성능 저하가 발생할수 있음
두번째는 인덱스 이름을 다르게 하는 경우임
이 경우 기존 인덱스를 삭제하지 않고 그대로 둔채로 신규 테이블에 신규 인덱스를 생성하는것임
이때는 기존 테이블에 들어오는 sql에는 영향이 없지만 마지막 절차인 테이블 rename 이후
기존 sql 중에 힌트로 인덱스 이름을 명시해둔 경우 영향이 갈 수 있음

 

 

2_1. 인덱스 이름을 동일하게 하는 경우
기존 인덱스 삭제
*이 시점부터 기존 테이블에 영향이 감

1 2 3 4

SQL> drop index idx_sample_t_id1_name; drop index idx_sample_t_dates; drop index idx_sample_t_extras;

 

 

새로운 테이블에 동일한 이름으로 인덱스 생성

1 2 3 4 5 6 7 8 9

SQL> create index idx_sample_t_id1_name on sample_temp (id1, name);   Index created. Elapsed: 00:00:00.85   SQL> create index idx_sample_t_dates on sample_temp (date1, date2, date3);   Index created. Elapsed: 00:00:00.29

각각 1초 미만 소요됨
참고로 clob 타입 컬럼은 인덱스 생성시 포함할수 없기 때문에 idx_sample_t_extras 인덱스는 생성하지 않음

 

 

2_2. 인덱스 이름을 다르게 하는 경우
새로운 테이블에 다른 이름으로 인덱스 생성

1 2 3

SQL> create index idx_sample_t_id1_name_new on sample_temp (id1, name); create index idx_sample_t_dates_new on sample_temp (date1, date2, date3);

참고로 clob 타입 컬럼은 인덱스 생성시 포함할수 없기 때문에 idx_sample_t_extras 인덱스는 생성하지 않음

 

 

인덱스 작업 끝(2_1 또는 2_2 이후 진행)

 

 

기존 테이블 백업 및 삭제

1 2 3 4

SQL> alter table sample_t rename to sample_t_backup;   Table altered. Elapsed: 00:00:00.03

 

 

신규 테이블 이름 변경

1 2 3 4

SQL> alter table sample_temp rename to sample_t;   Table altered. Elapsed: 00:00:00.01

 

 

테이블 구조 확인

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

SQL> desc sample_t  Name                 Null?    Type  -------------------- -------- ----------------------  ID1                           NUMBER  ID2                           NUMBER  NAME                          VARCHAR2(10)  DATE1                         VARCHAR2(8)  DATE2                         VARCHAR2(8)  DATE3                         DATE  PHONE                         VARCHAR2(13)  PRICE                         NUMBER  EXTRA1                        VARCHAR2(1000)  EXTRA2                        VARCHAR2(1000)  EXTRA3                        VARCHAR2(1000)  EXTRA4                        CLOB

EXTRA4 컬럼이 정상적으로 CLOB로 변경됨

 

 

lob 크기 확인

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

SQL> set lines 200 pages 1000  col owner for a20 col table_name for a20 select owner, table_name, sum(table_size) seg_mb, sum(lob_size) lob_mb from ( select a.owner, a.segment_name as table_name, a.bytes/1024/1024 as table_size,  (select sum(bytes)/1024/1024 from dba_segments where segment_name= b.segment_name) as lob_size from dba_segments a, (select owner, segment_name, table_name from dba_lobs) b where a.segment_name like '%SAMPLE_T%' and a.segment_name = b.table_name  and a.owner = b.owner)  group by owner, table_name;   OWNER                TABLE_NAME               SEG_MB     LOB_MB -------------------- -------------------- ---------- ---------- IMSI                 SAMPLE_T                    592   1344.125

테이블 크기는 592MB이고 LOB 크기는 1344MB임
컬럼 추가 삭제보다 훨씬 줄어듬

 

 

인덱스 상태 확인

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38

SQL> set lines 200 pages 1000 col owner for a15 col index_type for a10 col index_name for a30 col table_name for a30 col columns for a50 SELECT     ind.owner AS "OWNER",     ind.table_name AS "TABLE_NAME",     ind.index_name AS "INDEX_NAME",     ind.index_type AS "INDEX_TYPE",     LISTAGG(ind_col.column_name, ', ') WITHIN GROUP (ORDER BY ind_col.column_position) AS "COLUMNS", ind.status FROM     dba_indexes ind LEFT OUTER JOIN     dba_ind_columns ind_col     ON ind.owner = ind_col.index_owner     AND ind.index_name = ind_col.index_name WHERE     ind.table_name = 'SAMPLE_T' GROUP BY     ind.owner,     ind.index_name,     ind.index_type,     ind.table_name, ind.status ORDER BY     ind.owner,     ind.index_name;   OWNER           TABLE_NAME                     INDEX_NAME                     INDEX_TYPE COLUMNS                 STATUS --------------- ------------------------------ ------------------------------ ---------- ----------------------- -------- IMSI            SAMPLE_T                       IDX_SAMPLE_T_DATES             NORMAL     DATE1, DATE2, DATE3     VALID IMSI            SAMPLE_T                       IDX_SAMPLE_T_ID1_NAME          NORMAL     ID1, NAME               VALID IMSI            SAMPLE_T                       SYS_IL0000067899C00012$$       LOB                                VALID IMSI            SAMPLE_T                       SYS_IL0000067899C00013$$       LOB                                VALID

extra4 컬럼이 있던 인덱스는 자동으로 삭제됨

 

 

결론 :
오라클 db 환경에서 varchar2 타입 컬럼을 clob 로 변경하는 방법은 두가지가 존재함
1. 컬럼 추가 후 컬럼 rename
절차 요약 :
a. 기존 테이블에 임시 clob 컬럼을 추가
b. 데이터를 임시 clob 컬럼으로 변환 및 복제
c. 기존 varchar2 컬럼 삭제
d. 임시 clob 컬럼을 원래 컬럼 이름으로 변경
총 소요시간 : 37초(컬럼 업데이트) + 28초(컬럼 삭제) + 1초(컬럼 rename) = 65초
장점 : 테이블 재생성 불필요
단점 : 원본 테이블을 변경 하는것이기 때문에 롤백시 동일 작업 반복 필요, 컬럼 업데이트 및 삭제시 lock 발생
참고 : 컬럼 업데이트시 temp_clob1 = to_clob(extra4) 방식은 37초가 소요되었고, temp_clob2 = extra4 방식은 33초가 소요되었음, 시간차이가 얼마 나지 않으니 안전하게 to_clob 함수를 사용하는것이 좋아 보임

 

2. 테이블 생성 후 rename
절차 요약 :
a. 기존 테이블과 동일한 구조의 임시 테이블 생성 (varchar2 컬럼을 clob 컬럼으로 변경하여 생성)
b. 기존 테이블 데이터를 임시 테이블로 복제
c. 기존 테이블에 있는 인덱스 삭제 및 임시 테이블에 동일한 인덱스 생성 (또는 다른 이름으로 생성)
d. 기존 테이블 이름을 백업 테이블 이름으로 변경(또는 삭제)
e. 임시 테이블 이름을 원래 테이블 이름으로 변경
총 소요시간 : 37초(신규테이블에 insert) + 1초(인덱스 생성) + 1초(테이블 rename) = 39초
장점 : 신규 테이블을 생성 하는것이기 때문에 롤백이 용이함, 변경 작업 동안 기존 테이블 사용 가능, reorg 효과도 있음, lock 발생 없음
단점 : 신규 테이블에 insert 중 신규 데이터가 들어오는 경우 해당 데이터는 유실됨(ap 변경을 막아놓는다면 해당사항 없음), 인덱스명 변경시 sql 플랜 변경 가능성 존재, 인덱스명 미변경시 인덱스 삭제 및 재생성시 row수가 많을 경우 시간 소요됨

 

 

참조 : 

107570.1, 754542.1
https://javaoop.tistory.com/95
https://namjackson.tistory.com/14
https://positivemh.tistory.com/356
https://positivemh.tistory.com/895