3-2 Relation Model

https://eglife.tistory.com/62

3-1 Relation Model

 

SQL은 S , Q , L 이렇게 부르기도 하지만 보통 'sequel'로 부르기도 한다.

 

SQL 쿼리문은 직관적으로 사용할 수 있다.

Query가 주어지면(WHAT) 그와 관련해 답을 도출하는 방법(HOW)은 굉장히 많다. SQL은 그 중에서도 Best one optimizer를 찾아준다.

 

이런 What / How의 분리는 Data independence를 높혀준다.

 

결론, 쉽게 쓰고 효율적으로 계산하는 쿼리!  -->  Development of productivity!

 

 

 

- VIEWS

 

View는 가상의 Relation이다.

CREATE VIEW HIGHSCORESTUDENTS(SID,GPA0
AS SELECT S.SID S.SPA
FROM STUNDENTS S
WHERE S.GPA > 3.5

이렇다고 하면, VIEW를 통해 원하는 결과값을 얻을 수 있다. 일종의 바로가기 느낌

VIEW를 수정하고 싶으면 VIEW가 받고 있는 BASE를 찾아서 거기다가 SQL을 날려 수정한다.

 

 

 

스키마 구조에서 External Schema 구조가 결국 View였던 것이다.

우리가 쿼리문을 통해 이리저리 자르고 붙이던 코드결과가 결국 VIEW이다.

 

Physical / Conceptual Schema가 바뀌어도 View는 그대로 살아있다고 하는데.. 요건 뒤에서 확인해보자!

즉, Logical Data Independence!

 

- Views and Security

 

View는 필요한 정보를 보여주고, 그 detail은 숨기는 기능을 한다.

 

- Update on Views

 

View도 결국 또 다른 한 종류의 relation이라 Update( insert/delete/update)가 가능해야 하지만,

특정 TYPE의 view는 업데이트가 되지 않는다! ( = 스포하자면 Join view / 통계치를 포함한 veiw는 업뎃불가!)

 

보통 view delete 는 base에서 delete하고, insert도 마찬가지로 base에서 insert한다.

 

 

 

Materialized View ( 이하 MV )는 Base Table의 Data가 변한다면 어차피 다시 계산이 되어야 한다. 이걸 새로고침마냥 Refresh라고 하는데, 이 때 되려 쿼리시간이 많이 소요될 수도 있다.

 

이 때, 계산이 복잡한 쿼리의 경우엔 View가 계산했던 값을 Storage에 갖고 있다면 쿼리 속도가 높아진다. 업뎃된 Data를 저장중인 계산값에 적용만 시키면 되니까!

 

따라서 View가 차지하는 Storage 공간과 업뎃을 위한 쿼리 계산시간은 서로 Trade-Off 관계에 있다.

 

어쨌든, 기본적으로 MV는 Physically DB에 저장되어 있고, Index/Partitioned 되어 있다.

 

- 현재, Base에 data변화 발생했다. Storage에 View가 저장돼 있으면 새로운 쿼리를 계산할 때 Oracle이 알아서 View를 가져와 계산을 수행하므로 속도가 빨라진다. 만약에 저장된 View랑 계산법이 약간 다르다고 하면 이 역시 View를 쓰는게 나을 지 아닐 지도 알아서 DBMS가 판단해준다.

 

이 Case는 사용자가 Base에 계산 쿼리를 날린 뒤 새 값을 Base에 추가해주었다. 기존 View에 없는 기능이면 사용자가 일일히 따로 Data를 만져줘야 한다. 그리고 refresh를 때리며 끝 -> View에 자동 업데이트 되는건가.. 거기까진 모르겠다.

(맞네 DBMS_MVIEW.refresh는 Materialized View를 갱신하는 명령어--> VIEW를 수동으로 갱신해주는 상황이다.)

 

Option 중 on demand / on committ -> 전자는 즉시 수행, 후자는 스케줄에 따라 나중에 수행Refresh : Fast -> 변경된 데이터만 이용해서 빨리 갱신/ Complete -> Fast가 불가할 때  MV에 저장된 모든 데이터를 다시 계산 / Force옵션은 Fsst 사용 가능한지 자동확인 후 fast 수행! 안 되면 Complete로 가게 한다.

 

 

- SQL 실습

SELECT E.ename, E.sal FROM emp E, dept D
WHERE E.deptno = D.deptno and D.loc = 'NEW YORK';

요런 문장이 주어질 때, HOW가 여러 개이니까 같은 결과를 도출하는 다른 쿼리문도 짤 수 있어야 한다.

Like this yo(좀 얍삽하긴 하지만;;)

SELECT distinct E.ename, E.sal FROM emp E, dept D
WHERE E.deptno = (Select d.Deptno from dept d where d.loc = 'NEW YORK');

 

- VIEW 실습

drop view my_emp;
create or replace view my_emp
as select empno, ename, sal from emp where deptno = 30;

SK VIEW

select * from my_emp where sal > 1500;
--  |  The above query againt VIEW my_emp will be AUTOMATICALLY translated 
--  |  into following query for BASE TABLE emp 
--  V  by Oracle Query Optimizer
select ename, sal from emp where deptno = 30 and sal > 1500;

 

뭐야 ;; base에서 튜플 삭제하니까 View에도 바로 반영이 되네! 계산식 포함 or Join식 View가 아니어서 그런가보다.

 

그것도 확인해보자!

Join View 13rows

select * from user_views where view_name = 'MY_EMP';
select * from my_emp;

 

 

Base case인 emp를 drop시키니까 my_emp는 조회가 되지 않는데, user_view list에는 아직 존재를 한다.

이건 Join_view도 마찬가지다.

 

이 상태에서 emp table을 다시 만들어주면 다시 my_emp / join_view가 동작한다.

 

이것이 바로 VIEW dependency on base table!

 

- View Updatability

-- SINGLE VIEW
insert into my_emp values(9999, 'SIMON',10000);
select * from emp;
select * from my_emp; -- what about 'SIMON'?

View에다가 insert를 한 건데 emp에 자동으로 업데이 된다. 물론 언급 안 한 부분은 NULL(-) 값으로 자동입력된다는 것. 그래도 my_emp에는 나타나지 않는다. view에 걸었던 필터링? 때문에 데이터가 표출되지 않는다.

my_emp와 같이 Single View에는 필터링만 잘 되어있다면 사실 데이터 표출된다. 즉, Data Update가 가능한 형태라는 

 

create or replace view my_emp
as select empno, ename, sal 
from emp 
where deptno = 30
WITH CHECK OPTION;

With Check Option을 하면, 위와 같이 emp에만 업뎃되는 현상이 없다. emp / my_emp view 모두에게 업뎃이 안 된다.

 

insert into join_view values('SIMON','DATABASE');
-- What happens? Why?

--ORA-01776: cannot modify more than one base table through a join view

Join형태의 view에는 업뎃자체가 안 된다. 근데 또 Base 연동해서 삭제는 된다 ;; Join View에서 삭제를 해도 Base에도 자동 삭제가 된다.

 

create materialized view avg_sal_view as
select deptno, avg(sal) avg_sal
from emp
group by deptno; 
select * from avg_sal_view;
insert into avg_sal_view values (40, 1000);

--ORA-01732: data manipulation operation not legal on this view ORA-06512: at "SYS.DBMS_SQL", line 1721

통계값이 들어간 VIEW는 데이터 추가가 안 된다.

게다가 얘는 Base에 data를 삭제해도 따라서 값이 변동하지 않는다.

 

alter table emp add (new number default 0);
alter table emp drop column hiredate;
alter table emp set unused column comm;
select * from emp; 
alter table emp add constraint mgr_fk foreign key(mgr) references emp;

테이블의 각종 스키마를 바꾸는 Logical 스키마를 바꾸는 방법! col을 빼고 넣고, PK/FK를 재정의하고~

 

참고로 PK/FK도 Logical(Conceptual) 스키마에 해당한다.  Physical 스키마는 파일구조,저장소할당,물리적인덱스,엑세스경로.. 등이 해당된다.