-
[SQL] TRUNCATE 명령어에 대해서 알아보자SQL 2022. 8. 16. 00:56
데이터베이스에서 TRUNCATE, DELETE, DROP 명령어는 데이터를 삭제한다는 관점에서
동일하지만, 목적에 따라서 구분될 수 있다.
- TRUNCATE : 테이블 구조와 제약조건들을 유지시키면서 데이터만 삭제하는 경우
- DELETE : 테이블의 특정 ROWS만 삭제하는 경우
- DROP : 테이블 구조와 제약조건 그리고 데이터를 삭제하는 경우
위 세가지 명령어를 비교해보면서 TRUNCATE에 대해서 알아보자 🏃
🚀 TRUNCATE란?
TRUNCATE FROM TABLE은 테이블에 존재하는 모든 행을 제거하는 명령어이다.
(테이블 스키마와 의존성과 제약조건들은 남기고, 모든 ROW들을 모두 삭제하는 명령어)
간단하게 말하면 WHERE절이 없는 DELETE와 동일하다.
특히 롤백이 불가능하기에, 롤백이 불필요한 경우 사용한다.
1. DELETE 명령어와 비교
⚠️ 이때 TRUNCATE의 ROLLBACK 가능 여부는 데이터베이스의 종류에 따라 다르다.
(TRUNCATE는 SQL Server에서 롤백할 수 있지만 MySQL에서는 롤백할 수 없음).DELETE 명령어는 DML에 속한 반면, TRUNCATE 명령어는 DDL에 속합니다.
Although TRUNCATE TABLE is similar to DELETE , it is classified as a DDL statement rather than a DML statement. It differs from DELETE in the following ways:
🔗 13.1.33 TRUNCATE TABLE Statement
2. 명령어가 요구하는 권한
TRUNCATE은 DDL입니다.
필요한 권한의 경우, 데이터베이스의 종류에 따라 상이합니다.
- PostgreSQL에서는 TRUNCATE 권한이 필요하며
- SQL Server에서는 ALTER TABLE 권한이 필요하다.
- Oracle은 DROP ANY TABLE 명령을 사용할 수 있어야하고 이는 시스템 권한이 필요하다.
you need permission to use TRUNCATE TABLE. In PostgreSQL, you need the privilege TRUNCATE; in SQL Server, the minimum permission is ALTER table; in MySQL, you need the DROP privilege. Finally, Oracle requires the DROP ANY TABLE system privilege to use this command.
TRUNCATE TABLE vs. DELETE vs. DROP TABLE: Removing Tables and Data in SQL | LearnSQL.com즉 TRUNCATE 권한부여를 위한 명령어는 아래와 같습니다.
# truncate 명령어의 권한 부여 grant truncate가 아님 # Oracle 예시 SQL > grant drop any table to {DB_USERNAME};
3. 트랜잭션 로그 유무
DELETE의 경우 트랜잭션 로그를 각 ROW 별로 보관한다. (logfile)
하지만 TRUNCATE의 경우, ROW별로 트랜잭션 로그를 보관하지 않는다.
이 때문에 DELETE 명령어가 더 많은 트랜잭션 로그 공간이 필요하게 된다.
4. 처리속도 관점
1. DELETE에 비해서 TRUNCATE는 ROW별로 트랜잭션 로그를 저장하지 않는다.
ROW별로 로그를 저장하지 않기 때문에 TRUNCATE 명령어가 월등하게 빠르다.
(TRUNCATE 로그는 최소한의 로그만 쌓인다)2. DROP & RECREATE에 비해서 TRUNCATE가 더 빠르다.
3. TRUNCATE를 하고 DROP을 하는게 좋을까?
뭐가 더 좋은지는... 데이터베이스마다 다르다.
국내외 많은 회사들이 TRUNCATE를 하고 DROP을 통해서 테이블을 제거한다고 한다.
SQL Server의 경우)
DROP도 TRUNCATE만큼 충분히 빠르고, 무엇보다 처리행동도 비슷하다.
무엇보다 불필요한 오버헤드가 존재할 수 있다고 한다.
sql server - Why use both TRUNCATE and DROP? - Database Administrators Stack Exchange
5. 외래키에 대해서
TRUNCATE 시에 모든 ROW의 외래키와 같은 제약조건을 체크하지 않을 수 있다.
하지만 데이터 무결성에 손상 가능성이 있기 때문에, 사용하지 않는 편이 좋을 것이다.SET FOREIGN_KEY_CHECKS = 0; TRUNCATE table1; TRUNCATE table2; SET FOREIGN_KEY_CHECKS = 1;
6. Rollback에 대해서
위에서 언급한대로, DDL 명령어인 TRUNCATE는 ROLLBACK이 불가능하다.
(이는 트랜잭션 로그를 ROW 별로 남기지 않기 때문이기도 하다)롤백이 가능하다는 데이터베이스가 있지만, 가급적 불가능하다고 생각하고 안전하게 작업하는 것이 좋겠다.
https://www.techonthenet.com/mysql/truncate.php
7. 인덱스와 제약조건에 대해서
TRUNCATE 명령어는 테이블 구조를 유지시키며, 데이터만 삭제한다.
이를 통해서 초기 테이블 구조로 되돌린다.
즉 테이블의 열, 제약조건, 인덱스는 유지된다.
참고
- TRUNCATE (SQL) - 위키백과, 우리 모두의 백과사전
- TRUNCATE TABLE(Transact-SQL) - SQL Server | Microsoft Docs
- https://docs.oracle.com/database/121/SQLRF/statements_10007.htm#SQLRF01707
- 13.1.33 TRUNCATE TABLE Statement
- TRUNCATE TABLE vs. DELETE vs. DROP TABLE: Removing Tables and Data in SQL | LearnSQL.com
- MySQL :: MySQL 5.6 Reference Manual :: 13.1.33 TRUNCATE TABLE Statement
반응형'SQL' 카테고리의 다른 글
[SQL] MSSQL 프로시저 생성,사용,삭제 예제 (0) 2020.02.11 [MSSQL] Excel Insert Query maker (0) 2020.02.11