클라우드 데이터베이스 Oracle 12c 가이드 (2016년)
Partial index 0 0 12,391

by 구루비스터디 Partial index partition partition index [2023.09.03]


  • 지정 된 파티션에만 인덱스가 생성 되도록 하는 기능
  • 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 만 할 경우 아무것도 변하지 않음
"구루비 데이터베이스 스터디모임" 에서 2016년에 "클라우드 데이터베이스 Oracle 12c 가이드" 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/4264

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입