UPDATE와 DELETE

#핵심 요약

UPDATE 테이블 SET 컬럼 = 값 WHERE 조건, DELETE FROM 테이블 WHERE 조건
WHERE를 빼면 모든 행이 바뀌거나 지워짐

SET 절과 WHERE 절에 서브쿼리를 쓸 수 있음
예: KING의 월급을 ALLEN의 월급으로

지우는 방법은 DELETE, TRUNCATE, DROP 세 가지임

COMMIT한 UPDATE, DELETE도 Flashback으로 되돌릴 수 있었고, DROP한 테이블은 휴지통에서 되살렸음
TRUNCATE는 ROLLBACK도 Flashback도 안 되어 백업 테이블에서 다시 넣었음

#개념과 동작 원리

Flashback Table과 휴지통의 공식 문서 조건(행 이동 허용, UNDO 보존, 테이블 구조 변경 시 불가 등)은 Flashback 조회와 테이블 복구에 정리했음
그 글의 공식 문서 요약에 따르면 대상 시점과 현재 사이에 TRUNCATE 같은 구조 변경이 있으면 Flashback Table을 쓸 수 없음
실습의 "TRUNCATE는 Flashback이 안 된다"와 맞음

설명(PostgreSQL 비교): Oracle의 UPDATE는 기존 행을 UNDO 세그먼트에 보관하고, PostgreSQL은 기존 행을 같은 테이블 블록에 dead tuple로 남김
그래서 PostgreSQL은 VACUUM으로 정리함(PostgreSQL 공식 문서 미대조, 확인 불가)

#필수 명령어 또는 SQL

#UPDATE, DELETE

UPDATE emp SET sal = 3200 WHERE ename = 'SCOTT';
UPDATE emp SET sal = 6000, comm = 700 WHERE ename = 'KING';
DELETE FROM emp WHERE ename = 'SCOTT';
COMMIT;

결과 해석: 개별 실행 출력은 기재 없음

#서브쿼리를 쓴 UPDATE, DELETE

UPDATE emp
   SET comm = 5000
 WHERE sal > (SELECT sal FROM emp WHERE ename = 'SCOTT');

UPDATE emp
   SET job = 'SALESMAN'
 WHERE deptno = (SELECT deptno FROM emp WHERE ename = 'KING');

UPDATE emp
   SET sal = (SELECT sal FROM emp WHERE ename = 'ALLEN')
 WHERE ename = 'KING';

DELETE FROM emp
 WHERE hiredate > (SELECT hiredate FROM emp WHERE ename = 'ALLEN');

ROLLBACK;

결과 해석:

실습에서는 문장마다 결과를 보고 ROLLBACK했음

#COMMIT 후 되돌리기

UPDATE emp SET sal = 0;
COMMIT;

SELECT SYSTIMESTAMP FROM dual;
ALTER TABLE emp ENABLE ROW MOVEMENT;
FLASHBACK TABLE emp TO TIMESTAMP TO_TIMESTAMP('<YYYY/MM/DD:HH24:MI:SS>', 'RRRR/MM/DD:HH24:MI:SS');

결과 해석:

실습에서는 변경 전 시각을 넣어 되돌렸음
실습 속성 메모에는 "15분 동안 과거 데이터 보기 가능"이라고 적혀 있고, Flashback 가능 시간을 1시간으로 늘리는 문제를 냈음
이 시간은 UNDO 보존 시간(UNDO_RETENTION)과 관련됨(UNDO 공간과 보존)
바꿀 입력값: <YYYY/MM/DD:HH24:MI:SS>

#DROP과 휴지통, TRUNCATE와 백업

CREATE TABLE emp_backup AS SELECT * FROM emp;

DROP TABLE emp;

SHOW RECYCLEBIN
FLASHBACK TABLE emp TO BEFORE DROP;

TRUNCATE TABLE emp;

INSERT INTO emp SELECT * FROM emp_backup;
COMMIT;

결과 해석: 개별 실행 출력은 기재 없음

#어떤 테이블이 비었는지 찾기

실습에서는 짝이 자기 테이블 몇 개를 몰래 TRUNCATE하게 하고, 미리 저장해 둔 테이블별 건수와 지금 건수를 MINUS로 비교해 찾아냈음

EXEC DBMS_STATS.GATHER_SCHEMA_STATS('<SCHEMA>');

CREATE TABLE table_list_snapshot
AS
SELECT table_name, num_rows FROM user_tables ORDER BY num_rows DESC;

SELECT table_name, num_rows FROM user_tables WHERE table_name NOT LIKE '%BACKUP%'
MINUS
SELECT table_name, num_rows FROM table_list_snapshot WHERE table_name NOT LIKE '%BACKUP%';

결과 해석: 통계를 다시 모은 뒤 건수가 달라진 테이블이 나옴
NUM_ROWS는 통계 수집 시점의 값이므로 비교 전에 다시 수집했음
바꿀 입력값: <SCHEMA>

#사용 조건과 주의사항

실습 "78
UPDATE"는 DML을 insert, update, delete, merge, select 다섯 가지로, "77
INSERT"는 select를 뺀 네 가지로 적었음

UPDATE emp e SET loc = (SELECT ... WHERE d.deptno = e.deptno)처럼 상관 서브쿼리로 다른 테이블 값을 옮기는 방법은 MERGE에 있음