Partition, View and Sequence in Database

Contents

1️⃣ 파티션 (Partition)
2️⃣ 뷰 (View)
3️⃣ 시퀀스 (Sequence)

Summary

1️⃣ 파티션 (Partition) 큰 테이블이나 인덱스를 관리하기 쉬운 작은 단위로 나누어 저장하는 방법이다. 각 파티션은 고유한 이름을 가진다.

장점:

가용성 증가: 특정 파티션만 사용하거나 유지 관리할 수 있어 시스템 가용성을 높인다.
효율성: 스키마 객체를 관리할 때 효율적이다.
성능 향상: 데이터웨어하우스에서 쿼리 성능을 항상 일정하게 유지할 수 있다.
응용 프로그램 호환성: 파티셔닝을 사용해도 응용 프로그램에서는 하나의 테이블처럼 보이므로 DML 문장을 수정할 필요가 없다.

2️⃣ 뷰 (View): 하나 이상의 테이블에 대한 논리적 표현으로, 미리 정의된 질의를 저장하여 필요할 때마다 재사용할 수 있도록 합니다.

주요 기능:

재사용성: 자주 사용하는 복잡한 질의를 미리 정의해두고 필요할 때마다 재사용할 수 있다.
보안성: 특정 데이터에 대한 접근을 제한하는 데 유용하며, 민감한 정보를 숨길 수 있다.
간소화: 복잡한 SQL 쿼리를 간단하게 만들고 가독성을 높여준다.
중요성: 자주 사용되는 기능이므로 SQL 작업 시 숙지해야 한다.

3️⃣ 시퀀스 (Sequence): 고유한 숫자 식별자를 자동으로 생성하는 객체로, 보통 테이블의 기본 키나 유니크 키로 사용된다.

주요 속성:

고유 값 생성: 중복되지 않는 숫자를 생성해 테이블의 고유 식별자로 사용한다.
자동 증가: 필요에 따라 지정된 증분 값을 통해 자동으로 증가하도록 설정할 수 있다.
용도: 테이블의 고유 키 생성, 순차적 데이터 관리 등에 사용된다.

💡요약

파티션은 큰 데이터를 효율적으로 관리하고 쿼리 성능을 향상시키기 위해 사용된다.
뷰는 복잡한 쿼리를 간소화하고 보안을 강화할 수 있어 자주 사용되는 중요한 SQL 객체이다.
시퀀스는 고유한 식별자를 자동으로 생성해 데이터베이스에서 중복되지 않는 키를 제공하며, 데이터 관리의 자동화를 도와준다.

1️⃣ 파티션 (Partition)

파티션 (Partition): 크기가 매우 큰 테이블이나 인덱스를 파티션이라고 부르는 관리가 용이한 단위로 분할하는 것. 각 파티션은 자신의 이름을 가지고 있어 독립적으로 관리가 가능하다.

❓왜 인덱스 (Index)와 파티션 (Partition)이 필요한가❓

✅큰 데이터의 관리(Manage Bigger size Data): 데이터베이스에서 매우 큰 테이블이나 인덱스를 한꺼번에 관리하기는 어렵다. 파티션을 사용하면 데이터가 작은 단위로 나뉘어져 있어, 특정 부분만 관리하거나 조회할 수 있게 된다.

✅ 효율성(Efficiency): 데이터를 파티션별로 분리하면 검색, 삽입, 삭제 작업이 더 빨라진다. 예를 들어, 특정 파티션만 조회하면 전체 테이블을 검색하지 않아도 되므로 성능이 향상된다.

✅ 독립적인 관리(Independent Management): 각 파티션은 독립적으로 관리될 수 있어, 필요에 따라 특정 파티션만 백업하거나 복구할 수 있다.

HR Manager 예시 - 인덱스 및 파티션을 활용한 데이터 관리

인사담당자가 직원의 인사 기록 카드를 담은 큰 박스를 관리하고 있다.
특정 월에 입사한 직원을 찾아보는 일이 주요 작업이라고 가정해보자.

관리 방법

인덱스 (Index): 입사일을 기준으로 인덱스를 생성하여 관리하면 특정 입사일에 입사한 직원을 더 빠르게 찾을 수 있다.
월별 파티션 (Monthly Partition): 월별로 구분된 작은 박스로 나누어 관리하면 각 월에 입사한 직원만 따로 보관하므로, 특정 월의 직원 기록을 더 쉽게 찾을 수 있게 된다.

파티션 #1: 장점 (Advantages of Partition)

💡요약: 파티션의 주요 장점을 알아보자. 파티션이 데이터의 효율적 접근과 관리에 어떻게 기여하는지를 보여준다. 파티션을 사용하면 쿼리 성능을 높이고, 필요한 데이터만 선택적으로 접근할 수 있어 효율적이다. 또한, 개별 파티션을 관리할 수 있어 더 유연하게 데이터베이스를 운영할 수 있게 된다.

✅가용성의 증가 (Increased Availability):

쿼리 옵티마이저가 실행 계획 (Execution Plan)을 수립할 때, 불필요한 파티션을 제외할 수 있다. 이렇게 하면 데이터 조회가 더 빨라지고 리소스를 절약할 수 있게된다.

✅스키마 객체의 관리 (Management of Schema Objects):

분할된 객체는 개별적 (Individually)으로 관리할 수 있어 필요에 따라 특정 파티션만 다룰 수 있다.
예를 들어, 데이터 이동 중에 오류가 발생하면 해당 파티션만 다시 작업하면 된다.
또한, 파티션을 삭제할 때 많은 삭제 명령 (Delete Statements)을 실행하지 않아도 된다.

✅데이터웨어하우스 성능 향상 (Improved Query Performance in Data Warehouse):

데이터웨어하우스 (Data Warehouse) 환경에서는 큰 데이터를 다루므로 쿼리 성능이 중요하다. 파티션을 사용하면 데이터를 분할하여 검색할 수 있어, 쿼리 성능을 항상 높일 수 있게 된다.

✅ 응용 프로그램의 일관성 유지 (Maintaining Application Consistency):

응용 프로그램 입장에서는 여러 파티션이 있는 테이블이라도 하나의 큰 테이블처럼 보이기 때문에 DML문장 (DML Statements)을 수정할 필요가 없다. 이는 프로그램의 일관성을 유지하는 데 도움을 준다.

파티션 #2: 각 파티션 별 특성

(Characteristics of Each Partition)

💡요약: 파티션의 구조와 물리적 특성에 대해 알아본다. 모든 파티션은 동일한 컬럼과 데이터 타입을 가지므로 통일성이 유지되며, 동시에 각 파티션이 별도의 물리적 특성을 가질 수 있어 필요에 따라 최적화가 가능해진다.

✅ 동일한 컬럼과 데이터 타입 (Same Columns and Data Types):

각 파티션은 같은 이름의 컬럼 (Column)과 데이터 타입 (Data Type), 그리고 동일한 제약 조건을 가지고 있다. 즉, 하나의 테이블처럼 동일한 구조를 유지한다.

✅ 물리적 특성의 차별화 (Distinct Physical Attributes):

파티션별로 테이블 스페이스 (Table Space) 등의 물리적 특성을 다르게 설정할 수 있다. 이를 통해 파티션이 개별적으로 최적화되도록 설정할 수 있다.

파티션 #3: 키 (Partition Key)

💡요약: 파티션 키 (Partition Key)는 데이터가 속할 파티션을 결정하는 기준 컬럼이다. 범위 파티션 (Range Partition)을 사용하여 특정 범위의 데이터를 효율적으로 분할하고 관리할 수 있다.

✅ 파티션 키 (Partition Key):

파티션 키 (Partition Key)는 각 로우 (Row)가 어느 파티션에 속할지를 결정하는 기준이 되는 컬럼이다. 하나 이상의 컬럼을 파티션 키로 사용할 수 있다.
예를 들어, Sales 테이블에서 time_id 컬럼을 기준으로 범위 파티션 (Range Partition)을 설정하면, 데이터베이스는 이 파티션 키를 기준으로 적절한 파티션에 데이터를 삽입, 수정, 삭제할 수 있게 된다.

파티션 #4: 종류 (Types of Partitioning)

💡요약: Single-level 파티셔닝의 3가지 종류에 대해 알아본다. 각각의 파티셔닝 방식은 데이터를 효율적으로 관리하기 위해 다른 기준으로 데이터를 나누는 방법을 제공한다.

✅ 리스트 파티셔닝 (List Partitioning):

특정 값 목록에 따라 데이터를 나누는 방법이다. 예를 들어, 국가별로 데이터를 나누고자 할 때 리스트 파티셔닝을 사용하여 각 국가 데이터를 별도의 파티션에 저장할 수 있다.

✅ 범위 파티셔닝 (Range Partitioning):

연속적인 값의 범위에 따라 데이터를 나누는 방식이다. 예를 들어, 날짜를 기준으로 특정 연도별 데이터를 나누고자 할 때 범위 파티셔닝을 사용할 수 있다.

✅ 해시 파티셔닝 (Hash Partitioning):

해시 함수를 사용하여 데이터를 균등하게 분산시키는 방식이다. 특정 값이 고르게 분배되도록 해시 파티셔닝을 사용하면 데이터가 특정 파티션에 몰리지 않고 효율적으로 분배된다.

파티션 #5: 리스트 파티셔닝 (List Partitioning)

💡요약: 리스트 파티셔닝의 개념을 설명하며, 특정 값을 기준으로 데이터를 나누는 방법에 대해 알아본다. 이를 통해 다양한 기준 값에 따라 데이터를 효율적으로 관리할 수 있게 한다.

리스트 파티셔닝 (List Partitioning): 특정 값 (Value)을 기준으로 데이터를 구분하여 파티션하는 방식이다. 주로 고유한 값들로 데이터가 구분될 수 있을 때 사용된다. 예를 들어, 국가별, 지역별 등 특정 값에 따라 데이터를 분류할 때 유용하다.

⬇️

위의 예제 데이터는 PROD_ID (제품 ID), CUST_ID (고객 ID), TIME_ID (시간 ID) 등의 정보를 포함하고 있다.
예를 들어 CHANNEL_ID 또는 PROMO_ID를 기준으로 리스트 파티셔닝을 적용할 수 있다. 각 채널이나 프로모션 ID에 따라 데이터를 별도의 파티션으로 나누어 관리할 수 있으며, 이를 통해 특정 채널이나 프로모션에 대한 데이터를 더 효율적으로 조회할 수 있게 된다.

⬇️

아래의 SQL 예제에서는 channel_id 열을 기준으로 데이터를 특정 값에 따라 나누는 리스트 파티셔닝을 사용한다. PARTITION BY LIST (channel_id) 구문을 통해 channel_id 값에 따라 데이터를 나눈다. 짝수 채널 (even_channels)과 홀수 채널 (odd_channels)이라는 두 파티션을 생성하여, 짝수 채널 ID ('2', '4')와 홀수 채널 ID ('3', '9')에 해당하는 데이터를 각 파티션에 저장하도록 설정하였다.

CREATE TABLE list_sales는 list_sales라는 테이블을 생성한다.
channel_id를 기준으로 리스트 파티셔닝을 설정하여, 값이 '2'나 '4'일 경우 even_channels 파티션에, '3'나 '9'일 경우 odd_channels 파티션에 데이터가 저장된다.

⬇️

channel_id 값을 기준으로 짝수 채널과 홀수 채널로 데이터를 나눈 리스트 파티셔닝의 결과이다. EVEN_CHANNELS 파티션에는 channel_id가 2 또는 4인 데이터가 포함되어 있으며, ODD_CHANNELS 파티션에는 channel_id가 3 또는 9인 데이터가 포함되어 있다.

EVEN_CHANNELS: channel_id가 짝수 (2, 4)인 레코드를 포함하며, 주로 짝수 채널을 통해 발생한 판매 데이터가 저장된다.
ODD_CHANNELS: channel_id가 홀수 (3, 9)인 레코드를 포함하며, 홀수 채널을 통해 발생한 판매 데이터가 저장된다.

파티션 #6: 범위 파티셔닝 (Range Partitioning)

💡요약: 범위 파티셔닝의 특징과 사용할 수 있는 예제 데이터를 보여주며, 각 열의 정보가 어떻게 구성되어 있는지 알아본다. TIME_ID 열을 기준으로 데이터를 나누면, 시간에 따라 데이터를 효율적으로 접근하고 관리할 수 있게 된다.

범위 파티셔닝 (Range Partitioning)은 가장 일반적으로 사용되는 파티셔닝 방법으로, 특정 파티션 키 (Partition Key) 값의 범위 (Range)를 기준으로 데이터를 나눈다.
주로 날짜 타입의 컬럼을 기준으로 설정하여, 예를 들어 연도나 월별로 데이터를 구분해 저장할 수 있다. 이렇게 하면 시간 단위로 데이터를 효율적으로 관리할 수 있게된다.

⬇️

위의 데이터는 제품 ID (Product ID), 고객 ID (Customer ID), 시간 ID (Time ID) 등을 포함하고 있으며, 특정 날짜를 기준으로 판매된 양과 금액 등이 기록되어 있다.
범위 파티셔닝을 적용할 경우, TIME_ID를 기준으로 데이터가 특정 연도 또는 월별로 나뉘어져 저장될 수 있습니다. 이렇게 하면 특정 기간에 해당하는 데이터를 빠르게 조회할 수 있게된다.

⬇️

연도별로 데이터를 나누기 위해 범위 파티션을 사용하는 SQL 예제: time_id를 기준으로 연도별로 파티션을 설정하였다. 예를 들어, 1998년 데이터는 PARTITION SALES_1998에, 1999년 데이터는 PARTITION SALES_1999에 저장되게 된다.

✅ CREATE TABLE time_range_sales는 time_range_sales라는 테이블을 생성한다.

✅ PARTITION BY RANGE (time_id)를 통해 time_id 열의 값을 기준으로 파티셔닝이 설정되었다.

✅ 각 연도별로 VALUES LESS THAN 조건을 사용하여 범위를 설정하고, 마지막 파티션인 SALES_2001은 MAXVALUE를 사용하여 모든 나머지 값을 포함하도록 설정한다.

⬇️

범위 파티셔닝을 통해 데이터를 연도별로 나눈 결과를 살펴본다. 각 테이블은 특정 연도에 해당하는 데이터만 포함하고 있으며, 예를 들어 SALES_1998 파티션에는 1998년에 기록된 데이터가, SALES_1999 파티션에는 1999년에 기록된 데이터가 저장되었다.

✅ SALES_1998: 1998년의 판매 데이터를 포함하고 있으며, TIME_ID가 1998년인 데이터만 저장된다.

✅ SALES_1999: 1999년의 판매 데이터를 포함하고 있으며, 1999년에 해당하는 TIME_ID의 데이터를 저장한다.

✅ SALES_2000과 SALES_2001: 각 연도의 데이터를 각각의 파티션에 저장하여, 특정 연도에 해당하는 데이터만 빠르게 조회할 수 있게 한다.

파티션 #6: 해시 파티셔닝 (Hash Partitioning)

💡요약: 해시 파티셔닝 (Hash Partitioning)은 해시 함수를 이용해 데이터를 고르게 분산하며, 이를 통해 디스크 I/O 성능을 개선할 수 있다.

해시 함수 (Hash Function)를 이용하여 데이터를 균등하게 분산시키는 파티셔닝 방식이다.

해시 함수에 의해 자동으로 데이터가 여러 파티션에 고르게 나뉘며, 파티션 개수만큼 데이터가 분배된다.
데이터를 여러 위치에 분산 배치하여 디스크 I/O 성능 (Disk I/O Performance)을 개선하는 효과가 있다.

⬇️

✅ 해시 파티셔닝 (Hash Partitioning) 적용 가능성:

이 예제 데이터는 PROD_ID, CUST_ID, TIME_ID 등 여러 열을 포함하고 있으며, 특정 값을 해시 함수로 변환하여 데이터를 분산할 수 있다.
예를 들어, CUST_ID나 PROD_ID를 해시 함수로 처리하여 데이터를 여러 파티션에 균등하게 나눌 수 있다. 이렇게 하면 특정 파티션에 데이터가 몰리지 않게 되어 균형 잡힌 데이터 분포를 유지할 수 있게 된다.

⬇️

✅ CREATE TABLE hash_sales는 hash_sales라는 테이블을 생성하며, prod_id를 해시 파티셔닝의 기준 열로 사용한다. 데이터를 두 개의 파티션에 균등하게 분배한다.

✅ 데이터는 prod_id 값에 대한 해시 함수를 통해 두 파티션에 고르게 분배된다. 파티션 수를 2개로 설정하였으므로, prod_id 값에 따라 각 데이터가 두 파티션 중 하나에 할당된다.

⬇️

아래 예제는 prod_id 값을 기준으로 해시 파티셔닝을 적용하여 데이터를 두 개의 파티션(SYS_P33와 SYS_P34)으로 나눈 결과를 보여준다.

prod_id의 해시 값에 따라 데이터가 두 파티션에 균등하게 분배되었다.

✅ SYS_P33: 해시 함수 결과에 따라 prod_id 값이 할당된 데이터가 포함된 파티션이다.

✅ SYS_P34: 다른 해시 값에 해당하는 prod_id 데이터가 저장된 파티션이다.

파티션 #7: 파티셔닝된 테이블의 특징 (Partitioned Table)

💡요약: 파티셔닝된 테이블 (Partitioned Table)은 데이터를 여러 파티션으로 나누어 관리할 수 있어, 독립적인 관리와 저장이 가능하며, 특정 상황에서는 파티션을 압축하여 저장 공간을 절약할 수 있게 된다.

✅파티셔닝된 테이블 (Partitioned Table)의 특징:

다중 파티션 (Multiple Partitions): 하나의 테이블이 하나 또는 여러 개의 파티션으로 분할된다.
독립적인 관리 (Independent Management): 각 파티션은 독립적으로 관리할 수 있어 특정 파티션만 따로 백업하거나 복구할 수 있다.
독립된 저장 단위 (Independent Storage Units): 각 파티션은 별도의 파티션 세그먼트에 저장되어 효율적인 데이터 접근이 가능하다.
압축 가능 (Compressible in Low Update Environments): 데이터 수정이 적은 환경에서는 파티션 단위로 압축을 적용하여 저장 공간을 절약할 수 있게 된다.

2️⃣ 뷰 (View)

뷰 (View)는 하나 또는 그 이상의 테이블에 대한 논리적 표현 (Logical Representation)이다.
복잡한 쿼리를 미리 정의해 두고 이를 객체로 저장해 놓아, 필요할 때마다 해당 쿼리를 실행하지 않고도 데이터를 간단히 조회할 수 있게 한다.

❓보안적인 부분에서도 뷰가 정의 될 수 있나요❓

뷰는 데이터의 특정 부분만 노출하여 민감한 정보 보호, 권한 제한, 접근 추적 등 보안 강화에 유용하게 사용될 수 있다.

뷰 #1: 생성 예 (Examples of View Creation)

💡요약: 뷰는 기반 테이블 (Base Table)의 특정 부분이나 여러 테이블 간의 조인 (Join)을 통해 생성될 수 있으며, 데이터를 요약하여 제공하거나 다른 뷰와 결합하여 정보를 제공할 수 있다.

✅ 기반 테이블 (Base Table)의 행이나 열의 부분집합 (Subset):

원본 테이블의 특정 행이나 열만 포함하여 사용자가 필요로 하는 정보만 보여줄 수 있다.

✅ 기반 테이블 두 개 이상의 조인 (Join of Two or More Base Tables):

여러 테이블을 조인하여 한 뷰에 표시할 수 있다. 이를 통해 관련된 데이터를 한 번에 조회할 수 있게 된다.

✅ 기반 테이블의 통계적 요약 (Summary of Base Table Statistics):

원본 테이블의 통계적 요약 정보를 보여줄 수 있어, 데이터의 요약 정보를 쉽게 확인할 수 있다.

✅ 다른 뷰의 부분 집합, 뷰와 테이블의 결합 (Subset of Another View, Combination of Views and Tables):

기존 뷰와 테이블을 결합하거나 다른 뷰의 일부를 포함한 새로운 뷰를 생성할 수 있다.

뷰 #2: 정의 문법 (Syntax for Defining a View)

💡요약: 뷰 정의 문법 (Syntax for Defining a View)은 CREATE VIEW 구문을 사용하여 뷰를 생성하며, FORCE | NOFORCE, WITH CHECK OPTION, WITH READ ONLY 등의 옵션으로 뷰의 속성을 설정할 수 있다.

뷰를 정의할 때 사용하는 기본 문법은 다음과 같다

CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW view_name [(alias1 [, ...n ])]
AS
select_statement [WITH CHECK OPTION] [WITH READ ONLY]

CREATE [OR REPLACE]: 새로운 뷰를 생성하거나 기존 뷰를 대체한다.
FORCE | NOFORCE: 기본 테이블이 존재하지 않아도 뷰를 생성할 수 있게 할지 여부를 설정한다.
VIEW view_name: 생성할 뷰의 이름을 지정한다.
(alias1 [, ...n]): 선택 사항으로, 뷰에서 사용할 열의 별칭을 지정한다.
AS select_statement: 뷰에 표시할 데이터를 정의하는 SELECT 문이다.
WITH CHECK OPTION: 데이터 삽입 또는 수정 시, 뷰의 조건을 항상 만족하게 한다.
WITH READ ONLY: 뷰를 읽기 전용으로 설정하여 데이터를 변경할 수 없게 한다.

뷰 #3: 정의 문법 예제 (Syntax Example for Defining a View)

이 예제는 staff라는 이름의 뷰를 생성하는 SQL 문이다.

CREATE VIEW staff AS
SELECT employee_id, last_name, job_id, manager_id, department_id
FROM employees;

이 뷰는 employees 테이블에서 employee_id, last_name, job_id, manager_id, department_id 열만 선택하여 staff라는 이름의 뷰로 생성한다는 뜻다.
staff 뷰를 사용하면 employees 테이블의 전체 열을 조회할 필요 없이, 지정된 열만 간편하게 조회할 수 있다.

뷰 #4: 베이스 테이블과 뷰 비교 (Example of Defining a View - Comparison between Base Table and View)

💡요약: employees 테이블의 일부 열만 선택해 staff 뷰를 생성함으로써, 필요하지 않은 정보를 제외하고 중요한 데이터만 간단히 조회할 수 있다.

💡베이스 테이블 (Base Table)과 뷰 (View) 비교

employees 테이블은 직원의 employee_id, last_name, job_id, manager_id, hire_date, salary, department_id 열을 포함한다.
staff 뷰는 employees 테이블의 일부 열(employee_id, last_name, job_id, manager_id, department_id)만을 포함하도록 정의되었다.
이 뷰를 통해 불필요한 정보(hire_date, salary)를 제외하고 직원의 기본적인 정보만 접근할 수 있게 되었다.

뷰 #5: 특정 조건을 가진 뷰 정의 예제 (Example of Defining a View with Specific Condition)

💡요약: department_id가 10인 직원만을 포함하는 staff_dept_10 뷰를 생성하는 SQL 예제입니다. 특정 부서의 직원 정보를 조회할 때 유용한 방식이다.

아래 예제는 staff_dept_10라는 이름의 뷰를 생성하는 SQL 문이다.

CREATE VIEW staff_dept_10 AS
SELECT employee_id, last_name, job_id, manager_id, department_id
FROM employees
WHERE department_id = 10;

이 뷰는 employees 테이블에서 department_id가 10인 직원만 포함하도록 필터링된 뷰이다.
staff_dept_10 뷰를 사용하면 department_id가 10인 직원의 기본 정보를 간편하게 조회할 수 있다.

뷰 #6: 조인을 사용한 뷰 정의 예제 (Example of Defining a View with Join)

💡요약: 두 테이블을 조인하여 특정 조건(department_id = 10)에 맞는 데이터를 뷰로 생성하는 SQL 예제이다. employees_view 뷰는 employees와 departments 테이블의 정보를 조합하여 필요한 데이터를 조회할 수 있게 하는 역할을 한다.

아래 예제는 employees_view라는 이름의 뷰를 생성하는 SQL 문이다.

CREATE VIEW employees_view AS
SELECT employee_id, last_name, salary, location_id
FROM employees JOIN departments USING (department_id)
WHERE department_id = 10;

employees 테이블과 departments 테이블을 department_id를 기준으로 조인하여 department_id가 10인 직원의 employee_id, last_name, salary, location_id 정보를 조회할 수 있는 뷰를 만든다.

뷰 #7: 실행 예제 (Example of Executing a View)

아래 SQL 문은 employees_view 뷰에서 employee_id가 200인 직원의 last_name을 조회하는 쿼리이다. 뷰를 통해 필요한 데이터를 쉽게 조회할 수 있다.

SELECT last_name
FROM employees_view
WHERE employee_id = 200;

employees_view 뷰에서 조건에 맞는 last_name 값을 가져오며, 뷰를 통해 복잡한 테이블 구조를 간단히 조회할 수 있다.

뷰 #8: 복잡한 SQL문을 단순화 시키기 (Simplifying Complex SQL Statements)

💡요약: employees와 departments 테이블을 조인하여 특정 조건을 만족하는 직원의 last_name을 조회하는 SQL 예제이다. 뷰를 사용하면 이와 같은 복잡한 쿼리를 간단하게 작성할 수 있다.

아래 SQL 문은 employees와 departments 테이블을 조인하여 특정 조건을 만족하는 직원의 last_name을 조회하는 쿼리이다.

SELECT last_name
FROM employees, departments
WHERE employees.department_id = departments.department_id
  AND departments.department_id = 10
  AND employees.employee_id = 200;

employees.department_id와 departments.department_id가 일치하고, department_id가 10이며 employee_id가 200인 직원의 last_name을 조회하는 조건을 포함한다.
이와 같은 복잡한 쿼리는 뷰를 사용하여 단순화할 수 있다. 예를 들어, employees_view와 같은 뷰를 생성해두면, 복잡한 조인 조건을 매번 작성할 필요 없이 간단히 조회할 수 있게된다.

뷰의 유형 #1: 프로젝션 뷰 (Projection View)

💡요약: 프로젝션 뷰 (Projection View)는 테이블의 특정 열만 포함하는 가장 단순한 형태의 뷰이다. 이를 통해 사용자는 필요한 열만 간단히 조회할 수 있다.

위의 SQL 문은 employees 테이블에서 employee_id, last_name, job_id, manager_id, department_id 열만 선택하여 staff라는 뷰를 생성한다. 생성된 staff 뷰를 통해, 원본 테이블의 일부 열만 선택적으로 조회할 수 있게 되었다.

뷰의 유형 #2: 조인 뷰 (Join View)

💡요약: 조인 뷰 (Join View)는 여러 테이블을 조인하여 관련 데이터를 연결하는 뷰로, 여러 테이블 간의 연관된 정보를 한 번에 조회할 수 있게 한다. 지정된 열의 값을 비교하여 여러 테이블 간에 관련 있는 행을 결합할 수 있다.

위의 SQL 문은 employees와 departments 테이블을 조인하여 department_id가 10인 직원의 정보를 employees_view라는 뷰로 생성한다. 생성된 employees_view 뷰를 통해 두 테이블 간의 연관된 데이터를 한 번에 조회할 수 있게 된다.

뷰의 유형 #3: 인라인 뷰 (Inline View)

💡요약: 인라인 뷰 (Inline View)는SQL 문이 실행되는 동안에만 존재하는 일시적으로 정의된 서브쿼리로, 복잡한 데이터를 간편하게 처리하고 조회할 수 있도록 한다. 주로 집계 함수나 그룹화된 데이터를 처리할 때 유용하다.

아래 SQL 문은 employees 테이블에서 job_id별로 급여의 합계를 구한 후, 이를 원래의 employees 테이블과 조인하여 각 직원의 급여와 해당 job_id의 급여 합계를 조회하는 예제이다. 인라인 뷰 b는 job_id별로 급여의 합계를 계산한 결과를 임시 테이블처럼 사용하여, employees 테이블과 조인한다.

뷰의 수정과 삭제 #1

💡요약: ALTER VIEW 구문을 사용하여 기존 뷰의 열이나 내용을 수정할 수 있고, DROP VIEW 구문을 통해 뷰를 삭제할 수 있다.

✅뷰 수정 (Modifying a View):

ALTER VIEW 구문을 사용하여 기존 뷰의 정의를 수정할 수 있다.

  ALTER VIEW staff AS
  SELECT employee_id, last_name, job_id
  FROM employees;

이 예제에서는 staff 뷰를 수정하여 employee_id, last_name, job_id 열만 선택하도록 변경한다.

✅ 뷰 삭제 (Dropping a View):

DROP VIEW 구문을 사용하여 뷰를 삭제할 수 있다.
```
  DROP VIEW staff;
```
이 구문은 staff 뷰를 데이터베이스에서 완전히 제거한다.

뷰의 수정과 삭제 #2: 데이터 수정 (Data Modification Using Views)

💡요약: 뷰를 통해 데이터를 수정할 수 있지만, 단일 테이블 기반이어야 하며, 집계 함수나 다중 테이블을 참조하는 경우에는 수정에 제한이 있다.

✅뷰를 통한 데이터 수정의 제약:

뷰를 통해 데이터를 삽입 (Insert), 수정 (Update), 삭제 (Delete)할 수 있지만, 몇 가지 제약이 있다.

✅ 단일 테이블 기반의 뷰:

데이터 수정용 뷰는 일반적으로 단일 테이블을 기준으로 정의되어야 한다. 여러 테이블을 조인한 뷰는 데이터 수정이 제한될 수 있기 때문이다.

✅ 계산된 값 및 집계 함수 포함 뷰:

계산된 값이나 집계 함수(예: AVG, COUNT, MAX, MIN, SUM 등) 또는 GROUP BY 절을 포함하는 뷰는 데이터 수정이 불가능하다. 이러한 뷰는 조회만 가능하도록 사용된다.

✅ 다중 테이블 참조 뷰:

두 개 이상의 테이블을 참조하는 뷰를 통해 데이터를 수정할 때는 한 번에 한 테이블만 접근할 수 있다. 동시에 두 개 이상의 테이블에 접근하여 데이터를 수정하려 하면 오류가 발생하게 된다.

✅ 기반 테이블의 NOT NULL 제약 조건

뷰에서 참조되지 않는 기반 테이블의 열이 NOT NULL 제약을 가지고 있고 기본값이 없는 경우, 해당 열에 값을 입력하지 않으면 오류가 발생할 수 있다.

✅ WITH CHECK OPTION:

뷰에 WITH CHECK OPTION이 설정되어 있으면, 이 뷰를 통해 데이터를 수정할 때 뷰 정의의 SELECT 문 범위를 벗어나는지 여부를 검사하게 된다.
수정된 데이터가 뷰의 조건을 만족하지 않으면 오류가 발생한다. 이는 데이터의 일관성을 유지하기 위해 사용된다.

뷰의 수정과 삭제 #3: 구체화된 뷰 (Materialized Views)

💡요약: 구체화된 뷰는 실제 데이터를 저장하여 반복적인 쿼리의 성능을 향상시키고, 원거리 테이블의 근거리 사본으로 활용된다. DB2에서는 구체화된 쿼리 테이블로, SQL Server에서는 인덱스된 뷰로 불린다.

✅실제 데이터 저장 (Stores Actual Data): 구체화된 뷰는 쿼리 결과를 물리적으로 저장하여, 매번 원본 테이블을 조회하지 않고도 빠르게 데이터를 제공합니다.

✅저장 공간 사용 (Consumes Storage Space): 데이터를 저장하므로 추가적인 저장 공간이 필요합니다.

✅기반 테이블 데이터 변경 시 동기화 (Synchronizes with Base Table Changes): 원본 테이블의 데이터가 변경되면 구체화된 뷰의 데이터도 갱신되어 일관성을 유지합니다.

✅반복 쿼리 성능 향상 (Improves Performance for Repetitive Queries): 동일한 쿼리를 반복적으로 실행할 때, 구체화된 뷰를 사용하면 성능이 향상됩니다.

✅원거리 테이블의 근거리 사본으로 활용 (Acts as a Local Copy of Remote Tables): 원격 데이터베이스의 테이블을 로컬에서 구체화된 뷰로 생성하여 접근 속도를 높일 수 있습니다.

DBMS별 명칭:

DB2: 구체화된 쿼리 테이블 (Materialized Query Table)
SQL Server: 인덱스된 뷰 (Indexed View)

⬇️

다음 SQL 문은 sales_mv라는 실체화된 뷰를 생성하는 예제이다.

CREATE MATERIALIZED VIEW sales_mv AS
SELECT t.calendar_year, p.prod_id, SUM(s.amount_sold) AS sum_sales
FROM times t, products p, sales s
WHERE t.time_id = s.time_id
AND p.prod_id = s.prod_id
GROUP BY t.calendar_year, p.prod_id;

times, products, sales라는 세 개의 테이블을 조인하여, 각 상품(prod_id)의 연도별(calendar_year) 총 판매 금액(sum_sales)을 계산하여 저장한다.
이 구문을 실행하면 쿼리 결과가 sales_mv라는 뷰에 저장된다.

3️⃣ 시퀀스 (Sequence)

💡요약: 시퀀스 (Sequence)는 중복되지 않는 고유한 번호를 자동으로 생성하여 기본 키로 사용하거나 정렬에 활용할 수 있다.

시퀀스 (Sequence)는 특정한 숫자 식별자를 자동으로 생성하는 방법을 제공한다.
예를 들어, 인터넷 쇼핑몰에서 주문번호는 중복되지 않아야 하는 고유한 번호이다. 시퀀스를 사용하여 자동으로 번호를 생성하면 중복을 방지할 수 있게 된다.
오라클 (Oracle)과 같은 데이터베이스에서 고유 번호를 자동으로 생성하기 위해 시퀀스를 사용한다.
시퀀스는 주로 테이블의 기본 키 (Primary Key)나 유니크 키 (Unique Key)로 사용된다.
생성된 숫자는 순서대로 제공되므로 정렬 기준으로도 사용할 수 있다.

예제:

SELECT MAX(주문번호) + 1 FROM 주문테이블;

위 SQL은 주문테이블에서 가장 큰 주문번호에 1을 더해 새로운 주문번호를 생성한다.
현재 가장 큰 주문번호를 가져와서 그 값에 1을 더하여 새 주문번호를 만든다.

시퀀스 1#: 특징 (Characteristics of Sequence)

💡요약: 시퀀스 (Sequence)는 병렬 처리와 메모리 캐싱이 가능하여 효율적이며, 여러 테이블에서 독립적으로 사용할 수 있다.

✅병렬적으로 처리 가능 (Parallel Processing Capable): 여러 프로세스가 동시에 시퀀스를 사용할 수 있어, 데이터베이스에서 병렬 작업을 지원한다.

✅ 메모리에 번호를 미리 저장 (Pre-cached in Memory): 메모리에 시퀀스 번호를 미리 저장해 두므로 데이터베이스 접근 속도를 높여 효율성을 증가시킨다.

✅ 독립적으로 존재하여 여러 테이블에서 사용 가능 (Independently Usable Across Multiple Tables): 시퀀스는 테이블에 종속되지 않으므로, 하나의 시퀀스를 여러 테이블에서 독립적으로 활용할 수 있다.

시퀀스 2#: 예제 (Example of Sequence)

💡요약: HR 스키마에는 여러 시퀀스가 존재하며, 각 시퀀스는 테이블에 고유한 식별자를 부여하는 데 사용된다. 시퀀스는 독립적으로 작동하며, 다른 증가 규칙과 최대 값을 설정할 수 있다.

✅ department_seq: 이 시퀀스는 9,990까지 값을 가지며, 10씩 증가한다. 현재 값은 280이다.

✅ employees_seq: 이 시퀀스는 1씩 증가하며, 현재 값은 207이다.

✅ locations_seq: 이 시퀀스는 9,990까지 값을 가지며, 100씩 증가한다. 현재 값은 3,300이다.

아래의 속성들은 시퀀스의 동작 방식을 결정하는 데 필수적이다. 특히 max_value와 min_value는 시퀀스의 범위를 정의하고, increment_by는 증가 간격을 결정한다. sequence_name은 시퀀스를 식별하고, last_number는 현재 상태를 추적하며, created는 생성된 날짜를 제공한다.

시퀀스의 주요 속성 (Key Attributes of Sequence)

최대 값 (max_value): 시퀀스가 가질 수 있는 최대 숫자를 뜻한다. 이 값에 도달하면 시퀀스가 더 이상 증가하지 않거나, 사이클(CYCLE) 옵션이 설정된 경우 최소값으로 되돌아가게 된다.
최소 값 (min_value): 시퀀스가 가질 수 있는 최소 숫자이다. 일반적으로 시퀀스가 음수가 아닌 양수로 시작할 때 설정된다.
생성 일자 (created): 해당 시퀀스가 생성된 날짜를 나타낸다. 시퀀스가 데이터베이스 내에서 언제부터 사용되었는지 확인할 수 있다.
시퀀스 이름 (sequence_name): 시퀀스의 고유한 이름이다. 테이블의 기본 키 또는 유니크 키 등 특정 목적으로 사용될 때 시퀀스를 식별하는 데 사용된다.
현재 값 (last_number): 시퀀스가 마지막으로 생성한 숫자이다. 다음에 생성될 숫자를 추적하고, 마지막에 사용된 값을 나타낸다.
증가 값 (increment_by): 시퀀스가 증가 또는 감소하는 값이다. 예를 들어, increment_by가 1이면 시퀀스는 한 번 호출될 때마다 1씩 증가한다.

시퀀스 #3: 생성 방법 (How to Create a Sequence)

💡요약: 시퀀스는 데이터베이스에서 고유 식별자를 자동으로 생성하는데 유용합니다. 주요 옵션에는 INCREMENT BY, START WITH, MAXVALUE, CYCLE 등이 있으며, 각 옵션은 시퀀스의 증가 값, 시작 값, 최대 값, 순환 여부 등을 설정하게 된다.

✅시퀀스 생성의 주요 옵션 (Key Options for Creating a Sequence)

INCREMENT BY: 시퀀스가 증가하는 값이다. 예를 들어, INCREMENT BY 1로 설정하면 시퀀스가 1씩 증가하게 된다.
START WITH: 시퀀스가 시작하는 초기 값이다. 예를 들어, START WITH 20으로 설정하면 시퀀스는 20부터 시작하게 된다.
MAXVALUE: 시퀀스가 가질 수 있는 최대 값이다. 예를 들어, MAXVALUE 100으로 설정하면 시퀀스는 100에서 멈춘다.
NOMAXVALUE: 최대 값을 설정하지 않고 무한대로 증가할 수 있게 한다.
MINVALUE: 시퀀스가 가질 수 있는 최소 값이다.
NOMINVALUE: 최소 값을 설정하지 않고 기본값으로 시작하게 한다.
CYCLE | NOCYCLE: CYCLE은 시퀀스가 최대 값에 도달하면 최소 값으로 돌아가는 옵션이고, NOCYCLE은 최대 값에 도달한 후 더 이상 생성되지 않도록 한다.
CACHE: 시퀀스 값을 메모리에 미리 저장하여 성능을 높인다. CACHE 20으로 설정하면 20개의 시퀀스 값을 미리 캐싱해둔다.
NOCACHE: 캐싱을 사용하지 않는다.

CREATE SEQUENCE TEST_SEQ
INCREMENT BY 1
START WITH 20
MAXVALUE 100
NOCACHE
NOCYCLE;

이 예제에서 시퀀스 TEST_SEQ는 20에서 시작하여 1씩 증가하며, 최대 값은 100이다. NOCYCLE로 설정되어 있어 최대 값에 도달하면 더 이상 생성되지 않게 된다.

❓Value 100이 넘으면 어떻게 될까❓

시퀀스의 MAXVALUE를 100으로 설정하면, 시퀀스 값이 100에 도달하면 더 이상 증가하지 않으며, 이때 시퀀스 에러가 발생할 수 있다. 시퀀스가 계속 증가해야 하는 경우에는 다음 두 가지 방법 중 하나를 선택할 수 있다.

CYCLE 옵션 사용: 시퀀스가 최대 값에 도달하면 다시 최소 값으로 돌아가도록 설정한다. 이 경우 시퀀스는 순환되며 에러가 발생하지 않는다.
```
 CREATE SEQUENCE test_seq
 INCREMENT BY 1
 START WITH 20
 MAXVALUE 100
 CYCLE;
```
- 이 설정에서는 100에 도달한 후 시퀀스가 다시 시작 값으로 순환된다.
MAXVALUE 증가: MAXVALUE를 더 큰 값으로 설정하여 에러가 발생하지 않도록 한다.
```
 REATE SEQUENCE test_seq
 INCREMENT BY 1
 START WITH 20
 MAXVALUE 1000
 NOCYCLE;
```
- MAXVALUE를 1000으로 설정하면, 시퀀스는 1000까지 계속 증가할 수 있다.

요약: 시퀀스가 최대 값에 도달했을 때 에러가 발생하지 않도록 하려면 CYCLE 옵션을 사용하거나 MAXVALUE를 증가시켜야 한다.

시퀀스 #4: 생성 - 실행 화면 (Sequence Creation - Execution Screen)

💡요약: 아래 예제는 TEST_SEQ라는 시퀀스를 생성하는 과정을 보여주며, 1씩 증가, 20에서 시작, 최대 100까지 증가하는 설정을 포함합니다. NOCACHE와 NOCYCLE 옵션을 통해 캐싱을 사용하지 않고 최대 값에 도달하면 증가를 멈추도록 설정되어 있다.

위 이미지는 SQL 개발 도구에서 TEST_SEQ라는 시퀀스를 생성하는 SQL 코드와 실행 결과를 보여주며, "커밋 완료" 메시지를 통해 시퀀스 생성이 성공적으로 완료되었음을 나타낸다.

시퀀스 #5: 값 사용 및 삭제 (Using and Dropping Sequence Values)

💡요약: NEXTVAL을 사용하여 시퀀스의 다음 값을 얻고, CURRVAL을 사용하여 현재 값을 확인할 수 있다. 시퀀스를 삭제하려면 DROP SEQUENCE 명령을 사용한다.

✅시퀀스 값 사용 (Using Sequence Values)

시퀀스명.NEXTVAL: 시퀀스의 다음 값을 반환한다. 이 명령을 호출할 때마다 시퀀스 값이 증가하게 된다. NEXTVAL 호출 시 값이 증가하는 것을 확인할 수 있다.
시퀀스명.CURRVAL: 시퀀스의 현재 값을 반환한다. NEXTVAL을 먼저 호출한 후 사용할 수 있으며, 현재 증가된 값을 반환하게 된다.

✅예제 SQL 명령어:

다음 SQL 문은 시퀀스 test_seq의 현재 값과 다음 값을 호출하는 예제이다.

  SELECT test_seq.currval FROM dual;  -- 현재 값 조회
  SELECT test_seq.nextval FROM dual;  -- 다음 값 조회

시퀀스 삭제 (Dropping a Sequence)

시퀀스를 삭제할 때는 DROP SEQUENCE 시퀀스명; 명령을 사용한다. 이 명령은 시퀀스를 데이터베이스에서 제거하는 명령이다.

Command Palette