DELETE, TRUNCATE, DROP

DELETE와 TRUNCATE의 차이점을 대충 알고 있었지만 정작 질문을 받으면 항상 구글에 검색한 뒤에야 겨우 답했었다. 그런 모습이 아쉬워서 DELETE, TRUNCATE에 대해 확실하게 알아보기로 했다. 구글에 검색하면 보통 함께 등장하는 DROP도 결이 다르다고 느껴지긴 하지만 이참에 같이정리해 보았다.

 

 

DELETE

행 단위로 조건에 맞는 데이터를 삭제한다. 데이터를 삭제해도 HWM(High Water Mark)가 낮아지지 않기 때문에 테이블의 크기가 줄어들지는 않는다. 

DELETE FROM 테이블명 WHERE 컬럼명 = '조건';
COMMIT;

DELETE는 언두 데이터를 생성하기 때문에 다음과 같이 특정 시점 또는 특정 시간 이전으로 복구할 수 있다.

SELECT * FROM 테이블명 AS OF TIMESTAMP TO_TIMESTAMP('2024-01-07 01:01:01', 'YYYY-MM-DD HH24:MI:SS');

SELECT * FROM 테이블명 AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '2' HOUR);

 

 

TRUNCATE

테이블 내의 모든 데이터를 삭제한다. 속도가 빠르며 롤백이 불가능하다. TRUNCATE 하면HWM(High Water Mark)이 초기화되기 때문에 테이블의 크기도 테이블 생성 직후와 동일해진다.

TRUNCATE TABLE 테이블명;

다른 스키마의 테이블을 TRUNCATE 하려면 DROP ANY TABLE 권한이 필요하다.

 

 

DROP

테이블을 삭제한다. 인덱스도 같이 삭제된다. 해당 테이블과 관련된 SYNONYM, VIEW, FUNCTION은 INVALID 상태가 된다.

DROP TABLE 테이블명;

DROP 한 테이블은 휴지통과 유사한 DBA_RECYCLEBIN에서 다음과 같이 조회 가능하다. DROPTIME 컬럼으로 테이블을 삭제한 시간도 확인할 수 있다. 

SELECT * FROM DBA_RECYCLEBIN WHERE ORIGINAL_NAME = ‘기존테이블명’;

DROP 후 DBA_RECYCLEBIN에서 조회가능한 테이블은 FLASHBACK을 통해 복구 가능하다.

FLASHBACK TABLE "BIN$삭제된테이블명" TO BEFORE DROP RENAME TO 기존테이블명_TMP;

따라서 테이블을 DROP해도 DBA_RECYCLEBIN으로 그대로 이동하는 경우가 있어서 TABLESPACE 용량은 줄어들지 않을 수 있다.

아래처럼 DBA_RECYCLEBIN을 비울 수 있으나, DROP 된 테이블들 복구가 불가능하기 때문에 신중해야 한다.

PURGE DBA_RECYCLEBIN;

 

 

 

출처 : 오라클 truncate 와 delete의 차이