본문 바로가기

별걸다하는 IT/데이터베이스 Database

[ORACLE,SQL] drop vs truncate vs Delete 차이점. 테이블 삭제, 데이터 삭제 명령어 알아보자.

 

데이터베이스(DB, DBMS) 목차

DROP - 테이블 삭제

테이블을 삭제하는 명령어 입니다.

사용예시)

DROP TABLE CUSTOMER

만들어졌던 테이블이 싹 다 삭제됩니다. 존재 자체가 삭제!

 

TRUNCATE - 데이터만 통 삭제

truncate는 테이블이 삭제되는 명령어는 아니고 테이블 안에 들어있던 모든 레코드들을 제거하는 명령어입니다.

그리고 그 행들 즉 데이터들을 삭제한 후에 생기는 저장 공간을 재사용할 수 있도록 메모리를 해제해요.

truncate 명령어로만은 table 구조를 완전히 삭제하는게 아니라는걸 주의~

TRUNCATE TABLE CUSTOMER:

테스트를 위한 테이블일 경우 보통 메모리가 FULL되면 한번씩 truncate해주는 작업을 주기적으로 진행하기도 합니다.

테이블을 DROP했다가 CREATE하는 거기 때문에 모든 행을 삭제하는 데에는 가장 빠르고 효율적인 방법입니다.

DELETE - 데이터 골라서 삭제

DELETE FROM CUSTOMER;
DELETE FROM CUSTOMER WHERE ~;

DELETE도 TRUNCATE처럼 테이블 구조는 남기고 안의 데이터만 삭제합니다. 그럼 두 명령어의 차이점은 무엇아냐~

TRUNCATE는 부분별삭제가 안되지만 DELETE는 where 조건 등을 이용해서 원하는 데이터 삭제가 됩니다. TRUNCATE는 무조건 통으로 날려요.

또 TRUNCATE는 DDL에 속한다면 DELETE는 DML에 속합니다.

 

똑같은 삭제인데 왜 delete는 dml이고 truncate는 ddl인가..!?

내부 방식이 다르기 때문입니다. delete는 조건에 해당하는 것만 지울 수도 있고 전체를 지울수도 있는 이유가 한 줄 한 줄 삭제하기 때문입니다. 

 

하지만 truncate은 drop 후에 create를 하는거라는거~ 

즉 통째로 버린다음에 테이블을 생성해 초기상태로 만드는 거라, drop과 create로만 이루어졌으므로 당연히 ddl이면서 로그가 남지 않는다는 특징이 있습니다.

사진출처:https://ittutorial.org/sql-tuning-tips-and-tricks-tutorial-oracle-2/truncate-vs-delete/

또 당근 drop을 썼으니 한줄 한줄 지우는거보다 속도는 빠르지만 복구가 되지않아요~ 

DELETE의 경우 데이터가 삭제되어도 빈 테이블이나 클러스터에 그대로 남아있게 되는데 반면 TRUNCATE의 경우 메모리가 아예 해제되어 남아있지 않습니다. 

TRUNCATE는 ROLLBACK이 불가. DELETE는 가능. 

 

각 명령어 비교대조 

 DROP 

 TRUNCATE 

 DELETE 

 DDL 

 DDL 

 DML 

 Rollback 불가능 

 Rollback 불가능 

 commit 이전 rollback 가능 

 Auto Commit 

 Auto Commit 

 사용자 Commit 

 테이블이 사용했던 Storage 

 모두 Release 

 테이블이 사용했던 Storage 중

 최초 테이블 생성시 할당된 Storage만

 남기고 Release 

 데이터를 모두 Delete해도 사용했던

 Storage는 Release 되지 않음

 테이블의 정의 자체를 완전히 삭제함

 테이블을 최초 생성된 

 초기 상태로 만듬 

 데이터만 삭제

 로그 안남음 

 로그 안남음 

 로그 남음 

 

 

기출 문제~

■ 개발 프로젝트의 표준은 모든 삭제 데이터에 대한 로그를 남기는 것을 원칙으로 하고, 테이블 삭제의 경우는 허가된 인력만이 정기적으로 수행하도록 정하고 있다. 개발팀에서 사용 용도가 없다고 판단한 STADIUM 테이블의 데이터를 삭제하는 가장 좋은 방법은 무엇인가?

1. DELETE FROM STADIUM;

2. DELETE * FROM STADIUM;

3. TRUNCATE TABLE STADIUM;

4. DROP TABLE STADIUM;

답: 1번

 

  다음 중 아래와 같은 상황에서 사용할 수 있는 SQL 명령어는?

[ 우리가 관리하는 데이터베이스의 "매출" 테이블이 너무나 많은 디스크 용량을 차지하여 "매출"테이블에서 필요한 데이터만을 추출하여 별도의 테이블로 옮겨 놓았다. 이후 "매출" 원본 테이블의 데이터를 모두 삭제함과 동시에, 디스크 사용량도 초기화 하고자 한다. (단 "매출"테이블 스키마 정의는 유지된다.) ]

1. TRUNCATE TABLE 매출;

2 DELETE FROM 매출;

3 DROP TABLE 매출;

4 DELETE TABLE FROM 매출;

답: 1번

 

  다음 중 DELETE와 TRUNCATE, DROP 명령어에 대해 비교한 설명으로 가장 부적절한 것을 2개 고르시오

1. 특정 테이블에 대하여 WHERE 조건절이 없는 DELETE 명령을 수행하면 DROP TABLE 명령을 수행했을 때와 똑같은 결과를 얻을 수 있다.

2. DROP 명령어는 테이블 정의 자체를 삭제하고, TRUNCATE 명령어는 테이블을 초기상태로 만든다.

3. TRUNCATE 명령어는 UNDO를 위한 데이터를 생성하지 않기 때문에 동일 데이터량 삭제시 DELETE보다 빠르다.

4. DROP은 Auto commit이 되고, DELETE 와 TRUNCATE는 사용자 Commit으로 수행된다.

답: 1번, 4번 

 

오늘은 여기까지입니다. 모두들 고생하셨어요 :)