DB View (데이터베이스 뷰)

1. View 생성

CREATE OR REPLACE VIEW V_PROF
AS
    SELECT PROFNO, NAME, EMAIL, HPAGE
    FROM PROFESSOR;

 

2. Join으로 View 생성

CREATE OR REPLACE VIEW V_PROF_DEPT
AS
    SELECT
        P.PROFNO "교수",
        P.NAME "교수명",
        D.DNAME "소속학과명"
    FROM PROFESSOR P, DEPARTMENT D
    WHERE P.DEPTNO = D.DEPTNO;

 

 

3. inline View

SELECT
    D.DNAME "학과명",
    S.MAX_HEIGHT "최대키",
    S.MAX_WEIGHT "최대 몸무게"
FROM (SELECT DEPTNO1, MAX(HEIGHT) MAX_HEIGHT, MAX(WEIGHT) MAX_WEIGHT FROM STUDENT GROUP BY DEPTNO1) S, DEPARTMENT D
WHERE S.DEPTNO1 = D.DEPTNO;

 

SELECT
    D.DNAME "학과이름",
    A.MAX_HEIGHT "최대키",
    S.NAME "학생이름",
    S.HEIGHT "키"
FROM (SELECT DEPTNO1, MAX(HEIGHT) MAX_HEIGHT FROM STUDENT GROUP BY DEPTNO1) A,
STUDENT S, DEPARTMENT D
WHERE S.DEPTNO1 = A.DEPTNO1
AND S.HEIGHT = A.MAX_HEIGHT
AND S.DEPTNO1 = D.DEPTNO;

 

SELECT
    S.GRADE "학년",
    S.NAME "이름",
    S.HEIGHT "키",
    A.AVG_HEIGHT "평균키"
FROM (SELECT GRADE, AVG(HEIGHT) AVG_HEIGHT FROM STUDENT GROUP BY GRADE) A,
STUDENT S
WHERE A.GRADE = S.GRADE
AND S.HEIGHT > A.AVG_HEIGHT
ORDER BY 1;

 

 

4. Materialized View(MVIEW)

 - 대용량 데이터를 VIEW로 매번 가져오면 리소스 낭비가 심해서 최초 데이터를 조회하면 MVIEW 내부에 저장하고 쓰는 방식입니다.

 - 원본 테이블과 MVIEW 간의 데이터 동기화 문제가 내제되어 있습니다.

CREATE MATERIALIZED VIEW MV_PROF
    BUILD   IMMEDIATE
    REFRESH
    ON DEMAND
    COMPLETE
    ENABLE QUERY REWRITE
AS
    SELECT PROFNO, NAME, PAY
    FROM PROFESSOR
    WHERE DEPTNO IN (101, 102, 103);

 

5. MVIEW INDEX

 - MVIEW는 데이터가 있기때문에 인덱스를 생성할 수 있습니다.

CREATE INDEX IDX_MV_PROF_PAY ON MV_PROF(PAY);

 

6. MVIEW 동기화 방법

BEGIN
    DBMS_MVIEW.REFRESH('MV_PROF');
END;

 

7. MVIEW 조회

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