๋ณธ๋ฌธ ๋ฐ”๋กœ๊ฐ€๊ธฐ
ORACLE/SQL

[ORACLE] CTAS CREATE TABLE AS SELECT ์‚ฌ์šฉ :: ๋งˆ์ด์ž๋ชฝ

by ๐ŸŒปโ™š 2020. 3. 24.

CTAS

CREATE TABLE AS SELECT ...

  SUBQUERY SELECT๋ฌธ์„ ์ด์šฉํ•ด์„œ ์ƒˆ๋กœ์šด ํ…Œ์ด๋ธ”์„ ์ƒ์„ฑํ•˜๋Š” ๋ฐฉ์‹์ž…๋‹ˆ๋‹ค. CREATE TABLE AS SELECT์˜ ์•ž ๊ธ€์ž๋ฅผ ๋”ฐ์„œ CTAS๋ผ๊ณ ๋„ ํ•ฉ๋‹ˆ๋‹ค. CTAS๋ฌธ์€ ์ฃผ๋กœ OLAP์ฒ˜๋Ÿผ ๋ถ„์„์ž‘์—…์ด ๋งŽ์€ ํ™˜๊ฒฝ์—์„œ ์ฃผ๋กœ ์‚ฌ์šฉํ•˜๊ฒŒ ๋ฉ๋‹ˆ๋‹ค. ํ•„์š”ํ•œ ๋ฐ์ดํ„ฐ๋ฅผ ์›๋ณธ๋ฐ์ดํ„ฐ์˜ ์นดํ”ผ๋กœ ๋งŒ๋“ค์–ด ์†๋„๋„ ํ–ฅ์ƒ์‹œํ‚ฌ์ˆ˜ ์žˆ๊ณ  ์›๋ฐ์ดํ„ฐ๋ฅผ ํ›ผ์†์‹œํ‚ค์ง€ ์•Š๊ณ  ์ž‘์—…์„ ํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

 

CTAS ์ƒ์„ฑ

Oracle HR๊ณ„์ • EMPLOYEES ํ…Œ์ด๋ธ”์—์„œ 90๋ฒˆ๋ถ€์„œ์—์„œ ๊ทผ๋ฌดํ•˜๋Š” ์‚ฌ์›๋“ค๋งŒ ๋”ฐ๋กœ ๋ชจ์€ ํ…Œ์ด๋ธ”์„ ์ƒ์„ฑํ•˜๊ฒ ์Šต๋‹ˆ๋‹ค.

1
2
3
4
5
6
7
8
9
CREATE
TABLE DEPT_90
AS
SELECT
    EMPLOYEE_ID
    ,FIRST_NAME
    ,SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 90;

 

1
SELECT * FROM DEPT_90;

DEPT_90ํ…Œ์ด๋ธ”๋กœ ์ƒˆ๋กœ ์ƒ์„ฑ ์™„๋ฃŒํ–ˆ์Šต๋‹ˆ๋‹ค.

 

๋Œ“๊ธ€