꿈꾸는 개발자, DBA 커뮤니티 구루비

지정 된 파티션에만 인덱스가 생성 되도록 하는 기능
11g 에서 유사하게 구현 하려면 전체 Local Index 를 unusable 로 생성해고 개별 partition 에 rebuild 하는 방법 밖에 없었다.
12c 에서는 Global/Local 타입과 무관하게 개별 파티션에 인덱스 적용이 가능하다
full/partial indexing 타입이 있다.
테이블 속성으로 인덱싱 여부를 관리해야 한다.

Full/Partial index

Full indexing

모든 파티션에 인덱스가 생성

Partial indexing

지정 된 파티션에는 인덱스가 생성 혹은 생성되지 않게 지정 가능함
인덱스 사용 공간 절약 가능
유지 보수 작업을 빠르게 할 수 있음 ex> 인덱싱 되지 않은 글로벌 파티션의 리빌드 등


create table orders (
order_id number
,name varchar2(30)
,order_date date
)
indexing off ---(1)
partition by range (order_date)
(
partition p_1 values less than (to_date('2015/01/01','yyyy/mm/dd')) indexing off, ---(2)
partition p_2 values less than (to_date('2016/01/01','yyyy/mm/dd')) indexing on,  ---(3)
partition p_3 values less than (to_date('2017/01/01','yyyy/mm/dd'))               ---(4)
)
;

alter table orders modify partition p_1 indexing on; ---(5)
alter table orders modify partition p_1 indexing off;

SQL> select
table_name, partition_name, indexing
from user_tab_partitions where table_name='ORDERS'
;

TABLE_NAME                     PARTITION_NAME                 INDE
------------------------------ ------------------------------ ----
ORDERS                         P_1                            OFF
ORDERS                         P_2                            ON
ORDERS                         P_3                            OFF

(1) 개별 파티션에 indexing 옵션을 지정하지 않았을 때 기본 속성값을 지정
(2) p_1 파티션에는 index 적용
(3) p_2 파티션에는 index 적용하지 않음
(4) p_3 파티션에는 indexing 옵션을 명시하지 않았으므로 기본 속성인 indexing off(1) 에 따라 감
(5) Alter table 으로 indexing 속성 변경 가능

Index 의 full/partial index 생성 비교 - Global full/Global partial/Local full/Local partial

Global Full Case


SQL> create index ind1_orders_full on orders(order_date, order_id, name) global indexing full;

Index created.

SQL> SELECT INDEX_NAME, TABLE_NAME, INDEXING FROM USER_INDEXES WHERE INDEX_NAME='IND1_ORDERS_FULL';

INDEX_NAME                     TABLE_NAME                     INDEXING
------------------------------ ------------------------------ --------
IND1_ORDERS_FULL               ORDERS                         FULL

SQL>

SQL> select bytes from dba_Segments where segment_name='IND1_ORDERS_FULL';

     BYTES
----------
 284164096

SQL> SELECT INDEX_NAME, TABLE_NAME, INDEXING, NUM_ROWS FROM USER_INDEXES WHERE INDEX_NAME='IND1_ORDERS_FULL';

INDEX_NAME                     TABLE_NAME                     INDEXIN   NUM_ROWS
------------------------------ ------------------------------ ------- ----------
IND1_ORDERS_FULL               ORDERS                         FULL       4540569

Global Partial Case

SQL> create index ind1_orders_partial on orders(order_id, name, order_date) global indexing partial;

Index created.

SQL> SELECT INDEX_NAME, TABLE_NAME, INDEXING FROM USER_INDEXES WHERE INDEX_NAME='IND1_ORDERS_PARTIAL';

INDEX_NAME                     TABLE_NAME                     INDEXING
------------------------------ ------------------------------ --------
IND1_ORDERS_PARTIAL            ORDERS                         PARTIAL

SQL> select bytes from dba_Segments where segment_name='IND1_ORDERS_PARTIAL';

     BYTES
----------
     65536

SQL> SELECT INDEX_NAME, TABLE_NAME, INDEXING, NUM_ROWS FROM USER_INDEXES WHERE INDEX_NAME='IND1_ORDERS_PARTIAL';

INDEX_NAME                     TABLE_NAME                     INDEXIN   NUM_ROWS
------------------------------ ------------------------------ ------- ----------
IND1_ORDERS_PARTIAL            ORDERS                         PARTIAL        450

SQL>

local Full case


SQL> create index ind2_orders_full on orders(order_date, name) local indexing full;

Index created.

SQL> select segment_name, partition_name, bytes from dba_Segments where segment_name='IND2_ORDERS_FULL';

SEGMENT_NAME                   PARTITION_NAME                      BYTES
------------------------------ ------------------------------ ----------
IND2_ORDERS_FULL               P_3                                196608
IND2_ORDERS_FULL               P_2                                 65536
IND2_ORDERS_FULL               P_1                             234881024

SQL>
SQL> SELECT INDEX_NAME, TABLE_NAME, INDEXING, GLOBAL_STATS, NUM_ROWS FROM USER_INDEXES WHERE INDEX_NAME='IND2_ORDERS_FULL';

INDEX_NAME                     TABLE_NAME                     INDEXIN GLO   NUM_ROWS
------------------------------ ------------------------------ ------- --- ----------
IND2_ORDERS_FULL               ORDERS                         FULL    NO     4540569
SQL> select INDEX_NAME, PARTITION_NAME, NUM_ROWS from user_ind_partitions where index_name='IND2_ORDERS_FULL';

INDEX_NAME                     PARTITION_NAME                   NUM_ROWS
------------------------------ ------------------------------ ----------
IND2_ORDERS_FULL               P_1                               4537650
IND2_ORDERS_FULL               P_2                                   450
IND2_ORDERS_FULL               P_3                                  2469

Local Partial Case


SQL> create index ind2_orders_partial on orders(order_date, name) local indexing partial;

Index created.

SQL> select segment_name, partition_name, bytes from dba_Segments where segment_name='IND2_ORDERS_PARTIAL';

SEGMENT_NAME                   PARTITION_NAME                      BYTES
------------------------------ ------------------------------ ----------
IND2_ORDERS_PARTIAL            P_2                                 65536

SQL> SELECT INDEX_NAME, TABLE_NAME, INDEXING, GLOBAL_STATS, NUM_ROWS FROM USER_INDEXES WHERE INDEX_NAME='IND2_ORDERS_PARTIAL';

INDEX_NAME                     TABLE_NAME                     INDEXIN GLO   NUM_ROWS
------------------------------ ------------------------------ ------- --- ----------
IND2_ORDERS_PARTIAL            ORDERS                         PARTIAL NO         450

SQL> select INDEX_NAME, PARTITION_NAME, NUM_ROWS, STATUS, SEGMENT_CREATED from user_ind_partitions where index_name='IND2_ORDERS_PARTIAL';

INDEX_NAME                     PARTITION_NAME                   NUM_ROWS STATUS   SEG
------------------------------ ------------------------------ ---------- -------- ---
IND2_ORDERS_PARTIAL            P_1                                     0 UNUSABLE NO
IND2_ORDERS_PARTIAL            P_2                                   450 USABLE   YES
IND2_ORDERS_PARTIAL            P_3                                     0 UNUSABLE NO

Index 의 full/partial index rebuild 비교 - Global partial/Local partial

Global Partial case

SQL> alter index ind1_orders_partial rebuild ;

Index altered.

SQL> SELECT INDEX_NAME, TABLE_NAME, INDEXING, NUM_ROWS FROM USER_INDEXES WHERE INDEX_NAME='IND1_ORDERS_PARTIAL';

INDEX_NAME                     TABLE_NAME                     INDEXIN   NUM_ROWS
------------------------------ ------------------------------ ------- ----------
IND1_ORDERS_PARTIAL            ORDERS                         PARTIAL        450

SQL> alter table orders modify partition p_3 indexing on ;

Table altered.

SQL> SELECT INDEX_NAME, TABLE_NAME, INDEXING, NUM_ROWS, STATUS FROM USER_INDEXES WHERE INDEX_NAME='IND1_ORDERS_PARTIAL';

INDEX_NAME                     TABLE_NAME                     INDEXIN   NUM_ROWS STATUS
------------------------------ ------------------------------ ------- ---------- --------
IND1_ORDERS_PARTIAL            ORDERS                         PARTIAL        450 VALID

SQL> alter index IND1_ORDERS_PARTIAL rebuild ;

Index altered.

SQL> SELECT INDEX_NAME, TABLE_NAME, INDEXING, NUM_ROWS, STATUS FROM USER_INDEXES WHERE INDEX_NAME='IND1_ORDERS_PARTIAL';

INDEX_NAME                     TABLE_NAME                     INDEXIN   NUM_ROWS STATUS
------------------------------ ------------------------------ ------- ---------- --------
IND1_ORDERS_PARTIAL            ORDERS                         PARTIAL       2919 VALID

SQL>

Local Partial case


-- 생성되지 않은 partitial index 에 rebuild 시에는?
SQL> alter index IND2_ORDERS_PARTIAL rebuild partition p_1 ;

Index altered.

SQL> select INDEX_NAME, PARTITION_NAME, NUM_ROWS, STATUS, SEGMENT_CREATED from user_ind_partitions where index_name='IND2_ORDERS_PARTIAL';

INDEX_NAME                     PARTITION_NAME                   NUM_ROWS STATUS   SEG
------------------------------ ------------------------------ ---------- -------- ---
IND2_ORDERS_PARTIAL            P_1                               4537650 USABLE   YES
IND2_ORDERS_PARTIAL            P_2                                   450 USABLE   YES
IND2_ORDERS_PARTIAL            P_3                                     0 UNUSABLE NO

SQL>

-- indexing 되지 않은 partition 에 altering 시에는?

SQL> alter table orders modify partition p_3 indexing on ;

Table altered.

SQL>
SQL> select INDEX_NAME, PARTITION_NAME, NUM_ROWS, STATUS, SEGMENT_CREATED from user_ind_partitions where index_name='IND2_ORDERS_PARTIAL';

INDEX_NAME                     PARTITION_NAME                   NUM_ROWS STATUS   SEG
------------------------------ ------------------------------ ---------- -------- ---
IND2_ORDERS_PARTIAL            P_1                               4537650 USABLE   YES
IND2_ORDERS_PARTIAL            P_2                                   450 USABLE   YES
IND2_ORDERS_PARTIAL            P_3                                  2469 USABLE   YES

SQL>


-- 이 때 table partition 속성은?

SQL> select
table_name, partition_name, indexing
from user_tab_partitions where table_name='ORDERS'
;

TABLE_NAME                     PARTITION_NAME                 INDE
------------------------------ ------------------------------ ----
ORDERS                         P_1                            OFF -- local index rebuild
ORDERS                         P_2                            ON
ORDERS                         P_3                            ON  -- partition indexing on

SQL>

결론은 다음과 같다

복합적으로 두 가지 방법으로 운영해야 한다

1. 테이블의 개별 파티션에 indexing 속성 지정
2. 인덱스 생성 시 partial 키워드로 생성

Global / Local index 모두에 사용 가능하다

Local index 일 경우는 처음부터 특정 파티션에 unusable 로 인덱스가 생성되는 개념

Partition 을 altering 하거나 index rebuild 시 파티션 인덱스가 생성
index rebuild 만 할 경우 인덱스만 생성되고 파티션 속성은 변하지 않음

Global index 일 경우는 특정 파티션에만 global 하게 인덱스가 생성

partition 을 altering 후 index rebuild 시 신규 altering 한 파티션 데이터가 인덱스에 포함 됨
index rebuild 만 할 경우 아무것도 변하지 않음