Delete vs ALTER TABLE ... MOVE INCLUDING ROWS WHERE 조건절 TABLESPACE.. UPDATE INDEXEX

문서 보다가 일반 DELETE 와 ALTER TABLE MOVE UPDATE INDEXES 기능비교

 

 

비교 )

샘플이지만, CASE 1인 ALTER TABLE MOVE 의 경우, UNDO 발생이 없고, REDO 가 휠씬 적다

고려 휠씬 빠르게 수행 될것 같다.

상대적이겠지만, 삭제 량이 그렇게 많지 않다면 ALTER TABLE MOVE ( rollback 안됨 주의 !!! ) 

삭제 량이 엄청 많다면,평소에 나눠서, LOOPING 식으로 야금야금 삭제..

 

-- CASE 1  ALTER TABLE ... MOVE INCLUDING ROWS WHERE 조건절 TABLESPACE.. UPDATE INDEXEX
ALTER TABLE T1 MOVE INCLUDING ROWS WHERE C1 <= 500000 TABLESPACE USERS UPDATE INDEXES;
--> WHERE 절에 만족하는 않는 ROW 는 삭제됨

--> TABLESPACE USERS 로  특정 TABLE 로 MOVE 가능

--> UPDATE INDEXES 옵션으로 관련 인덱스 VALID 유지 가능 ,  이 옵셥 빼고 MOVE 시, INVALID 상태로 변경됨 

 

-- CASE 2 일반적인DELETE 
DELETE T1 WHERE C1 <= 500000 ;

 

 

-- 1.  샘플 테이블 생성 
DROP TABLE T1 PURGE ;
CREATE TABLE T1 AS SELECT ROWNUM AS C1, SYSDATE - ROWNUM AS C2  FROM XMLTABLE('1 to 1000000');

ALTER TABLE T1 ADD CONSTRAINT T1_PK PRIMARY KEY ( C1 ) ;

-- 삭제 점 테이블 / 인덱스 사이즈 체크 
SELECT SEGMENT_NAME, BYTES/1024/1024 AS SIZE_MB, BLOCKS  FROM DBA_SEGMENTS WHERE SEGMENT_NAME  IN ('T1','T1_PK') ;
--    T1_PK	17	2176
--    T1	20	2560

-- 인덱스 상태 체크 
SELECT INDEX_NAME, STATUS,NUM_ROWS FROM DBA_INDEXES WHERE INDEX_NAME ='T1_PK';
-- T1_PK	VALID	1000000

--  >>>>  테이블 생성 후, 신규 세션으로 테스트 진행 필요 <<<<

-- 샘플 SID 지정 
select distinct sid from v$mystat ;


-- 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;


-- 조건에 해당하지 않은 데이터는 재배치되지 않고 제거된다. 
-- CASE 1 
ALTER TABLE T1 MOVE INCLUDING ROWS WHERE C1 <= 500000 TABLESPACE USERS UPDATE INDEXES;

-- CASE 2 
DELETE T1 WHERE C1 <= 500000 ;

-- 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 ;
-- CASE 1 
-- DDL 이라 UNDO 발생 없음

-- CASE 2 
-- USED_UBLK 14282
-- USED_UREC 1000000

-- 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 
--redo size:936   >> redo size:  19,100,536

-- CASE 2 ( alter table 비교 10배 이상 redo 발생 )
--redo size:936   >> redo size: 263,581,944



SELECT SEGMENT_NAME, BYTES/1024/1024 AS SIZE_MB, BLOCKS  
FROM DBA_SEGMENTS WHERE SEGMENT_NAME  IN ('T1','T1_PK') ;
-- 테스트 전 
--    T1_PK	17	2176
--    T1	20	2560
-- CASE 1 -- ALTER TABLE 결과로 TABLE/INDEX 모두 사이즈가 줄어듦
--    T1_PK	9	1152
--    T1	10	1280
-- CASE 2  -- DELETE 로, TABLE/INDEX 모두 사이즈 변경 없음 
--    T1_PK	17	2176
--    T1	20	2560

-- INDEX 상태 및 NUM_ROWS 체크 
SELECT INDEX_NAME, STATUS,NUM_ROWS FROM DBA_INDEXES WHERE INDEX_NAME ='T1_PK';
-- 테스트 전 
-- T1_PK	VALID	1000000

-- CASE 1 --> DDL 로 자동으로  통계정보 수집 되는것으로 보임 
-- T1_PK    VALID    500000

-- CASE 2 --> DML 이라 변경 없음 
-- VALID	1000000

-- 테스트 후, NUM_ROWS 
SELECT COUNT(*) AS C1 FROM T1 ;
-- CASE1,2 모두 동일
--  500000