View

상위 문서: SQL

개요

모든 유저들이 데이터베이스 내의 모든 logical model(relation)을 볼 수 있는 권한을 가지는 것은 보안상의 측면에서 권장되지 않는다. 또한 어떤 사용자는 특정 릴레이션(relation)에 접근할 수는 있어도, 모든 속성(attribute)에 접근하는 것이 바람직하지 않을 수 있다. 예를 들어, 어떤 직원(employee)는 어떤 강사(instructor)의 ID, name, dept_name 속성에 접근할 필요가 있지만, 강사의 salary 속성의 값을 볼 필요는 없을 수 있다. 따라서 해당 직원은 다음과 같은 SQL 쿼리(query)로 표현된 릴레이션에 접근해야 한다.

select ID, name, dept_name from instructor;

즉, 위 쿼리의 결과를 계산하여 저장한 다음 저장된 릴레이션을 사용자에게 제공하는 방식으로 특정 사용자에게 제한된 릴레이션을 제공할 수 있다. 하지만 instructor, course, section 릴레이션 안의 데이터가 변경된다면, 저장된 쿼리 결과는 다시 쿼리를 실행했을 때의 결과와 일치하지 않는다.(out-of-date) 따라서 위와 같은 해결 방식, 즉 쿼리 결과를 계산해서 저장하는 것은 좋은 생각이 아니다.

대신 SQL에는 view라는 기능이 있다. View는 개념적인 모델(conceptual model)에는 없지만 사용자에게 virtual relation으로 보이도록 만든 릴레이션이다. 이를 위해 SQL은 쿼리에 의해 정의되는 virtual relation이라는 기능을 제공한다. Virtual relation은 미리 계산되어 저장되는 것이 아니라, virtual relation이 사용될 때마다 쿼리를 실행하여 계산된다. 이는 with 절과 유사한 기능이다.
즉, 해당 예시에서 view는 원본 릴레이션에 직접적으로 접근할 권한을 부여하지 않는다. 대신 salary 속성이 포함되지 않은 view를 만들고 해당 직원에게 view에 대한 접근 권한을 부여한다. 따라서 해당 직원은 salary를 제외한 속성만 볼 수 있게 된다.

View definition

SQL에서 view를 정의하기 위해서는 create view 명령어를 사용해야 한다. 또한 view를 정의하기 위해서는 이름을 부여해야 하며, view를 계산할 쿼리를 명시해야 한다. create view 명령어는 v라는 이름의 view를 정의하기 위해 아래와 같이 사용된다.

create view v as <query expression>;

이때 <query expression>는 어떤 쿼리 표현식이라도 문법만 맞다면 가능하다. 이를 활용하여 instructor 릴레이션에서 salary 속성을 제외한 모든 속성에 접근하는 직원에게 제공하는 view는 다음과 정의된다.

create view faculty as select ID, name, dept_name from instructor;

또한, 2017년 가을 학기에 물리학과에서 개설한 모든 course_id 속성을, building과 room_number 속성과 함께 나열하는 view를 생성하려면, 다음과 같이 작성한다:

create view physics_fall_2017 as
select course.course_id, sec_id, building, room_number
from course, section
where course.course_id = section.course_id
  and course.dept_name = 'Physics'
  and section.semester = 'Fall'
  and section.year = 2017;

View definition은 위 문단에서 언급했듯이 해당 쿼리를 실행하여 릴레이션을 생성하는 것과는 구별된다. View definition은 단순히 표현식을 저장할 뿐이며, 해당 표현식을 통해 view relation이 필요할 때마다 즉시(on demand) 생성된다.

Using Views in SQL Queries

위에서 만들어진 faculty와 physics_fall_2017라는 view name은 추후에도 해당 view 정의를 통해서 만들어진 virtual relation을 호출하기 위해서 사용될 수 있다. 예를 들어 2017년 가을 학기에 Watson 건물에서 개설된 모든 물리학과 강좌들을 다음과 같이 찾아낼 수 있다:

select course_id
from physics_fall_2017
where building = 'Watson';

또한 view name은 쿼리 안에서 릴레이션이 나타날 수 있는 모든 위치에서 나타날 수 있다. 이때 view의 속성 이름은 다음과 같이 명시적으로 지정될 수 있다:

create view departments_total_salary(dept_name, total_salary) as
select dept_name, sum(salary)
from instructor
group by dept_name;

위 코드에서 정의된 view는 각 department에 대해서 해당 department와 모든 instructor들의 급여의 총 합을 제공한다. 이때 sum(salary)라는 이름 대신, total_salary라는 이름을 명시적으로 지정한다.

Views Defined Using Other Views

View는 다른 view를 기반으로 정의할 수 있다. 이때 다음과 같은 용어가 사용된다:

• v1이 v2를 view를 정의하는데 직접 사용 → directly depends on
• v1이 v2에 view를 정의하는데 직접 또는 간접적으로 의존 → depends on
• v가 view를 정의하는데 자기 자신에 의존 → recursive

위 용어들을 통해서 view 간의 의존성을 명확하게 구분할 수 있으며, 나중에 시스템이 view를 확장할 때 올바르게 해석되도록 한다.
예를 들어 2017년 가을 학기에 Watson 건물에서 개설된 모든 물리학과 section과 해당 강좌의 room_number를 나타내는 view는 다음과 같이 정의될 수 있다.

create view physics_fall_2017_watson as
  select course_id, room_number
  from physics_fall_2017
  where building = 'Watson';

View expansion

View는 쿼리를 단순화 시켜주지만, 실제 실행 시에는 원래의 정의로 대체(확장)된다. 예를 들어, 위 문단에서 작성한 physics_fall_2017_watson라는 view는 실제 실행 시 아래와 같이 확장(expansion)된다:

create view physics_fall_2017_watson as
  select course_id, room_number
  from (
    select course.course_id, building, room_number
    from course, section
    where course.course_id = section.course_id
      and course.dept_name = 'Physics'
      and section.semester = 'Fall'
      and section.year = '2017'
  )
  where building = 'Watson';

위와 같이 view는 실행시 내부적으로 정의된 쿼리로 확장되며, view가 중첩될 경우에는 계속해서 확장되는 구조이다. 이때 다른 view를 이용하여 정의된 view는 아래와 같은 view 확장 알고리즘을 통해서 처리된다.

반복:
  표현식 e₁에서 뷰 vᵢ를 찾는다
  vᵢ를 정의하는 쿼리 표현식으로 vᵢ를 대체한다
더 이상 뷰가 없을 때까지 반복

만약 recusive 방식으로 정의된 view가 아니라면 위 알고리즘은 항상 종료되며, 위 알고리즘 덕분에 SQL은 view를 매크로의 일종처럼 해석하여 처리할 수 있다.

Materialized view

어떤 데이터베이스 시스템은 view 릴레이션을 저장하는 것을 허용하지만, view(virtual relation) 정의에 사용된 릴레이션의 튜플이 변경되면 view가 최신 상태로 유지되도록 보장한다. 이러한 view를 materialized view라고 한다. 예를 들어 department_total_salary view가 구체화(materialized)되면, 그 결과가 데이터베이스에 저장되고, 해당 view를 사용하는 쿼리는 매번 view 결과를 다시 계산하는 대신 미리 계산된 view 결과를 사용함으로써 훨씬 더 빠르게 실행될 수 있다. 하지만 강사 릴레이션에서 튜플이 추가/삭제/갱신된다면, 해당 materialized view도 업데이트 되어야 한다. 이와 같이 materialized view를 최신 상태로 유지하는 것을 materialized view maintenance(view maintenance)라고 한다.

View maintenence의 실행은 view를 정의하는데 사용된 릴레이션들 중 어느 하나가 갱신될 때 즉시 이뤄질 수 있다. 하지만 일부 데이터베이스 시스템은 view maintenance 작업을 지연시켜 view가 실제로 사용될 때 비로소 실행할 수 있다. 어떤 데이터베이스는 view maintenance를 주기적으로만 실행한다. 이 경우 materialized view의 내용은 out-of-date될 수 있으며, 문제를 일으킬 수도 있다. 그리고 view maintenance의 실행시점을 데이터베이스의 관리자(DBA)가 제어할 수 있도록 허용하기도 하는 시스템 또한 존재한다.

Update of a view

View는 쿼리를 사용하기 더욱 편하게 만들어주는 유용한 도구이지만 튜플의 추가/삭제/업데이트를 view를 통해 수행하고자 하면 심각한 문제가 발생할 수 있다. 그 이유는, view를 기준으로 표현된 데이터베이스에 대한 수정 작업을, 데이터베이스의 logical model에서 view를 정의하는데 사용된 릴레이션에 대한 수정 작업으로 변환해야 하기 때문이다. 위 문단에서 정의된 faculty view가 직원에게 제공된다면, view 이름은 릴레이션 이름이 사용될 수 있는 모든 곳에 사용될 수 있으므로, 해당 직원은 다음과 같은 insert 문을 작성할 수 있다:

insert into faculty
values ('30765', 'Green', 'Music');

위 삽입은 실제로는 faculty view를 정의하기 위해 사용된 instructor 릴레이션에 대한 insert 문으로 표현되어야 한다. 하지만 instructor 릴레이션에 튜플을 삽입하기 위해서는 salary 속성 값이 추가로 필요하다. 이를 해결하기 위해서는 삽입을 거부하고 사용자에게 오류 메시지를 반환하거나, 강사의 salary 속성 값에 null 값을 삽입하는 접근 방법이 있다. 하지만 후자의 방식을 이용한 튜플의 업데이트는 또 다른 문제들을 야기한다. 이를 이해하기 위해 instructor_info view를 새로 정의하고 이를 이용한 insert 문을 살펴보자.

create view instructor_info as
select ID, name, building
from instructor, department
where instructor.dept_name = department.dept_name;

insert into instructor_info
values ('69987', 'White', 'Taylor');

위는 아래와 같이 두가지 문제가 존재한다.
1) 만약 Taylor 건물에 여러 학과가 존재한다면 문제가 생긴다. 그 이유는 instructor_info view는 instructor.dept_name = department.dept_name와 같은 조건을 가지고 있기 때문이다. 해당 insert 문을 실행시키기 위해서 시스템은 department.building = 'Taylor'인 department를 찾고 해당 릴레이션의 dept_name을 instructor 릴레이션에 넣어야 하는데, Taylor 건물에 여러 department가 존재하기 때문에 어떤 department를 선택할 지 알 수 없다.
2) 또한 Taylor 건물에 학과가 존재하지 않을 경우에도 문제가 생긴다. 그 이유는 department.building = 'Taylor'인 튜플이 없다면 instructor.dept_name = department.dept_name 조건을 만족시킬 방법이 없기 때문이다. 이 상황을 더욱 자세히 살펴보기 위해 ID = 69987인 instructor가 존재하지 않고, Taylor 건물에 위치한 department도 존재하지 않는다고 가정해보자. 이때 instructor와 department 릴레이션에 튜플을 삽입할 수 있는 유일한 방법은 ('69987', 'White', null, null)를 instructor에, 그리고 (null, 'Taylor', null)을 department에 삽입하는 것이다. 하지만 이러한 방식의 업데이트는 의도한 효과를 내지 못한다. 왜냐하면 instructor_info view 릴레이션에는 여전히 ('69987', 'White', 'Taylor') 튜플이 포함되지 않기 때문이다. 따라서 의도대로 instructor_info view를 업데이트하기 위해 instructor와 department 릴레이션을 null 값을 이용해 수정하는 방법은 존재하지 않는다.

Updatable view

위와 같은 문제들 때문에 일반적으로 view relation에 대한 수정 작업은 제한된 경우에만 허용된다. 일반적으로 SQL view가 갱신 가능(updatable)하기 위해서는 view를 정의하는 쿼리가 다음 조건들을 모두 만족해야 한다.

1. from 절에 하나의 데이터베이스 릴레이션만 포함되어야 한다.
2. select 절에는 릴레이션의 속성 이름만 포함되어야 하며, 표현식(expression), aggregate 함수, distinct 키워드는 포함되어서는 안 된다.
3. select 절에 포함되지 않은 속성들은 null 값이 가능해야 한다. 즉, not null 제약 조건이 없고 primary key의 일부도 아니어야 한다.
4. 쿼리에 group by 또는 having 절이 없어야 한다.

예를 들어, 아래와 같은 view는 갱신 가능하다:

create view history_instructors as 
select * 
from instructor 
where dept_name = 'History';

그러나, view가 갱신 가능하더라도 문제는 여전히 존재한다. 예를 들어 어떤 사용자가 다음과 같이 튜플을 view에 삽입하고자 한다:

insert into history_instructors
values ('25566', 'Brown', 'Biology', 100000);

이 튜플은 instructor 릴레이션에는 삽입될 수 있지만, history_instructors view에서는 dept_name = 'History' 조건을 만족하지 않기 때문에 이 view에 나타나지 않는다.
위와 같은 무의미한 삽입은 SQL 내에서 허용된다. 하지만 view definition의 끝에 with check 절을 사용하면, view에 삽입된 튜플이 view의 where 조건을 만족하지 않을 경우 삽입이 거부된다. 그 뿐만 아니라 update 연산을 수행할 때에도 새로운 값이 where 조건을 만족하지 않으면 갱신이 거부된다.