오라클 SQL 문법 공부23- DELETE 와 TRUNCATE

서론

이번에는 테이블의 데이터를 지우는 DELETE 문에 대해서 알아보고자 한다. DELETE 문은 DML이라고 데이터 조작어이다. 말 그대로 데이터를 조작, DELETE이니 데이터를 조작하여 삭제하는 것이고 제목에서 표기한 TRUNCATE은 테이블의 모든 데이터를 즉시 삭제하는 명령어이며 DDL, 데이터 정의어 이다. 이 두가지의 가장 큰 차이점이라면 바로 COMMIT과 ROLLBACK은 DML에서 사용하는 것이고 DDL은 테이블을 생성하거나 수정, 삭제 할때 사용하는 것이며 명령어를 치는 즉시 실행이 된다. DML은 방금 실행한 명령어를 취소할 수 없다는 것이다. DELETE문과 TRUNCATE를 사용하는 방법은 아래에서 확인하자.

본론

DELETE

--DELETE
--DELETE 문장은 데이터를 삭제하는 구문입니다.
--DELETE FROM TABLE_NAME WHERE 조건;
--TRUNCATE 로 지우면 기록들을 남기지 않으니 엄청 빨리 지워진다.
--큰데이터의 테이블을 DELETE 하면 데이터는 안보이지만 SELECT를 해도 느리다.
--그리고 DELETE 하고 난 후 데이터는 없지만 저장공간은 비어 있는 채 그대로 자리만 차지하고 있다.
--새로운 데이터들도 비어있는 공간에 들어가는게 아니라 신규 공간에 들어가게 된다
--따라서 데이터들을 많이 지우고 다시 넣고 반복 하다보면 데이터 크기만 커져 스캔하는데 시간이 많이 차지한다.
--불필요한 공간까지 스캔한다고 보면 된다. 이 부분은 오브젝트 튜닝에 대한 부분이다.
--해결 하는 방법은 새로운 공간을 만들어서 이사를 시킨다. (디스크 조각 모음 같은거)
--보통 리오그? 작업을 일년에 1번이나 2번 정도 한다. 대상은 DELETE가 자주 되는 오브젝트


--실습문제--
--1) PROFESSOR_TEST 테이블에서 전자공학과에 소속된 교수를 모두 삭제하여라.
--(삭제확인 후 ROLLBACK;)
DELETE FROM PROFESSOR_TEST WHERE DEPTNO = (SELECT DEPTNO FROM DEPARTMENT_TEST WHERE DNAME = '전자공학과');
SELECT * FROM PROFESSOR_TEST;
ROLLBACK;
--2)STUDENT_TEST 테이블에서 학년별 평균 몸무게 중에서 가장 많이 나가는 몸무게 보다도
--큰 학생들을 모두 삭제하여라.
--(삭제확인 후 ROLLBACK;)
DELETE FROM STUDENT_TEST S1 WHERE S1.WEIGHT > (SELECT MAX(WEIGHT) FROM (SELECT GRADE, AVG(WEIGHT) AS "WEIGHT" FROM STUDENT_TEST GROUP BY GRADE));
SELECT * FROM STUDENT_TEST;
ROLLBACK;

DELETE는 DELETE FROM 테이블명 WHERE 조건 형식으로 사용한다. 만약 WHERE 절이 없다면 당연히 모든데이터가 삭제되는 것이다. DELETE는 DML이라 실수하여 삭제하였다 하더라도 실제 데이터가 삭제되는 것은 아니기 때문에 ROLLBACK을 통해 원래 상태로 돌아 갈 수 있다.

위에 설명이 꽤 긴데 간단하게 설명하자면 DELETE를 한 후에 COMMIT; 까지 해줘도 실제 데이터가 삭제는 되었지만 사용하였던 공간은 그대로 존재하고 있어 실질적으로 데이터가 빠져나간 만큼의 여유 공간을 확보하지 못 한다는 말이다. 만약 1~100까지의 블록이 있다고 가정할 때 예를들어 1~50까지의 데이터를 DELETE를 사용하여 지웠는데 해당 테이블에 데이터를 추가하면 1~50 사이의 공간에 다시 들어가는 것이 아닌 51번부터 데이터가 입력된다는 것이다. 실제 데이터는 없지만 해당 블록만큼 데이터를 잡아 먹고 있는 것이다. 따라서 대용량의 테이블의 내용을 DELETE 하고 SELECT 했을 때도 똑같이 오래 걸리는 이유가 이 부분이다.

동일한 십만건의 데이터를 넣었다가 지우고 다시 넣는다면 1~50 까지의 데이터와 (1~50 데이터는 없지만 용량을 차지하고 있는 블록) + (51~100 다시 넣은 데이터) 일 경우 SELETE 할 경우 당연히 전자보다 후자의 블록이 더 크기 때문에 수행속도가 늦어질 것이다.

실질적으로 DELETE 한 후에 사용했던 공간을 회수 하려면 흔히 윈도우에서 해 보았던 디스크조각 모음과 느낌으로 재배치 작업인 리오그(REORG)를 해주어야 한다.

DELETE와 TRUNCATE 실습

--[실습] DELETE와 TRUNCATE 수행시 저장공간 반환여부 테스트
--목적 : DELETE와 TRUNCATE로 데이터 삭제 시 각각 저장공간이 즉시 반환되는지 여부를 테스트하고,
--REORG 작업 후 와 비교해본다.
--
--1. 테스트용 테이블 생성
--CREATE TABLE SCOTT.TEST_DELETE (NO NUMBER, NAME VARCHAR2(20), ADDR VARCHAR2(20));
--CREATE TABLE SCOTT.TEST_TRUNCATE (NO NUMBER, NAME VARCHAR2(20), ADDR VARCHAR2(20));

--2. 대용량 데이터 insert
--BEGIN
--FOR I IN 1..500000 LOOP
--INSERT INTO SCOTT.TEST_DELETE
--VALUES(I, DBMS_RANDOM.STRING('A', 19),
--DBMS_RANDOM.STRING('Q', 19));
--END LOOP;
--COMMIT;
--END;
--/

--BEGIN
--FOR I IN 1..500000 LOOP
--INSERT INTO SCOTT.TEST_TRUNCATE
--VALUES(I, DBMS_RANDOM.STRING('A', 19),
--DBMS_RANDOM.STRING('Q', 19));
--END LOOP;
--COMMIT;
--END;

--DBA_SEGMENTS
SELECT segment_name as table_name, bytes/1024/1024 as "size(mb)" FROM DBA_SEGMENTS
WHERE OWNER = 'SCOTT'
AND SEGMENT_NAME IN ('TEST_TRUNCATE', 'TEST_DELETE');
--테이블을 만들면 세그먼트가 할당이 된다.
--세그먼트 이름은 테이블 이름과 동일하다.
--실제 저장되는 공간
--디스크를 구성하고 잇는 최소 공간은 블럭
--테이블은 껍데기일 뿐, 디스크 어딘가에 저장이 된다.
--INSERT될때마다 세그먼트에 되어있는 공간에 저장이 된다.
--실제 어디에 저장되어있는지 공간을 확인하기 위해서는 DBA_SEGMENTS 뷰를 확인하면됨

--삭제해봄
delete from scott.test_delete; --수행 시간 8.84sec
commit;

truncate table scott.test_truncate; --수행 시간 0.sec


--확인 후 다시 저장공간 확인
SELECT segment_name as table_name, bytes/1024/1024 as "size(mb)" FROM DBA_SEGMENTS
WHERE OWNER = 'SCOTT'
AND SEGMENT_NAME IN ('TEST_TRUNCATE', 'TEST_DELETE');

--truncate는 즉시 삭제하기 때문에 최소할당 공간으로 됨 (64k)
--진짜 정확히 지울꺼면(복구 안할꺼면) truncate로 하면 됨. 이건 되돌릴 수가 없다.
--REORG 방식은 여러 방식이있는데..(REORG = 재배치)
select * from dba_tablespaces;
alter table scott.test_delete move tablespace users;
-- scott_.test_Delete 내용을 가져갈건데 빈공간은 안 가져갈 것이다.
-- users 라는 테이블스페이스에 재배치 할 것이다.
--실행을 하고 나서 다시 사이즈를 재보면

SELECT segment_name as table_name, bytes/1024/1024 as "size(mb)" FROM DBA_SEGMENTS
WHERE OWNER = 'SCOTT'
AND SEGMENT_NAME IN ('TEST_TRUNCATE', 'TEST_DELETE');
--공간이 사라진 것을 확인 할 수 있다.
--현업에서는 온라인리오그 툴이 있다..? 클릭클릭해서 간단히
--전체사이즈에 비해서 실제 사용되고 있는 블럭 수를 체크하여 리오그 대상을 선별

따로 샘플 화면은 없지만 실제로 동일한 테이블 두개를 만들어 데이터를 입력 한 후 DELETE와 TRUNCATE를 할 때 수행 속도도 다르고 DBA_SEGMENTS 라는 뷰를 확인해 볼 때도 DELETE는 공간이 유지되고 있었다. 그리고 나서 리오그 작업 중에 테이블 스페이스 옮기기를 통해 빈공간은 제외하고 실제 데이터만 옮겨가기 때문에 사이즈가 줄어드는 것을 볼 수가 있다. DELETE를 한 후에 왜 빈 공간에 다시 데이터를 넣지 않고 다음 공간에 계속 넣느냐면 INSERT 수행속도가 늦어질 수 있기 때문에 즉, 성능상의 문제이기 때문이다. 현업에서는 정기적으로 이러한 테이블들이 있을 경우 리오그 작업을 통해 낭비되고 있는 공간을 다시 확보하고 속도 유지도 하는 것 같다.

TRUNCATE는 해당 테이블의 모든 데이터를 즉시 날리기 때문에 백업본이 있지 않는 이상 복구가 불가능하다. 주의해서 사용해야할 명령어이다.

결론

DELETE와 TRUNCATE에 대해서 알아보았다. 사실 필자도 SQL만 공부 했을 경우에는 쉽게쉽게 그냥 외워서 사용을 했었는데 왜 이러는지 저러는지 오라클 구조에 대해 조금씩 알아가고 있으니 더 복잡해졌다. 오라클의 구조에 대해서 좀 더 깊게 공부를 할 필요성을 더 느낀다.