8 제장오라클 81 8.1 뷰 8.2 관계 DBMS의시스템카탈로그 8.3 오라클의시스템카탈로그연습문제
뷰와시스템카탈로그 관계데이터베이스시스템의뷰 (view) 는다른릴레이션으로부터유도된릴레이션 (derived relation) 으로서 ANSI/SPARC 3단계아키텍처의외부 뷰와다름 뷰는관계데이터베이스시스템에서데이터베이스의보안메카니즘으로서, 복잡한질의를간단하게표현하는수단으로서, 데이터독립성을높이기위해서사용됨 시스템카탈로그는시스템내의객체 ( 기본릴레이션, 뷰, 인덱스, 사용자, 접근권한등 ) 에관한정보를포함 시스템카탈로그를적절히활용하면원하는릴레이션을데이터베이스에서 찾고, 그릴레이션에어떤애트리뷰트들이들어있으며, 각애트리뷰트의데이터타입은무엇인가등을쉽게파악할수있음 2
뷰의개요 8.1 뷰 ANSI/SPARC 3단계아키텍처에서외부뷰는특정사용자가보는데이터베이스의구조 관계데이터베이스에서의뷰는한사용자의전체외부뷰대신에하나의가상릴레이션 (virtual relation) 을의미 뷰는기존의기본릴레이션 (base relation. 실제릴레이션 ) 에대한 SELECT문의형태로정의됨 사용자는여러개의릴레이션과뷰를사용할수있음 뷰는릴레이션으로부터데이터를검색하거나갱신할수있는동적인창 (dynamic window) 의역할 3
스냅샷 (snapshot) 81 8.1 뷰 ( 계속 ) 어느시점에 SELECT문의결과를기본릴레이션의형태로저장해놓은것 스냅샷은사진을찍은것과같아서스냅샷을정의하는시점의기본 릴레이션의내용이스냅샷에반영됨 어떤시점의조직체의현황, 예를들어몇년몇월시점에근무하던 사원들의정보, 재고정보등이스냅샷으로정의될수있음 4
8.1 뷰 ( 계속 ) 5
뷰의정의 8.1 뷰 ( 계속 ) 뷰를정의하는 SQL문의구문 CREATE VIEW 뷰이름 [( 애트리뷰트 ( 들 ))] AS SELECT 문 [WITH CHECK OPTION]; 뷰의이름다음에애트리뷰트들을생략하면뷰를정의하는데사용된 SELECT문의 SELECT절에열거된애트리뷰트들의이름과동일한애트리뷰트들이뷰에포함됨 뷰를정의하는 SELECT절에산술식또는집단함수에사용된애트리뷰트가있는경우, 뷰의정의에조인이포함되어있고두개이상의다른릴레이션으로부터가져온애트리뷰트들의이름이같아서뷰에서두개이상의애트리뷰트의이름이같게되는경우에는뷰를정의할때모든애트리뷰트들의이름을지정해야함 6
8.1 뷰 ( 계속 ) 예 : 한릴레이션위에서뷰를정의 그림 4.8의 EMPLOYEE 릴레이션에대해서 3번부서에근무하는사원들의사원번호, 사원이름, 직책으로이루어진뷰 를정의해보자. 아래의뷰의정의에는뷰의애트리뷰트들을별도로명시했기때문에뷰에는 EMPNO, EMPNAME, TITLE 의세애트리뷰트가포함됨 CREATE VIEW AS SELECT FROM WHERE EMP_DNO3 (ENO, ENAME, TITLE) EMPNO, EMPNAME, TITLE EMPLOYEE DNO=3; 7
8.1 뷰 ( 계속 ) 8
8.1 뷰 ( 계속 ) 예 : 두릴레이션위에서뷰를정의그림 4.8의 EMPLOYEE와 DEPARTMENT 릴레이션에대해서 기획부에근무하는사원들의이름, 직책, 급여로이루어진뷰 를정의해보자. 아래의뷰의정의에는뷰의애트리뷰트들을별도로명시하지않았기때문에뷰에속하는애트리뷰트들의이름은기본릴레이션의애트리뷰트들의이름과같다. 즉뷰에는 EMPNAME, TITLE, SALARY의세애트리뷰트가포함된다. 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 = 기획 ; 9
8.1 뷰 ( 계속 ) 뷰를사용하여데이터를접근할때관계 DBMS 에서거치는과정 시스템카탈로그로부터뷰의정의, 즉 SELECT 문을검색 기본릴레이션에대한뷰의접근권한을검사 뷰에대한질의를기본릴레이션에대한동등한질의로변환 10
뷰의장점 8.1 뷰 ( 계속 ) 뷰는복잡한질의를간단하게표현할수있게함 기획부에근무하는사원들중에서직책이부장인사원의사원이름과급여를 검색하는질의를기본릴레이션을사용하여표현하면아래와같이다소복잡한 형태의질의가됨 뷰에대해서같은결과를검색하는질의를표현하면 11
뷰의장점 ( 계속 ) 8.1 뷰 ( 계속 ) 뷰는데이터무결성을보장하는데활용됨 기본적으로뷰를통해투플을추가하거나수정할때투플이뷰를 정의하는 SELECT문의 WHERE절의기준에맞지않으면뷰의내용에서사라짐 이뷰의정의할때 WITH CHECK OPTION 을명시했다고가정 12
뷰의장점 ( 계속 ) 8.1 뷰 ( 계속 ) 뷰는데이터독립성을제공함 뷰는데이터베이스의구조가바뀌어도기존의질의 ( 응용프로그램 ) 를 다시작성할필요성을줄이는데사용될수있음 예 : 응용의요구사항이변경되어기존의 EMPLOYEE 릴레이션이두 개의릴레이션 EMP1(EMPNO, EMPNAME, SALARY) 과 EMP2(EMPNO, TITLE, MANAGER, DNO) 로분해되었다고가정하자. 응용프로그램에서기존의 EMPLOYEE 릴레이션을접근하던 SELECT문은더이상수행되지않으므로, EMP1과 EMP2에대한 SELECT문으로변경해야한다. 아래와같이 EMPLOEE 라는뷰를정의했다면응용프로그램에서 EMPLOYEE 릴레이션을접근하던 SELECT문은계속해서수행될수있음 13
뷰의장점 ( 계속 ) 8.1 뷰 ( 계속 ) 뷰는데이터독립성을제공함 ( 계속 ) 14
뷰의장점 ( 계속 ) 8.1 뷰 ( 계속 ) 뷰는데이터보안기능을제공함 뷰는뷰의원본이되는기본릴레이션에직접접근할수있는권한을 부여하지않고뷰를통해데이터를접근하도록하기때문에보안메커니즘으로사용할수있음 뷰는일반적으로기본릴레이션의일부애트리뷰트들또는일부투플들을검색하는 SELECT문으로정의되므로뷰를통해서기본릴레이션을접근하면기본릴레이션의일부만검색할수있음 예 : EMPLOYEE 릴레이션의 SALARY 애트리뷰트는숨기고나머지애트리뷰트들은모든사용자가접근할수있도록하려면 SALARY 애트리뷰트를제외하고 EMPLOYEE 릴레이션의모든애트리뷰트를포함하는뷰를정의하고, 사용자에게뷰에대한 SELECT 권한을허가 15
뷰의장점 ( 계속 ) 8.1 뷰 ( 계속 ) 동일한데이터에대한여러가지뷰를제공함 뷰는사용자들의그룹이각자특정한기준에따라데이터를접근하도록 함 16
뷰의갱신 8.1 뷰 ( 계속 ) 뷰에대한갱신도기본릴레이션에대한갱신으로변환됨 아래의갱신들이성공적으로수행될수있는가? 갱신 1: 한릴레이션위에서정의된뷰에대한갱신 17
8.1 뷰 ( 계속 ) 뷰의갱신 ( 계속 ) 갱신 2: 두개의릴레이션위에서정의된뷰에대한갱신 18
뷰의갱신 ( 계속 ) 8.1 뷰 ( 계속 ) 갱신 3: 집단함수등을포함한뷰에대한갱신 19
갱신이불가능한뷰 8.1 뷰 ( 계속 ) 한릴레이션위에서정의되었으나그릴레이션의기본키가포함되지않은뷰 기본릴레이션의애트리뷰트들중에서뷰에포함되지않은애트리뷰트에대해 NOT NULL이지정되어있을때 집단함수가포함된뷰 조인으로정의된뷰 20
8.1 뷰 ( 계속 ) 21
시스템카탈로그 8.2 관계 DBMS 의시스템카탈로그 시스템카탈로그는데이터베이스의객체 ( 사용자, 릴레이션, 뷰, 인덱스, 권한등 ) 와구조들에관한모든데이터를포함 시스템카탈로그를메타데이터라고함. 메타데이터는데이터에관한데이터라는의미 시스템카탈로그는사용자및질의최적화모듈등 DBMS 자신의구성요소에의해서사용됨 시스템카탈로그는관계 DBMS마다표준화되어있지않아서관계 DBMS마다서로다른형태로시스템카탈로그기능을제공함 시스템카탈로그는데이터사전 (data dictionary) 또는시스템테이블 이라고도부름 22
8.2 관계 DBMS 의시스템카탈로그 ( 계속 ) 시스템카탈로그가질의처리에어떻게활용되는가 SELECT 문이문법적으로정확한가를검사함 SELECT문에서참조하는 EMPLOYEE 릴레이션이데이터베이스에존재하는가를검사함 EMPLOYEE 릴레이션에 SELECT 절에열거된애트리뷰트와 WHERE 절에서조건에사용된애트리뷰트가존재하는가를확인함 SALARY 애트리뷰트가수식에사용되었으므로이애트리뷰트의데이터타입이 숫자형 ( 정수형이나실수형 ) 인가를검사하고, TITLE이문자열과비교되었으므로이애트리뷰트의데이터타입이문자형 (CHAR(n) 또는 VARCHAR(n) 등 ) 인가등을검사함 23
8.2 관계 DBMS 의시스템카탈로그 ( 계속 ) 시스템카탈로그가질의처리에어떻게활용되는가 ( 계속 ) 이질의를입력한사용자가 EMPLOYEE 릴레이션의 EMPNAME, SALARY 애트리뷰트를검색할수있는권한이있는가를확인함 TITLE애트리뷰트와DNO애트리뷰트에인덱스가정의되어있는지확인함 두애트리뷰트에각각인덱스가존재한다고가정하자. DBMS가두인덱스 중에서조건을만족하는투플수가적은것을선택하기위해서는관계 데이터베이스시스템에데이터베이스외에추가로정보를유지해야함 한릴레이션의전체투플수와그릴레이션에정의된각인덱스에존재하는 상이한값들의개수를유지한다면어느인덱스를사용하는것이유리한가를예상할수있음 24
8.2 관계 DBMS 의시스템카탈로그 ( 계속 ) 시스템카탈로그가질의처리에어떻게활용되는가 ( 계속 ) 그림 4.8에서 EMPLOYEE 릴레이션의전체투플수는 7이고, TITLE 애트리뷰트에는사원, 대리, 과장, 부장, 사장의다섯가지값들이존재함 DNO 애트리뷰트에는 1, 2, 3의세가지값들이존재함 따라서 TITLE 애트리뷰트에정의된인덱스가 DNO에정의된인덱스보다 대상투플들을더좁혀주므로유리함 25
8.2 관계 DBMS 의시스템카탈로그 ( 계속 ) 질의최적화 DBMS가질의를수행하는여러가지방법들중에서가장비용이적게드는방법을찾는과정 질의최적화모듈이정확한결정을내릴수있도록 DBMS는자체목적을위해서시스템카탈로그에다양한정보를유지함 사용자가질의최적화모듈을깊이있게이해할필요는없지만질의최적화모듈이정확한수행방법을결정하기위해서는릴레이션에관한다양한통계정보가정확하게유지돼야한다는것을알고있는것이바람직 26
8.2 관계 DBMS 의시스템카탈로그 ( 계속 ) 관계 DBMS 의시스템카탈로그 사용자릴레이션과마찬가지형태로저장되기때문에사용자릴레이션에적용되는회복기법과동시성제어기법을동일하게사용할수있음 시스템카탈로그는사용자릴레이션처럼 SELECT문을사용하여내용을검색할수있음 시스템카탈로그에는릴레이션, 애트리뷰트, 인덱스, 사용자, 권한등각유형마다별도의릴레이션이유지됨 EMPLOYEE 릴레이션과 DEPARTMENT 릴레이션에대해서시스템 카탈로그에어떤정보들이유지되는가를이해하기쉽도록시스템카탈로그를매우단순화하여설명함 릴레이션에관한정보를유지하는릴레이션의이름이 SYS_RELATION, 애트리뷰트에관한정보를유지하는릴레이션의이름이 SYS_ATTRIBUTE 라고가정 27
8.2 관계 DBMS 의시스템카탈로그 ( 계속 ) 28
8.2 관계 DBMS 의시스템카탈로그 ( 계속 ) 시스템카탈로그의갱신 어떤사용자도시스템카탈로그를직접갱신할수없음 즉 DELETE, UPDATE 또는 INSERT문을사용하여시스템카탈로그를 변경할수없음 EMPLOYEE 릴레이션의소유자인 KIM이 EMPLOYEE 릴레이션에서 MANAGER 애트리뷰트를삭제하기위해서 ALTER TABLE EMPLOYEE DROP COLUMN MANAGER; 라고하는대신에아래와같이시스템카탈로그에대해 DELETE문을 사용하면 DBMS가거절함 DELETE FROM SYS_ATTRIBUTE WHERE AttRelId = 'EMPLOYEE' AND AttName = 'MANAGER'; 29
8.2 관계 DBMS 의시스템카탈로그 ( 계속 ) 시스템카탈로그에유지되는통계정보 릴레이션마다투플의크기, 투플수, 각블록의채우기비율, 블록킹인수, 릴레이션의크기 ( 블록수 ) 뷰마다 뷰의이름과정의 애트리뷰트마다애트리뷰트의데이터타입과크기, 애트리뷰트내의상이한값들의수, 애트리뷰트값의범위, 선택율 ( 조건을만족하는투플수 / 전체투플수 ) 30
8.2 관계 DBMS 의시스템카탈로그 ( 계속 ) 시스템카탈로그에유지되는통계정보 ( 계속 ) 사용자마다접근할수있는릴레이션과권한 인덱스마다인덱스된애트리뷰트 ( 키애트리뷰트또는비키애트리뷰트 ), 클러스터링인덱스 / 비클러스터링인덱스여부, 밀집 / 희소인덱스여부, 인덱스의높이, 1단계인덱스의블록수 31
8.3 오라클의시스템카탈로그 오라클의시스템카탈로그 시스템카탈로그를데이터사전 (data dictionary) 이라고부름 데이터사전은시스템테이블스페이스에저장됨 데이터사전은기본테이블과데이터사전뷰로구성됨 사용자는기본테이블의정보가암호화된형태로저장되어있기때문에 직접접근할필요가거의없으며, 일반적으로이해하기쉬운형식의정보를 제공하는데이터사전뷰를접근 32
8.3 오라클의시스템카탈로그 ( 계속 ) 데이터사전뷰의세부류 DBA_xxx 뷰데이터베이스내의모든객체들에관한정보 ALL_xxx 뷰현재의사용자가접근할수있는객체들에관한정보 USER_xxx 뷰현재의사용자가소유하고있는객체들에관한정보 33
8.3 오라클의시스템카탈로그 ( 계속 ) 34
8.3 오라클의시스템카탈로그 ( 계속 ) 35
8.3 오라클의시스템카탈로그 ( 계속 ) 사용자 KIM 이소유한테이블이나뷰에관한정보를검색하기위해서 KIM 으로 Oracle SQL Developer 에로그인을한후에다음과같은 질의를수행 SELECT * FROM ALL_ CATALOG WHERE OWNER='KIM'; OWNER는사용자의이름, TABLE_NAME은테이블이나뷰의이름, TABLE_TYPE은테이블의유형으로서테이블, 뷰등을나타냄 36
8.3 오라클의시스템카탈로그 ( 계속 ) 37
8.3 오라클의시스템카탈로그 ( 계속 ) 사용자 KIM 이소유한 EMPLOYE 테이블의애트리뷰트정보를찾기위해서 다음과같은질의를수행 TABLE_NAME 은테이블의이름, COLUMN_NAME 은애트리뷰트의이름, DATA_TYPE 은애트리뷰트의데이터타입을각각나타냄 이밖에도 USER_TAB_COLUMNS 뷰를통해서각애트리뷰트의길이, 널값 허용여부, 디폴트값등을검색할수있음 38
8.3 오라클의시스템카탈로그 ( 계속 ) 39
8.3 오라클의시스템카탈로그 ( 계속 ) 3 장의예제 3.2 에서생성한 EMP_PLANNING 뷰가어떤 SELECT 문으로 정의되어있는가를알기위해서다음과같은질의를수행 VIEW_NAME 은뷰의이름이고, TEXT 는뷰를정의한 SQL 문 40
8.3 오라클의시스템카탈로그 ( 계속 ) 41
8.3 오라클의시스템카탈로그 ( 계속 ) EMP_PLANNING 뷰는 EMPLOYEE 와 DEPARTMENT 테이블을조인해서정의한 뷰인데, 이뷰가갱신이가능한지확인해보기위해서아래와같은 INSERT 문을 수행 이뷰에는 EMPLOYEE 테이블의기본키인 EMPNO 가포함되지않았으므로 뷰를통해서아래와같이투플을삽입하면 EMPNO 의값을입력하는것이 불가능 42
8.3 오라클의시스템카탈로그 ( 계속 ) 43
8.3 오라클의시스템카탈로그 ( 계속 ) EMPLOYEE 테이블의부서번호애트리뷰트인 DNO 에대해인덱스를생성하고, 생성된인덱스를통해서통계정보를확인 CREATE INDEX EMPDNO_IDX ON EMPLOYEE(DNO); 44
8.3 오라클의시스템카탈로그 ( 계속 ) 통계정보를확인하기위해서아래와같은명령문을수행 SELECT FROM WHERE INDEX_NAME,INITIAL_EXTENT,DISTINCT_KEYS, NUM_ROWS,SAMPLE_SIZE,LAST_ANALYZED USER_INDEXES INDEX_NAME = 'EMPDNO_IDX'; INDEX_NAME은인덱스의이름, INITIAL_EXTENT는초기익스텐트의크기, DISTINCT_KEYS는상이한인덱스값들의개수, NUM_ROWS는테이블의투플수, SAMPLE_SIZE는인덱스분석을위해사용된투플수, LAST_ANALYZED는통계가마지막으로갱신된날짜를나타냄 45
8.3 오라클의시스템카탈로그 ( 계속 ) 46
8.3 오라클의시스템카탈로그 ( 계속 ) EMPLOYEE 테이블에새로운투플을한개삽입해보자 47
8.3 오라클의시스템카탈로그 ( 계속 ) 다시통계정보를확인하기위해서아래와같은명령문을수행 SELECT FROM WHERE INDEX_NAME,INITIAL_EXTENT,DISTINCT_KEYS, NUM_ROWS,SAMPLE_SIZE,LAST_ANALYZED USER_INDEXES INDEX_NAME = 'EMPDNO_IDX'; 그림 8.12 는그림 8.10 과동일 한테이블에투플이삽입되자마자데이터사전뷰의정보가갱신되지는 않음 48
8.3 오라클의시스템카탈로그 ( 계속 ) 49
8.3 오라클의시스템카탈로그 ( 계속 ) 통계정보는 ANALYZE 문을사용하여갱신할수있음 ANALYZE INDEX EMPDNO_IDX COMPUTE STATISTICS; 테이블에대한통계정보는 ANALYZE TABLE 명령을사용해서갱신하고, 인덱스에대한통계정보는 ANALYZE INDEX 명령을사용해서갱신 50
8.3 오라클의시스템카탈로그 ( 계속 ) ANALYZE 명령의문법 ANALYZE 객체 _ 유형객체 _ 이름연산 STATISTICS; 객체 _ 유형은테이블, 인덱스등을나타냄 객체 _ 이름은객체의이름을의미 연산에 COMPUTE 가사용되면전체테이블을접근하여통계정보를계산 연산에 ESTIMATE 가사용되면데이타표본을추출하여통계정보를구함 주기적으로 ANALYZE 작업을수행해야함 다량의데이터를일괄작업으로처리한경우등에는바로 ANALYZE 작업을 수행하는것이필요 51
8.3 오라클의시스템카탈로그 ( 계속 ) ANALYZE 명령의수행결과를확인하기위해서아래와같은명령문을 다시수행 SELECT FROM INDEX_NAME,INITIAL_EXTENT,DISTINCT_KEYS, NAME INITIAL EXTENT DISTINCT NUM_ROWS,SAMPLE_SIZE,LAST_ANALYZED USER_INDEXES WHERE INDEX_NAME NAME = 'EMPDNO_IDX'; 52
8.3 오라클의시스템카탈로그 ( 계속 ) 53
8.3 오라클의시스템카탈로그 ( 계속 ) EMPLOYEE 테이블에정의된인덱스정보를찾기위해서아래와같은 명령을수행 INDEX_NAME 은인덱스의이름, TABLE_NAME 은인덱스가정의된 테이블의이름, COLUMN_NAMENAME 은인덱스가정의된애트리뷰트의이름, COLUMN_POSITION 은인덱스가정의된애트리뷰트의위치, COLUMN_LENGTHLENGTH 는인덱스가정의된애트리뷰트의길이, DESCEND 는 정렬방식 ( 오름차순또는내림차순 ) 을나타냄 54
8.3 오라클의시스템카탈로그 ( 계속 ) 3 장의예제 3.2 에서 EMPLOYEE 테이블을정의할때 EMPNO 애트리뷰트를 기본키로선정했으므로오라클이자동적으로인덱스를생성 EMPNAME 애트리뷰트에는 UNIQUE 키워드를명시했으므로오라클이 자동적으로인덱스를생성 DNO 애트리뷰트에는사용자가 CREATE INDEX 문을사용하여명시적으로 인덱스를정의 55
8.3 오라클의시스템카탈로그 ( 계속 ) 56