APPEND VS CTAS ( DB FORCE_LOGGING=NO) VS CTAS ( DB FORCE_LOGGING=YES)

APPEND VS CTAS ( DB FORCE_LOGGING=NO) VS CTAS ( DB FORCE_LOGGING=YES)

 

결론, REDO 발생 량은 
        CTAS NOLOGGING DB < APPEND < CATS LOGING FR0CE DB  순이다.
        CTAS LOGGING FORCE DB 상태에서는 REDO 가 무지 많이 발생한다.  ( 하단 표 참조 )

 

2022.12.13 - [Oracle/Oh Oracle ] - Append 와 Index( Normal, Unique, PK ) 관계

	Undo used_ublk	Undo use_rec	Redo
APPEND TEST	1	1	415,692
CTAS TEST WITH FORCE_LOGGING=NO	0	0	311,720
CTAS TEST WITH FORCE_LOGGING=YES	0	0	112,487,632

** CTAS 는 DDL 로 자동 COMMIT 으로 UNDO 측정 불가 
** 참고 링크 : https://www.orafaq.com/wiki/CTAS

-- 테스트 스크립트 
-- 샘플 테이블 생성 
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 ;

-- 1. APPEND TEST                     
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 ;

-- DROP TABLE TARGET_TABLE PURGE ; -- CASE 1 테스트 및 REDO 측정 후, DROP 

-- 2. CTAS TEST WITH FORCE_LOGGING=NO 
CREATE TABLE TARGET_TABLE NOLOGGING  AS SELECT * FROM SOURCE_TABLE WHERE 1=1;

-- DROP TABLE TARGET_TABLE PURGE ; -- CASE 1 테스트 및 REDO 측정 후, DROP 

-- 3. CTAS TEST WITH FORCE_LOGGING=YES
CREATE TABLE TARGET_TABLE NOLOGGING  AS SELECT * FROM SOURCE_TABLE WHERE 1=1;

-- UNDO 및 REDO 측정 하기 
-- 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;

-- FORCE LOGING 설정 

SQL>  alter database force logging;

Database altered.

SQL> exit

2022-12-22T20:05:49.899849+09:00
 alter database force logging
2022-12-22T20:05:49.901810+09:00
ALTER DATABASE FORCE LOGGING command is waiting for existing direct writes to finish. 
This may take a long time.
Completed:  alter database force logging