기술노트 블로그

* Youtube Link - https://www.youtube.com/watch?v=qA8tflkxop0&list=PLVsNizTWUw7FzFgU1qe-n7_M7eMFA9d-f&index=22

뷰의 개념

SELECT로 조회된 결과는 테이블이다.

이 결과를 테이블로 만든 것이 뷰이다.

뷰를 SELECT하면 결국 SELECT문이 실행되는 것이다.

테이블처럼 접근하는 것이다.

사용자는 뷰를 테이블로 착각하고 접근을 한다.

제약은 있지만 데이터 수정도 가능하다.

뷰의 장점

- 보안에 도움이 된다. (정보를 가리고 보여줄 수 있다.)

- 복잡한 쿼리를 단순화 시켜줄 수 있다. (복잡한 쿼리를 사용을 하면 뷰로 만들어서 처리를 할 수 있다.)

뷰 실습

- 뷰 생성

CREATE OR REPLACE VIEW : 뷰를 생성하거나, 기존의 뷰를 덮어쓴다.

AS "": 뷰를 만들 때 기존의 테이블의 컬럼의 이름을 변경해서 저장할 수 있다.

SELECT에서 띄어쓰기 컬럼을 조회 시 ""(큰따옴표)로 묶어주어야 한다.

- 한글 열 이름 뷰 생성

큰 따옴표를 활용하여 한글 열 이름도 SELECT할 수 있다.

- 뷰 삭제

DROP VIEW: 뷰를 삭제할 수 있다.

- 만든 뷰 보기

SYSTEM 뷰이다.

SELECT * FROM USER_VIEWS: 만든 뷰들을 조회할 수 있다.

- 뷰 업데이트

UPDATE v_userTBL SET addr = '부산' WHERE userID = 'JKW';

뷰의 데이터를 수정할 수 있다.

- 뷰 입력

실제 테이블의 birth 컬럼이 NOT NULL인데 INSERT하는 쿼리에서는 birth 컬럼을 입력하지 않고 있다.

그래서 NULL을 입력할 수 없으므로 오류가 발생한다.

DEFAULT 값을 추가하거나, NULL을 허용하도록 제약조건을 변경하면 가능하다.

- WITH READ ONLY

WITH READ ONLY 옵션을 주면

UPDATE를 뷰에 실행 시 

cannot perform a DML operation on a read-only view 에러가 발생한다.

- 합계 뷰

합계 뷰를 생성할 수 있다.

INSERT는 되지 않는다.

실제 컬럼이 없고, 그 때 그 때 만들어 내는 집계 함수 및 GROUP BY를 사용했기 때문이다.

이를 가상열이라고 부른다.

또는 UNION, DISTINCT는 가상 열을 사용하기 때문에 INSERT가 안 된다.

- 범위 뷰

범위에 해당하지 않는 데이터가 뷰에 INSERT 되는 것을 볼 수 있다.

이러한 경우 WITH CHECK OPTION을 줄 수 있다.

이렇게 WITH CHECK OPTION을 주면

범위에 맞지 않는 데이터는 INSERT되지 않는 것을 볼 수 있다.

그래서 범위 뷰일 시 WITH CHECK OPTION을 주는 것이 바람직하다.

- 복합 뷰

조인된 테이블들의 결과를 저장하는 것이 복합뷰이다.

복합뷰는 INSERT가 되지 않는다.

만일 뷰가 참조하는 테이블을 삭제하면

뷰는 당연히 SELECT되지 않고, 오류를 발생시킨다.

구체화된 뷰

뷰는 가상화된 뷰이다.

구체화된 뷰는 실제 데이터를 가져다 놓는다.

그래서 바로 조회가 된다.

장점은 빨리 가져올 수 있다.

단점은 만일 참조하는 테이블들이 실시간으로 변경이 자주 일어나는 테이블들이라면

구체화된 뷰는 실제 테이블의 데이터를 가져다 놓았는데 그러면 내부적으로 계속 데이터를 변경해서 가져다 놓는다.

그러면 오라클 내부의 성능이 저하될 수 있다.

- 테스트 데이터 생성

CREATE TABLE bigTBL AS SELECT * FROM HR.bigEmployees;

CREATE TABLE smallTBL AS SELECT * FROM HR.Employees;

이렇게 테이블들을 복사했다.

CROSS JOIN을 해서 birth_date의 년도의 평균값을 구하니

시간이 굉장히 오래걸린다.

CREATE MATERIALIZED VIEW: 실체화된 뷰 생성

실제 데이터를 저장하다보니 시간이 오래걸린다.

실제 데이터를 가져와놓은 구체화된 뷰이다보니

조회시간이 거의 0초 수준으로 빠르다.

- BUILD DEFFERED

CREATE MATERIALIZED VIEW mv_AvgYear BUILD DEFFERED AS SELECT문;

BUILD DEFFERED를 사용을 하면 나중에 구체화된 뷰에 데이터를 가져올 수 있다.

그래서 뷰를 SELECT를 해보면 데이터가 아무런 데이터도 조회가 되지 않는다.

EXECUTE DBMS_MVIEW.REFRESH(LIST => 'mv_AvgYear');

를 통해서 구체화된 뷰에 데이터를 가져올 수 있다.

- GENERATED ALWAYS AS 

buyTBL에 sales 가상 열을 추가했다.

GENERATED ALWAYS AS (price * amount); 를 통해서 실시간으로 가상 열이 만들어지도록 판매 실적 가상 열을 생성하였다.

그리고 조회를 해보면 정상적으로 sales 컬럼도 잘 조회된다.

sales 컬럼이 이렇게 생성되었다. 

- BUILD IMMEDIATE, REFRESH COMPLETE, ON COMMIT

BUILD IMMEDIATE: 즉시 빌드됨

REFRESH COMPLETE: 전체 뷰가 새로고침됨

ON COMMIT: 원본 테이블이 COMMIT 되는 즉시 변경됨

실제 테이블인 buyTBL을 UPDATE 후 COMMIT 시

뷰의 결과가 바로 반영되는 것을 볼 수 있다.

SELECT * FROM mv_SumSales;

결과가 바로 반영되었다.

감사합니다.