테이블 백업이나 분석에 사용되는 CTAS (CREATE TABLE AS SELECT) 쿼리

테이블 백업, 분석에 사용되는 CTAS 쿼리 (CREATE TABLE AS SELECT)

CTAS 쿼리

 

CTAS 쿼리란?

CTAS(CREATE TABLE AS SELECT)는 선택(SELECT)된 데이터셋을 새로운 테이블로 생성할 때 사용되는 SQL 구문입니다.

해당 구문은 기존 테이블의 데이터를 기반으로(원본 데이터를 훼손하지 않고) 새로운 테이블을 만들고자 할 때 유용하기 때문에 테이블 백업이나 데이터 분석용 테이블을 생성하는 데 주로 활용됩니다.

 

CREATE TABLE new_table AS
SELECT column1, column2, ...
FROM existing_table
WHERE condition;

 

CTAS는 기본적으로 다음과 같은 구문으로 사용되며, 전체 테이블을 복사하는 데 사용되거나 조건부 데이터를 복사, 테이블 구조만 복사하는 데 사용될 수 있습니다.

 

아래 이어지는 내용을 통해 CTAS 구문의 특징과 위에서 언급한 여러 사용 예시를 간단하게 살펴보겠습니다.

 

 

---

CTAS 구문의 특징

 

1. 생성되는 테이블 구조

새로 생성되는 테이블의 구조는 'SELECT' 구문에 의해 결정됩니다.

때문에 'SELECT * ~' 구문을 통해 대상 테이블의 모든 컬럼을 복사하거나, 'SELECT column1, column2, ...' 구문을 통해 특정 컬럼을 지정하여 복사할 수도 있습니다. 또한 이렇게 생성된 컬럼은 기존 컬럼의 데이터 타입을 기대로 가져가게 됩니다.

 

2. 원본 데이터 유지

CTAS 구문은 새로 생성되는 테이블에 기존 테이블의 전체 데이터 또는 조건부 데이터를 복사합니다.

때문에 기존 테이블의 원본 데이터는 훼손되지 않고 그대로 유지된다는 특징이 있습니다.

 

3. 인덱스 및 제약 조건

CTAS 구문을 통해 생성되는 테이블은 'NOT NULL'을 제외한 기존 테이블의 인덱스나 제약 조건을 상속받지 않습니다.

때문에 필요한 경우 CTAS 구문으로 테이블을 생성한 뒤 인덱스 및 제약 조건을 걸어 주어야 합니다.

 

4. 데이터 타입 제한

'LONG' 및 'LONG RAW' 타입의 컬럼은 복사 시 오류가 발생할 수 있으며, 'CLOB', 'BLOB' 타입의 컬럼은 복사를 지원합니다.

 

/*

LONG: VARCHAR 타입을 확장한 데이터 타입으로 대용량의 문자열을 저장할 수 있으며, 한 테이블에서 하나의 컬럼에만 선언할 수 있습니다.

LONG RAW: RAQ 타입을 확장한 데이터 타입으로 대용량의 바이너리 데이터를 저장할 수 있으며, 한 테이블에서 하나의 컬럼에만 선언할 수 있습니다.

BLOB: 대용량의 바이너리 데이터를 저장하는 데이터 타입으로 한 테이블에서 여러 컬럼에 대해 선언할 수 있습니다.

CLOB: 대용량의 문자열 데이터를 저장하는 데이터 타입으로 한 테이블에서 여러 컬럼에 대해 선언할 수 있습니다.

*/

 

 

---

CREATE TABLE AS SELECT 사용 예시

-- 1. 테이블 및 전체 데이터 복사
CREATE TABLE copy_employees AS
SELECT * 
FROM employees;

 

-- 2. 테이블 및 조건부 데이터 복사
CREATE TABLE copy_employees_deptno_100 AS
SELECT *
FROM employees
WHERE deptno = 100;

 

CTAS 구문을 통한 테이블 생성 시 전체 데이터가 아닌 특정 데이터만 복사하고 싶은 경우 다음과 같이 SELECT 문의 조건절에 원하는 조건을 입력하여 조건부 데이터만 복사할 수도 있습니다.

 

 

-- 3. 데이터를 제외한 테이블 구조만 복사
-- WITH NO DATA 옵션을 지원하는 Oracle의 경우
CREATE TABLE copy_employees_no_data AS
SELECT *
FROM employees
WITH NO DATA;

-- WITH NO DATA 옵션을 지원하지 않는 경우
CREATE TABLE copy_employees_no_data AS
SELECT *
FROM employees
WHERE 1=2;

 

오라클의 경우 'WITH NOT DATA' 옵션을 지원하기 때문에 해당 옵션을 사용하여 데이터를 제외한 테이블 구조만 복사할 수 있습니다.

하지만 'WITH NO DATA' 옵션을 지원하지 않는 MySQL 등의 경우 'WHERE 1=2'와 같이 SELECT에 대한 조건을 거짓(false)으로 줌으로써 테이블의 구조만 복사할 수 있습니다.