[DataBase] (Oracle vs MySQL) CTAS(Create Table As Select), INSERT INTO SELECT, TEMP Table, View + with절,Inline view

이번 포스트에는 oracle과 MySQL에서 각각 CTAS, INSERT INTO SELECT가 어떻게 사용되는지, TEMP table과 View의 개념과 장단점 그리고 Oracle과 MySQL에서는 어떻게 다른지에 대해 포스팅 하겠습니다.

---

1. CTAS(Create Table As Select)

CTAS의 기본 개념은 MySQL과 오라클 모두 같다.

1) 기본 문법

CREATE TABLE 'target' AS
SELECT *
FROM 'origin';

--AS 이후에는 기존의 조회쿼리처럼 원하는 컬럼만 지정하거나 조건절 및 정렬도 가능하다.

2) 데이터는 복사하지 않기

CREATE TABLE 'target' AS
SELECT *
FROM 'origin'
WHERE 1=0;

MySQL

기본 문법

데이터 복사하지 않기

Oracle CTAS

기본 문법

데이터 복사하지 않기

	Oracle	MySQL
기본 문법	CREATE TABLE 'target' AS SELECT * FROM 'origin';
데이터 복제 X	where 1=0 처럼 거짓 조건을 준다.

---

2. INSERT INTO SELECT

INSERT INTO SELECT의 기본 개념은 MySQL과 오라클 모두 같다.

1) 기본 문법

INSERT INTO 'target'
SELECT *
FROM 'origin';

-- 마찬가지로 SELECT에 원하는 컬럼을 지정할 수 있다.

Oracle

MySQL

2) 테이블이 완전 동일하진 않을 때

origin 테이블의 데이터가 target테이블의 컬럼정의에 벗어나지만 않는다면, 데이터가 삽입 될 수 있다.

 

Oracle

INSERT INTO test2 (col2)
SELECT col1
FROM test_ctas2;

--컬럼의 이름이 달라도 되며, test2의 col2컬럼의 데이터 타입이 VARCHAR(5)이여도
--데이터 길이는 1인 String값이므로 데이터가 부어진다.

MySQL

*actor_F2 테이블의 first_nm 컬럼이 정의한 길이보다 긴 값이 들어오면 에러가 난다.

	Oracle	MySQL
기본 문법	INSERT INTO 'target' SELECT * FROM 'origin';
컬럼 지정 및, 동일하지 않은 컬럼정의	INSERT INTO 'target' (target column,...) SELECT origin column,... FROM 'origin'; *origin 테이블의 컬럼정의를 벗어나지만 않으면 된다.

---

3. TEMP Table

1) 개념

임시테이블,

oracle - 세션이 끊기기 전까지 데이터가 유지

MySQL - 세션이 끊기기 전까지 테이블이 유지

 

2) 장점

• Temp Table을 사용 하는 경우는 복잡한 Query를 실행할 때 임시 테이블을 사용하여 성능을 향상시킬 목적으로 사용
• 예를 들어 여러 개의 복잡한 Query를 실행하면 반환된 각 행에 대한 테이블이 여러 번 액세스되므로 상대적으로 속도가 저하
• 복잡한 Query에서 값을 Temp Table 저장한 다음 Temp Table에서 Query를 실행하는 것이 속도가 훨씬 빠름
  (원하는 데이터셋의 테이블을 만듦으로써 스캔을 줄이는 원리)

3) 활용하는 case

데이터 처리 시 중간 단계의 결과를 저장하고, 그것을 기준으로 다음 단계에서 계속 데이터를 처리하기 위해 이용

임시 테이블은 스테이징 테이블(Staging Table)이라고도 부른다

1. 여러 단계를 거쳐 동일한 데이터에 반복작업을 해결할 때
2. 복잡한 쿼리를 단순화 시킬때
3. 서버상의 커서(Cursor) 사용으로 인한 부하를 줄이고자 할 때

Oracle

1) 기본 문법

CREATE GLOBAL TEMPORARY TABLE 'target'(ON COMMIT option) AS
SELECT *
FROM 'origin';

--ON COMMIT DELETE ROWS(DEFAULT) 커밋시, 데이터를 지운다.(TRUNCATE 된다.)
--ON COMMIT PRESERVE ROWS 커밋시, 데이터를 보존한다.

--oracle에서는 TEMP Table을 CTAS로 만들면 데이터를 붓지않는다.
--ON COMMIT DELETE ROWS(DEFAULT) 일때만
--ON COMMIT PRESERVE ROWS 구문으로 CTAS 하면 데이터를 붓는다.

CREATE GLOBAL TEMPORARY TABLE my_temp_table (
  id           NUMBER,
  description  VARCHAR2(20)
)
ON COMMIT PRESERVE ROWS;

-- 기존 테이블 생성 하듯 임시 테이블 만드는 방법

CREATE GLOBAL TEMPORARY TABLE SALES_TMP ON COMMIT PRESERVE ROWS AS
SELECT *
FROM SH.SALES;

SELECT *
FROM SALES_TMP;

--COMMIT 이후에도 데이터는 유지되며, 세션이 끊겨야 데이터가 사라진다.

2) 특징(oracle)

• Temp Table 데이터는 해당 세션이나 트랜잭션에서만 볼 수 있음
• Global Temp Table은 모든 세션에서 볼 수 있음
• Local Temp Table은 이를 생성한 세션이 끝나면 더 이상 유지되지 않음
• Temp Table 데이터는 세션이 사용하는 정렬 공간 내에 저장되며, 정렬 공간이 데이터를 수용하기에 충분하지 않으면 사용자의 임시 테이블스페이스에 공간이 할당
• Temp Table에 대한 인덱스의 범위 및 지속 기간은 대응하는 테이블의 범위 및 지속 기간과 동일

MySQL

CREATE TEMPORARY TABLE actor_tmp AS
SELECT *
FROM actor;

 

CREATE TEMPORARY TABLE my_temp_table (
  id           int,
  description  VARCHAR(20)
);

-- 기존 테이블 생성 하듯 임시 테이블 만드는 방법

• 오라클과 다르게 CTAS로 임시 테이블 생성 시 데이터가 부어짐
• 오라클과 다르게 commit시에도 데이터는 유지가 됨
• session이 종료시 테이블 자체가 삭제

---

with절

• with절은 동일한 SQL이 반복되어서 사용될 때 성능을 높이기 위해 사용
• table을 만들지 않고도 table 만든 것과 같은 효과를 내는데,실제로는 temp라는 임시 테이블에 저장되는 것
• 사용법은 with 'origin' as ( 임시 테이블에 넣을 데이터) 임시 테이블을 이용해 사용할 쿼리

WITH SALES_TMP AS
(
SELECT *
FROM SH.SALES
)
SELECT PROD_ID,CUST_ID
FROM SALES_TMP
WHERE TIME_ID BETWEEN '1998/01/01' AND '1998/02/01';

 

장점

• temp라는 임시 테이블을 사용해서 장시간 걸리는 쿼리의 결과를 저장해놓고 저장해놓은 데이터를 엑세스하기 때문에 성능
• 임시로 테이블을 생성해서 쿼리를 해야할때에 굳이 테이블을 생성하지 않고 with절을 사용해서 임시로 테이블을 생성

단점

• with절을 너무 남발해서 같은 시간에 여러 개의 with절을 동시에 돌리면 temp(임시 테이블)가 견딜 수 있는 정도가 넘어가서 다같이 느려짐

---

4. View

1) 개념

• 하나 이상의 테이블 (또는 다른 뷰)에서 원하는 모든 데이터를 선택하여, 그들을 사용자 정의하여 나타낸 것
• 뷰는 사용자에게 접근이 허용된 자료만을 제한적으로 보여주기 위해 하나 이상의 기본 테이블로부터 유도된 이름을 가지는 가상 테이블

2) 장점

• 논리적 데이터 독립성을 제공
• 동일 데이터에 대해 동시에 여러 사용자의 상이한 응용이나 요구를 지원
• 사용자의 데이터 관리를 간단하게 해줌
• 접근 제어를 통한 자동 보안이 제공(보안이 가장 큰 장점)

3) 활용하는 case

• view 는 오브젝트이며 dba_objects 테이블에서 정보를 확인할 수 있음
• view는 원본테이블과 동기화되며, 테이블 스페이스를 차지하지 않음
• view에서 데이터를 지우나 원본테이블에서 데이터를 지우나 동기화 됨
• read only만도 가능(with read only)(원본테이블의 데이터와 동기화는 여전히 됨)(mysql은 지원하지 않는다.)
• 수,union,groupby를 사용한 뷰는 ddl불가
• 조인만 사용한 복합뷰는 제한적으로 사용가능

4) inline view(subquery, view와의 차이점)

인라인 뷰는 SQL문이 실행될 때만 생성되는 동적인 뷰라고 생각하면 된다.

 

Oracle

기본 문법

CREATE VIEW SALES_VW AS
SELECT *
FROM SH.SALES;

select *
FROM SALES_VW;

MySQL

CREATE VIEW actor_vw AS
SELECT actor_id
	  ,first_name
      ,REGEXP_REPLACE(last_name,'[a-zA-Z]','*') as last_name
      ,last_update
FROM actor;

SELECT *
FROM actor_vw;