Partitioning Table/Index란?

Partitioning이란? Table/Index Data를 Partition 단위로 나누어 저장하는 것으로 Partitioning Key에 따라 물리적으로 별도의 Segment에 저장됩니다. 각 Partition은 고유한 이름과 스토리지 특성을 가진 독립적인 Object입니다.

 

Partitioning Key란? Partitioned Table의 각 row가 저장되는 Partition을 결정하는 하나 이상의 Column입니다.

 

이러한 Partitioning은 LONG/LONG RAW data type이 있는 Table을 제외한 모든 Table에 적용할 수 있습니다. (CLOB/BLOB data type이 있는 Table도 가능)

 

Partitioning Strategies

Single-level Partitioning

• Range Partitioning : Partitioning Key 값의 범위를 기반으로 row를 매핑합니다.  -> Interval을 지정하여 Partition을 자동 생성 할 수도 있습니다.

• List Partitioning : Partitioning Key 값의 목록을 기반으로 row를 매핑합니다.
• Hash Partitioning : Partitioning Key에 적용하는 Hash 알고리즘을 기반으로 row를 매핑합니다.
• Reference Partitioning : Parent Table과의 Foreign Key를 통해서 정의됩니다. 

List,Range,and Hash Partitioning

​Composite Partitioning

Single-level Partitioning을 조합하여 Subpartition으로 더 세분화된 Partitioning 기법입니다. 아래 그림은 여러가지 Composite Partitioning 기법 중 Range-Hash/Range-List Partitioning을 나타낸 것입니다.

Composite Partitioning 예시

Composite Partitioned Table의 각 Subpartition을 별도의 Segment에 저장합니다.

 

Partitioned Tables

Oracle의 Table은 Partitioned Table과 Nonpartitioned Table로 나뉩니다. 이 두가지의 차이점은 무엇일까요?

Nonpartitioned Table은 Table이 생성되고 Data를 저장하는 공간으로 Table Segment를 사용합니다. 하지만 Partitioned Table의 경우, Table Segment가 아니라 각 Partition Segment에 Data를 나누어 저장합니다. 이러한 이유로 Partition이 딱 한개이더라도 Partitioned Table과 Nonpartitioned Table은 다르다는 것을 기억해야합니다.

※Partitioned/Nonpartitioned Table을 생성 확인 및 비교

--Create Partitioned Table(Range)
SQL> 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)
     ); 

Table created.

--Create Nonpartitioned Table
SQL> CREATE TABLE time_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)
    );

Table created.

--Data insert 수행

--Segment 확인
SQL> select segment_name, partition_name, segment_type, tablespace_name
    from dba_segments
    where segment_name like 'TIME%SALES';

SEGMENT_NAME         PARTITION_NAME       SEGMENT_TYPE       TABLESPACE_NAME
-------------------- -------------------- ------------------ ------------------------------
TIME_RANGE_SALES     SALES_1999           TABLE PARTITION    TESTTS
TIME_RANGE_SALES     SALES_2000           TABLE PARTITION    TESTTS
TIME_RANGE_SALES     SALES_2001           TABLE PARTITION    TESTTS
TIME_SALES                                TABLE              TESTTS

 

Partitioned Indexes

Partitioned Table과 유사한 개념으로 Index를 세분화한 것입니다. Index 종류는 아래 사진과 같습니다.

Index Partitioning Options

Local Partitioned Index

Table과 동일한 Partition 개수, 동일한 Partition 구분값을 사용하여 동일한 Column으로 Partitioning 된 Index입니다. 각 Index Partition은 Table의 하나의 Partition과 연결되므로 Index Partition의 모든 Key는 Table Partition에 저장된 row만 참조합니다. 이러한 방식으로 Database는 Index Partition을 연결된 Table Partition과 자동으로 동기화하여 독립적인 각 table-index pair를 만듭니다.

Local Index Partitions

※Local Partitioned Index 생성 및 확인

 

--Create Local Partitioned Index
SQL> CREATE INDEX time_range_sales_idx ON time_range_sales(time_id) LOCAL;

Index created.

--Partitioned Index 조회
SQL> select index_owner, index_name, partition_name, high_value, partition_position, status, tablespace_name
    from dba_ind_partitions
    where index_name='TIME_RANGE_SALES_IDX';

INDEX_OWNE INDEX_NAME           PARTITION_NAME  HIGH_VALUE                                                                       PARTITION_POSITION STATUS   TABLESPACE_NAME
---------- -------------------- --------------- -------------------------------------------------------------------------------- ------------------ -------- -----------------
TUSER      TIME_RANGE_SALES_IDX SALES_1998      TO_DATE(' 1999-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                  1 USABLE   TESTTS
TUSER      TIME_RANGE_SALES_IDX SALES_1999      TO_DATE(' 2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                  2 USABLE   TESTTS
TUSER      TIME_RANGE_SALES_IDX SALES_2000      TO_DATE(' 2001-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                  3 USABLE   TESTTS
TUSER      TIME_RANGE_SALES_IDX SALES_2001      MAXVALUE                                                                                          4 USABLE   TESTTS

SQL> select owner, index_name, table_name, partitioning_type, partition_count, partitioning_key_count, locality
    from dba_part_indexes
    where index_name='TIME_RANGE_SALES_IDX';

OWNER  INDEX_NAME           TABLE_NAME        PARTITION PARTITION_COUNT PARTITIONING_KEY_COUNT LOCALITY
------ -------------------- ----------------- --------- --------------- ---------------------- ----------
TUSER  TIME_RANGE_SALES_IDX TIME_RANGE_SALES  RANGE                   4                      1 LOCAL

SQL> select owner, name, object_type, column_name, column_position 
    from dba_part_key_columns
    where name like 'TIME_RANGE_SALES%';

OWNER   NAME                 OBJEC COLUMN_NAME          COLUMN_POSITION
------- -------------------- ----- -------------------- ---------------
TUSER   TIME_RANGE_SALES     TABLE TIME_ID                            1
TUSER   TIME_RANGE_SALES_IDX INDEX TIME_ID                            1

-> Local Partitioned Index의 구조가 Partitioned Table과 동일함.

Local Partitioned Index는 Index Definition에 Partition Key 선두 유무에 따라 아래 두가지로 나뉩니다. 

• Local Prefixed Index
• Local Nonprefixed Index

Global Partitioned Index

Table과 독립적으로 Partitioning 된 B-tree Index입니다. Locally Partitioned Index에서는 Index Partition과 Table Partition이 1:1로 매핑되는 반면, Global Partitioned Index는 몇몇의(혹은 모든) Table Partition을 가리킬 수 있습니다.

Global Partitioned Index

※Global Partitioned Index 생성 및 확인

 

--Create Global Partitioned Index
SQL> CREATE INDEX time_channel_sales_idx ON time_range_sales (channel_id)
    GLOBAL PARTITION BY RANGE (channel_id)
    (PARTITION p1 VALUES LESS THAN (3),
    PARTITION p2 VALUES LESS THAN (4),
    PARTITION p3 VALUES LESS THAN (MAXVALUE)
    );

Index created.

--Partitioned Index 조회
SQL> select index_owner, index_name, partition_name, high_value, partition_position, status, tablespace_name
    from dba_ind_partitions
    where index_name='TIME_CHANNEL_SALES_IDX';

INDEX_OWNE INDEX_NAME              PARTITION_NAME  HIGH_VALUE  PARTITION_POSITION STATUS   TABLESPACE_NAME 
---------- ----------------------- --------------- ----------- ------------------ -------- ----------------
TUSER      TIME_CHANNEL_SALES_IDX  P1              '3'                          1 USABLE   TESTTS          
TUSER      TIME_CHANNEL_SALES_IDX  P2              '4'                          2 USABLE   TESTTS          
TUSER      TIME_CHANNEL_SALES_IDX  P3              MAXVALUE                     3 USABLE   TESTTS                                               
																				4 USABLE   TESTTS

SQL> select owner, index_name, table_name, partitioning_type, partition_count, partitioning_key_count, locality
    from dba_part_indexes
    where index_name='TIME_CHANNEL_SALES_IDX';

OWNER   INDEX_NAME               TABLE_NAME         PARTITION PARTITION_COUNT PARTITIONING_KEY_COUNT LOCALITY
------- ------------------------ ------------------ --------- --------------- ---------------------- ----------
TUSER   TIME_CHANNEL_SALES_IDX   TIME_RANGE_SALES  RANGE                   3                      1 GLOBAL

SQL> select owner, name, object_type, column_name, column_position 
    from dba_part_key_columns
    where name like 'TIME%SALES%';

OWNER   NAME                      OBJEC COLUMN_NAME          COLUMN_POSITION
------- ------------------------- ----- -------------------- ---------------
TUSER   TIME_CHANNEL_SALES_IDX    INDEX CHANNEL_ID                         1
TUSER   TIME_RANGE_SALES          TABLE TIME_ID                            1

-> Global Partitioned Index의 구조(Partition Key, Partition 개수 등)가 Partitioned Table과 다름.

이렇게 Partition Table/Index에 대한 간단한 소개를 마쳤습니다. 이러한 각 Partition은 그저 Create하는 것이 중요한 것이아니라 종류에 따라 어떠한 장점이 있는지, 어떤 제약사항이 있는지 등의 관리적/성능적 측면이 아주 중요합니다. 이에 대한 내용은 따로 포스팅하도록 하겠습니다.

 

 

참조: 

https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/partitions-views-and-other-schema-objects.html#GUID-91498562-1809-4E67-B7AD-9718ED60DEFF

Database Concepts