파티션(Partition)

오라클 데이터베이스에서는 파티셔닝을 통해 큰 테이블과 인덱스를 관리하기 쉬운 작은 파티션으로 분해할 수 있다. 각 파티션은 독립적인 객체로 이름을 가지고 있으며, 저장 특성을 옵션으로 가질 수 있다.

파티셔닝을 통해 DB가 나누어지더라도, 어플리케이션 관점에서는 하나의 스키마 객체만 존재한다. 따라서 SQL문장이 바뀔 일은 없다. 파티셔닝은 대량의 데이터를 관리해야 할 경우 적합하다.

파티셔닝의 장점

• 높은 가용성
  특정 파티션이 사용 불가능해져도 다른 파티션에는 영향이 가지 않음. 쿼리 옵티마이저가 자동으로 참조할 수 없는 파티션들을 query plan 에서 제외하기 때문이다.

• 스키마 객체들을 관리하기 쉬워짐
  전체 테이블이나 인덱스가 아닌, 각 파티션 조각들의 집합 또는 병합을 통해 DDL문을 조작할 수 있다.

• OLTP 시스템에서 공유 자원 경합 감소
  DML작업이 하나의 세그먼트가 아니라 여러 세그먼트에 분산되어 수행되어지기 때문에 공유 자원에 대한 경합을 줄일 수 있다.

• 데이터 웨어하우스에서 쿼리 성능 향상
  애드혹 쿼리의 처리속도를 향상 시킬 수 있다.

파티션의 특징

각 파티션들은 반드시 같은 논리적 속성들을 가지고 있어야 한다. 하지만 테이블스페이스 같은 물리적 속성을 별로도 가질 수 있다.

테이블스페이스: 성능 향상을 위해 테이블들을 테이블스페이스란 공간에 미리 정의된 용량만큼 나누어 관리함.

파티션 키

테이블에서 특정 행(row)이 어떤 파티션에 저장되어야 하는지를 결정한다. 각각의 행은 정확하게 하나의 파티션에 할당이 된다.

파티셔닝 전략

range, list, hash 등 다양한 파티셔닝 전략이 존재한다.

• 싱글레벨 파티셔닝
  한 가지 메소드(range,list 등)만 사용해서 데이터를 분산한다.

• 복합 파티셔닝
  하나의 메소드로 파티션을 나누고 나누어진 파티션을 두번째 메소드를 통해 또 나누어 서브 파티션을 만드는 것이다.

범위 파티셔닝(Range Partitioning)

가장 기본적인 파티셔닝 방식으로, 파티션 키 값의 범위에 따라 행들을 파티션에 매핑한다.

CREATE TABLE time_range_sales
 ( prod_id NUMBER(6)
 , cust_id NUMBER
 , time_id DATE
 , channel_id CHAR(1)
 , promo_id NUMBER(6)
 , quantity_sold NUMBER(3)
 , amount_sold NUMBER(10,2)
 )
PARTITION BY RANGE (time_id)
 (PARTITION SALES_1998 VALUES LESS THAN (TO_DATE('01-JAN-1999','DD-MON-YYYY')),
 PARTITION SALES_1999 VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY')),
 PARTITION SALES_2000 VALUES LESS THAN (TO_DATE('01-JAN-2001','DD-MON-YYYY')),
 PARTITION SALES_2001 VALUES LESS THAN (MAXVALUE)
 ); 

인터벌 파티셔닝(Interval Partitioning)

범위 파티셔닝의 확장으로, 범위를 벗어나는 데이터를 삽입하면 미리 지정된 간격(interval)으로 파티션을 자동 생성한다. 즉, 명시적으로 파티션을 생성하지 않고도 새로운 파티션을 고정된 간격으로 생성할 수 있다. 인터벌 파티셔닝을 사용할 땐 최소 1개의 range 파티션이 존재해야 한다.

CREATE TABLE interval_sales
 ( prod_id NUMBER(6)
 , cust_id NUMBER
 , time_id DATE
 , channel_id CHAR(1)
 , promo_id NUMBER(6)
 , quantity_sold NUMBER(3)
 , amount_sold NUMBER(10,2)
 )
 PARTITION BY RANGE (time_id)
 INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')) // 1달 interval
 ( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2010', 'DD-MM-YYYY'))
 , PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2011', 'DD-MM-YYYY'))
 , PARTITION p2 VALUES LESS THAN (TO_DATE('1-7-2012', 'DD-MM-YYYY'))
 , PARTITION p3 VALUES LESS THAN (TO_DATE('1-1-2013', 'DD-MMYYYY')) );

위 코드는 2013년 1월 1일까지는 범위 파티셔닝이고, 그 이후는 1달 인터벌 파티셔닝이다.

리스트 파티셔닝(List Partitioning)

파티션의 키로 개별 값 목록을 사용한다. 리스트 파티셔닝을 사용하면 각각의 행이 어떤 파티션에 매핑되는지 설정할 수 있다. 키를 이용한 정렬을 사용하지 않고 관련 데이터 집합들을 그룹화할 때 사용한다.

CREATE TABLE list_sales
 ( prod_id NUMBER(6)
 , cust_id NUMBER
 , time_id DATE
 , channel_id CHAR(1)
 , promo_id NUMBER(6)
Chapter 6
Overview of Partitions
6-5
 , quantity_sold NUMBER(3)
 , amount_sold NUMBER(10,2)
 )
PARTITION BY LIST (channel_id)
 ( PARTITION even_channels VALUES ('2','4'),
 PARTITION odd_channels VALUES ('3','9')
 ); 

해시 파티셔닝(Hash Partitioning)

해시 알고리즘을 기반으로 행을 파티션에 매핑시킨다. 파티션의 수가 2의 거듭제곱일 때 균등하게 분배할 수 있다. 해시 파티셔닝은 대형 테이블을 분할할 때 유용하다. 또한 높은 업데이트 충돌이 발생하는 OLTP 시스템에서도 유용하다.

CREATE TABLE hash_sales
 ( prod_id NUMBER(6)
 , cust_id NUMBER
 , time_id DATE
 , channel_id CHAR(1)
 , promo_id NUMBER(6)
 , quantity_sold NUMBER(3)
 , amount_sold NUMBER(10,2)
 )
PARTITION BY HASH (prod_id)
PARTITIONS 2; 

참조 파티셔닝(Reference Partitioning)

부모(parent) 테이블과의 외래 키(foreign key) 관계를 통해 정의된 자식(child) 테이블을 이용하여 파티셔닝을 한다.
부모 테이블 파티션과 대응되는 자식 테이블 파티션이 반드시 한 개가 존재한다.
자식 테이블에 있는 레코드들을 상속받은 부모 파티션에 저장한다.

참조 파티셔닝의 장점:

• 부모와 자식이 동일한 파니셔닝 전략을 사용하기 때문에 파티셔닝 키 중복이 발생하지 않아, 비규정규화의 오버헤드를 줄여주고 공간을 절약할 수 있음.

• 부모 테이블에서 유지보수 작업이 발생될 경우 자식 테이블에도 자동으로 전파됨.

• 성능 향상을 위해 자동으로 부모와 자식 테이블의 파티션을 조인해서 사용할 수 있음.

참조 파티셔닝은 모든 기초 파티셔닝 전략들과 함께 사용이 가능하다.

CREATE TABLE orders
 ( order_id NUMBER(12),
 order_date DATE,
 order_mode VARCHAR2(8),
 customer_id NUMBER(6),
 order_status NUMBER(2),
 order_total NUMBER(8,2),
 sales_rep_id NUMBER(6),
 promotion_id NUMBER(6),
 CONSTRAINT orders_pk PRIMARY KEY(order_id)
 )
Chapter 6
Overview of Partitions
6-7
 PARTITION BY RANGE(order_date)
 ( PARTITION Q1_2015 VALUES LESS THAN (TO_DATE('01-APR-2015','DD-MONYYYY')),
 PARTITION Q2_2015 VALUES LESS THAN (TO_DATE('01-JUL-2015','DD-MONYYYY')),
 PARTITION Q3_2015 VALUES LESS THAN (TO_DATE('01-OCT-2015','DD-MONYYYY')),
 PARTITION Q4_2015 VALUES LESS THAN (TO_DATE('01-JAN-2006','DD-MONYYYY'))
 );
CREATE TABLE order_items
 ( order_id NUMBER(12) NOT NULL,
 line_item_id NUMBER(3) NOT NULL,
 product_id NUMBER(6) NOT NULL,
 unit_price NUMBER(8,2),
 quantity NUMBER(8),
 CONSTRAINT order_items_fk
 FOREIGN KEY(order_id) REFERENCES orders(order_id) // 참조 파티셔닝 외래 키 조건
 )
 PARTITION BY REFERENCE(order_items_fk); // 참조 파티셔닝

복합 파티셔닝(Composite Partitioning)

하나의 데이터 분배 메소드로 분리한 후에 각각의 파티션을 또 분배 메소드를 통해 서브파티션으로 나누는 전략이다. 즉, 기본 데이터 분배 메소드의 조합이다.

복합 파티셔닝의 장점:

• SQL문에 따라 파티션 프루닝(pruning)이 성능 향상을 이끌어낼 수 있음.
• 어느 차원에서든지 파티션 조인이 가능함.
• 단일 테이블의 병렬 백업 및 복구가 가능해짐.
• 단일 레벨 파티셔닝보다 파티션 수가 많아 병렬 실행에 유리하다.
• 롤링 윈도우를 구현하여 과거 데이터를 제공하면서 다른 차원에서 파티셔닝을 할 수 있어 파티션 프루닝이나 파티션 조인의 이점을 얻을 수 있음.
• 파티션 키를 기준으로 데이터를 다르게 저장할 수 있음.

복합 파티셔닝된 테이블의 각 서브파티션은 별도의 세그먼트로 저장된다. 따라서 서브파티션의 속성은 테이블의 속성이나 서브파티션이 속한 파티션의 속성과 다를 수 있다.

Partitioned Tables

하나 이상의 파티션으로 구성되며, 각각 다른 파티션과 독립적으로 작동한다.
파티셔닝된 테이블이 단 하나의 파티션으로만 구성되더라도, 이 테이블은 비파티셔닝된 테이블과는 다르다.

Segments for Partitioned Tables

파티셔닝된 테이블은 하나 이상의 테이블 파티션 세그먼트로 구성된다.

파티셔닝된 테이블을 생성하면 별도의 테이블 세그먼트가 할당되지 않고, 각 테이블 파티션의 데이터를 해당 파티션 세그먼트에 저장한다.

외부 파티셔닝 또는 하이브리드 파티셔닝인 경우 전체 또는 일부분이 외부에 저장된다.

Compression for Partitioned Tables

테이블스페이스, 테이블, 테이블 파티션 압축을 할 수 있다.

• 테이블스페이스 압축
  테이블스페이스에 있는 모든 테이블들이 적용됨.

• 테이블, 테이블 파티션 압축
  새로 추가되는 데이터들이 적용됨. 따라서 압축된 블록과 그렇지 않은 블록 두 가지가 존재.

Partitioned Indexes

마찬가지로 더 작게 나뉘어진 인덱스

글로벌 인덱스는 테이블과 독립적으로 파티셔닝되고, 로컬 인덱스는 해당 테이블 파티셔닝 방식에 자동으로 링크된다.

인덱스 파티셔닝도 테이블 파티셔닝과 동일한 장점을 갖는다.

Local Partitioned Indexes

인덱스가 테이블과 동일하게 파티셔닝된다. 각 인덱스 파티션은 하나의 테이블 파티션과 1대1 연관되어 있어 인덱스 파티션의 모든 키가 테이블 파티션에 저장된 행만을 참조한다.

데이터베이스가 인덱스 파티션을 연관된 테이블 파티션과 자동으로 동기화하여 각 테이블-인덱스 쌍이 독립적으로 작동하게 한다.

로컬 인덱스에 파티션을 명시적으로 추가, 삭제할 수는 없다. 기본 테이블에 파티션을 추가,삭제할 때 로컬 인덱스에 새로운 파티션이 추가,삭제 된다.

파티셔닝된 테이블에 비트맵 인덱스를 생성할 수 있다. 단, 비트맵 인덱스는 반드시 파티셔닝된 테이블에 로컬로 존재해야 한다. 글로벌 인덱스로 사용하고 싶다면 비파티셔닝 테이블에 사용해야한다.

• Local prefixed indexes

• Local nonprefixed indexes

두 가지 방식 모두 파티션 프루닝의 이점을 활용할 수 있다. prefixed 인덱스를 사용하는 쿼리는 파티셔닝 제거가 항상 가능한 반면, nonprefixed 인덱스는 가능 하지 않을 수도 있다.

로컬 인덱스 파티션도 자체 세그먼트에 저장된다. 각 세그먼트는 전체 인덱스 데이터의 일부를 가지고 있다. 즉, 4개의 파티션으로 만들어진 인덱스라면 4개의 개별 세그먼트에 저장된다.

Global Partitioned Indexes

테이블과 독립적으로 파티셔닝된 B-tree 인덱스이다. 단일 인덱스 파티션은 모든 테이블 파티션을 가리킬 수 있다.

OLTP 같이 하나의 키로 파티셔닝된 테이블을 다른 여러 키들을 사용해서 데이터에 접근하고 싶은 경우 유용하게 사용할 수 있다.

Partial Indexes for Partitioned Tables

부분 인덱스는 파티셔닝된 테이블의 인덱싱 속성과 관련된 인덱스이다.

테이블 파티션 중 어떤 것들을 인덱싱할지를 지정할 수 있게 한다.

부분 인덱스의 장점:

• 인덱싱되지 않은 테이블 파티션이 저장 공간을 소비하지 않는다.
• 데이터 로드 및 쿼리의 성능이 개선될 수 있다.

테이블의 개별 파티션에 대해 인덱싱을 켜거나 끌 수 있다.
인덱싱을 끈 파티션은 인덱스가 데이터에 접근할 수 없다.

Partitioned Index-Organized Tables

인덱스 기반 테이블 (IOT)은 범위(range), 리스트(list), 해시(hash)를 이용한 파티셔닝을 지원한다.

특성:

• 파티션 컬럼은 기본 키 컬럼의 하위 집합이어야 한다.
• 보조 인덱스는 로컬 및 글로벌로 파티셔닝될 수 있다.
• OVERFLOW 데이터 세그먼트는 항상 테이블 파티션과 동일하게 파티셔닝 된다.

IOT에 비트맵 인덱스를 생성하려면 매핑 테이블이 필요하다.

뷰(View)

뷰(view)는 하나 이상의 테이블의 논리적인 표현이다. 뷰는 저장된 쿼리다.

뷰는 베이스 테이블(base table)로부터 데이터를 만든다. 베이스 테이블은 테이블이거나 다른 뷰이다. 뷰에 대한 모든 작업들은 베이스 테이블에 영향을 미친다.

뷰를 사용하는 이유:

• 테이블 보안 강화
  특정 테이블의 미리 정의된 일부 행 또는 열에 대한 접근을 제한하여 추가적인 테이블 보안 수준을 제공한다.

• 데이터 복잡성 숨기기
  단일 뷰에서 여러 테이블의 관련 열 또는 행을 조인으로 유래를 모르게 정의할 수 있다. 즉, 사용자가 조인이나 계산을 알지 못해도 뷰를 쿼리할 수 있다.

• 다른 관점의 데이터 제공
  뷰의 열 이름을 변경하여 기본 테이블에 영향을 주지 않고 데이터를 다른 관점으로 볼 수 있다.

• 응용 프로그램으로 인한 기본 테이블 정의의 변경으로부터 격리
  뷰를 사용하여 기본 테이블의 정의가 변경되어도 논리적 구조를 유지하도록 할 수 있다.

뷰의 특징

뷰는 테이블과 달리 저장 공간을 할당받지 않으며 데이터를 포함하지 않는다. 뷰는 다른 객체를 기반으로 하기 때문에 데이터 사전에 뷰를 정의하는 쿼리의 저장 공간 이외에는 저장 공간이 필요하지 않다.

뷰는 참조하는 객체에 의존성을 가지며, 데이터베이스가 이를 자동으로 처리한다. 만약 뷰의 기본 테이블을 삭제하고 다시 생성하면 데이터베이스는 새로운 기본 테이블이 뷰의 정의에 적합한지 결정한다.

뷰에서 데이터 조작

뷰에서 제한적으로 DML을 수행할 수 있는데, 이때 발생하는 DML은 실제 테이블에 영향을 미친다.
해당 DML은 무결성 조건과 트리거의 영향을 받는다.

뷰에서 데이터에 접근하는 방법

1. 뷰 및 기본 뷰를 정의하는 쿼리를 사용하여 뷰에 대한 쿼리를 (가능한 경우)머지한다.
2. 병합된 문장을 공유 SQL 영역에서 파싱한다.
3. SQL문을 실행한다.

Updatable Join View

Updatable Join View는 두 개 이상의 기본 테이블 또는 뷰를 포함하는 DML 작업을 허용한다.

join view가 업데이트가 가능하려면 기본 테이블에 대한 작업을 한 번에 하나의 작업만 수행해야 한다. 또한 join view의 업데이트 가능한 열은 키-보존 테이블의 열에 매핑되어야 한다.

Object View

각각의 행이 객체 유형의 인스턴스인 뷰.

객체 뷰는 프로토타입 제작이나 객체 지향 애플리케이션으로의 전환 과정에서 유용하다.

Materialized View

미리 계산된 쿼리 결과를 스키마 객체로 저장한 것. 스냅샷이라고도 불린다.

데이터 웨어하우스에서 주로 마스터 테이블로 사용되며, 집계 함수를 사용하여 생성된 데이터를 저장하는 데 유용하다.

Materialized View는 분산된 사이트에서 데이터를 복제하고, 여러 사이트에서 수행된 업데이트를 동기화하는 데 유용하다. 이러한 특성 때문에 DB가 항상 네트워크와 연결되어 있지 않는 환경에 적합하다.

Materialized View의 특성

Materialized View는 인덱스와 일반 뷰의 몇 가지 특성들을 가지고 있다.

인덱스와 같은 점:

• 실제 데이터를 포함하고 저장 공간을 사용한다.
• 마스터 테이블의 데이터가 변경될 때 리프레시 된다.
• query rewrite 작업에 사용될 때 SQL 실행 성능을 개선한다.
• SQL 애플리케이션과 사용자에게는 존재가 투명하다.

일반 뷰와 유사한 점은 다른 테이블이나 뷰의 데이터를 나타낸다는 것이다.
인덱스와 다른 점이라면 select 문을 통해 쿼리문을 날릴 수 있다.

Materialized View도 파티셔닝이 가능하다.

Materialized View의 새로고침

구체화 뷰는 기본 테이블에 변경이 생기면 새로고침을 통해 동기화를 한다.

완전 리프레시(Complete Refresh)

뷰를 정의하는 쿼리를 실행. 데이터의 양이 많거나 처리해야할 작업이 많다면 시간이 걸릴 수 있다. 언제든지 가능하다.

증가 리프레시(Incremental Refresh)

변경된 데이터만 처리하는 빠른 리프레시 방식.

• 로그 기반 리프레시
  로그를 기반으로 증가 리프레시를 실행

• PCT(Partition change tracking) 기반 리프레시
  테이블이 파티셔닝 되어 있을 경우에만 가능. 구체화 뷰의 파티션 또는 모든 데이터를 제거하고 재계산하여 영향을 받는 파티션 또는 데이터를 식별한다.

In-Place and Out-of-Place Refresh

• In-Place Refresh
  뷰에 직접적으로 리프레시 문을 실행

• Out-Of-Place Refresh
  외부 테이블을 생성하고 리프레시 문을 실행한 다음 영향을 받는 파티션을 외부 테이블과 전환

Query Rewrite

쿼리 리라이트는 마스터 테이블을 기반으로 한 사용자 요청을 구체화 뷰를 이용해 의미적으로 동등한 요청으로 변환하는 최적화 기법이다.

DB에 데이터가 많다면 집계 또는 조인을 하는 것은 상당한 시간이 소요된다. 구체화 뷰를 이용하여 미리 계산된, 조인된 결과를 사용하면 쿼리를 신속하게 처리할 수 있다.

시퀸스(Sequences)

시퀀스는 여러 사용자가 고유한 정수를 생성할 수 있는 스키마 객체다.

특징

시퀸스는 다음과 같이 정의한다.

• 숫자 사이의 간격
• 데이터베이스가 메모리에 생성된 시퀀스 번호 세트를 캐시할지 여부
• 시퀀스가 한계에 도달했을 때 순환할지 여부

동시성 접근

시퀀스 생성기는 디스크 I/O나 트랜잭션 잠금 오버헤드 없이 고유한 숫자를 생성하기 위해 다중 사용자 환경에서 유용하게 사용된다.

디멘션(Dimension)

데이터 웨어하우스의 전형적인 구성 요소에는 두 가지 주요 요소가 있는데, 디멘션(Dimension)과 팩트(Fact)이다.

• 팩트
  명확한 정량이 있는 카테고리.

• 디멘션
  특정 디멘션 값 집합과 관련된 이벤트나 엔티티.

차원을 생성함으로써 쿼리 리라이트 기능을 더 넓게 사용할 수 있다.

계층적 구조

차원 테이블은 열 또는 열 집합 간의 계층적(상위/하위) 관계를 정의하는 논리적 구조이다.

하위 수준의 각 값은 상위 수준의 하나의 값이랑만 연관되어 있다.

디멘션은 데이터 저장 공간이 할당되지 않는다. 디멘션 정보는 디멘션 테이블에 저장되고, 팩트 정보는 팩트 테이블에 저장된다.

Synonym

동의어(Synonym)는 스키마 객체의 별칭(alias)이다.

동의어는 단순히 별칭이므로 데이터 사전에서의 정의 이외에는 저장 공간이 필요하지 않다.

동의어는 데이터베이스 사용자들에게 SQL 문을 간소화하거나 기존 스키마 객체의 식별자와 위치를 숨기는데 유용하다.

• 개인 동의어
  특정 사용자 스키마에 속하며 공개여부를 정할 수 있음.

• 공용 동의어
  PUBLIC 사용자 그룹이 소유하며 모든 사용자가 접근 가능함.