Oracle 23c에 LOB 세그먼트명을 변경할 수 있는 기능이 추가되었습니다.
To rename an existing LOB segment users perform an operation such as ALTER TABLE ... MOVE, which could perform slowly since the operation physically moves the LOB data as part of the renaming. This enhancement improves the performance of renaming a LOB segment, at the table, partition and subpartition level by eliminating the physical movement of the LOB data.
테스트를 위해 아래와 같이 테이블을 생성하고, prc_rename_partition 프로시저로 파티션명을 변경하겠습니다.
-- 1-1
DROP TABLE t1 PURGE;
CREATE TABLE t1 (c1 NUMBER, c2 NUMBER, c3 CLOB)
PARTITION BY HASH (c1) SUBPARTITION BY HASH (c2) SUBPARTITIONS 2 PARTITIONS 2;
-- 1-2
EXEC prc_rename_partition ('TUNA', 'T1')
LOB 세그먼트는 기본적으로 접두어가 SYS_LOB인 세그먼트명으로 생성됩니다.
-- 2-1
SELECT column_name, segment_name, index_name
FROM user_lobs
WHERE table_name = 'T1';
COLUMN_NAME SEGMENT_NAME INDEX_NAME
----------- ------------------------- ------------------------
C3 SYS_LOB0000161657C00003$$ SYS_IL0000161657C00003$$
1개의 행이 선택되었습니다.
-- 2-2
SELECT column_name, lob_name, partition_name, lob_partition_name, lob_indpart_name
FROM user_lob_partitions
WHERE table_name = 'T1';
COLUMN_NAME LOB_NAME PARTITION_NAME LOB_PARTITION_NAME LOB_INDPART_NAME
----------- ------------------------- -------------- ------------------ ----------------
C3 SYS_LOB0000161657C00003$$ P001 SYS_LOB_P2631 SYS_IL_P2641
C3 SYS_LOB0000161657C00003$$ P002 SYS_LOB_P2634 SYS_IL_P2642
2 행이 선택되었습니다.
-- 2-3
SELECT column_name, lob_name, lob_partition_name, subpartition_name, lob_subpartition_name, lob_indsubpart_name
FROM user_lob_subpartitions
WHERE table_name = 'T1';
COLUMN_NAME LOB_NAME LOB_PARTITION_NAME SUBPARTITION_NAME LOB_SUBPARTITION_NAME LOB_INDSUBPART_NAME
----------- ------------------------- ------------------ ----------------- --------------------- -------------------
C3 SYS_LOB0000161657C00003$$ SYS_LOB_P2631 P001_SP001 SYS_LOB_SUBP2632 SYS_IL_SUBP2637
C3 SYS_LOB0000161657C00003$$ SYS_LOB_P2631 P001_SP002 SYS_LOB_SUBP2633 SYS_IL_SUBP2638
C3 SYS_LOB0000161657C00003$$ SYS_LOB_P2634 P002_SP001 SYS_LOB_SUBP2635 SYS_IL_SUBP2639
C3 SYS_LOB0000161657C00003$$ SYS_LOB_P2634 P002_SP002 SYS_LOB_SUBP2636 SYS_IL_SUBP2640
4 행이 선택되었습니다.
아래와 같이 LOB 세그먼트명을 변경하는 prc_rename_lob 프로시저를 생성하겠습니다.
-- 3
CREATE OR REPLACE PROCEDURE prc_rename_lob (
i_owner IN VARCHAR2
, i_table_name IN VARCHAR2
)
AUTHID CURRENT_USER
IS
BEGIN
FOR v IN (SELECT owner
, table_name
, column_name
, segment_name
, 'LOB_' || column_name AS segment_name_n
FROM dba_lobs
WHERE owner = i_owner
AND table_name = i_table_name
AND segment_name != 'LOB_' || column_name)
LOOP
EXECUTE IMMEDIATE 'ALTER TABLE ' || v.owner || '.' || v.table_name
|| ' RENAME LOB (' || v.column_name || ') ' || v.segment_name
|| ' TO ' || v.segment_name_n;
END LOOP;
FOR v IN (SELECT table_owner
, table_name
, column_name
, lob_partition_name
, 'LOB_' || column_name || '_' || partition_name AS lob_partition_name_n
FROM dba_lob_partitions
WHERE table_owner = i_owner
AND table_name = i_table_name
AND lob_partition_name != 'LOB_' || column_name || '_' || partition_name)
LOOP
EXECUTE IMMEDIATE 'ALTER TABLE ' || v.table_owner || '.' || v.table_name
|| ' RENAME LOB (' || v.column_name || ') PARTITION ' || v.lob_partition_name
|| ' TO ' || v.lob_partition_name_n;
END LOOP;
FOR v IN (SELECT table_owner
, table_name
, column_name
, lob_subpartition_name
, 'LOB_' || column_name || '_' || subpartition_name AS lob_subpartition_name_n
FROM dba_lob_subpartitions
WHERE table_owner = i_owner
AND table_name = i_table_name
AND lob_subpartition_name != 'LOB_' || column_name || '_' || subpartition_name)
LOOP
EXECUTE IMMEDIATE 'ALTER TABLE ' || v.table_owner || '.' || v.table_name
|| ' RENAME LOB (' || v.column_name || ') SUBPARTITION ' || v.lob_subpartition_name
|| ' TO ' || v.lob_subpartition_name_n;
END LOOP;
END;
/
t1 테이블의 LOB 세그먼트명을 변경하기 위해 prc_rename_lob 프로시저를 수행하겠습니다.
-- 4
EXEC prc_rename_lob ('TUNA', 'T1')
아래는 t1 테이블의 LOB 세그먼트명을 변경한 결과입니다.
-- 5-1
SELECT column_name, segment_name, index_name
FROM user_lobs
WHERE table_name = 'T1';
COLUMN_NAME SEGMENT_NAME INDEX_NAME
----------- ------------ ------------------------
C3 LOB_C3 SYS_IL0000161657C00003$$
1개의 행이 선택되었습니다.
-- 5-2
SELECT column_name, lob_name, partition_name, lob_partition_name, lob_indpart_name
FROM user_lob_partitions
WHERE table_name = 'T1';
COLUMN_NAME LOB_NAME PARTITION_NAME LOB_PARTITION_NAME LOB_INDPART_NAME
----------- -------- -------------- ------------------ ----------------
C3 LOB_C3 P001 LOB_C3_P001 SYS_IL_P2641
C3 LOB_C3 P002 LOB_C3_P002 SYS_IL_P2642
2 행이 선택되었습니다.
-- 5-3
SELECT column_name, lob_name, lob_partition_name, subpartition_name, lob_subpartition_name, lob_indsubpart_name
FROM user_lob_subpartitions
WHERE table_name = 'T1';
COLUMN_NAME LOB_NAME LOB_PARTITION_NAME SUBPARTITION_NAME LOB_SUBPARTITION_NAME LOB_INDSUBPART_NAME
----------- -------- ------------------ ----------------- --------------------- -------------------
C3 LOB_C3 LOB_C3_P001 P001_SP001 LOB_C3_P001_SP001 SYS_IL_SUBP2637
C3 LOB_C3 LOB_C3_P001 P001_SP002 LOB_C3_P001_SP002 SYS_IL_SUBP2638
C3 LOB_C3 LOB_C3_P002 P002_SP002 LOB_C3_P002_SP002 SYS_IL_SUBP2640
C3 LOB_C3 LOB_C3_P002 P002_SP001 LOB_C3_P002_SP001 SYS_IL_SUBP2639
4 행이 선택되었습니다.
관련 링크