MVIEW, 시퀀스

View는 원본 테이블에 가서 데이터를 가져올 후 사용자에게 반환하기 때문에 부하가 많이 발생. (요청할 때 마다 접근)

 

11) Materialized View (MVIEW): 구체화된 뷰

Mview는 사용자가 요청하는 데이터를 가지고 있다가 요청이 들어오면 즉시 사용자에게 보내는 형태로 처리.

View는 데이터가 없기 때문에 실체가 없는 테이블이라고 하며 Mview는 데이터를 가지고 있기 때문에 "실체화된 View"라고 한다. => 사용자가 많고 데이터가 많을수록 Mview를 사용하는 것이 더 효율적이고 성능이 좋다.

 

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

GRANT QUERY REWRITE TO HR;
GRANT CREATE MATERIALIZED VIEW TO HR;

--PDB_SYS로 권한 부여
--GRANT QUERY REWRITE TO HR;
--GRANT CREATE MATERIALIZED VIEW TO HR;

--MATERIALIZED VIEW(MVIEW): 구체화된 뷰 생성
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;

SELECT * FROM M_EMP;

DELETE FROM EMP01 WHERE EMPLOYEE_ID = 250;

SELECT COUNT(*) FROM EMP01; --107 : 데이터를 지워서 적어짐
SELECT COUNT(*) FROM M_EMP; --108 : 개수가 달라짐. 기존 테이블의 데이터로 존재

--DBMS_MVIEW 패키지의 REFRESH구문으로 동기화를 수행, 자동으로 동기화하지 않고 수동으로 동기화를 해서 한번에 적용
--BIGIN부터 END까지 BLOCK설정

BEGIN
    DBMS_MVIEW.REFRESH('M_EMP');
END;
/
--오라클에서는 패키지.프로시저명을 사용

SELECT COUNT(*) FROM M_EMP; --107 : 동기화됨.

--확인
SELECT MVIEW_NAME, QUERY FROM USER_MVIEWS
WHERE MVIEW_NAME = 'M_EMP';

DROP MATERIALIZED VIEW M_EMP;
--변경 시 ALTER 사용 X, 삭제하고 다시 CREATE

 

PARTITION BY

오라클에서 그룹함수를 사용할 때 PARTITION BY를 사용하여 그룹으로 묶어서 연산.

GROUP BY 를 사용하지 않고, 조회된 각 행에 그룹으로 집계된 값을 표시 할 때 OVER 절과 함께 PARITION BY절 사용

--부서 번호와 사원명, 부서별 급여의 합을 함께 출력.
SELECT DEPARTMENT_ID, FIRST_NAME,
SUM(SALARY) OVER (PARTITION BY DEPARTMENT_ID) AS SALARY_TOTAL
FROM EMPLOYEES
WHERE DEPARTMENT_ID BETWEEN 10 AND 30
ORDER BY DEPARTMENT_ID;

--그룹함수([칼럼]) OVER(PARTITION BY 칼럼1, 칼럼2,...[ORDER BY 절]..)

SELECT DEPARTMENT_ID, FIRST_NAME, SALARY,
        SUM(SALARY) OVER(PARTITION BY DEPARTMENT_ID) AS DEPARTMENT_TOTAL,
        SUM(SALARY) OVER() AS SALARY_TOTAL --10~30 SALARY들의 합
FROM EMPLOYEES
WHERE DEPARTMENT_ID BETWEEN 10 AND 30
ORDER BY DEPARTMENT_ID;

--각 부서에 소속된 사원 한명만을 출력, 이때 사원번호, 사원명, 직무번호, 급여, 부서번호
SELECT EMPLOYEE_ID, FIRST_NAME, DEPARTMENT_ID, HIRE_DATE
FROM(SELECT ROW_NUMBER() OVER(PARTITION BY DEPARTMENT_ID ORDER BY EMPLOYEE_ID) AS RNUM,
            EMPLOYEE_ID, FIRST_NAME, DEPARTMENT_ID, HIRE_DATE
            FROM EMPLOYEES) DATA
WHERE DATA.RNUM = 1;

---

시퀀스

레코드를 식별하기 위해 사용

PRIMARY KEY(중복 허용하지 않음)로 지정한 칼럼에 일련번호를 자동으로 부여받기 위해 시퀀스의 생성

 

(1) 시퀀스 생성: 유일(UNIQUE)한 값을 생성해주는 오라클 객체(=>CREATE), 기본키와 같이 순차적으로 증가하는 칼럼을 자동적으로 생성, 보통 테이블 당 하나의 시퀀스

중간이 비어도 채울필요X, 자동으로 MAX값의 다음 값

CREATE SEQUENCE SEQUENCE_NAME
	[START WITH N]
    [INCREMENT BY N]
    [{MAXVALUE N|NOMAXVALUE}]	--최대값//안잡으면 잡을 수 있는 값만큼
    [{MINVALUE N|NOMINVALUE}]	--최소값
    [{CYCLE|NOCYCLE}]			--최대 값까지 간 후에 다시 최소값부터 사용할 것인지
    [{CACHE N|NOCACHE}]

 

① START WITH: 시퀀스의 시작 값을 지정. N을 1로 지정하면 1부터 순차적으로 시퀀스 번호가 증가

② INCREMENT BY: 시퀀스의 증가 값. N을 2로 하면 2씩 증가. START WITH를 1로 하고 INCREMENT BY를 2로 하면 1,3,5,7로 시퀀스 번호가 증가

③MAXVALUE N|NOMAXVALUE: MAXVALUE는 시퀀스가 증가할 수 있는 최댓값을 말한다. NOMAXVALUE는 시퀀스의 값을 무한대로 지정

④MINVALUE N | NOMINVALUE: 시퀀스의 최솟값. 기본값 1, NOMINVALUE를 지정할 경우 최솟값은 무한 소

⑤CYCLE(사이클)|NOCYCLE: 시퀀스 값이 최댓값까지 증가가 완료되면 다시 최솟값에서부터 시퀀스를 시작하도록 하려면 CYCLE, NOCYCLE은 최댓값을 넘어서면 오류(디폴트 값: NOCYCLE)

⑥CACHE(캐쉬) N|NOCACHE: 오라클 서버가 미리 지정하고 메모리에 유지할 값의 수로 디폴트 값은 20, 값 들어갈때 같이 올라간다.

 

--테이블 생성
CREATE TABLE TEST(
    NO NUMBER NOT NULL PRIMARY KEY
);
--테이블 TEST에 사용할 시퀀스 TEST_SEQ 생성
CREATE SEQUENCE TEST_SEQ
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 100000
NOCYCLE
CACHE 2;	--최소값인 1보다 커야한다.

--DROP SEQUENCE TEST_SEQ;	--SEQUENCE는 DROP 다음 다시 CREATE로 수정

INSERT INTO TEST VALUES(TEST_SEQ.NEXTVAL);
--시퀀스를 사용하지 않았을 경우 자동증가하는 값으로 설정하기 위한 쿼리문
INSERT INTO TEST VALUES((SELECT MAX(NO)+1 FROM TEST));

SELECT * FROM TEST;

--시퀀스의 현재값 반환
SELECT TEST_SEQ.CURRVAL FROM DUAL;

--시퀀스의 현재값에서 증가한 값(+1) 반환
SELECT TEST_SEQ.NEXTVAL FROM DUAL;