[SQL] 임시테이블 생성, 쿼리 단순화, 검색속도 향상, 중복되지 않는 번호 - CREATE TEMPORARY TABLE, VIEW, INDEX, SEQUENE

임시테이블 생성하기 - CREATE TEMPORARY TABLE

데이터를 임시로 보관하는 임시 테이블을 생성할 때 CREATE TEMPORARY TABLE문을 사용한다.

CREATE GLOBAL TEMPORARY TABLE 테이블명
(COL1 DATATYPE,
 COL2 DATATYPE,
 ...)
ON COMMIT DELETE ROWS / ON COMMIT PRESERVE ROWS;

임시 테이블 생성임을 나타내기 위해 CREATE와 TABLE 사이에 GLOBAL TEMPORARY를 기술한다. 임시 테이블은 데이터를 영구 저장하지 않는다.

데이터를 보관하는 주기를 결정하는 옵션을 기술한다.

• ON COMMIT DELETE ROWS : 임시 테이블에 데이터를 입력하고 COMMIT할 때까지 데이터를 보관
• ON COMMIT PRESERVE ROWS : 임시 테이블에 데이터를 입력하고 세션이 종료될 때까지 데이터를 보관

EX ) 사원번호, 이름, 월급을 저장할 수 있는 테이블을 생성하는데 COMMIT할 때까지만 데이터를 저장할 수 있도록 생성하라

CREATE GLOBAL TEMPORARY TABLE EMP37
( EMPNO NUMBER(10),
  ENAME VARCHAR2(10),
  SAL   NUMBER(10))
	ON COMMIT DELETE ROWS;

-- 데이터를 저장
INSERT INTO EMP37 VALUES(1111, 'SCOTT', 3000);

-- 테이블 조회
SELECT * FROM EMP37;

-- 커밋 후 다시 조회
COMMIT;
SELET * FROM EMP37

커밋 후 다시 조회했을땐 선탠된 레코드가 없음을 알 수 있다.

EX ) ON COMMIT PRESERVE ROWS 예제

CREATE GLOBAL TEMPORARY TABLE EMP38
( EMPNO NUMBER(10),
  ENAME VARCHAR2(10),
   SAL NUMBER(10))
   ON COMMIT PRESERVE ROWS;

-- 데이터 저장   
INSERT INTO EMP38 VALUES(1111, 'SCOTT', 3000);

-- 커밋 후 테이블 조회
COMMIT;
SELECT * FROM EMP38;

Oracle developer를 사용시 Oracle 접속을 오른쪽마우스로 클릭하면 접속해제 버튼을 눌러 접속을 해제할 수 있다.

-- 재 접속 후 테이블 조회
SELECT * FROM EMP38;

 

복잡한 쿼리를 단순하게 하기 - VIEW

VIEW의 장점 활용하기

복잡한 쿼리를 단순하게 하기 위해 VIEW를 사용한다.

CREATE VIEW VIEW_NAME
AS
테이블

EX ) 직업이 SALESMAN인 사원들의 사원번호, 이름, 월급, 직업, 부서번호를 출력하는 VIEW

CREATE VIEW EMP_VIEW
AS
SELECT EMPNO, ENAME, SAL, JOB, DEPTNO
FROM EMP
WHERE JOB = 'SALESMAN';

-- VIEW 조회
SELECT * FROM EMP_VIEW;

예제에서 처럼 테이블에서 일부의 컬럼만을 볼 수 있다. 그래서 VIEW는 보안상 공개하면 안 되는 데이터들이 있을 때 유용하다.

• EX ) 사원 테이블에서 커미션은 공개하면 안되는 경우 커미션을 제외한 나머지 컬럼으로VIEW를 생성하고 EMP 테이블 대신 VIEW를 제공

VIEW를 변경해도 실제 테이블에 변경사항이 업데이트 된다

-- MARTIN의 월급을 0으로 UPDATE한다
UPDATE EMP_VIEW SET SAL = 0 
WHERE ENAME = 'MARTIN';

-- MARTIN의 월급이 0으로 바뀐 것을 확인할 수 있다.
SELECT * FROM EMP;

VIEW는 데이터를 가지고 있지 않고 단순히 테이블을 바로 보는 객체이다. VIEW를 쿼리하면 VIEW를 만들 때 작성했던 쿼리문이 수행되면서 실제 원 테이블을 쿼리한다.

-- VIEW를 쿼리
SELECT * FROM EMP_VIEW;

-- 실제로 실행되는 쿼리
SELECT EMPNO, ENAME, SAL, JOB, DEPTNO
FROM EMP
WHERE JOB = 'SALESMAN';

따라서 UPDATE문도 마찬가지로 VIEW를 갱신하면 실제 원 테이블의 데이터가 갱신된다.

복합 VIEW 사용하기

복합VIEW는 VIEW에 함수나 그룹 함수가 포함되어 있는 경우를 의미한다.

	단순 VIEW	복합 VIEW
테이블의 개수	1개	2개 이상
함수 포함여부	포함 안함	포함
데이터 수정 여부	수정 가능	수정 불가능할 수 있음

 

EX ) 부서 번호와 부서 번호별 평균 월급을 출력하는 VIEW

CREATE VIEW EMP_VIEW2
AS
SELECT DEPTNO, ROUND(AVG(SAL)) AS 평균_월급
FROM EMP
GROUP BY DEPTNO;

-- VIEW 조회
SELECT * FROM EMP_VIEW2;

VIEW의 쿼리문에 그룹함수를 사용하고 있다. VIEW 생성시 함수나 그룹 함수를 작성할 때는 반드시 별칭컬럼을 사용해야한다.

 

EX ) 복합 VIEW 데이터 변경하기

UPDATE EMP_VIEW2
SET 평균_월급 = 3000
WHERE DEPTNO = 30;
UPDATE EMP_VIEW2;

-- ERROR : data manipulation operation not legal on this view

이 처럼 복합 VIEW에 대해서는 데이터가 변경되지 않는다. 복합VIEW는 데이터가 수정되지 않지만 복잡한 쿼리를 단순화시킬 수 있다는 장점이 있다.

-- 실제 쿼리
SELECT E.ENAME, E.SAL, E.DEPTNO, V.평균_월급
FROM EMP E, (SELECT DEPTNO, ROUND(AVG(SAL))
						 FROM EMP
						 GROUP BY DEPTNO) V
WHERE E.DEPTNO = V.DEPTNO AND E.SAL > V.평균_월급

-- VIEW 쿼리
SELECT E.ENAME, E.SAL E.DEPTNO, V.평균_월급
FROM EMP E, EMP_VIEW2 V
WHERE E.DEPTNO = V.DEPTNO AND E.SAL > V.평균_월급

 

반응형

728x90

 

데이터 검색속도를 높이기 - INDEX

데이터의 검색속도를 높이기 위해 CREATE INDEX문으로 데이터 베이스 객체인 인덱스를 생성한다.

CREATE INDEX 인덱스명
ON TABLE_NAME(COL1);

EX ) EMP 테이블의 월급 조회시 검색속도 높이기

CREATE INDEX EMP_SAL
ON EMP(SAL);

인덱스가 없는 경우의 데이터 검색

EX )
SELECT ENAME, SAL
FROM EMP
WHERE SAL =1600;

1. 월급(SAL)을 처음부터 스캔
2. 스캔 중 SAL = 1600인 값을 찾아도 그 다음에 더 있을 수도 있기 때문에 테이블 끝까지 스캔한다.

→ 테이블 전체를 FULL로 스캔

 

절대로 중복되지 않는 번호 만들기 - SEQUENE

중복되지 않는 숫자 데이터를 생성하기 위해 SEQUENCE를 사용한다.

# SEQUENCE 만들기

CREATE SEQUENCE 시퀀스 이름
START WITH 시작숫자
INCREMENT BY 증가치
MAXVALUE 최대숫자
NOCYCLE; 

 

EX ) 사원테이블의 사원번호 자동 생성하기

-- EMP 테이블의 테이블 구조만 COPY
CREATE TABLE EMP2
AS
SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE 1 = 2;

-- 사원번호 SEQUENCE 만들기
CREATE SEQUENCE EMPNO_SEQUENCE
START WITH 1000
INCREMENT BY 1
MAXVALUE 9999
NOCYCLE;

-- EMP2 테이블에 새로운 사원들의 데이터 입력
-- SEQUENCE의 다음 번호를 출력 또는 확인할 때는 시퀀스 이름.NEXTVAL을 사용
INSERT INTO EMP2 VALUES(EMPNO_SEQUENCE.NEXTVAL, 'JACK', 3500)
INSERT INTO EMP2 VALUES(EMPNO_SEQUENCE.NEXTVAL, 'JAMES', 4000)

-- EMP2 테이블 확인
SELECT * FROM EMP2