# 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 예시 - 인덱스 및 파티션을 활용한 데이터 관리 * 인사담당자가 직원의 **인사 기록 카드**를 담은 큰 박스를 관리하고 있다. * 특정 월에 입사한 직원을 찾아보는 일이 주요 작업이라고 가정해보자. #### 관리 방법 1. **인덱스 (Index)**: 입사일을 기준으로 인덱스를 생성하여 관리하면 특정 입사일에 입사한 직원을 더 빠르게 찾을 수 있다. 2. **월별 파티션 (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가지 종류에 대해 알아본다. 각각의 파티셔닝 방식은 데이터를 효율적으로 관리하기 위해 다른 기준으로 데이터를 나누는 방법을 제공한다. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1732205967262/f9129400-7ebc-4968-a181-533ce88516cf.png align="center") #### **✅ 리스트 파티셔닝 (List Partitioning)**: * 특정 값 목록에 따라 데이터를 나누는 방법이다. 예를 들어, 국가별로 데이터를 나누고자 할 때 **리스트 파티셔닝**을 사용하여 각 국가 데이터를 별도의 파티션에 저장할 수 있다. #### **✅ 범위 파티셔닝 (Range Partitioning)**: * 연속적인 값의 범위에 따라 데이터를 나누는 방식이다. 예를 들어, 날짜를 기준으로 특정 연도별 데이터를 나누고자 할 때 **범위 파티셔닝**을 사용할 수 있다. #### **✅ 해시 파티셔닝 (Hash Partitioning)**: * 해시 함수를 사용하여 데이터를 균등하게 분산시키는 방식이다. 특정 값이 고르게 분배되도록 **해시 파티셔닝**을 사용하면 데이터가 특정 파티션에 몰리지 않고 효율적으로 분배된다. --- ### 파티션 #5: 리스트 파티셔닝 (List Partitioning) **💡요약**: **리스트 파티셔닝**의 개념을 설명하며, 특정 값을 기준으로 데이터를 나누는 방법에 대해 알아본다. 이를 통해 다양한 기준 값에 따라 데이터를 효율적으로 관리할 수 있게 한다. * **리스트 파티셔닝 (List Partitioning)**: 특정 **값 (Value)**을 기준으로 데이터를 구분하여 파티션하는 방식이다. 주로 고유한 값들로 데이터가 구분될 수 있을 때 사용된다. 예를 들어, 국가별, 지역별 등 특정 값에 따라 데이터를 분류할 때 유용하다. ### ⬇️ ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1732206669067/f81751e5-4f86-47ea-91cd-3fe2652f02ed.png align="center") * 위의 예제 데이터는 **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'`)에 해당하는 데이터를 각 파티션에 저장하도록 설정하였다. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1732206753336/2588af6a-33c1-472c-bab5-4dbc6b0500d8.png align="center") * `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`인 데이터가 포함되어 있다. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1732206896833/aed0b6e3-bd72-47b1-b124-414410fd8e03.png align="center") * **EVEN\_CHANNELS**: **channel\_id**가 짝수 (`2`, `4`)인 레코드를 포함하며, 주로 짝수 채널을 통해 발생한 판매 데이터가 저장된다. * **ODD\_CHANNELS**: **channel\_id**가 홀수 (`3`, `9`)인 레코드를 포함하며, 홀수 채널을 통해 발생한 판매 데이터가 저장된다. --- ### 파티션 #6: 범위 파티셔닝 (Range Partitioning) **💡요약: 범위 파티셔닝**의 특징과 사용할 수 있는 예제 데이터를 보여주며, 각 열의 정보가 어떻게 구성되어 있는지 알아본다. **TIME\_ID** 열을 기준으로 데이터를 나누면, 시간에 따라 데이터를 효율적으로 접근하고 관리할 수 있게 된다. * **범위 파티셔닝 (Range Partitioning)**은 가장 일반적으로 사용되는 파티셔닝 방법으로, 특정 **파티션 키 (Partition Key)** 값의 **범위 (Range)**를 기준으로 데이터를 나눈다. * 주로 날짜 타입의 컬럼을 기준으로 설정하여, 예를 들어 연도나 월별로 데이터를 구분해 저장할 수 있다. 이렇게 하면 시간 단위로 데이터를 효율적으로 관리할 수 있게된다. ### ⬇️ ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1732206139526/1bb583dc-47af-4f31-8a02-285969b3e50b.png align="center") * 위의 데이터는 **제품 ID (Product ID)**, **고객 ID (Customer ID)**, **시간 ID (Time ID)** 등을 포함하고 있으며, 특정 날짜를 기준으로 판매된 양과 금액 등이 기록되어 있다. * **범위 파티셔닝**을 적용할 경우, **TIME\_ID**를 기준으로 데이터가 특정 연도 또는 월별로 나뉘어져 저장될 수 있습니다. 이렇게 하면 특정 기간에 해당하는 데이터를 빠르게 조회할 수 있게된다. ### ⬇️ **연도별로 데이터를 나누기 위해 범위 파티션을 사용하는 SQL 예제:** time\_id를 기준으로 연도별로 파티션을 설정하였다. 예를 들어, 1998년 데이터는 PARTITION SALES\_1998에, 1999년 데이터는 PARTITION SALES\_1999에 저장되게 된다. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1732206252927/608d2fb7-b232-4e32-9070-e78db5c2cc84.png align="center") ✅ `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년에 기록된 데이터가 저장되었다. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1732206456251/9d4ef571-c6b5-421d-9304-282d628e48d0.png align="center") ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1732206493984/88370774-a37b-4cf6-a878-840a15657fdd.png align="center") ✅ **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)**을 개선하는 효과가 있다. ### ⬇️ ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1732207077628/61748a4c-09a4-474e-bd7c-4be7f9e5ea09.png align="center") #### **✅ 해시 파티셔닝 (Hash Partitioning)** 적용 가능성: * 이 예제 데이터는 **PROD\_ID**, **CUST\_ID**, **TIME\_ID** 등 여러 열을 포함하고 있으며, 특정 값을 해시 함수로 변환하여 데이터를 분산할 수 있다. * 예를 들어, **CUST\_ID**나 **PROD\_ID**를 해시 함수로 처리하여 데이터를 여러 파티션에 균등하게 나눌 수 있다. 이렇게 하면 특정 파티션에 데이터가 몰리지 않게 되어 균형 잡힌 데이터 분포를 유지할 수 있게 된다. ### ⬇️ ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1732207151775/74ab137b-1354-441c-bbd2-f56f482868c7.png align="center") ✅ `CREATE TABLE hash_sales`는 **hash\_sales**라는 테이블을 생성하며, `prod_id`를 해시 파티셔닝의 기준 열로 사용한다. 데이터를 두 개의 파티션에 균등하게 분배한다. ✅ 데이터는 **prod\_id** 값에 대한 해시 함수를 통해 두 파티션에 고르게 분배된다. 파티션 수를 2개로 설정하였으므로, **prod\_id** 값에 따라 각 데이터가 두 파티션 중 하나에 할당된다. ### ⬇️ 아래 예제는 **prod\_id** 값을 기준으로 해시 파티셔닝을 적용하여 데이터를 두 개의 파티션(**SYS\_P33**와 **SYS\_P34**)으로 나눈 결과를 보여준다. **prod\_id**의 해시 값에 따라 데이터가 두 파티션에 균등하게 분배되었다. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1732207253253/00ceccac-1c44-40b7-b8da-bedb855a8d1d.png align="center") ✅ **SYS\_P33**: 해시 함수 결과에 따라 **prod\_id** 값이 할당된 데이터가 포함된 파티션이다. ✅ **SYS\_P34**: 다른 해시 값에 해당하는 **prod\_id** 데이터가 저장된 파티션이다. --- ### 파티션 #7: 파티셔닝된 테이블의 특징 (Partitioned Table) **💡요약:** 파티셔닝된 테이블 (Partitioned Table)은 데이터를 여러 파티션으로 나누어 관리할 수 있어, 독립적인 관리와 저장이 가능하며, 특정 상황에서는 파티션을 압축하여 저장 공간을 절약할 수 있게 된다. #### **✅파티셔닝된 테이블 (Partitioned Table)**의 특징: 1. **다중 파티션 (Multiple Partitions)**: 하나의 테이블이 하나 또는 여러 개의 파티션으로 분할된다. 2. **독립적인 관리 (Independent Management)**: 각 파티션은 독립적으로 관리할 수 있어 특정 파티션만 따로 백업하거나 복구할 수 있다. 3. **독립된 저장 단위 (Independent Storage Units)**: 각 파티션은 별도의 파티션 세그먼트에 저장되어 효율적인 데이터 접근이 가능하다. 4. **압축 가능 (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 등의 옵션으로 뷰의 속성을 설정할 수 있다. 뷰를 정의할 때 사용하는 기본 문법은 다음과 같다 ```sql CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW view_name [(alias1 [, ...n ])] AS select_statement [WITH CHECK OPTION] [WITH READ ONLY] ``` 1. **CREATE \[OR REPLACE\]**: 새로운 뷰를 생성하거나 기존 뷰를 대체한다. 2. **FORCE | NOFORCE**: 기본 테이블이 존재하지 않아도 뷰를 생성할 수 있게 할지 여부를 설정한다. 3. **VIEW view\_name**: 생성할 뷰의 이름을 지정한다. 4. **(alias1 \[, ...n\])**: 선택 사항으로, 뷰에서 사용할 열의 별칭을 지정한다. 5. **AS select\_statement**: 뷰에 표시할 데이터를 정의하는 SELECT 문이다. 6. **WITH CHECK OPTION**: 데이터 삽입 또는 수정 시, 뷰의 조건을 항상 만족하게 한다. 7. **WITH READ ONLY**: 뷰를 읽기 전용으로 설정하여 데이터를 변경할 수 없게 한다. --- ### 뷰 #3: 정의 문법 예제 (Syntax Example for Defining a View) 이 예제는 **staff**라는 이름의 뷰를 생성하는 SQL 문이다. ```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** 뷰를 생성함으로써, 필요하지 않은 정보를 제외하고 중요한 데이터만 간단히 조회할 수 있다. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1732207944046/136445b9-ac4f-4803-94f5-061f953b355e.png align="center") #### **💡베이스 테이블 (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 문이다. ```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 문이다. ```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**을 조회하는 쿼리이다. 뷰를 통해 필요한 데이터를 쉽게 조회할 수 있다. ```sql 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**을 조회하는 쿼리이다. ```sql 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)는 테이블의 특정 열만 포함하는 가장 단순한 형태의 뷰이다. 이를 통해 사용자는 필요한 열만 간단히 조회할 수 있다. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1732208574226/5d01a7d7-08c3-4e21-861e-e0873802912e.png align="center") 위의 SQL 문은 **employees** 테이블에서 **employee\_id**, **last\_name**, **job\_id**, **manager\_id**, **department\_id** 열만 선택하여 **staff**라는 뷰를 생성한다. 생성된 **staff** 뷰를 통해, 원본 테이블의 일부 열만 선택적으로 조회할 수 있게 되었다. --- ### 뷰의 유형 #2: 조인 뷰 (Join View) **💡요약:** 조인 뷰 (Join View)는 여러 테이블을 조인하여 관련 데이터를 연결하는 뷰로, 여러 테이블 간의 연관된 정보를 한 번에 조회할 수 있게 한다. 지정된 열의 값을 비교하여 여러 테이블 간에 관련 있는 행을 결합할 수 있다. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1732208639090/5b161b42-b362-4f52-b922-43ca2621de9a.png align="center") 위의 SQL 문은 **employees**와 **departments** 테이블을 조인하여 **department\_id**가 10인 직원의 정보를 **employees\_view**라는 뷰로 생성한다. 생성된 **employees\_view** 뷰를 통해 두 테이블 간의 연관된 데이터를 한 번에 조회할 수 있게 된다. --- ### 뷰의 유형 #3: 인라인 뷰 (Inline View) **💡요약:** **인라인 뷰 (Inline View)**는SQL 문이 실행되는 동안에만 존재하는 일시적으로 정의된 서브쿼리로, 복잡한 데이터를 간편하게 처리하고 조회할 수 있도록 한다. 주로 집계 함수나 그룹화된 데이터를 처리할 때 유용하다. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1732208793597/66b6f9c2-8a23-4afd-90f8-20727f12f41c.png align="center") 아래 SQL 문은 **employees** 테이블에서 **job\_id**별로 급여의 합계를 구한 후, 이를 원래의 **employees** 테이블과 조인하여 각 직원의 급여와 해당 **job\_id**의 급여 합계를 조회하는 예제이다. 인라인 뷰 **b**는 **job\_id**별로 급여의 합계를 계산한 결과를 임시 테이블처럼 사용하여, **employees** 테이블과 조인한다. --- ### 뷰의 수정과 삭제 #1 **💡요약:** **ALTER VIEW** 구문을 사용하여 기존 뷰의 열이나 내용을 수정할 수 있고, **DROP VIEW** 구문을 통해 뷰를 삭제할 수 있다. #### **✅뷰 수정 (Modifying a View)**: * **ALTER VIEW** 구문을 사용하여 기존 뷰의 정의를 수정할 수 있다. ```sql 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** 구문을 사용하여 뷰를 삭제할 수 있다. ```sql 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**라는 실체화된 뷰를 생성하는 예제이다. ```sql 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)**로 사용된다. * 생성된 숫자는 **순서대로** 제공되므로 정렬 기준으로도 사용할 수 있다. #### 예제: ```sql 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 스키마에는 여러 시퀀스가 존재하며, 각 시퀀스는 테이블에 고유한 식별자를 부여하는 데 사용된다. 시퀀스는 독립적으로 작동하며, 다른 증가 규칙과 최대 값을 설정할 수 있다. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1732212216365/0f41d19a-9a35-43a1-b7c6-26669bee08c3.png align="center") ✅ **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)** 1. **최대 값 (max\_value)**: 시퀀스가 가질 수 있는 최대 숫자를 뜻한다. 이 값에 도달하면 시퀀스가 더 이상 증가하지 않거나, 사이클(CYCLE) 옵션이 설정된 경우 최소값으로 되돌아가게 된다. 2. **최소 값 (min\_value)**: 시퀀스가 가질 수 있는 최소 숫자이다. 일반적으로 시퀀스가 음수가 아닌 양수로 시작할 때 설정된다. 3. **생성 일자 (created)**: 해당 시퀀스가 생성된 날짜를 나타낸다. 시퀀스가 데이터베이스 내에서 언제부터 사용되었는지 확인할 수 있다. 4. **시퀀스 이름 (sequence\_name)**: 시퀀스의 고유한 이름이다. 테이블의 기본 키 또는 유니크 키 등 특정 목적으로 사용될 때 시퀀스를 식별하는 데 사용된다. 5. **현재 값 (last\_number)**: 시퀀스가 마지막으로 생성한 숫자이다. 다음에 생성될 숫자를 추적하고, 마지막에 사용된 값을 나타낸다. 6. **증가 값 (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**: 캐싱을 사용하지 않는다. ```sql 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에 도달하면 더 이상 증가하지 않으며, 이때 **시퀀스 에러**가 발생할 수 있다. 시퀀스가 계속 증가해야 하는 경우에는 다음 두 가지 방법 중 하나를 선택할 수 있다. 1. **CYCLE 옵션 사용**: 시퀀스가 최대 값에 도달하면 다시 최소 값으로 돌아가도록 설정한다. 이 경우 시퀀스는 순환되며 에러가 발생하지 않는다. ```sql CREATE SEQUENCE test_seq INCREMENT BY 1 START WITH 20 MAXVALUE 100 CYCLE; ``` * 이 설정에서는 100에 도달한 후 시퀀스가 다시 시작 값으로 순환된다. 2. **MAXVALUE 증가**: MAXVALUE를 더 큰 값으로 설정하여 에러가 발생하지 않도록 한다. ```sql 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** 옵션을 통해 캐싱을 사용하지 않고 최대 값에 도달하면 증가를 멈추도록 설정되어 있다. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1732212738757/fea5948b-5606-45ed-9430-1ef15e1c06be.png align="center") 위 이미지는 SQL 개발 도구에서 `TEST_SEQ`라는 시퀀스를 생성하는 SQL 코드와 실행 결과를 보여주며, "커밋 완료" 메시지를 통해 시퀀스 생성이 성공적으로 완료되었음을 나타낸다. --- ### 시퀀스 #5: 값 사용 및 삭제 (Using and Dropping Sequence Values) **💡요약: NEXTVAL**을 사용하여 시퀀스의 다음 값을 얻고, **CURRVAL**을 사용하여 현재 값을 확인할 수 있다. 시퀀스를 삭제하려면 `DROP SEQUENCE` 명령을 사용한다. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1732212832471/0c8a69f5-cd9f-46a3-8781-e8f8eda48097.png align="center") #### **✅시퀀스 값 사용 (Using Sequence Values)** * **시퀀스명.NEXTVAL**: 시퀀스의 **다음 값**을 반환한다. 이 명령을 호출할 때마다 시퀀스 값이 증가하게 된다. `NEXTVAL` 호출 시 값이 증가하는 것을 확인할 수 있다. * **시퀀스명.CURRVAL**: 시퀀스의 **현재 값**을 반환한다. NEXTVAL을 먼저 호출한 후 사용할 수 있으며, 현재 증가된 값을 반환하게 된다. #### **✅예제 SQL 명령어**: * 다음 SQL 문은 시퀀스 `test_seq`의 현재 값과 다음 값을 호출하는 예제이다. ```sql SELECT test_seq.currval FROM dual; -- 현재 값 조회 SELECT test_seq.nextval FROM dual; -- 다음 값 조회 ``` #### **시퀀스 삭제 (Dropping a Sequence)** * 시퀀스를 삭제할 때는 `DROP SEQUENCE 시퀀스명;` 명령을 사용한다. 이 명령은 시퀀스를 데이터베이스에서 제거하는 명령이다.