오라클 SQL과 PL/SQL ( DELETE, TRUNCATE, DROP 차이 / 읽기전용테이블 )

DELETE, TRUNCATE, DROP 명령어의 차이점

DELETE : 데이터만 지워지고 쓰고 있던 디스크상의 공간은 그대로 가지고 있습니다. 그래서 데이터가 DELETE 되어도 테이블 용량은 줄어들지 않습니다.
TRUNCATE : 최초에 테이블이 만들어졌던 상태, 즉 데이터가 1건도 없는 상태로 모두 삭제하고 컬럼값만 남겨놓습니다. 용량도 줄어들고 인덱스등도 모두 삭제됩니다.
DROP : 데이터와 테이블 전체를 삭제합니다. 사용하고 있던 공간도 모두 반납하고 인덱스 제약조건 등 오브젝트도 삭제됩니다.

11g에서 추가된 기능소개

1. 읽기 전용 테이블로 변경하기

만약 어떤 경우에 특정 테이블의 내용을 모두 변경은 할 수 없게하고 조회만 가능하도록 설정해야 한다면 트리거를 사용하거나 아니면 제약조건을 DISABLE NOVALIDATE해서 작업해야 합니다.
하지만 11g버전에서는 아주 간단하게 읽기전용으로 변경할 수 있습니다.

CREATE TABLE t_readonly
( no NUMBER, name VARCHAR2(10));

INSERT INTO t_readonly VALUES (1, 'AAA');

COMMIT;

SELECT * FROM t_readonly;

ALTER TABLE t_readonly READ ONLY;

위와같이 아주 간단하게 읽기전용 테이블로 변경됩니다.

읽기전용 테이블에 테이터나 컬럼을 추가하려고 하면 어떻게 될까요 ?

INSERT INTO t_readonly VALUES (2, 'BBB');
SQL Error [12081] [99999]: ORA-12081: update operation not allowed on table "SCOTT"."T_READONLY"

ALTER TABLE t_readonly ADD (tel NUMBER DEFAULT 111); 
SQL Error [12081] [99999]: ORA-12081: update operation not allowed on table "SCOTT"."T_READONLY"

// 읽기 전용인 테이블을 다시 읽기/쓰기모드로 변경 함

ALTER TABLE t_readonly READ WRITE;

// 읽기 전용인 테이블은 읽기 전용상태일 때 아래의 명령으로 삭제 가능함.

DROP TABLE t_readonly;

2. 가상컬럼 테이블사용하기.

11g부터는 테이블을 생성할 때 가상 컬럼이라는 것을 설정할 수 있습니다.
아래의 쉬운 예로 가상 컬럼 테이블을 살펴보겠습니다.


2-1 : 0000 가상컬럼을 가지는 vt1 테이블을 생성합니다.

CREATE TABLE vt1
( col1 NUMBER ,
  col2 NUMBER ,
  col3 NUMBER GENERATED ALWAYS AS (col1 + col2)
)

2-2 : vt테이블에 데이터를 입력합니다.

INSERT INTO vt1 values(1, 2, 3);
SQL Error [54013] [99999]: ORA-54013: INSERT operation disallowed on virtual columns

가상컬럼에는 사용자가 데이터를 입력할 수 없습니다.

2-3 : 입력된 데이터를 조회합니다.

SELECT * FROM vt1;

2-4 : 기존 값을 변경한 후 가상 컬럼에 반영되는지 확인

UPDATE vt1 SET col1 = 5;
SELECT * FROM vt1;

테스트 결과 기존 컬럼의 값이 변경될 경우 즉시 가상 컬럼에도 반영된다는 것을 알 수 있습니다.

2-5 : 새로운 가상 컬럼을 추가

ALTER TABLE vt1 ADD (col4 GENERATED ALWAYS AS ((COL1*12)+COL2));

2-6 : 테이블에서 가상 컬럼 내역을 조회

SELECT COLUMN_NAME, DATA_TYPE, DATA_DEFAULT 
FROM USER_TAB_COLUMNS 
WHERE TABLE_NAME = 'VT1'
ORDER BY COLUMN_ID

2-7 : 조건절을 활용한 가상 컬럼 생성하기

CREATE TABLE sales10
( no NUMBER,
pcode CHAR(4),
pdate CHAR(8),
pqty NUMBER,
pbungi NUMBER(1),
GENERATED ALWAYS AS
(
CASE 
WHEN SUBSTR(PDATE, 5 ,2) IN ('01', '02', '03') THEN 1
WHEN SUBSTR(PDATE, 5 ,2) IN ('04', '05', '06') THEN 2
WHEN SUBSTR(PDATE, 5 ,2) IN ('07', '08', '09') THEN 3
ELSE 4
END ) virtual );


INSERT INTO sales10 (no, pcode, pdate, pqty) VALUES(1, '100', '20110112', 10);
INSERT INTO sales10 (no, pcode, pdate, pqty) VALUES(2, '200', '20110505', 20);
INSERT INTO sales10 (no, pcode, pdate, pqty) VALUES(3, '300', '20110812', 30);
INSERT INTO sales10 (no, pcode, pdate, pqty) VALUES(4, '400', '20111024', 40);

COMMIT;

SELECT * FROM sales10;