[데이터베이스] 8. 뷰와 시스템 카탈로그

뷰

: 가상 릴레이션
기본 릴레이션에 대한 SELECT문의 형태로 정의
=> 데이터 검색, 갱신할 수 있는 동적인 창

• 스냅샷: 어느 시점에 SELECT문의 결과를 기본 릴레이션의 형태로 저장
• 뷰 정의
  : 데이터베이스에 뷰를 정의한 SELECT문 저장
  뷰의 이름 != 기본 릴레이션 이름
  뷰를 정의하는 SELECT문에 ORDER BY절 포함 불가

CREATE VIEW	EMP_DNO3 (EMO, ENAME, TITLE)
AS SELECT	EMPNO, EMPNAME, TITLE	// 중첩 select, 여러 relation 참조 select 가능
FROM		EMPLOYEE
WHERE		DNO = 3;

CREATE VIEW	EMP_PLANNING
AS SELECT	E.EMPNAME, E.TITLE, E.SALARY
FROM		EMPLOYEE E, DEPARTMENT D
WHERE		E.DNO = D.DEPTNO
AND			D.DEPTNAME = '기획';

• 뷰를 사용하여 데이터를 접근할 때 관계 DBMS에서 거치는 과정
  - SELECT문 검색
  - 접근 권한 검사
  - 기본 릴레이션에 대한 동등한 질의로 변환

• 뷰의 장점

1. 복잡한 질의 간단하게 표현 가능

	SELECT	E.EMPNAME, E.SALARY
    FROM	EMPLOYEE E, DEPARTMENT D
    WHERE	D.DEPTNAME = '기획'
    		AND D.DEPTNO = E.DNO
            AND E.TITLE = '부장';

	SELECT	EMPNAME, SALARY
    FROM	EMP_PLANNING
    WHERE	TITLE = '부장';

2. 데이터 무결성 보장
   뷰를 통해 튜플 추가, 수정 시에 뷰를 정의하는 SELECT문이 WHERE절의 기준에 맞지 않으면 뷰의 내용에서 사라짐

	UPDATE	EMP_DNO3
	SET		DNO = 2
	WHERE	ENO = 3427;
	// WITH CHECK OPTION 명시 X: DNO가 2로 업데이트 되었기 때문에 DNO가 3인 사원을 명시한 뷰에서 사라짐
	// WITH CHECK OPTION 명시 O: 위의 UPDATE문 실행 불가. 데이터 무결성 유지

3. 데이터 독립성 제공
   데이터베이스 구조가 바뀌어도 기존의 질의를 다시 작성할 필요성을 줄임
   ex) 기존 EMPLOYEE 릴레이션 -> EMP1(EMPNO, EMPNAME, SALARY) / EMP2(EMPNO, TITLE, MANAGER, DNO)
   EMPLOYEE라는 뷰 정의 시, EMPLOYEE 릴레이션 접근하던 SELECT문 계속 수행 가능

	CREATE VIEW	EMPLOYEE
    AS SELECT	E1.EMPNO, E1.EMPNAME, E2.TITLE, E2.MANAGER, E1.SALARY, E2.DNO
    FROM		EMP1 E1, EMP2 E2
    WHERE		E1.EMPNO = E2.EMPNO;

4. 데이터 보안 기능 제공

• 보안 매커니즘: 뷰의 원본이 되는 기본 릴레이션에 직접 접근할 수 있는 권한 부여 X, 뷰를 통해 데이터 접근
• 기본 릴레이션의 일부만 검색 가능
  ex) EMPLOYEE 릴레이션의 SALARY 애트리뷰트는 숨기고 나머지 애트리뷰트들은 모든 사용자가 접근할 수 있도록 하려면?
  => SALARY 애트리뷰트를 제외하고 EMPLOYEE 릴레이션의 모든 애트리뷰트를 포함하는 뷰 정의, 사용자에게 뷰에 대한 SELECT 권한 허가

5. 동일한 데이터에 대한 여러 가지 뷰 제공
   : 사용자들의 그룹이 각자 특정한 기준에 따라 데이터 접근 가능

• 뷰 갱신: 기본 릴레이션에 대한 갱신으로 변환

1. 한 릴레이션 위에서 정의된 뷰에 대한 갱신

	INSERT INTO	EMP_DNO3
    VALUES (4293, '김정수', '사원');
    
    INSERT INTO	EMPLOYEE
    VALUES (4293, '김정수', '사원',,,);

2. 두 개의 릴레이션 위에서 정의된 뷰에 대한 갱신

	INSERT INTO	EMP_PLANNING
    VALUES ('박지선', '대리', 2500000);
    
    INSERT INTO	EMPLOYEE
    VALUES (,'박지선', '대리', ,2500000, );

3. 집단 함수 등을 포함한 뷰에 대한 갱신 => 갱신 불가능

CREATE VIEW	EMP_AVGSAL (DNO, AVGSAL)
AS SELECT	DNO, AVG(SALARY)	// 집단 함수를 사용한 view 갱신
FROM		EMPLOYEE
GROUP BY	DNO;

// 거절
UPDATE	EMP_AVGSAL
SET		AVGSAL = 3000000
WHERE	DNO = 2;
// 거절
INSERT INTO	EMP_AVGSAL
VALUES (3, 3200000);

• 갱신 불가능한 뷰
  - 한 릴레이션 위에서 정의되었으나 그 릴레이션의 기본 키가 포함되지 않은 뷰 (기본키 null 불가 => 무결성 제약조건 위배)
  - 기본 릴레이션의 애트리뷰트들 중에서 뷰에 포함되지 않은 애트리뷰트에 대해 NOT NULL이 지정되어 있을 때
  - 집단 함수가 포함된 뷰
  - 조인으로 정의된 뷰

관계DBMS 시스템 카탈로그

• 시스템 카탈로그
  : 데이터베이스의 객체(사용자, 릴레이션, 뷰, 인덱스, 권한 등)와 구조들에 관한 모든 데이터
  = 메타데이터 (데이터에 관한 데이터)
  질의 최적화 모듈 등 DBMS 구성요소에 의해 사용.
  관계 DBMS마다 서로 다른 형태로 시스템 카탈로그 기능 제공
  데이터 사전, 시스템 테이블과 동일한 의미

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

	SELECT	EMPNAME, SALARY, SALARY * 1.1
	FROM	EMPLOYEE
	WHERE	TITLE = '과장' AND DNO = 2;

1. SELECT문이 문법적으로 정확한가 검사
2. SELECT문에서 참조하는 EMPLOYEE 릴레이션이 데이터베이스에 존재하는가 검사
3. EMPLOYEE 릴레이션에 SELECT절에 열거된 애트리뷰트와 WHERE절에서 조건에 사용된 애트리뷰트가 존재하는가를 확인
4. SALARY 애트리뷰트가 수식에 사용되었으므로 이 애트리뷰트의 데이터 타입이 숫자형(정수형 or 실수형)인가를 검사, TITLE이 문자열과 비교되었으므로 이 애트리뷰트의 데이터 타입이 문자형(CHAR or VARCHAR)인가 검사
5. 질의를 입력한 사용자가 EMPLOYEE 릴레이션의 EMPNAME, SALARY 애트리뷰트를 검색할 수 있는 권한이 있는가 확인
6. TITLE 애트리뷰트와 DNO 애트리뷰트에 인덱스가 정의되어 있는지 확인
7. 두 애트리뷰트에 각각 인덱스가 존재한다고 가정. DBMS가 두 인덱스 중에서 조건을 만족하는 튜플 수가 적은 것을 선택하기 위해서는 관계 데이터베이스 시스템에 데이터베이스 외에 추가로 정보 유지
8. 한 릴레이션의 전체 튜플 수와 그 릴레이션에 정의된 각 인덱스에 존재하는 상이한 값들의 개수 유지 시, 어느 인덱스를 사용하는 것이 유리한지 예상 가능

• 질의 최적화
  : DBMS가 질의를 수행하는 여러 가지 방법들 중에서 가장 비용이 적게 드는 방법을 찾는 과정
  - 정확한 결정 내릴 수 있도록 DBMS는 시스템 카탈로그에 다양한 정보 유지
  - 릴레이션에 관한 다양한 통계 정보가 정확하게 유지되어야 함
  
• 관계 DBMS 시스템 카탈로그
  : 사용자 릴레이션과 동일한 형태로 저장. 사용자 릴레이션에 적용되는 회복 기법, 동시성 제어 기법 사용 가능
  - SELECT문을 사용하여 내용 검색
  - 릴레이션, 애트리뷰트, 인덱스, 사용자, 권한 등 각 유형마다 별도의 릴레이션 유지
  - SYS_RELATION: 릴레이션 정보 유지, SYS_ATTRIBUTE: 애트리뷰트 정보 유지

  SYS_RELATION: RelId(릴레이션 id), RelOwner(릴레이션 관리자), RelTups(튜플 수), RelAtts(애트리뷰트 수), RelWidth(튜플 길이)
  SYS_ATTRIBUTE: AttreId(애트리뷰트의 릴레이션 id), AttId(애트리뷰트 id), AttName(애트리뷰트 이름), AttOff, AttType(애트리뷰트 유형), AttLen(애트리뷰트 길이), PkorFk(기본키 or 외래키)

• 시스템 카탈로그 갱신
  : 어떤 사용자도 시스템 카탈로그 직접 갱신 X. (DELETE, UPDATE, INSERT문 사용 불가)
  동시 접근 수: 시스템 카탈로그 >>>>> 사용자 릴레이션
  => 오버헤드 크고 자원 소비 큼. DBMS 성능 저하.
  - EMPLOYEE 릴레이션의 소유자인 KIM이 EMPLOYEE 릴레이션에서 MANAGER 애트리뷰트를 삭제

    ALTER TABLE EMPLOYEE DROP COLUMN MANGER;

	// DBMS가 거절
	DELETE FROM	SYS_ATTRIBUTE
	WHERE AttRelId = 'EMPLOYEE' AND AttName = 'MANAGER';

• 시스템 카탈로그 통계 정보
  - 릴레이션: 튜플 크기, 튜플 수, 각 블록 채우기 비율, 블록킹 인수, 릴레이션 크기(블록 수)
  - 뷰: 뷰 이름, 정의
  - 애트리뷰트: 애트리뷰트 데이터 타입, 크기, 애트리뷰트 내의 상이한 값들의 수, 애트리뷰트 값 범위, 선택율 (조건 만족 튜플 수 / 전체 튜플 수)
  - 사용자: 접근 가능한 릴레이션, 권한
  - 인덱스: 인덱스된 애트리뷰트(키 애트리뷰트 or 비 키 애트리뷰트), 클러스터링 인덱스/비 클러스터링 인덱스 여부, 밀집/희소 인덱스 여부, 인덱스 높이, 1단계 인덱스 블록 수
  

오라클 시스템 카탈로그

• 오라클 시스템 카탈로그: 데이터 사전
  - 시스템 테이블스페이스에 저장.
  - 기본 테이블, 데이터 사전 뷰로 구성.
  - 사용자: 기본 테이블의 정보 암호화된 형태로 저장, 직접 접근 X. 데이터 사전 뷰 접근.

• 데이터 사전 뷰
  - DBA_xxx 뷰: 데이터베이스 내의 모든 객체들에 관한 정보
  - ALL_xxx 뷰: 현재의 사용자가 접근할 수 있는 객체들에 관한 정보
  - USER_xxx 뷰: 현재의 사용자가 소유하고 있는 객체들에 관한 정보

• 질의 방식

1. 사용자 KIM이 소유한 테이블이나 뷰에 관한 정보 검색

	SELECT	*
	FROM	ALL_CATALOG
	WHERE	OWNER = 'KIM';
	// OWNER: 사용자 이름, TABLE_NAME: 테이블이나 뷰 이름, TABLE_TYPE: 테이블 유형. 테이블, 뷰

2. 사용자 KIM이 소유한 EMPLOYEE 테이블의 애트리뷰트 정보 검색

	SELECT	TABLE_NAME, COLUMN_NAME, DATA_TYPE // 테이블 이름, 애트리뷰트 이름, 애트리뷰트 타입
    FROM 	USER_TAB_COLUMNS	// 뷰. 애트리뷰트 길이, 널값 허용 여부, 디폴트값 등 검색 가능
    WHERE	TABLE_NAME = 'EMPLOYEE';

3. EMP_PLANNING 뷰가 어떤 SELECT문으로 정의되어있는지 검색

	SELECT	VIEW_NAME, TEXT	// 뷰 이름, 뷰 정의한 SQL문
    FROM	USER_VIEWS;

4. EMPLOYEE 테이블의 부서번호 애트리뷰트인 DNO에 대해 인덱스 생성, 생성된 인덱스 통해서 통계 정보 확인

	CREATE INDEX EMPDNO_IDX ON EMPLOYEE(DNO);

	SELECT	INDEX_NAME, INITIAL_EXTENT, DISTINCT_KEYS, NUM_ROWS, SAMPLE_SIZE, LAST_ANALYZED
	FROM	USER_INDEXES
	WHERE	INDEX_NAME = 'EMPDNO_IDX';

5. EMPLOYEE 테이블에 새로운 튜플 한 개 삽입 후 통계 정보 확인
   => 한 테이블에 튜플이 삽입되자마자 데이터 사전 뷰의 정보 갱신 X. 4번 질의 결과랑 똑같음
6. ANALYZE문을 사용하여 통계 정보 갱신

	// 테이블 통계 정보 갱신: ANALYZE TABLE, 인덱스 통계 정보 갱신: ANALYZE INDEX
	ANALYZE INDEX	EMPNO_IDX
	COMPUTE STATISTICS;

ANALAYZE 명령

ANALYZE	객체_유형 객체_이름
연산	STATISTICS;

• 객체_유형: 테이블, 인덱스
• 객체_이름: 객체 이름
• 연산: COMPUTE 전체 테이블 접근, ESTIMATE 데이터 표본 추출
  => 주기적으로 ANALYZE 작업 수행 (다량 데이터 일괄 작업 처리 시, 바로 ANALYZE 작업 필요)

7. EMPLOYEE 테이블에 정의된 인덱스 정보 검색

	SELECT	*
    FROM	USER_IND_COLUMNS
    WHERE	TABLE_NAME = 'EMPLOYEE';
    // INDEX_NAME: 인덱스 이름, TABLE_NAME: 인덱스 정의된 테이블 이름,
    // COLUMN_NAME: 인덱스 정의된 애트리뷰트 이름, COLUMN_POSITION: 인덱스 정의된 애트리뷰트 위치
    // COLUMN_LENGTH: 인덱스 정의된 애트리뷰트 길이, DESCEND: 정렬 방식(오름차순 또는 내림차순)