DDL (Data Definition Language)

• 데이터 정의어
• 오브젝트 생성, 변경, 삭제 가능
  • 테이블, 인덱스, 파티션, 뷰, 시퀀스, 시노님, DB 링크 등

테이블

create table
alter table
drop table
truncate table

CREATE TABLE

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-TABLE.html#GUID-F9CE0CC3-13AE-4744-A43C-EAC7A71AAAB6

열 정의 방식

CREATE TABLE [shema].table (
	column datatype [DEFAULT [ON NULL] expr]
    , column datatype [DEFAULT [ON NULL] expr] ...) [TABLESPACE tablespace];

• DBA_TABLES 뷰에서 테이블에 대한 정보 조회
• DBA_TAB_COLUMNS 뷰에서 열에 대한 정보 조회

CREATE TABLE t1 (
	c1 number,
    c2 number(2) default 2,
    c3 number(3) default 3 not null);

서브 쿼리 방식

CREATE TABLE ~~~ AS subquery;

• 테이블이 생성된 후 서브 쿼리의 결과가 테이블에 입력됨. (CTAS 방법이라고도 부름)

CREATE TABLE t2 AS SELECT * FROM t1 WHERE 0=1;

• 0=1 조건은 항상 false 이기 때문에 서브 쿼리의 결과가 테이블에 입력되지 않음. 테이블만 생성할 때 위 쿼리 사용

• 아래와 같이 컬럼 이름(c4)과 기본값 지정 가능

CREATE TABLE t2 (c1, c2 DEFAULT 2, c4 DEFAULT 3) AS SELECT * FROM t1 where 0=1;

• dba_extents 뷰에서 익스텐트에 대한 정보 조회 가능.

• 특정 유저의 기본 테이블스페이스 조회방법
  select default_tablespace from dba_users where username = 'SCOTT';

ALTER TABLE

• 테이블, 열, 제약 조건 변경

RENAME 절 : 테이블명 변경

ALTER TABLE [SCHEMA'.TABLE RENAME TO NEW_TABLE_NAME;
ALTER TABLE t2 RENAME TO t1;

MOVE 절 : 세그먼트의 데이터 재배치

12.2 버전부터 사용가능 : including rows update indexes

alter table schema.table MOVE
[INCLUDING ROWS WHERE_CLUASE]
[ONLINE]
[TABLESPACE tablespace]
[UPDATE INDEXES] [(index [, index])];

• including rows : 재배치한 데이터의 조건 지정
• online : ddl문이 수행되는 동안 dml 문이 수행될 수 있도록 허용
• tablesapce : 테이블스페이스 지정
• update indexes : 인덱스도 함께 재구성

CREATE TABLE ta AS SELECT ROWNUM AS c1 FROM XMLTABLE ('1 to 10000');

• c1 <= 5000 조건에 해당하는 데이터를 users 테이블스페이스에 재배치.
• 테이블의 데이터를 재배치하면 행의 ROWID가 변경되기 때문에 종속된 인덱스를 REBUILD 해야함. (인덱스 재구축)

ALTER TABLE t1 MOVE INCLUDING ROWS WHERE c1 <= 5000 TABLESPACE users

원래 t1 테이블은 10000개의 행이 존재했으나 move를 통해 5000개의 행만 남아있게 된다.

READ ONLY 절

• 테이블을 읽기 전용 테이블로 변경
  ALTER TABLE table {READ ONLY | READ WRITE|;

• READ ONLY : 테이블을 읽기 전용으로 변경

• READ WRITE : 테이블을 읽기, 쓰기 모드로 변경 (기본값)
  ALTER TABLE t1 READ ONLY

• 읽기 전용 테이블에 DML 작업을 수행하면 에러 발생. (ORA-12081)

• DBA_TABLES 뷰에서 읽기 전용 여부 확인 가능

truncate table

• 테이블 초기화
  • 테이블에 있는 모든 데이터 삭제
• 최초 생성되었을 당시의 storage(MINEXTENTS)만 남기고, 스토리지 저장 공간을 release함. (DROP STORAGE)
  • 단) REUSE STORAGE 옵션을 주면 삭제된 행의 저장 공간을 유지할 수 있음.
• 롤백 불가능.

TRUNCATE TABLE 테이블명 [{DROP[ALL]|REUSE} STORAGE] [CASCADE]

• DROP STORAGE : 기본값

• DROP ALL STORAGE : MINEXTENTS 파라미터에 의해 할당된 공간을 포함한 모든 공간 해제

• REUSE STORAGE : 삭제된 행의 공간 유지

• CASCADE : ON DELETE CASCADE FK 제약 조건으로 참조하는 테이블을 TRUNCATE

• 테이블의 크기가 큰 경우 DROP ALL STORAGE를 사용하면 수행 시간을 단축시킬 수 있다.

drop table

DROP TABLE 테이블명 [CASCADE CONSTRAINTS] [PURGE]

• 테이블 삭제
• recycle bin으로 테이블 세그먼트가 이동됨.
  -> recycle bin에 남아있기 때문에 flashback table 테이블명 to before drop 기능을 사용하여 복원 가능.
  • SELECT * FROM RECYCLEBIN;
• CASCADE CONSTRAINTS : 테이블을 참조하는 FK 제약조건도 함께 삭제
• PURGE : RECYCLE BIN을 사용하지 않고 테이블 즉시 삭제
  • 이 경우에는 바로 스토리지 저장 공간을 release 하기 때문에 원복 불가능

purge

• RECYCLEBIN
  이 절을 사용하여 현재 사용자의 리사이클 빈(recycle bin)을 삭제.

• 사용자의 recycle bin에서 모든 객체를 제거하고 recycle bin에 있는 객체와 관련된 모든 스토리지 공간을 해제.

• DBA_RECYCLEBIN

이 절은 SYSDBA 또는 PURGE DBA_RECYCLEBIN 시스템 권한이 있는 경우에만 사용 가능.
시스템 전역의 recycle bin에서 모든 객체를 제거할 수 있으며, 모든 사용자의 recycle bin을 삭제하는 것과 동일.

delete

• WHERE절을 사용하여 테이블에 있는 데이터를 하나하나 선택하여 제거하는 방식
• WHERE절을 사용하지않고 테이블의 모든 데이터를 삭제하더라도, 내부적으로는 한줄 한줄 일일히 제거하는 과정을 거침
• 롤백 정보를 기록하므로 (아마 undo, redo?) 처리속도가 늦고, 퍼포먼스에 좋지않은 영향을 줄 수 있음
• 원하는 데이터만 골라서 삭제할 때에는 DELETE 사용 / 전체 데이터 삭제할 때에는 TRUNCATE 사용
• 데이터를 삭제하더라도 데이터가 담겨있던 Storage는 Release 되지않는다.
• commit 이전에는 롤백 가능 (dml문이라서 자동 commit X)

테이블 유형

구조, 분산, 저장 기준에 따라 테이블을 아래와 같이 구분할 수 있다.

• 구조 : 힙 구조, 인덱스 구조, 익스터널 구조 테이블
  • 힙 구조 : 힙 구조에 데이터 저장, 임의의 위치에 데이터 저장, 오라클 DB의 기본 테이블은 힙 구조 테이블
    create table 테이블명 ( ~~ ) ORAGANIZATION HEAP
  • 인덱스 구조(Index-oraganized table) : b-tree 인덱스 구조에 데이터 저장. PK 제약조건의 열 순서에 따라 데이터가 정렬되어 저장.
    • 주로 OLAP 시스템의 조회 성능을 개선하기 위해 사용, IOT 라고도 부름
    • 열 개수가 적은 테이블에 사용해야함.
    • OLTP 시스템에 사용할 경우, 코드 테이블에 주로 사용.
    • dba_segments뷰에서 조회를 해보면 IOT 테이블은 논리적으로 존재하기 때문에 세그먼트가 조회 안됨. 물리적으로 PK 제약 조건이 사용하는 t1_pk 인덱스에 저장됨.
    • IOT는 dba_tables 뷰의 iot_type 열이 IOT로 표시됨.
      create table 테이블명 (~~ constraint 제약조건이름 primary key (column) organization index
    • create table t1 (c1 number, c2 number, constraint t1_pk primary key(c1)) organization index;
    • dba_objects 뷰에서 오브젝트에 대한 정보 조회 가능.
  • 익스터널 구조
    • 외부 데이터를 조회하거나 외부에 데이터를 저장할 수 있는 테이블
    • 주로 DW 시스템의 ETL 작업에 사용.
    • preprocessor 기능 : 데이터베이스 서버의 OS 명령어를 수행하고 수행 결과를 테이블로 조회 가능.
• 분산 : 클러스터 테이블, 파티션 테이블
  • 클러스터 테이블
    - 클러스터에 데이터 저장
    - 클러스터 : 동일한 데이터를 동일한 위치에 저장하는 세그먼트
    - 하나의 블록에 여러 테이블의 행이 저장될 수 있음.
    - 관리가 어려워 활용도는 높지 않음.
  • 인덱스 클러스터
    • 데이터 저장과 조회에 인덱스를 사용.
      create cluster c1# (c1 number) index; : 인덱스 클러스터 생성
      create index c1#_x1 on cluster c1#l : 위에서 생성한 클러스터의 인덱스 생성
    • 클러스터 테이블은 동일한 클러스터에 속한 다른 테이블과 블록을 공유하기 때문에 개별 테이블을 truncate 할 수 없다.
      (대신 truncate cluster 문을 수행하여 클러스터에 저장된 모든 테이블을 truncate 한다.)
  • 해시 클러스터
    • 데이터 저장과 조회에 해시 함수 사용
  • 단일 테이블 해시 클러스터
    • 클러스터에 단일 테이블만 저장할 수 있는 해시 클러스터
  • 정렬 해시 클러스터
    • sort 키워드를 지정한 열로 데이터를 정렬하여 클러스터에 저장.
  • 클러스터 테이블에 대한 정보 조회 : dba_clusters
• 저장 : 영구 테이블, 임시 테이블
  • 임시 테이블 : 트랜잭션 또는 세션 레벨로 관리되는 테이블
  • OLAP 시스템이나 DW 시스템에서 중간 집계를 저장하는 용도로 사용.

  create global temporary table 테이블명 
  ( column datatype ~~~) 
  [on commit {delete | preserve } rows];

• on commit delete rows : 트랜잭션 레벨로 데이터 저장 (트랜잭션이 종료되면 즉 commit 되면 테이블 초기화됨.)
• on commit preserve rows : 세션 레벨로 데이터 저장 (세션이 종료되면 테이블 초기화됨.)

create global temporary table t1 (C1 number) on commit delete rows;

create global temporary table t1 (C1 number) on commit preserve rows;