SQL - VIEW(뷰)

 

VIEW(뷰)

: 가상 테이블을 의미하며, 원래 테이블은 데이터가 들어 있지만 VIEW에는 데이터가 없고 원본 테이블에 가서 데이터를 불러오는 SQL Query만 저장되어 있다. 따라서 사용자가 해당 VIEW를 사용하는 SQL 문장을 수행하면 그때 VIEW에 들어 있던 SQL이 수행된다. (예시로 식당의 메뉴판 같이 음식에 대한 요약정보(사진)만 있고 사용자가 메뉴판을 보고 주문을 하면 실제로 음식이 주방에서 나오는 것과 비슷한 원리이다.) 

: 오라클에서 아주 많이 사용되는 데이터 관리용 OBJECT 중에 하나

: 보안과 사용자의 편의성을 위해 사용 (보여주고 싶지 않은 컬럼을 제외하고 일부 컬럼만 보여줄 수 있다.)

: VIEW를 생성하면 사용자가 VIEW에 접근하는 순간 원본 테이블에 가서 데이터를 가져오기 때문에 언제나 정확한 데이터를 가져올 수 있다.

 

※ view ->  물리적 테이블에 근거한 논리적 가상 테이블이다.
               테이블의 특정 부분을 보여준다. 

               저장장소를 거의 사용하지 않는다.

 

 

단순 뷰(SIMPLE VIEW)

: 단순 VIEW는 VIEW를 생성할 서브 쿼리에 조인 조건이 안들어가고 1개의 테이블로 만들어지는 간단한 VIEW를 의미한다.

 

** VIEW를 생성하기 위해서는 CREATE VIEW 라는 권한이 필요하며 SYS 계정으로 권한을 할당해야 한다.

SCOTT>CONN / AS SYSDBA;
GRANT CREATE VIEW TO scott;

 

생성 문법

CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW view [(alias,alias,...)]
AS sub-query
[ WITH CHECK OPTION [CONSTRAINT 제약조건] ]
[ WITH READ ONLY ]

- OR REPLACE : 같은 이름의 VIEW가 있을 경우 삭제 후 다시 생성

- FORCE : 기본 테이블의 존재 여부에 상관없이 VIEW를 생성

- NOFORCE : 기본 테이블이 존재할 경우에만 VIEW 생성, 기본 값이다.

- ALLIAS : 기본 테이블의 컬럼 이름과 다르게 지정한 VIEW의 컬럼 이름을 저정한다.

- WITH CHECK OPTION : 주어진 제약 조건에 맞는 데이터만 입력 및 수정을 허용한다.

- WITH READ ONLY: SELECT만 가능한 읽기 전용 뷰를 생성

 

일반 단순 뷰 생성하기

 

EX)

CREATE OR REPLACE VIEW V_EMP
AS
SELECT EMPNO, ENAME, HIREDATE
FROM EMP;

-> VIEW를 생성한 후 해당 VIEW에 SQL을 수행하면 그때 VIEW 내부의 서브쿼리를 수행하게 된다. 즉, 위 코드와 같이 VIEW를 생성하면 오라클은 해당 VIEW 정보를 딕셔너리에 저장해 놓고 사용자가 이 VIEW를 사용하게 되면 3,4번째 라인에 있는 서브쿼리가 수행되어 원본 테이블인 EMP에 가서 SELECT 절에 있는 컬럼을 가져오게 된다.

이는 평소에 V_EMP VIEW에는 아무런 데이터가 없다는 뜻이다. 따라서 이 VIEW에는 제약 조건이나 인덱스 등을 생성할 수 없다.

 

 

VIEW를 통한 테이터 변경하기

 

CREATE TABLE EX_TABLE
(a NUMBER, b NUMBER);

CREATE VIEW EX_VIEW
AS
SELECT a,b
FROM EX_TABLE;

INSERT INTO EX_VIEW VALUE (1,2);

SELECT * FROM EX_VIEW;

SELECT * FROM EX_TABLE;

 

WITH READ ONLY(읽기 전용 뷰)

 

EX)

CREATE VIEW EX_VIEW2
AS
SELECT a,b
FROM EX_TABLE
WITH READ ONLY;

-> 읽기 전용 뷰는 DML 작업을 수행할 수 없다.

 

-> 앞의 VIEW 코드로 데이터 입력 시

 

 

복합 뷰(COMPLEX VIEW)

: 복합 뷰는 Sub Query 부분에 여러 개의 테이블이 Join되어 생성되는 것이다. 생성 문법은 단순 View와 동일하다.

: JOIN, GROUP BY, HAVING, GROUP FUNCTION 등과 같이 쓰인다.

 

EX)

CREATE VIEW EMP_DEPT
AS
SELECT E.EMPNO, E.ENAME, E.DEPTNO, D.DNAME, D.LOC
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;

SELECT *
FROM EMP_DEPT;

-> 복잡한 서브 쿼리를 생성해서 조회하기 힘들 때 View를 생성해 놓고 간단하게 조회할 수 있다는 것이 View의 큰 편리함이다.  그러나 뷰를 잘못 사용할 경우 성능 저하의 주 원인이 될 수 있다.

 

 

INLINE VIEW(인라인 뷰)

: View는 필요할 때 생성해 놓고 다른 쿼리에서 여러 번 반복해 재사용할 수 있다. 그러나 다른 쿼리에서 사용할 필요 없이 해당 SQL에서만 필요한 VIew일 경우 번거롭게 View를 생성하지 말고 SQL 문장의 FROM 절에 VIew의 서브 쿼리 부분을 바로 적어서 사용 가능하며 이런 View를 Inline View라고 한다.

 

EX)

SELECT E.DEPTNO, D.DNAME, E.SAL
FROM (SELECT DEPTNO, MAX(SAL) SAL
	  FROM EMP
      GROUP BY DEPTNO ) E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;

-> 2번째 줄 FROM절 뒤 부분부터 4번째 까지의 쿼리를 INLINE VIEW라고 한다.

 

INLINE VIEW를 실행해보면

-> 이 결과를 가상의 테이블로 생성하여 다른 TABLE과 JOIN하여 원하는 컬럼을 가져올 수 있다.

 

FROM 절에 있는 INLINE VIEW 쿼리를 수행해서 나온 테이블은 원래 존재하지 않지만 원하는 결과를 만들기 위해 메모리에 임시로 생성한 것이다. 즉, INLINE VIEW를 사용해서 원하는 형태의 테이블을 먼저 만드는 것이 중요하다. 

 

 

VIEW 조회 및 삭제하기

 

-> 위 코드와 같이 VIEW를 조회하고 필요 없는 VIEW를 DROP을 이용해 삭제할 수 있다.

 

 

MATERIALIZED VIEW(MVIEW) - 구체화된 뷰

: VIEW는 데이터를 가지고 있지 않고 SQL문장을 가지고 있다. 따라서 사용자가 VIEW를 조회할 때 해당 SQL 문장이 실행되어 원본 테이블에 가서 데이터를 가지고 온 후 사용자에게 반환하고 데이터를 삭제하는데, 이런 특성이 대용량 VIEW일 경우에는 성능상 문제가 된다. 이럴때 MVIEW를 사용하게 된다. MVIEW는 어떤 결과를 뽑아 내는 Query가 빈번히 사용 될 경우, 데이터베이스의 한 테이블로 저장 하여 그 테이블을 조회 하도록 하는 것이다. 따라서 Query 실행 시간의 수행속도가 향상되게 된다. 실질적 데이터가 존재하는 VIEW인 것이다.

 

 

MVIEW 생성하기

 

MVIEW를 생성하기 위해선 QUERY REWRITE 권한과 CREATE MATERIALIZED VIEW 라는 권한이 있어야 한다.

CONN / AS SYSDBA;
GRANT QUERY REWRITE TO scott;
GRANT CREATE MATERIALIZED VIEW TO scott;
CONN scott/tiger;

CREATE MATERIALIZED VIEW M_EMP
BUILD IMMEDIATE
REFRESH
ON DEMAND
COMPLETE
ENABLE QUERY REWRITE
AS
SELECT EMPNO, ENAME, SAL
FROM EMP;

-- BUILD IMMEDIATE : MVIEW를 생성하면서 즉시 서브 쿼리 부분을 수행해서 데이터를 가져와라

-- ON DEMAND : 사용자가 수동으로 동기화 명령을 수행해서 동기화

** 이 라인은 원본 테이블에 데이터가 변경되었을 경우 MVIEW와 언제 어떻게 동기화를 시킬 것인지에 대한 옵션이며 ON DEMAND, ON COMMIT 옵션이 있다. 

** ON COMMIT 옵션은 원본 테이블에 데이터 변경후 COMMIT이 발생하면 자동으로 동기화 시킨다. 하지만 ON COMMIT 옵션은 원본 테이블에 데이터 변경이 많을 경우 동기화 시키느라 많은 부하를 발생시킬 수 있기 때문에 원본 테이블에 그룹 함수를 사용하거나 MVIEW에 조인이 되는 SQL만 있거나 또는 GROUP BY절에 사용된 컬럼에 COUNT 함수가 사용되는 경우에만 사용 가능하다.

-- COMPLETE : MVIEW 내의 데이터 전체가 원본 테이블과 동기화되는 방법

** REFRESH의 한 방법이며 이 외 3가지가 더 있다. (REFRESH 하는 방법 : COMPLETE, FAST , FORCE, NEVER)

** FAST : 원본 테이블에 새로운 데이터가 입력될 경우 그 부분만 MVIEW로 동기화하는 방법

** FORCE : FAST 방법이 가능한지 살펴보고 불가능하면 COMPLETE 방법을 사용하여 동기화

** NEVER : 동기화 하지 않음

 

-> 위와 같이 MVIEW의 조회가 가능하다. MVIEW는 데이터가 존재하기 때문에 INDEX 등도 생성할 수 있다.

 

 

MVIEW 관리하기

 

수동으로 원본 테이블과 MVIEW 데이터 동기화하기

: 원본 테이블의 내용과 MVIEW의 내용이 다를 때 차이가 나는 부분을 수동으로 동기화 시켜 동일하게 만들어 준다.

 

-> 원본 테이블의 하나의 데이터를 지워주자

-> 원본 테이블과 MVIEW의 내용이 다르다.

-> 다음 코드를 통해 동기화

 

 

MVIEW 조회하기 / 삭제하기

: 현재 사용자가 생성한 MVIEW를 조회하고 싶으면 USER_MVIEWS 딕셔너리르 조회하면 되고 데이터베이스 내의 모든 MVIEW를 조회하려면 DBA_MVIEWS를 조회하면 된다.

 

SELECT MVIEW_NAME, QUERY
FROM USER_MVIEWS
WHERE MVIEW_NAME = 'M_EMP';

 

MVIEW 삭제

DROP MATERIALIZED VIEW M_EMP;