[database] View와 Materialized View

본 게시글은

서울대학교 데이터사이언스대학원 이상원 교수님의

데이터사이언스 응용을 위한 빅데이터 및 지식관리시스템 강의를

학습을 목적으로 재구성하였습니다

---

앞의 수업 내용에서 DB의 개념적, 논리적 스키마와

data independence 개념에 대해 공부하며

view에 대한 내용을 배웠었다

 

오늘은 View와 view의 종류 중 하나인

materialized view에 대해 수업을 진행했다

 

우선 view에 대해서 다시 한 번 보자

view를 만들기 위해서는 SQL문으로

CREATE VIEW를 해주면 된다

 

view를 처음에 만들어줄 때 사용하는 table을

기본 table이라고 해서

Base Relation이라고 한다

 

base table은 우리가 일반적으로 생각하는 table이므로

물리적으로 storage에 저장되어있다

하지만 view의 경우는 base relation으로부터

정보를 저장하는 것이기 때문에

물리적으로 저장이 안되어있다

 

그래서 view를 다른 말로는

virtual relation이라고도 부른다

 

view에서 쿼리를 수행하는 예시이다

위에서 HighStudents라고 해서

gpa가 높은 학생들을 대상으로 View table을 만들었다

 

view table을 이용해서 쿼리문을 작성해도

우리는 원래 base relation인 Students 테이블에서

쿼리를 하는 것과 동일한 결과를 받아온다

 

이렇게 되는 이유는 우리가 view를 이용해서

쿼리를 날리면 DBMS에서 자동으로

그 쿼리를 base relation을 타고 들어가서

해당 조건으로 조회를 하도록 쿼리를 바꾸기 때문이다

 

위 ppt의 예시를 보면

HighStudents라는 view table에서 쿼리를 하면

DBMS는 자동으로

base relation인 Students table에서

gpa가 3.5이상인 학생들의 sid를 불러오는

쿼리로 변경한다

 

 

 

앞의 수업에서도 계속 강조가 되었던 내용이지만

view에서 가장 중요한건 data indepence다

 

view는 external schema라고 외부 스키마인데

주로 user와 상호작용하기 위한 스키마이다

 

data independence의 종류는

physical과 logical 두가지가 있는데

 

physical data independence는

물리적으로 데이터가 저장되는 장소나 형식이 바뀌어도

view나 query와 같은 스키마에는 영향을 주지 않는다는 것이고

logical data independence는

base relation이 변경되더라도 view에는 영향을 미치지 않는다는 것이다

 

그런데..

이건 내가 여기서 잘 이해가 안간건데

view는 물리적으로 실체가 없으므로

view를 통해 쿼리하는 모든건 결국 base relation에서 가져오는건데

영향을 안받는다는게 말이되나?

base relation에서 column이 drop되거나 alter되면

어떻게 view에 영향이 안갈 수가 있는거지? 생각했는데

gpt에 물어보니...

내 생각이 맞더라 ㅋㅋㅋㅋ

 

logical data independence는  

base relation에 새로운 데이터가 

insert, update, delete된다고 하더라도

view는 무조건 최신상태를 받아올 수 있는 것인데

이걸 logical data independence라고 하는 것이고

base relation의 스키마 자체가 변경되는 경우

(alter, drop)가 발생하면

그건 view에서 에러뜬다고한다 

(그럼 독립성이 없는거 아님?)

 

 

 

아무튼 이전 수업시간에도 한 번 나왔었는데

view를 사용하면 DB의 보안을 지킬 수 있다

 

사용자에게 전체 테이블을 보여주고 싶지 않을 때

일부만 보여주는 것이 가능하기 때문이다

전체 table에서 특정 column 혹은 특정 tuple만

접근하도록 할 수 있는

content-based access control이 가능하다

 

 

그렇다면 view를 이용해서 데이터를 변경하는

insert, delete, update는 가능할까?

 

이상적으로는 모든 변경이 가능해야하지만 그렇지는 않고 

특정 조건에 대해서는 변경이 안된다고한다

 

가장 대표적인 안되는 사례는

Aggregate Function인 Sum, Avg, Count 등이

포함되는 경우이다

 

그래서 oracle에서는 view에서 insert 혹은 delete를 수행할 때

view의 조건을 항상 만족하는지 체크하는

WITH CHECK OPTION이 있다

 

WITH CHECK OPTION을 사용하면

view의 조건을 벗어나는 쿼리를 방지할 수 있다

 

 

 

그럼 위에서 sum이나 avg같은 aggregate function이 있는 경우

view에서의 dml 쿼리가 불가능하다고 했는데

그럼 view를 통해서 aggregate function을 수행할 수 있는 다른 방법은 없을까?

 

그 방법이 바로 materialized view이다

 

Materalized View는 일반적인 view와 달리

쿼리의 결과를 실제 table에 저장하여

크고 복잡한 데이터의 연산을 빠르게 처리할 수 있는 view이다

 

여기서 가장 기억해야할 점은

Materialized View는

실제 물리적인 table이 있다는 점이다

 

현재 시점의 Table을 대상으로 복잡한 쿼리를 수행한 후

그 결과를 table에 미리 저장해두는 것인데

일반적으로 min, max, average, sum과 같은

통계와 관련된 작업들을 자주 수행한다

자주 사용되는 통계 데이터에 대해서

Materialized View로 저장을 해놓는 것이다

 

앞의 설명을 들었으면 당연히 알겠지만

Materialized View(MV)는

당연히 trade off가 존재한다

MV를 생성하는 이유는

복잡한 쿼리, 특히 통계와 같이 연산에 관련된 것은

매번 필요할때마다 수행하게 되면

수행 시간도 오래걸리고 컴퓨터 자원도 많이 사용하게된다

 

그래서 MV를 만든다는 것은

복잡한 쿼리를 수행할 때마다 매번 발생하는 컴퓨팅을

storage에 미리 저장해둬서 복잡한 컴퓨터 연산을

피하거나 단축시키겠다는 뜻이다

 

하지만 당연히 이것에 대한 단점은

하드디스크의 용량을 그만큼 차지한다는 것이다

왜냐면 MV는 물리적으로 저장되는 table이기 때문이다

 

 

 

위 쿼리는 Materialized View를 만드는 예시이다

MV의 이름은 product_sales_mv이고

밑에 옵션을 간단하게 살펴보자

 

BUILD DEFERRED는 지금 즉시 데이터를 로드하지말고

refresh 명령어를 실행했을 때

view에 데이터를 채워달라는 뜻이다

 

REFRESH COMPLETE는 전체 데이터를 삭제하고

다시 insert하는 방식을 뜻하는데

비효율적일수있지만 정확한 데이터를 반영할 수 있다

 

마지막 ENABLE QUERY REWRITE는

optimizer가 기존 쿼리를 받았을 때

MV를 사용하여 자동으로 최적화할 수 있도록

허용하는 것이다

 

또한, MV는 일반 base table과 동일하게

물리적으로 저장공간을 차지하는 테이블이기때문에

index table 생성도 가능하고

데이터가 커지면 partition도 가능하다

 

 

위는 오라클에서 MV를 사용하는 시나리오이다

쿼리를 위에서부터 한 번 자세히 살펴보자

 

우선 scott/tiger라는 계정으로 로그인을 해준다

그런 다음 scott에게 Materialized View를 생성할 수 있는 권한을 준다

 

그런 다음 Materialized View를 생성하는데

BUILD IMMEDIATE는 즉시 데이터를 넣어달라는 뜻이고

REFRESH FORCE는 업데이트가 수행되었을 때

Fast(변경된 데이터만)와 Complete(전체 다시 계산) 중에서

가능하면 Fast로 하지만 안될 때 Complete로 계산해달라는 뜻이다

ON DEMAND는 데이터가 변경되었을 때

명시적으로 refresh 명령을 수행해야만 변경된 데이터가 반영이 되는 것이고

ON COMMIT으로 해주면 자동으로 반영해준다

그리고 마지막 ENABLE QUERY REWRITE은

위에서 말했다시피 쿼리를 수행했을 때 optimizer가 알아서

MV를 사용하도록 설정해주는 것이다

 

그런 다음

EXEC DBMS_STATS.gather_table_stats문을 이용해서

optimizer가 MV를 효율적으로 사용할 수 있도록

통계정보를 업데이트해준다

 

그런 다음 SELECT문을 통해서 emp table을 사용하여

부서별 급여 합계를 계산하는 쿼리를 작성한다

그리고 이 쿼리를 수행했을 때

base table인 emp에서 수행하는지

emp_aggr_mv에서 수행하는지 체크한다

 

그런 다음 아래에서 emp 테이블에 새로운 row를

insert해준다

하지만 우리가 MV를 생성할 때

ON DEMAND로 생성해줬기 때문에

데이터를 insert하더라도 refresh 명령을 수행하지 않으면

새로 추가된 row가 반영되지 않는다

 

그래서 아래에서 EXEC DBMS_MVIEW.refresh를 통해

새로 insert한 row를 MV에 반영해준다

 

그러너 다음 다시 SELECT문을 사용해 조회하면

아마 해당 조회는 emp_aggr_mv를 사용해

조회할 확률이 더 높아질것이다

 

 

 

그렇다면 MV 사용 시 고려해야할

기술적인 issue들을 알아보자

 

우선 가장 먼저 고민해야하는 것은

어떤 view를 MV로 만들것인가이다

 

복잡한 쿼리가 자주 시행되는 table인지

Group by, Join과 같은 aggregate function이

자주 사용되는 table인짖

혹은 변경이 자주되면 refresh를 자주해줘야되어서

MV 생성에 적절하지 않은 view일수있기때문에

변경 빈도도 고려해줘야할 요소가 된다

 

다음으로는 MV가 있으면 쿼리 작성 시

우리가 MV를 자동으로 사용할 수 있으며

MV를 사용하여 최적화 할 수 있는가?이다

 

마지막으로는 갱신에 관한 고민이다

MV를 생성할 때는 기존 테이블에서 변경되는 데이터에 대해

어떤 방식으로 갱신할 것인지

충분히 고민해야한다

 

 

 

MV말고도 2020년대에 들어서

새롭게 나온 view에 대해 알아보자

 

oracle에서 새롭게 지원하는 view는

Oracle JSON Relational Duality라고

RDB형식으로 저장되어있는 데이터를

JSON 형식으로 쓰고 읽을 수 있도록 해주는 기능이다

내부에서는 table 형태로 저장하지만

읽을 때, 쓸 때는 모두 json 형식으로 변환해서

json 형식으로 데이터가 저장되어있는 것처럼 보이게 한다

 

아래는 Normalized table와 Nested table에 대한 예시인데

RDB에서 정규화에 대해서 많이 들어봤을 것이다

RDB 정규화에서 가장 핵심이 되는 개념은

중복방지인데, 이 중복방지를 위해

데이터를 여러 table로 쪼개서 나눠서 저장하게된다

이게 바로 normalized table이고

하지만 이런 방식으로 하면 join 연산을 자주 수행해야해서

쿼리 성능을 악화시킬 수 있다

따라서 join 연산이 많이 수행되는

특정 table에 한해서

중첩된 관계를 저장하도록 허용하는 것이 nested table이다

 

 

 

그렇다면 기존에 view가 존재하는 base table을

drop하면 어떻게 될까?

해당 base table만 사라지고

view는 삭제되지않는다

 

따라서 DROP TABLE할 때 뒤에 CASCADE 옵션을 넣어줘야

table과 함께 view도 삭제가 가능하다

 

그리고 잠깐 Delete와 Truncate와 Drop의 차이점에 대해서 살펴보자

다들 알겠지만 delete는 table 내부의 데이터에 대해 삭제하는 sql이고

drop은 테이블 자체를 삭제하는 sql이다

 

delete를 할 때 where를 사용하지 않으면

table에 있는 전체 row가 삭제되게된다

그렇다면 truncate은 무엇일까

truncate은 drop과는 다르게 테이블 자체를 삭제하지않고

테이블의 스키마는 그대로 유지하면서 내부 데이터만

초기화시키는 명령어이다

 

where를 쓰지 않은 delete와 결과는 똑같지만

수행속도는 truncate가 훨씬 빠르다

 

 

 

지금까지 배운 내용에 대해서 간단하게 정리해보자

 

Relational Model의 structure는

tablular 형식으로 데이터를 표현하는 방식이다

 

그런 다음 sql이라는 쿼리 언어를 통해

데이터를 조회 및 변경한다

 

또, DBMS에는 각종 constraint가 있는데

pk, fk에 대한 내용들을 배웠었다