Append 와 Index( Normal, Unique, PK ) 관계

 Index 와 Append 관계에 대한 문의가 있어, 예전에 정리했던 글이 있어 찾아봤다.

입맛에 맞는 내용이 없어, 예전 글을 참고해서 테스트 및 정리 

 

<< 결론 >>

1. PK 나 UK 가 있는 상태에선 UNDO 및 REDO 가 발생한다.

2. NORMAL INDEX 는 UNUSABLE 로 설정 후, 작업 하면 UNDO 는 1, REDO 도 최소화 가능

3. UNIQUE INDEX 를 UNUSABLE 로 하면 "ORA-26026" 에러가 발생 하고, DML 불가함 

4. 결론적으로 PK, UK INDEX / CONSTRAINT DROP + Normal Index Unsuable  + TABLE NOLOGING 후, APPEND 작업이 가장 효과적이다. 

5. (주의) APPEND 작업 시에, 다른 세션에서 DML 불가, 이행 이나 배치 작업만 가능하다. 

		Normal / Unique /PK Index 순서 (  Unusable 인덱스 )	Undo ( USED_UBLK )	Undo ( USED_UREC)	Redo
1	No Index	0 / 0 / 0	1	1	281,136
2	Normal Index	1 / 0 / 0	1,700	5,099	58,518,760
3	Normal Index Unusable	1(1) / 0 / 0	1	1	300,987
4	Unique Index Unusable	1(1) / 1(1) / 0
5	Unique Index Usable  + Normal Index Unusable	1(1) / 1(0) / 0	3,881	7,792	129,132,596
6	PK Index Usable + Unique Index Unusable + Normal Index Usable	1(1) / 1 (0 ) / 1(0)	8,886	17,801	296,275,316
7	Normal Index Unusable + Table Nologging	1(1) / 0 / 0	1	1	281,232

*** 4 Unique Index Unusable 설정 시,  DML 수행 불가 

 

-- 샘플 테이블 생성 
DROP TABLE  SOURCE_TABLE  ;
CREATE TABLE SOURCE_TABLE 
AS SELECT B.NUM, A.*
FROM  DBA_OBJECTS A, 
     ( SELECT (COLUMN_VALUE).GETNUMBERVAL() AS NUM FROM XMLTABLE('1 to 30')  )  B  ;

SELECT COUNT(*) FROM SOURCE_TABLE ;
-- 730,950

CREATE TABLE TARGET_TABLE  AS SELECT * FROM SOURCE_TABLE WHERE 1=2;

-- PK 를 위한 데이타 보정
UPDATE SOURCE_TABLE
SET    SUBOBJECT_NAME ='NO PARITION' -- NVL(SUBOBJECT_NAME,'NO PARTITION')
WHERE SUBOBJECT_NAME IS NULL  ;

COMMIT ;

UPDATE SOURCE_TABLE
SET    OBJECT_ID = 9999 --'NO OBJECT_ID' -- NVL(SUBOBJECT_NAME,'NO PARTITION')
WHERE OBJECT_ID IS NULL  ;

COMMIT ;

-- 테스트 세션 체크 ( CASE 별로 매번 새로 접속 후, 테스트 ) 
SELECT DISTINCT SID FROM V$MYSTAT ;
-- 37 

-- Check Undo 
SELECT s.inst_id, s.sid, s.serial#, s.status, t.used_ublk, t.used_urec 
FROM gv$session s, gv$transaction t, gv$process p
WHERE s.taddr = t.addr
and s.inst_id = t.inst_id
and s.paddr= p.addr
and s.INST_ID =p.INST_ID
and s.sid  = :v_sid
ORDER BY t.used_ublk desc, t.used_urec desC ;

-- Check Redo 
select  ss.sid, 'redo size:'||ss.value,    s.program,    s.module
from  v$statname     sn,v$sesstat     ss,v$session s
where ss.statistic#=sn.statistic# 
and   sn.name='redo size' 
and   s.sid=ss.sid 
and   s.sid = :v_sid 
and   ss.value>0
order by  ss.value;

CASE 1 ) 
DROP TABLE TARGET_TABLE PURGE ;
CREATE TABLE TARGET_TABLE  AS SELECT * FROM SOURCE_TABLE WHERE 1=2;
INSERT /*+ APPEND */ INTO  TARGET_TABLE  SELECT * FROM SOURCE_TABLE ;

CASE 2 )
일반 인덱스 추가 
DROP TABLE TARGET_TABLE PURGE ;
CREATE TABLE TARGET_TABLE  AS SELECT * FROM SOURCE_TABLE WHERE 1=2;
CREATE INDEX XAK01_TARGET_TABLE ON TARGET_TABLE ( NUM, OBJECT_ID ) ;
INSERT /*+ APPEND */ INTO  TARGET_TABLE  SELECT * FROM SOURCE_TABLE ;

CASE 3)
일반 인덱스 UNUSABLE 
DROP TABLE TARGET_TABLE PURGE ;
CREATE TABLE TARGET_TABLE  AS SELECT * FROM SOURCE_TABLE WHERE 1=2;
CREATE INDEX XAK01_TARGET_TABLE ON TARGET_TABLE ( NUM, OBJECT_ID ) ;
ALTER INDEX XAK01_TARGET_TABLE UNUSABLE ;
INSERT /*+ APPEND */ INTO  TARGET_TABLE  SELECT * FROM SOURCE_TABLE ;

-- ALTER SESSION SET SKIP_UNUSABLE_INDEXES= TRUE ; 는 DB LEVEL 에서TRUE 로 불필요 
SQL> SELECT NAME, VALUE,DISPLAY_VALUE
FROM V$PARAMETER WHERE NAME ='skip_unusable_indexes';

NAME                        VALUE   DISPLAY_VALUE  
-------------------------- -------- ---------------
skip_unusable_indexes       TRUE     TRUE

CASE 4)
UNIQUE INDEX  UNUSABLE  >> DML 불가
DROP TABLE TARGET_TABLE PURGE ;
CREATE TABLE TARGET_TABLE  AS SELECT * FROM SOURCE_TABLE WHERE 1=2;
CREATE INDEX XAK01_TARGET_TABLE ON TARGET_TABLE ( NUM, OBJECT_ID ) ;
ALTER INDEX XAK01_TARGET_TABLE UNUSABLE ;
CREAET UNIQUE INDEX XUK01_TARGET_TABLE ON TARGET_TABLE ( NUM, OBJECT_ID, OBJECT_NAME ); 

ALTER INDEX XUK01_TARGET_TABLE UNUSABLE ;
INSERT /*+ APPEND */ INTO  TARGET_TABLE  SELECT * FROM SOURCE_TABLE ;
--ORA-26026: 유일 SCOTT.XUK01_TARGET_TABLE 인덱스는 처음과 같은 사용불가능한 상태입니다
-- https://dbaora.com/loading-data-in-direct-mode-and-unique-index-ora-26026/ 
<< UNIQUE INDEX 가 UNUSABLE 상태에서는 DML 불가함 
ALTER INDEX XUK01_TARGET_TABLE REBUILD  ;
INSERT /*+ APPEND */ INTO  TARGET_TABLE  SELECT * FROM SOURCE_TABLE ;

CASE 5)
UNIQUE INDEX  유지 상태에서 ( WITH 일반인덱스 UNUSABLE ) 
DROP TABLE TARGET_TABLE PURGE ;
CREATE TABLE TARGET_TABLE  AS SELECT * FROM SOURCE_TABLE WHERE 1=2;
CREATE INDEX XAK01_TARGET_TABLE ON TARGET_TABLE ( NUM, OBJECT_ID ) ; -- 일반인덱스 존재 
ALTER INDEX XAK01_TARGET_TABLE UNUSABLE ; -- 일반 인덱스 UNUSABLE 조치 
CREAET UNIQUE INDEX XUK01_TARGET_TABLE ON TARGET_TABLE ( NUM, OBJECT_ID, OBJECT_NAME ); 
INSERT /*+ APPEND */ INTO  TARGET_TABLE  SELECT * FROM SOURCE_TABLE ;

CASE 6)
PK CONSTRAINT ( UNIQUE INDEX) 유지 상태에서  ( WITH 일반인덱스 UNUSABLE ) 
DROP TABLE TARGET_TABLE PURGE ;
CREATE TABLE TARGET_TABLE  AS SELECT * FROM SOURCE_TABLE WHERE 1=2; 
CREATE INDEX XAK01_TARGET_TABLE ON TARGET_TABLE ( NUM, OBJECT_ID ) ; -- 일반인덱스
ALTER INDEX XAK01_TARGET_TABLE UNUSABLE ; -- 일반 인덱스 UNUSABLE 조치 
CREATE UNIQUE INDEX XUK01_TARGET_TABLE ON TARGET_TABLE ( NUM, OWNER, OBJECT_ID, OBJECT_NAME ); 
-- UNIQUE INDEX
CREATE UNIQUE INDEX XPK_TARGET_TABLE ON TARGET_TABLE (  NUM, OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID); 
-- PK INDEX
ALTER TABLE TARGET_TABLE ADD CONSTRAINT XPK_TARGET_TABLE PRIMARY KEY (  NUM, OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID);
-- PK CONSTRAINT 
INSERT /*+ APPEND */ INTO  TARGET_TABLE  SELECT * FROM SOURCE_TABLE ;

CASE 7)
일반 인덱스 추가 + TABLE NOLOGING 
DROP TABLE TARGET_TABLE PURGE ;
CREATE TABLE TARGET_TABLE  AS SELECT * FROM SOURCE_TABLE WHERE 1=2;
CREATE INDEX XAK01_TARGET_TABLE ON TARGET_TABLE ( NUM, OBJECT_ID ) ;
SELECT TABLE_NAME, LOGGING FROM DBA_TABLES WHERE  TABLE_NAME ='TARGET_TABLE';
ALTER TABLE TARGET_TABLE NOLOGGING  ;
SELECT TABLE_NAME, LOGGING FROM DBA_TABLES WHERE  TABLE_NAME ='TARGET_TABLE';
INSERT /*+ APPEND */ INTO  TARGET_TABLE  SELECT * FROM SOURCE_TABLE ;