[오라클] 뷰

뷰 ( VIEW )

 

뷰의 개념

뷰는 한마디로 물리적인 테이블을 근거한 논리적인 가상 테이블이라고 정의할 수 있다
뷰는 기본 테이블에서 파생된 객체로서 기본 테이블에 대한 하나의 쿼리문이다
뷰란 '보다'란 의미를 갖고 있는 점을 감안해 보면 알 수 있듯이 실제 테이블에 저장된 데이터를 뷰를 통해서 볼 수 있도록 한다

뷰는 물리적인 구조인 테이블과는 달리 데이터 저장 공간이 없다. 
뷰는 단지 쿼리문을 저장하고 있는 객체라고 표현 할 수 있다
실행시 오류가 발생하면 권한 문제이기에 SYS로 접근하여 권한 부여

 

뷰의 사용 목적

직접적인 테이블 접근을 제한하기 위해서 사용된다(보안성)
복잡한 질의를 쉽게 만들기 위해서 사용된다(편의성)

 

뷰의 특징 

뷰는 테이블에 대한 제한을 가지고 테이블의 일정한 부분만 보일 수 있는 가상의 테이블이다
뷰는 실제 자료를 갖지는 않지만, 뷰를 통해 테이블을 관리할 수 있다. 하나의 테이블에 뷰의 개수는 제한이 없다.

 

뷰 생성과 조회

뷰를 생성하기 위해서는 테이블 생성과 같이 CREATE문을 사용

 

기본 테이블

뷰에 의해 제한적으로 접근해서 사용하는 실질적으로 데이터를 저장하고 있는 물리적인 테이블을 말한다

> 기본테이블 생성

CREATE VIEW VIEW_EMP01
AS
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, DEPARTMENT_ID
FROM EMP_COPY
WHERE DEPARTMENT_ID=10;

 

뷰 생성

OR RELPACE VIEW

새로운 뷰를 만들 수 있을 뿐만 아니라 기존에 뷰가 존재하더라도 삭제하지 않고 새로운 구조의 뷰로 변경 할 수 있다
기본 테이블의 존재 여부에 상관없이 뷰를 생성한다

WITH CHECK OPTION

해당 뷰를 통해서 볼 수 있는 범의 내에서만 UPDATE 또는 INSERT가 가능
 

뷰에 관련된 데이터 딕셔너리

데이터 딕셔너리 USER_VIEWS에 사용자가 생성한 모든 뷰에 대한 정의가 저장되어 있다
뷰의 이름을 위한 VIEW_NAME이란 칼럼과 뷰를 작성할 때 기술한 서브 쿼리문이 저장되어있는 TEXT 칼럼을 확인하면

SELECT VIEW_NAME, TEXT FROM USER_VIEWS;

 

뷰의 동작원리

• 사용자가 뷰에 대해서 질의를 하면 USER_VIEWS에서 뷰에 대한 정의를 조회
• 기본 테이블에 대한 뷰의 접근 권한을 살핀다
• 뷰에 대한 질의를 기본 테이블에 대한 질의로 변환
• 기본 테이블에 대한 질의를 통해 데이터 검색
• 검색된 걸과를 출력

뷰의 종류

뷰를 정의하기 위해서 사용되는 기본 테이블의 수에 따라 단순 뷰와 복합 뷰로 나뉜다

단순뷰	복합 뷰
하나의 테이블로 생성	여러 개의 테이블로 생성
그룹 함수의 사용이 불가능	그룹 함수의 사용이 가능
DISTINCT 사용이 불가능	DISTINCT 사용이 가능
DML(INSERT/UPDATE/DELETE) 사용 가능	DML(INSERT/UPDATE/DELETE) 사용 불가능

 

단순뷰

 

단순 뷰의 칼럼에 별칭 부여하기

사원번호, 사원명, 급여, 부서번호로 구성된 뷰를 작성하되 기본 테이블은 EMP01로 하고 칼럼명은 한글화 한다.
 
단순 뷰에 DML 명령어로 조작 불가능한 경우

• 뷰 정의에 포함되지 않은 칼럼 중에 기본 테이블의 칼럼이 NOT NULL 제약 조건이 지정되어 있는 경우 INSERT 문이 사용 불가능 하다
• SALARY*12와 같이 산술 표현식으로 저의된 가상 칼럼이 뷰에 정의 되면 INSERT나 UPDATE가 불가능 하다
• DISTINCT을 포함한 경우에도 DML 명령을 사용할 수 없다.
• 그룹 함수나 GRUOP BY 절을 포함한 경우에도 DML 명령을 사용할 수 없다.

 

복합 뷰

두 개 이상의 기본 테이블에 의해 정의한 뷰
 
EX 1 ) 사원번호, 사원명, 급여, 뷰서번호로 구성된 뷰를 작성하되 기본 테이블은 EMP01로 하고 칼럼명은 한글화

CREATE OR REPLACE VIEW VIEW_EMP02 
AS
SELECT EMPLOYEE_ID 사원번호, FIRST_NAME 사원명, SALARY 급여, DEPARTMENT_ID 부서번호
FROM EMP01;

EX 2 ) 부서별 급여의 합, 급여의 평균을 조회 할 수 있는 VIEW_SALARY 뷰 생성

CREATE OR REPLACE VIEW VIEW_SALARY
AS
SELECT DEPARTMENT_ID, SUM(SALARY) AS "SalarySum", TRUNC(AVG(SALARY)) AS "SalaryAvg"
FROM EMP01
GROUP BY DEPARTMENT_ID
ORDER BY DEPARTMENT_ID;

EX 3 )  사원번호, 사원이름, 급여, 부서번호, 부서명 조회할 수 있는 VIEW_EMP_DEPT 생성(INNER JOIN)

CREATE VIEW VIEW_EMP_DEPT
AS
SELECT E.EMPLOYEE_ID 사원번호, E.FIRST_NAME 사원이름, E.SALARY 급여, E.DEPARTMENT_ID 부서번호, D.DEPARTMENT_NAME 부서명
FROM EMPLOYEES E INNER JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;

 EX 3-1 ) USING

CREATE VIEW VIEW_EMP_DEPT
AS
SELECT E.EMPLOYEE_ID 사원번호, E.FIRST_NAME 사원이름, E.SALARY 급여, DEPARTMENT_ID 부서번호, D.DEPARTMENT_NAME 부서명
FROM EMPLOYEES E INNER JOIN DEPARTMENTS D
USING (DEPARTMENT_ID);
DESC VIEW_EMP_DEPT;

 

뷰 수정을 위한 OR REPLACE 옵션

CREATE VIEW 대신 CREATE OR REPLACE VIEW를 사용하면 존재하지 않은 뷰이면 새로운 뷰를 생성하고
기존에 존재하는 뷰이면 그 내용을 변경한다.
 
기본 테이블 없이 뷰를 생성하기 위한 FORCE 옵션을 
기본 테이블이 존재하지 않더라도 뷰를 생성하려면 FORCE 옵션을 추가해야 한다
 

WITH CHECK OPTION

WITH CHECK OPTION 옵션은 뷰 생성시 조건으로 지정한 칼럼 값을 변경하지 못하도록 하는 것이다.
 
1)  생성

CREATE OR REPLACE VIEW VIEW_CHK
AS
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, DEPARTMENT_ID
FROM EMP01
WHERE DEPARTMENT_ID = 20 WITH CHECK OPTION;

2) 변경 > X

UPDATE VIEW_CHK
SET DEPARTMENT_ID = 10
--> 급여가 5000이상인 사원을 10번 부서로 이동하는 쿼리문
WHERE SALARY >=5000;
--> 부서번호에 옵션을 지정하였으므로 이 뷰를 통해서는 부서 번호를 변경할 수 없다.

WITH READ ONLY

WITH READ ONLY 옵션은 읽기 전용이기 때문에 변경 할수 없다
 
1) 생성

CREATE OR REPLACE VIEW VIEW_READ
AS
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, DEPARTMENT_ID
FROM EMP01
WHERE DEPARTMENT_ID = 30 WITH READ ONLY;

2) 변경 > X

UPDATE VIEW_READ
SET SALARY=1000;
--> 읽기 전용 뷰에서는 DML 작업을 수행할 수 없습니다.

 
 

뷰 활용하기

사원중에 최근 입사한 사원 5명만을 얻어 오는 질의문을 작성한다. ROWNUM 칼럼을 이용한다. ROWNUM 칼럼은 오라클에서 내부적으로 부여 되는 INSERTM문에 의해 입력한 순서에 따라 1씩 증가되면서 값이 지정된다.

SELECT ROWNUM, EMPLOYEE_ID, FIRST_NAME, HIRE_DATE
FROM EMPLOYEES;

 
EX ) 최근에 입사한 사원 5명을 출력해주세요.
1) 생성

CREATE OR REPLACE VIEW VIEW_HIRE
AS
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
ORDER BY HIRE_DATE DESC;

> 결과 : 입사일을 기준으로 내림차순으로 정렬을 하였는데도 해당 행의 ROWNUM은 바뀌지 않는다
> 이유 : 데이터가 입력된 시점에서 결정되면 다시는 값이 바뀌지 않기 떼문이다
> 해결 :  새로운 테이블에 입사일을 기준으로 내림차순하여 정렬한 쿼리문의 결과를 저장하면 최근에 입사한 순으로 ROWNUM 컬럼 값이 1부터 부여된다
2) 출력

SELECT ROWNUM, EMPLOYEE_ID, FIRST_NAME, HIRE_DATE
FROM VIEW_HIRE
WHERE ROWNUM BETWEEN 1 AND 5;

OR (서브쿼리)

SELECT ROWNUM, EMPLOYEE_ID, FIRST_NAME, HIRE_DATE
FROM (SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE
      FROM EMPLOYEES
      ORDER BY HIRE_DATE DESC)
WHERE ROWNUM <= 5;

 

인라인 뷰로 TOP- N 구하기

인라인 뷰는 SQL 문장에서 사용하는 서브 쿼리의 일종으로 보통 FROM 절에 위치해서 테이블 처럼 사용 하는 것이다 인라인 뷰란 메인 쿼리의 SELECT 문의 FROM 절 내부에 사용된 서브 쿼리문을 CREATE  VIEW 로 생성하는 것이 아니라  SQL문 내부에 뷰를 정의하고 이를 테이블 처럼 사용한다.
 

 Materialized View(MVIEW) / 구체화 된 뷰

원본 테이블에 데이터가 10억건이고  VIEW로 가져오는 내용일 1억건일 경우 일반적으로 뷰는 원본 테이블에 가서 데이터를 가져온 후 사용자에게 반환하기 때문에 만약 사용자가 4명일 경우 원본 테이블에 접근을 4번 발생시켜야하기에 부하도 많이 발생할 수 있다.

 

MVIEW 를 사용하면 사용자가 요청하는 데이터를 가지고 있다가 요청이 들어오면 즉시 사용자에게 보내는 형태로 처리 된다. 그래서 뷰는 데이터가 없기 때문에 실체가 없는 테이블이라고 하며 MVIEW를 데이터를 가지고 있기 때문에 실체화된 뷰라고 한다. 사용자가 많고 데이터가 많을수록 MVIEW를 사용하는 것이 더 효율적이고 성능도 좋다.

 

MVIEW를 사용하기 위해서는 QUERY REWRITE라는 권한과 CREATE MATERIALIZED VIEW 권한이 필요하다.

 

1) 생성

CREATE MATERIALIZED VIEW M_EMP
BUILD IMMEDIATE  
--> 서브 쿼리 부분을 수행해서 데이터를 가져오라는 의미
REFRESH
ON DEMAND 
--> 원본테이블 변경시 동기화 여부결정. 사용자가 수동으로 동기화 명령을 수행해서 설정
--> ON COMMIT 옵션은 원본 테이블에 데이터 변경 후 COMMIT이 발생하면 자동으로 동기화 시키라는 의미
COMPLETE 
--> MVIEW 내의 데이터 전체가 원본 테이블과 동기화되는 방법.
ENABLE QUERY REWRITE
AS
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY FROM EMP01;