8장 뷰와 시스템 카탈로그

- 여기서의 뷰는 1장에서의 3단계 아키텍쳐 뷰가 아니라 다른 릴레이션으로부터 유도된 릴레이션!

- 뷰를 만든 목적 : 1 보안 메커니즘 2 복잡한 질의 간단 표현

- 시스템 카탈로그는 시스템 내의 객체에 관한 정보를 가짐 -> 카탈로그를 잘 이해하고 활용하면 효율적이고, 편하다.

 

- 관계 DB에서 뷰는 한 사용자가 DB를 보는 전체 관점이 아니라, 하나의 가상 릴레이션임.

- create table해서 만드는게 기본 릴레이션임. 실제 릴레이션은 디스크에 레코드를 갖고 있는 릴레이션

-> 그럼 뷰는? 실제 레코드를 갖고있지 않음 .create view로 만듦. 창문을 통해 밖의 상태를 쉽게 알 수 있듯이, 뷰는 기본 릴레이션을 바탕으로 정의를 내림. 혹은 또 다른 뷰를 참조할 수도 있음.

 

- 뷰는 레코드를 안 갖고있음. 뷰를 통해서 실렉트 인서트 딜리트 업데이트 다 할 수 있는데, 잠깐기다려 하고 태어날 때 참조한 릴레이션한테 레코드를 받아옴.

→ 뷰에 대한 실렉트문을 요쳥하면 기본 릴레이션에 대한 실렉트문이 됨. → 실렉트문을 통해서 현재상태를 늘 볼 수 있는 것. 뷰는 이 동적인 창문의 역할을 함.

 

- 오라클은 가장 앞서가니까 하고싶은 대로 많이 함 → materialized view가 있음. 실체화or구체화 뷰. 이게 스냅샷 역할을 함. → ibm에서 처음 뷰를 발표했을 땐 이런 개념은 없었다.

- 이 스냅샷은 어느 시점에 어떤 조건을 만족하는 것을 SELECT 해서 저장한 것.

 

- 뷰3는 기본 릴레이션 2개를 사용해서 정의 -> JOIN 조건이 들어가 있을 것

- 뷰4는 기본 렐리에션 + 다른 뷰와 JOIN -> 뷰를 기반으로 뷰를 만든다? 별루당~

 

- 따라서 하나 이상의 소스 테이블을 기반으로 뷰를 정의 -> 실렉트문이 뷰의 정의에 붙고, 저장공간을 요구하지 않음

 

- [위드 체크 옵션]은 생략 가능.

뷰의 이름 다음에 [애트리뷰트들]을 생략하면,  AS SELECT 다음엔 컬럼 하나 이상 열거해야하니까, 이 컬럼들이 뷰의 구성 컬럼이 됨.

-> 컬럼들의 이름을 그대로 따를거면 생략해도 되고, 컬럼 이름을 다르게 부여하겠다 → 쓸 이름을 일일히 적어줌!

 

- 두 개 이상의 다른 릴레이션으로부터 불러온 컬럼들의 이름이 같아서, 어느 릴레이션에서 가져온거냐를 구분하기 위해, 이름을 구분해서 부여하는 것이 가능함.

-> R.A, R.B도 되겠지만, 컬럼 이름엔 .이 들어가면 안되니까. 뷰를 정의할 때 모든 컬럼의 이름을 지정해야함.

+ 한 놈만 이름을 다르게 부여하는건 불가능함. 하나라도 이름을 다르게 부여하려하면 모든 컬럼의 이름을 다 적어줘야함.

 

- 이렇게 뷰를 만들면, DBMS는 아래 실렉트문이 결국 뷰의 정의에 해당하니까 이걸 시스템카탈로그에 저장함.

- EMP_DNO3 이라는건 실제로 안 갖고 있지만, 여기에 대해 질의가 들어오면 실렉트문을 사용해서 질의를 수행함.

 

- 뷰를 통해서는 기본릴레이션의 7개의 레코드 중 2개만. 컬럼 6개 중에선 3개만 볼 수 있음.

뷰를 정의한 이후에 3번 부서에 근무하는 사원이 추가? → 볼 수 있음 / 최종철이 그만뒀다? → 못보는 거고

 

- EMP_PLANNING(SELECT문의 컬럼 3개가 들어잇는 것. 생략되어있음) 이 뷰를 통해서 볼 수 있는 내용은 뭐가 될거냐, 2번에 근무하는 사람들이 누군가?

-> 김창섭, 조민희, 이성래가 될 것

 

 

- 실렉트문이 들어오면 from이 뷰든 릴레이션이든 구분하지 않고 찾아주는데, 찾아보니까 뷰다!

-> 어떻게? 기본릴레이션과 뷰 이름을 동일하게 정의 못함. 구분해줘야함!

→ 뷰의 몸체. 그 실렉트문은 카탈로그에 있을 것.

- 모든 컬럼이다 하는건 뷰의 모든 컬럼이라는거니까, 우측 실렉트문으로 대치가 되고, 뷰 중에서 DNO3과 title=’사원’과 AND를 해줌 → 3427 사원만 보여주게 될 것.

- 질의는 당근 왼쪽이 편함. 아주 복잡한 조인 사용한 실렉트문을 뷰로 정의하고, 뷰에 대해 실렉트문을 던지면 조인사용한 실렉트문보다 쉽다!

 

- 뷰의 장점 1 : 위와 같은 실렉트문을 자주던진다 하면 뷰를 정의해놓고 뷰에 대해서 부장만 검색하자. 아래가 쉽지.

-> 자주던진다 하면 기본릴레이션 사용해서 질의 던지면 긴 실렉트문을 던져야하는데, 뷰에 대해 질의하면 더 단순해진다. 복잡하면 할수록 뷰를 사용해서 질의하는게 훨씬 편할 것이다.

 

- 3427은 최종철인데. 얘가 3번에 근무하는데, 2로 고치자는 것.3번 부서에 근무하는 사람들의 정보를 얻는게 뷰인데. 그 뷰를 업뎃했어. 2로 고치래. 그럼 못본다!

-> 기본릴레이션을 고치는건 아무 문제가 없지만, EMPDNO3은 3번을 검색하는 질의인데, DNO를 2로 고쳐라.

→ 그럼 이제 이 뷰를 통해서 질의 던졌을 때 최종철이는 검색할 수 없음. 이런걸 막으려면 위드 체크 옵션.

 

- 아주 드문 경우인데, EMP릴레이션에 컬럼 6개가 있다 하자. A~F
-> 필요에 의해 두 개로 분해를 했어. 정규화 같은걸 적용해서, EMP1 ABC,EMP2 ADEF 로 분해했음.

 

- 그 전에 임플로이가 있을 때 개발된 수많은 응용프로그램들이 안돌아감. 분해 후 EMP를 날려버렸으니까, 임플로이를 참조하는 프로그램들이 안 돌아갈 것.

→ 쪼개진 둘을 조인해서 뷰를 만들자! 뷰의 이름을 똑같이 임플로이로 부여하고! 그럼 차이가 뭐냐? 기본 릴레이션이었는데 뷰로 바뀐 것.

 

- 그렇다!

 

- 오라클에서는 뷰 앞에 V를 붙임. 인사 시스템이 있는데, 사원들의 번호 입사일자 학력 등 수많은 컬럼들이 있을 것.

-> 테이블을 통째로 회계시스템쪽에서 볼 수 있도록 하지 말고, 사원 번호, 명, 입사일자 정도는 뭐 크게 지장없으니까 이 컬럼 3개만 골라서 뷰를 정의함.

-> 이 뷰를 회계시스템쪽에서 볼 수 있도록 하면, 연동 효과는 거두면서 프라이버시에 민감한 컬럼들은 가릴 수 있음.

 

- 사용자마다 보고자하는 컬럼 모임이 다르다면, 뷰로 필요로 하는 컬럼들의 모임들을 만드는 것을 허용하자

-> 뷰는 실렉트문에 아무 제한이 없음.

 

 

 

- 뷰에 대한 갱신도 자유로이 가능하나? 지금 번호 이름 타이틀 검색하니까. 하늘색 부분만 볼 수 있는데,

-> 뷰에 대한 인서트는 기본릴레이션에 대한 인서트로 변환됨. 컬럼이 6개 있는데 뷰엔 컬럼이 3개재지.

-> 4293 김정수 사원은 줬는데, 나머지 컬럼의 값은 null이 들어갈 수 밖에 없다.

 

테이블 만들 때 널 값 허용하지 않는다면 들어갈 수 없음. 기본키를 포함한 뷰니까 기본키가 값은 준다.

나머지 뷰에 포함되지 않은 컬럼에 대해 not null 로 표현하지 않았다면, 가능함.

 

- 2개의 테이블을 조인한 뷰에서 이 뷰에 대해 이름하고 대리, 250만원을 줬음.

-> 어떨때는 컬럼 몇 개는 임플로이, 어떨때는 디파트먼트에 채워넣게 되는데. 근데 이 기본키가 null이 삽입 불가.

+ 기본키를 포함하는 뷰라고 하더라도 인서트를 허용하지 않음? 이건 잘 모르겠다...

 

- 집단 함수를 포함한 뷰에서는 기본키를 포함하는 뷰라고 하더라도 인서트를 허용하지 않음.

위 실렉트문 실행하면 결과를 얻을 수 있음.

- 이 뷰를 통해 업데이트 한다면,, 평균 300을 만들 방법은 여러가지임.

->DBMS가 알아서 결정하도록 하는건 좀 이상함.

 

 

뷰에 포함되지 않은 컬럼을 정의할 때 not null을 명시했었다? 안되겠지..

한 릴레이션에서 정의됐고, 기본키가 정의되어야함. 갱신이 가능한 뷰는 극히 드물다.

 

결론적으로 이렇게 됨.

아예 오라클같은 상용 DBMS에서는 굉장히 좁은 뷰만 갱신을 허용한다.

갱신이 가능한 뷰 → 조인 사용하면서 기본키 갖고 있으면,,, → DBMS에서 차단함(!)

 

- 카탈로그는 사용자가 직접 검색할 수도 있고 질의최적화모듈 등 DBMS의 구성요소에서 사용됨. DBMS마다 완즈이 다름.

 

- 질의를 던지면 오라클은 이걸 받아서 내부적으로 굉장히 세세한 체크를 다 함.

-> 오라클의 질의최적화모듈은 문법적으로 정확한가, 쉼표 잘 쳤는가, 세미콜론 안빠뜨렸는가, 등등. 그리고 임플로이 릴레이션이 지금 접근한 데이터베이스에 있는가.

- 샐러리에 대해 산술연산을 하는데, 샐러리를 숫자형으로 선언을 했어야해. 그리고 타이틀은 싱글쿼오트로 쌌는데 문자열이었는지, DNO는 정수형인지 이런걸 다 확인해야함.

 

- 중요한 체크는 다 끝났는데, 이 릴레이션의 레코드가 7개가 있는데. 인덱스 쓰지 않고 다 보자. 순차탐색 하자. 과장이냐 과장인데, 부서가 아니네. 그러니까 발견했어도 다 읽어봐야함

 

- 순차탐색하면 시간이 오래걸리기에 인덱스 확인. 그리고 기본키에 인덱스가 있지. 이엠피네임 유니크라 지정해서 인덱스가 있어. 근데 타이틀은 모름.

-> 오라클은 훼어절에서 사용된 타이틀과 디엔오에 대해서 각각 인덱스가 있는지 없는지를 확인할 수 있다.

 

- 실렉트 권한이 없으면 실행 못하게!

- 부서 종류 3가지, 타이틀은 5가지. 그럼 타이틀이 과장인 사람의 개수는 전체 레코드 7개 * 1/5. 1.4개가 과장이야 평균적으로. 이 부서번호는 3가지인데, 총 7개 레코드니까 7 * 1/3 = 2.33

→ 1.4가 더 적음 1.4가 더 유리함.

 

- 우리학교에서 질의 던진다고 하면 학과수가 40개 8000 * 1/40, 휴대폰은 8000명 다 다르니까 8000 * 1/8000 → 1. 휴대폰이 좋은 것. 그 결과가 적을수록 선별력이 높다 → 유리하다.

- 복잡한 질의를 던져도 어떤 질의가 카탈로그에 있는지 확인하고, 어떤 인덱스를 쓰는게 수행시간이 적게 걸릴 것인가를 꽤 정확하게 예측할 수 있다.

→ AND로 결합된 것 중에 선별력이 높은 인덱스를 통해 골라오고 나머지 조건들을 체크한다.

 

- DBMS가 질의 수행하는 방법들 중에서 cost 얘기가 가끔 나오는데, 이 비용은 자원을 적게 쓰는 것.

-> 질의 최적화 모듈이 정확한 (수행방법을) 결정 내릴 수 있도록 스스로 수행성능을 높이기 위해 (자체목적)

시스템 카탈로그에 인덱스 등에 대해 다양한 (통계)정보를 유지한다.

 

- 우상단 알제브라 : 질의 트리 만들고 옵티마이저는 가장 자원 적게쓰는 놈 찾아서 → 실행코드 만들면(런타임 데이터베이스 매니저) → 디스크의 데이터베이스에 접근해서 결과를 구해 옴. 

-> 최적화(옵티마이저)할 때 가장 전적으로 의존하는게 카탈로그.

 

 

- 카탈로그를 dbms가 내부적으로 질의최적화 하는데 엄청 활용하고, 사용자는 카탈로그가 있는지도 잘 모름

+ 카탈로그에 대해서도 질의를 던져서 내용을 확인할 수 있다. 회복기법, 동시성 제어를 여기도 적용할 수 있음.

- 카탈로그 하나가 릴레이션, 애트리뷰트 등등을 다 저장하는게 아니라 이거 각각 저장하는 수많은 카탈로그가 있음. 100개가 넘음. 여기서는 카탈로그를 매우 단순화 해서 설명함.

 

인그리스에서의 구조.

위 : 릴레이션마다 레코드 하나. 릴레이션 id가 기본키 → 한 db에 있는 릴레이션 이름들은 달라야함.

-> 이미 있는 릴레이션하고 같은 이름 테이블 만드려하면 인그리스가 거절할 것.

- RelWidth : 레코드 하나의 길이. 크리에이트 테이블 할 때 컬럼 각각 데이터 타입 지정할텐데 그 길이를 다 더하면 EMP와  DEPT는 36바이트 / 18(4 + 10 + 4)바이트.

 

아래 : 애트리뷰트에 대한 정보 저장하는 시스템 카탈로그. 좌측 두개 컬럼이 모여서 기본키.

- pk밑에 다 s(econd)k 등 뭐 키가 될텐데. 지금 이미지는 기본키와 외래키만 지정했음.

- 이걸 다 마치면 카탈로그에는 어떤게 반영되냐, 테이블 하나 지우면 뭐 해당되는 레코드들 다 지우게 되는 것.

 

- 오라클의 질의최적화기는 얘한테 레코드를 인서트 딜리트하는 용도로 접근하지않고, 임플로이는 레코드가 몇 개 있지? 인덱스는 어느 컬럼에 정의되어있지? 등을 찾아봄.

 

- DBA 할애비가 와도 시스템 카탈로그를 인서트 업데이트 등으로 직접 갱신할 수 없음.

- 소유자인 KIM이 컬럼을 지우려고 데이터 정의어 ALTER TABLE 이렇게 하는게 정상이고, 카탈로그 테이블에서 임플로이의 매니저를 지우려하면 위와 동등하지 않다. 모든 DBMS에서 불가능하다.

→ 데이터베이스의 임플로이 테이블엔 매니저 테이블이 그대로 남아있거든.

 

- 시스템 카탈로그에는 릴레이션을 구성하는 블럭들의 개수, 투플수, 용량, 채우기 비율, 블로킹인수, 투플의 길이 등..뷰의 이름과 정의, 애트리뷰트마다의 정보 등.. 뭐 이런저런 것들을 유지한다.

 

- 사용자는 기본 테이블의 정보가 암호화된 형태로 저장. 카탈로그를 구성하는 기본 테이블에 질의 던질 일은 없고,

데이터 사전 뷰를 접근함.

 

- 이렇게 뷰가 3가지 있음.

 

- 사용자 김이 소유한 테이블이나 뷰에 관한 정보 검색하려고, 이런 방법을 사용해서 질의를 던져보자.

김으로 로그인해서 모든 컬럼을 다 보자. 그럼 이 오너는 인그리스에서 릴레이션 오너하고 비슷한 역할을 하는 것.

 

- 잘 안보이지만, 첫번째 나오는건 무시해도 되는데 임시로 생성하는 테이블 이름. 오라클 내부적으로 쓰는 것.

-> 그래서 보면 오너는 김, 만든게 디파트먼트, 임플로이 → 기본테이블 / 임플로이 플래닝 → 뷰 / 프로젝트 → 기본 테이블 이렇게 테이블의 유형까지 다 보여준다.

 

- 다음 질의. 임플로이 테이블에 어떤 컬럼들이 있지? 인데, 어떤 컬럼들이 있는지 데이터타입은 뭔지 까먹을 수 있음

-> 항상 기억하는게 어려우니까 이걸 필요할 때 가끔씩 접근하는 릴레이션에 대해서,
FROM 유저 탭 컬럼즈 = 사용자의 테이블, 뷰에 속한 컬럼들에 관한 정보, -> 근데 테이블 이름이 임플로이다.

그 뷰에서 테이블 이름, 컬럼이름, 데이터타입을 보겠다.

 

- 다음과 같이 나온다. 테이블 이름은 모두 임플로이고, 컬럼들 보겠다 했으니 컬럼도 7개. 데이터타입도 넘버 캐릭터 캐릭터 넘버.

 

- 뷰를 정의한 실렉트문, 그 몸체를 보고싶다. 실렉트 뷰네임, 텍스트.

- 뷰의 이름은 이엠피 플래닝인데, 텍스트 = 실렉트문 전체는 저렇게 생겼다 하고 보여줌.

-> 그래서 몸체를 잘 알고있어야함. 기억못하면 이렇게 확인하고.

 

 

- 갱신이 가능한지. 하나 인서트 해보자.

-> 결국은 임플로이 테이블에 이 레코드에 대응되는게 들어가야 하는데 사원번호가 포함되지 않으니까 사원번호를 넣어줄 수 없다. 기본키의 값을 입력할 수 없으니 령의 1행에서 시작하는 중 오류발생.

 

- 외래키는 인덱스를 만들자. 그럼 성공 메시지가 나타남.

- 그 다음에, 유저 인덱스는 현재 로그인한 사용자가 소유한 인덱스들에 관한 정보.

저 뷰를 통해서, 인덱스 이름 이니셜 익스텐트 등등을 보는 것.

 

- 하나의 로우가 선택된 것. 3개 7개. 전체 레코드는 7개, 상이한 키의 개수는 3개(1,2,3번부서)

 

 

- 레코드하나를 집어넣었음. 임플로이 테이블에다가 직접 삽입. 3428 김지민 사원 2106 150만원.

-> 그럼 1번부서 2개, 2번부서 3개, 3번부서가 2개 + 이제 2번부서가 4개가 됨. 전체레코드는 8개가 되어야함.

근데 7개네? 왜 7개지?

-> 1/3 * 7 을 구하나, 1/3 * 8 을 구하나, 타이틀을 사용해서 1 / 5 * 8 1 / 5 * 7을 해도 서로 최신의 레코드 개수를 사용해서 비교하나, 옛날 레코드를 가지고 비교하나 결정이 뒤바뀌진 않음.

 

- 그래서 질의 최적화 하는 놈은 어떤 경로를 쓰는게 레코드를 적게 접근하는가만 알면 됨. 실제 레코드를 꼭 몇개 봐야하는가는 중요하지 않음. 상대적으로 선별력이 높은 경로를 접근할 수 있으면 됨.

→ 레코드 하나 넣었다고 즉시 큰일날 것 처럼 카탈로그를 7→8개로 고치면 부담이 큼.

→ 왜? 딜리트할 때 고치고 뭐 할 때 고치고.. 카탈로그 고치는 수요가 엄청 늘어나니까 즉시 고치지 않음.

 

- 이런 명령어를 던지면, 통계정보를 최신화 하는 것. -> 쪼끔 변했을 때 애널라이즈를 던지지 말자.

이런걸 잘 모르면 강박증. 쫌만 바뀌어도 애널라이즈 계속 하면 수행시간이 오래걸릴 수 있음.

 

- 주기적으로 애널라이즈작업을 수행해야하는데 오라클이 자체적으로 샘플링을 스스로 해서 카탈로그에 있는 숫자들이 현실을 부정확하게 나타낸다면 알아서 애널라이즈를 할것.

- 사용자는 대량의 데이터를 입력했다. 그러고 가만히 있으면 통계 정보가 실제하고 많이 다를 수 있음.

→ 그럴땐 바로 애널라이즈 작업을 수행.

 

- 애널라이즈 이후 아까와 같은 실렉트문을 던져보면, 8개로 갱신된 것을 볼 수 있음!

 

- 유저_ind_컬럼즈 : 현재 로그인한 사용자가 소유한 인덱스 및 테리블상의 인덱스에 포함된 애트리뷰트에 관한 정보 → 보통 3~4개정도를 유지하라고 함. 접근하는 테이블이 20개 된다고 하면 각 인덱스 수를 알고잇는건 아니잖아.

 

- 던지면 3개가 나온다. 포지션은 1로 나오는데 나도 잘 모르겠다!

- 캐릭터 렝스는 문자열일 때의 길이. 정수니까 걍 4바이트 해주면 좋을텐데.

-> EMPNO는 길이가 0. ENAME은 유니크라고 해서 인덱스가 만들어지는데 10바이트.

-> DNO 외래키 → 문자열 아니니까 0바이트.

 

- 임플로이에 인덱스 4개가 있구나 이렇게 알 수 있음.