Move Fast

  뷰와 시스템 카탈로그

• 관계 데이터베이스 시스템의 뷰(View)는 다른 릴레이션으로부터 유도된 릴레이션(Derived Relation)으로서 ANSI/SPARC 3단계 아키텍처의 외부 뷰와는 다르다.
• 뷰의 사용목적

- 관계 데이터베이스 시스템에서 데이터베이스의 보안 메커니즘

- 복잡한 질의를 간단하게 표현하는 수단

- 데이터독립성을 높이기 위한 수단

• 시스템 카탈로그는 시스템 내의 객체(기본 릴레이션, 뷰, 인덱스, 사용자, 접근 권한 등)에 관한 정보를 포함한다.
• 시스템 카탈로그를 적절히 활용하면 원하는 릴레이션을 데이터베이스에서 찾고, 그 릴레이션에 어떤 애트리뷰트들이 들어 있으며, 각 애트리뷰트의 데이터 타입은 무엇인가 등을 쉽게 파악할 수 있다.

  뷰의 개요

• ANSI/SPARC 3단계 아키텍처에서 외부 뷰 : 특정 사용자가 보는 데이터베이스의 구조
• 관계 데이터베이스에서의 뷰 : 한 사용자의 전체 외부 뷰 대신에 하나의 가상 릴레이션(Virtual Relation)을 의미
• 뷰는 기존의 기본(실제) 릴레이션(Base Relation)에 대한 SELECT문의 형태로 정의된다.
• 사용자는 여러 개의 릴레이션과 뷰를 사용할 수 있다.
• 뷰는 릴레이션으로부터 데이터를 검색하거나 갱신할 수 있는 동적인 창(Dynamic Window)
• 뷰는 실제 릴레이션이 아니므로, 레코드를 가지지 않는다.

  스냅샷(Snapshot)

• 어느 시점에 SELECT문의 결과를 기본 릴레이션의 형태로 저장해 놓은 것
• 스냅샷은 사진을 찍은 것과 같아서 스냅샷을 정의하는 시점의 기본 릴레이션의 내용이 스냅샷에 반영된다.
• 어떤 시점의 조직체의 현황 (Ex. 몇 년 몇월 시점에 근무하던 사원들의 정보, 재고 정보 등이 스냅샷으로 정의될 수 있다.)
• 정적인 창(Static Window)

  뷰의 정의

• 뷰를 정의하는 SQL문

• 뷰의 이름 다음에 애트리뷰트들을 생략하면 뷰를 정의하는데 사용된 SELECT문의 SELECT절에 열거된 애트리뷰트들의 이름과 동일한 애트리뷰트들이 뷰에 포함된다.
• 아래의 조건에 해당될 경우, 뷰를 정의할 때 모든 애트리뷰트들의 이름을 지정해야 한다.

- 뷰를 정의하는 SELECT절에 산술식 또는 집단 함수에 사용된 애트리뷰트가 있는 경우

- 뷰의 정의에 조인이 포함되어 있고 두 개 이상의 다른 릴레이션으로부터 가져온 애트리뷰트들의 이름이 같아서 뷰에서 두 개 이상의 애트리뷰트의 이름이 같게 되는 경우

• Ex. EMPLOYEE 릴레이션에 대해서 “3번 부서에 근무하는 사원들의 사원번호, 사원이름, 직책으로 이루어진 뷰”를 정의

- 아래의 뷰의 정의에는 뷰의 애트리뷰트들을 별도로 명시했기 때문에 뷰에는 ENO, ENAME, TITLE의 세 애트리뷰트가 포함된다.

• Ex. EMPLOYEE와 DEPARTMENT 릴레이션에 대해서 “기획부에 근무하는 사원들의 이름, 직책, 급여로 이루어진 뷰”를 정의

  뷰를 사용하여 데이터를 접근할 때 관계 DBMS에서 거치는 과정

• 시스템 카탈로그로부터 뷰의 정의에서 사용된 SELECT 문을 검색한다.
• 기본 릴레이션에 대한 뷰의 접근 권한을 검사한다.
• 뷰에 대한 질의를 기본(실제) 릴레이션에 대한 동등한 질의로 변환한다.
• 뷰를 정의할 때 사용한 SELECT문이 시스템 카탈로그에 저장되어 있어 뷰를 참조한 질의가 가능
• 뷰를 정의할 때 사용한 WHERE절 조건 + 뷰를 참조한 질의에서의 WHERE절 조건을 모두 만족해야 한다.

  뷰의 장점

• 뷰는 복잡한 질의를 간단하게 표현할 수 있게 한다.

- 기획부에 근무하는 사원들 중에서 직책이 부장인 사원의 사원이름과 급여를 검색하는 질의를 기본 릴레이션을 사용하여 표현하면 아래와 같이 다소 복잡한 형태의 질의가 된다.

- 뷰(위에서 정의한 EMP_PLANNING사용)에 대해서 같은 결과를 검색하는 질의를 표현하면,

• 뷰는 데이터 무결성을 보장하는데 활용된다.

- 기본적으로 뷰를 통해 투플을 추가하거나 수정할 때 투플이 뷰를 정의하는 SELECT 문의 WHERE절의 기준에 맞지 않으면 뷰의 내용에서 사라진다. (즉, 투플은 삽입 되고, 뷰의 조건까지 맞는지 확인함)

- 이 뷰를 정의할 때 WITH CHECK OPTION을 명시했다고 가정한다.

- WITH CHECK OPTION : 뷰를 통해서 갱신한 결과를 뷰를 통해 보지 못하는 경우, 볼 수 있게 해준다.

• 뷰는 데이터 독립성을 제공한다.

- 뷰는 데이터베이스의 구조가 바뀌어도 기존의 질의(응용 프로그램)를 다시 작성할 필요성을 줄여준다.

- 응용의 요구사항이 변경되어 기존의 EMPLOYEE 릴레이션이 아래의 두 릴레이션으로 분해되었다고 가정하자. 응용 프로그램에서 기존의 EMPLOYEE 릴레이션을 접근하던 SELECT문은 더 이상 수행되지 않으므로, EMP1과 EMP2에 대한 SELECT문으로 변경해야 한다. (질의가 많을수록 시간이 많이 걸린다.)

EMP1(EMPNO, EMPNAME, SALARY),

EMP2(EMPNO, TITLE, MANAGER, DNO)

- 그러나 아래와 같이 EMPLOYEE 라는 뷰를 정의했다면 응용 프로그램에서 EMPLOYEE 릴레이션을 접근하던 SELECT문(기존의 질의)은 계속해서 수행될 수 있다.

• 뷰는 데이터 보안 기능을 제공한다.

- 뷰는 뷰의 원본이 되는 기본 릴레이션에 직접 접근할 수 있는 권한을 부여하지 않고 뷰를 통해 데이터를 접근하도록 하기 때문에 보안 메커니즘으로 사용할 수 있다.

- 뷰는 일반적으로 기본 릴레이션의 일부 애트리뷰트들 또는 일부 투플들을 검색하는 SELECT문으로 정의되므로 뷰를 통해서 기본 릴레이션을 접근하면 기본 릴레이션의 일부만 검색할 수 있다.

- Ex. EMPLOYEE 릴레이션의 SALARY 애트리뷰트는 숨기고 나머지 애트리뷰트들은 모든 사용자가 접근할 수 있도록 하려면 SALARY 애트리뷰트를 제외한 EMPLOYEE 릴레이션의 모든 애트리뷰트를 포함하는 뷰를 정의하고, 사용자에게 뷰에 대한 SELECT 권한을 허가하면 된다.

• 동일한 데이터에 대한 여러 가지 뷰를 제공한다.

- 뷰는 사용자들의 그룹이 각자 특정한 기준에 따라 데이터를 접근하도록 한다.

  뷰의 갱신

• 뷰에 대한 갱신도 기본 릴레이션에 대한 갱신으로 변환된다.
• 갱신이 불가능한 뷰

- 한 릴레이션 위에서 정의되었으나 그 릴레이션의 기본 키가 포함되지 않은 뷰 (기본키!=널값)

- 기본 릴레이션의 애트리뷰트들 중에서 뷰에 포함되지 않은 애트리뷰트에 대해 NOT NULL이 지정되어 있을 때 (갱신 시 널값 허용X)

- 집단 함수가 포함된 뷰

- 조인으로 정의된 뷰

  시스템 카탈로그

• 데이터베이스의 객체(사용자, 릴레이션, 뷰, 인덱스, 권한 등)와 구조들에 관한 모든 데이터를 포함한다.
• 시스템 카탈로그를 메타데이터(metadata, 데이터에 관한 데이터)라고 한다.
• 시스템 카탈로그는 사용자 및 질의 최적화 모듈 등 DBMS 자신의 구성요소에 의해서 사용된다.
• 시스템 카탈로그는 관계 DBMS마다 표준화되어 있지 않아서 관계 DBMS마다 서로 다른 형태로 시스템 카탈로그 기능을 제공한다.
• 데이터 사전(data dictionary) 또는 시스템 테이블(system table)이라고도 부른다.

  시스템 카탈로그가 질의 처리에 활용되는 방식

• SELECT 문이 문법적으로 정확한가를 검사한다.
• SELECT 문에서 참조하는 릴레이션이 데이터베이스에 존재하는가를 검사한다.
• SELECT 절에 열거된 애트리뷰트와 WHERE 절에서 조건에 사용된 애트리뷰트가 해당 릴레이션에 존재하는가를 검사한다.

• 위의 질의에서, SALARY 애트리뷰트가 수식에 사용되었으므로 이 애트리뷰트의 데이터 타입이 숫자형(정수형이나 실수형)인가를 검사하고, TITLE이 문자열과 비교되었으므로 이 애트리뷰트의 데이터 타입이 문자형(CHAR(n) 또는 VARCHAR(n) 등)인지 등을 검사한다.
• 만약 질의에서 DNO = ‘2’ 라는 조건이 WHERE절에 있다면, 위의 데이터 타입을 검사하는 단계에서 걸러진다.
• 위 질의를 입력한 사용자가 EMPLOYEE 릴레이션의 EMPNAME, SALARY 애트리뷰트를 검색할 수 있는 권한이 있는가를 확인한다.
• WHERE 절 조건에 사용된 애트리뷰트(TITLE 애트리뷰트와 DNO 애트리뷰트)에 인덱스가 정의되어 있는지 확인한다.
• 두 애트리뷰트에 각각 인덱스가 존재한다고 가정하자. DBMS가 두 인덱스 중에서 선별력 있는 것(검색되는 투플 수가 적은 것)을 선택하기 위해서는 관계 데이터베이스 시스템에 데이터베이스 외에 추가로 정보를 유지해야 한다.
• 한 릴레이션의 전체 투플 수와 그 릴레이션에 정의된 각 인덱스에 존재하는 상이한 값들의 개수를 유지한다면 어느 인덱스를 사용하는 것이 유리한가를 예상할 수 있다.

- EMPLOYEE 릴레이션의 전체 투플 수가 7일 때, 직책(TITLE) 애트리뷰트에는 사원, 대리, 과장, 부장, 사장의 다섯 가지 값들이 존재한다. 부서번호(DNO) 애트리뷰트에는 1, 2, 3의 세 가지 값들이 존재한다.

- 전체 레코드 수/애트리뷰트에 속한 값의 개수의 확률이 낮을수록 검색 시 디스크 블록에서부터 가져오는 레코드 수가 적으므로 성능 상으로 유리하다.

- 따라서 TITLE 애트리뷰트에 정의된 인덱스가 DNO에 정의된 인덱스보다 대상 투플들을 더 좁혀 주므로 선별력이 높다.

  질의 최적화

• DBMS가 질의를 수행하는 여러 가지 방법들 중에서 가장 비용이 적게 드는 방법을 찾는 과정
• 질의 최적화 모듈이 정확한 결정을 내릴 수 있도록 DBMS는 자체 목적을 위해서 시스템 카탈로그에 다양한 정보를 유지한다.
• 릴레이션을 생성할 때 시스템 카탈로그에서 기능별로 필요한 정보를 세분화하여 저장한다.
• 질의 최적화 모듈이 정확한 수행 방법을 결정하기 위해서는 릴레이션에 관한 다양한 통계 정보가 정확하게 유지되어야 한다.

  관계 DBMS의 시스템 카탈로그

• 사용자 릴레이션과 마찬가지 형태로 저장되기 때문에 사용자 릴레이션에 적용되는 회복 기법과 동시성 제어 기법을 동일하게 사용할 수 있다.
• 시스템 카탈로그는 사용자 릴레이션처럼 SELECT 문을 사용하여 내용을 검색할 수 있다.
• 시스템 카탈로그에는 릴레이션, 애트리뷰트, 인덱스, 사용자, 권한 등 각 유형마다 별도의 릴레이션이 유지된다.
• 릴레이션에 관한 정보를 유지하는 릴레이션의 이름이 SYS_RELATION, 애트리뷰트에 관한 정보를 유지하는 릴레이션의 이름이 SYS_ATTRIBUTE라고 가정하면, 아래와 같은 릴레이션들이 시스템 카탈로그에 존재한다.

  시스템 카탈로그의 갱신

• 어떤 사용자도 시스템 카탈로그를 직접 갱신할 수 없다.
• DELETE, UPDATE, INSERT문을 사용하여 시스템 카탈로그를 변경할 수 없다.
• EMPLOYEE 릴레이션의 소유자인 KIM이 EMPLOYEE 릴레이션에서 MANAGER 애트리뷰트를 삭제하기 위해 시스템 카탈로그에 대해 DELETE 문을 사용하면 DBMS가 거절한다.
  

• 데이터 정의어(DDL)을 통해서만 시스템 카탈로그를 갱신할 수 있다.

ALTER TABLE EMPLOYEE DROP COLUMN MANAGER;

  시스템 카탈로그에 유지되는 통계 정보

• 릴레이션마다 투플의 크기, 투플 수, 각 블록의 채우기 비율, 블록킹 인수(블록당 삽입가능한 레코드 수), 릴레이션의 크기(블록 수) 정보를 유지한다.
• 뷰마다 뷰의 이름과 정의에 대한 정보를 유지한다.
• 애트리뷰트마다 애트리뷰트의 데이터 타입, 크기, 애트리뷰트 내의 상이한 값들의 수, 애트리뷰트 값의 범위, 선택율(조건을 만족하는 투플 수/전체 투플 수)에 대한 정보를 유지한다.
• 사용자마다 접근할 수 있는 릴레이션과 권한에 대한 정보를 유지한다.
• 인덱스마다 인덱스된 애트리뷰트, 클러스터링 인덱스/비 클러스터링 인덱스의 여부, 밀집/희소 인덱스의 여부, 인덱스의 높이, 1단계 인덱스의 블록 수에 대한 정보를 유지한다.

  MS SQL Server의 시스템 카탈로그

• 통계 정보는 UPDATE STATISTICS문을 사용하여 수동으로 갱신할 수 있다.
• SQL Server는 릴레이션의 데이터가 변경될 때 주기적으로 통계 정보를 자동으로 갱신한다.
• 통계 정보를 갱신할 필요성이 있는가를 확인하기 위해서 샘플링 방법을 사용한다.
• 통계 정보가 갱신되는 빈도는 애트리뷰트 또는 인덱스의 크기와 변경되는 데이터의 양에 따라 결정된다.
• 모든 릴레이션들에 대한 구성을 정의하는 데이터를 시스템 테이블(System Table)이라고 부르는 특수한 테이블 집합에 저장한다.
• 사용자나 응용 프로그램이 정보 스키마 뷰, Transact-SQL문 및 함수, 시스템 저장 프로시저 등을 사용하여 시스템 테이블에 저장된 정보를 검색한다.

[ 각 데이터베이스에 있는 주요 시스템 테이블들의 이름과 기능 ]

• 시스템 테이블을 사용하여 사용자 KIM이 소유한 개체들을 찾기 위해서 SQL Server 관리 스튜디오의 편집기 창에서 다음과 같은 질의를 수행한다.
  

- OWNER : 개체를 소유한 사용자의 식별자

- NAME : 개체의 이름

- xtype은 개체의 유형으로서, 아래 항목들 중 하나를 반환한다.

C : CHECK 제약 조건

D : 디폴트값 제약 조건

F : 외래 키 제약 조건

PK : 기본 키 제약 조건

U : 사용자 릴레이션

UQ : UNIQUE 제약조건

V : 뷰

• INFORMATION_SCHEMA를 사용하여 사용자 KIM이 소유한 릴레이션이나 뷰에 관한 정보를 검색하기 위해서 다음과 같은 질의를 수행한다.

• INFORMATION_SCHEMA의 컬럼

- TABLE_CATALOG : 데이터베이스의 이름

- TABLE_SCHEMA : 릴레이션이나 뷰를 소유한 사용자의 식별자

- TABLE_NAME : 릴레이션이나 뷰의 이름

- TABLE_TYPE : 릴레이션의 유형 (BASE TABLE or VIEW 가 될 수 있다.)

• INFORMATION_SCHEMA를 사용하여 사용자 KIM이 소유한 EMPLOYEE 릴레이션의 애트리뷰트 정보를 찾기 위해서 다음과 같은 질의를 수행한다.

- COLUMN_NAME : 애트리뷰트의 이름

- ORDINAL_POSITION : 애트리뷰트의 번호(ID)

- DATA_TYPE : 데이터 타입

- COLUMN_DEFAULT : 애트리뷰트의 디폴트 값

• 뷰가 어떤 select문으로 정의되어 있는지 알기 위해서 아래와 같은 시스템 저장 프로시저를 수행한다.

• 특정 릴레이션이 사용하는 디스크 공간 정보를 알아보기 위해서 아래와 같은 시스템 저장 프로시저를 수행한다.

- name : 릴레이션의 이름

- rows : 투플의 수

- reserved : 예약된 총 공간

- data : 실제로 사용되는 공간

- index_size : 인덱스가 사용하는 공간

- unused : 사용되지 않은 공간

• EMPLOYEE 릴레이션의 부서 번호 애트리뷰트인 DNO에 대해 인덱스를 생성하고, 생성된 인덱스를 통해서 통계 정보를 확인하는 질의

• 통계 정보는 레코드를 가져올 때의 선별력과 연관된다.
• 릴레이션에 정의된 인덱스에 관한 정보를 검색하려면 아래의 질의를 실행

- index_name : 인덱스의 이름

- index_description : 인덱스에 관한 설명

- index_keys : 인덱스가 정의된 애트리뷰트

- 기본 키는 SQL Server가 자동적으로 클러스터링 인덱스를 생성한다.

- UNIQUE 키워드를 사용한 애트리뷰트는 SQL Server가 비 클러스터링이면서 고유한 인덱스(보조 인덱스 - 밀집 인덱스)를 자동적으로 생성한다.

- 외래 키는 CREATE INDEX문을 사용하여 사용자가 명시적으로 인덱스를 정의해야 한다.