프린트 하기

OS환경 : Oracle Linux 7.6 (64bit)

 

DB 환경 : Oracle Database 19.3.0.0

 

방법 : 오라클 19c 파티션 테이블 merge 작업 시 인덱스 영향도 확인

오라클에서 파티션 테이블에 merge 작업을 할때 발생하는 현상에 대해 확인해봄

 

 

샘플 유저 생성

1
2
3
SQL>
create user imsi identified by imsi account unlock default tablespace users quota unlimited on users;
grant resource, connect to imsi;

 

 

파티션 테이블 생성

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
SQL>
conn imsi/imsi
drop table ptable purge;
create table ptable
(
col1 varchar2(8),
col2 number
)
tablespace users
partition by range (col1)
(
partition p202101 values less than ('20210200'),
partition p202102 values less than ('20210300'),
partition p202103 values less than ('20210400'),
partition p202104 values less than ('20210500'),
partition p202105 values less than ('20210600'),
partition p202106 values less than ('20210700'),
partition p202107 values less than ('20210800'),
partition p202108 values less than ('20210900'),
partition p202109 values less than ('20211000'),
partition p202110 values less than ('20211100'),
partition p202111 values less than ('20211200'),
partition p202112 values less than ('20220100'),
partition p_max values less than (maxvalue)
);
 
Table created.

 

 

파티션 테이블 샘플 데이터 삽입(원하는 만큼 실행)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SQL>
DECLARE
TYPE tbl_ins IS TABLE OF PTABLE%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
for j in 1..10 loop
FOR i IN 1..100000 LOOP 
   w_ins(i).col1   := round(dbms_random.value(2021,2022))||to_char(round(dbms_random.value(1,12)) , 'FM09')||to_char( round(dbms_random.value(1,28)), 'FM09');
   w_ins(i).col2   := ceil(dbms_random.value(1, 10000000));
END LOOP;
FORALL i in 1..100000 INSERT INTO PTABLE VALUES w_ins(i);
   COMMIT;
end loop;
END;
/
 
PL/SQL procedure successfully completed

 

 

글로벌 인덱스 생성

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SQL> 
create index pt_ix01 on ptable(col1) global 
partition by range (col1)
(
partition p202101 values less than ('20210200'),
partition p202102 values less than ('20210300'),
partition p202103 values less than ('20210400'),
partition p202104 values less than ('20210500'),
partition p202105 values less than ('20210600'),
partition p202106 values less than ('20210700'),
partition p202107 values less than ('20210800'),
partition p202108 values less than ('20210900'),
partition p202109 values less than ('20211000'),
partition p202110 values less than ('20211100'),
partition p202111 values less than ('20211200'),
partition p202112 values less than ('20220100'),
partition p_max values less than (maxvalue)
);
 
Index created.

 

 

로컬 인덱스 생성

1
2
3
SQL> create index pt_ix02 on ptable(col1, col2) local;
 
Index created.

 

 

파티션 별 용량 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
SQL>
set lines 200 pages 1000
col owner for a10
col segment_name for a20
col tablespace_name for a20
col partition_name for a20
col segment_type for a20
select owner, tablespace_name, segment_name, partition_name, segment_type, round(bytes/1024/1024) mb
from dba_segments 
where segment_name = 'PTABLE'
and segment_type in ('TABLE', 'TABLE PARTITION','TABLE SUBPARTITION')
order by 3,4;
 
OWNER      TABLESPACE_NAME      SEGMENT_NAME         PARTITION_NAME       SEGMENT_TYPE                 MB
---------- -------------------- -------------------- -------------------- -------------------- ----------
IMSI       USERS                PTABLE               P202101              TABLE PARTITION               8
IMSI       USERS                PTABLE               P202102              TABLE PARTITION               8
IMSI       USERS                PTABLE               P202103              TABLE PARTITION               8
IMSI       USERS                PTABLE               P202104              TABLE PARTITION               8
IMSI       USERS                PTABLE               P202105              TABLE PARTITION               8
IMSI       USERS                PTABLE               P202106              TABLE PARTITION               8
IMSI       USERS                PTABLE               P202107              TABLE PARTITION               8
IMSI       USERS                PTABLE               P202108              TABLE PARTITION               8
IMSI       USERS                PTABLE               P202109              TABLE PARTITION               8
IMSI       USERS                PTABLE               P202110              TABLE PARTITION               8
IMSI       USERS                PTABLE               P202111              TABLE PARTITION               8
IMSI       USERS                PTABLE               P202112              TABLE PARTITION               8
IMSI       USERS                PTABLE               P_MAX                TABLE PARTITION              16
 
13 rows selected.

 

 

인덱스 상태 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
SQL> 
set lines 200 pages 1000
col index_owner for a10
col index_name for a20
col partition_name for a20
select dip.index_owner, dip.index_name, dip.partition_name, dip.status, dpi.locality, dpi.alignment
from dba_ind_partitions dip, dba_part_indexes dpi
where 1=1
and dpi.index_name = dip.index_name
and dpi.table_name ='PTABLE'
order by dip.index_name, dip.partition_name;
 
INDEX_OWNE INDEX_NAME           PARTITION_NAME       STATUS   LOCALI ALIGNMENT
---------- -------------------- -------------------- -------- ------ ------------
IMSI       PT_IX01              P202101              USABLE   GLOBAL PREFIXED
IMSI       PT_IX01              P202102              USABLE   GLOBAL PREFIXED
IMSI       PT_IX01              P202103              USABLE   GLOBAL PREFIXED
IMSI       PT_IX01              P202104              USABLE   GLOBAL PREFIXED
IMSI       PT_IX01              P202105              USABLE   GLOBAL PREFIXED
IMSI       PT_IX01              P202106              USABLE   GLOBAL PREFIXED
IMSI       PT_IX01              P202107              USABLE   GLOBAL PREFIXED
IMSI       PT_IX01              P202108              USABLE   GLOBAL PREFIXED
IMSI       PT_IX01              P202109              USABLE   GLOBAL PREFIXED
IMSI       PT_IX01              P202110              USABLE   GLOBAL PREFIXED
IMSI       PT_IX01              P202111              USABLE   GLOBAL PREFIXED
IMSI       PT_IX01              P202112              USABLE   GLOBAL PREFIXED
IMSI       PT_IX01              P_MAX                USABLE   GLOBAL PREFIXED
IMSI       PT_IX02              P202101              USABLE   LOCAL  PREFIXED
IMSI       PT_IX02              P202102              USABLE   LOCAL  PREFIXED
IMSI       PT_IX02              P202103              USABLE   LOCAL  PREFIXED
IMSI       PT_IX02              P202104              USABLE   LOCAL  PREFIXED
IMSI       PT_IX02              P202105              USABLE   LOCAL  PREFIXED
IMSI       PT_IX02              P202106              USABLE   LOCAL  PREFIXED
IMSI       PT_IX02              P202107              USABLE   LOCAL  PREFIXED
IMSI       PT_IX02              P202108              USABLE   LOCAL  PREFIXED
IMSI       PT_IX02              P202109              USABLE   LOCAL  PREFIXED
IMSI       PT_IX02              P202110              USABLE   LOCAL  PREFIXED
IMSI       PT_IX02              P202111              USABLE   LOCAL  PREFIXED
IMSI       PT_IX02              P202112              USABLE   LOCAL  PREFIXED
IMSI       PT_IX02              P_MAX                USABLE   LOCAL  PREFIXED
 
26 rows selected.

파티션들이 모두 정상임(USABLE 상태)

 

 

P202110, P202111, P202112 파티션에 존재하는 데이터 확인

1
2
3
4
5
6
7
8
SQL> select (select count(*) from ptable partition (P202110)) "P202110"
, (select count(*) from ptable partition (P202111)) "P202111"
, (select count(*) from ptable partition (P202112)) "P202112" 
from dual;
 
   P202110    P202111    P202112
---------- ---------- ----------
     45494      45608      22737

값을 모두 합치면 113839임

 

 

이 3개 파티션을 합쳐 P2021Q4로 만들 예정

 

 

파티션 merge

1
2
3
SQL> alter table PTABLE merge partitions P202110, P202111, P202112 into partition P2021Q4;
 
Table altered.

 

 

alert log 확인

1
2
3
$ vi alert.log
2024-01-25T10:31:02.827955+09:00
Some indexes or index [sub]partitions of table IMSI.PTABLE have been marked unusable

일부 파티션이 unusable로 되었나고 나옴

 

 

파티션 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
SQL>
set lines 200 pages 1000
col owner for a10
col segment_name for a20
col tablespace_name for a20
col partition_name for a20
col segment_type for a20
select owner, tablespace_name, segment_name, partition_name, segment_type, round(bytes/1024/1024) mb
from dba_segments 
where segment_name = 'PTABLE'
and segment_type in ('TABLE', 'TABLE PARTITION','TABLE SUBPARTITION')
order by 3,4;
 
OWNER      TABLESPACE_NAME      SEGMENT_NAME         PARTITION_NAME       SEGMENT_TYPE                 MB
---------- -------------------- -------------------- -------------------- -------------------- ----------
IMSI       USERS                PTABLE               P202101              TABLE PARTITION               8
IMSI       USERS                PTABLE               P202102              TABLE PARTITION               8
IMSI       USERS                PTABLE               P202103              TABLE PARTITION               8
IMSI       USERS                PTABLE               P202104              TABLE PARTITION               8
IMSI       USERS                PTABLE               P202105              TABLE PARTITION               8
IMSI       USERS                PTABLE               P202106              TABLE PARTITION               8
IMSI       USERS                PTABLE               P202107              TABLE PARTITION               8
IMSI       USERS                PTABLE               P202108              TABLE PARTITION               8
IMSI       USERS                PTABLE               P202109              TABLE PARTITION               8
IMSI       USERS                PTABLE               P2021Q4              TABLE PARTITION               8
IMSI       USERS                PTABLE               P_MAX                TABLE PARTITION              16
 
11 rows selected.

정상적으로 P2021Q4가 만들어짐

 

 

P2021Q4 파티션에 존재하는 데이터 확인

1
2
3
4
5
SQL> select count(*) from ptable partition (P2021Q4);
 
  COUNT(*)
----------
    113839

정상적으로 데이터가 merge 됨

 

 

인덱스 상태 재확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
SQL> 
set lines 200 pages 1000
col index_owner for a10
col index_name for a20
col partition_name for a20
select dip.index_owner, dip.index_name, dip.partition_name, dip.status, dpi.locality, dpi.alignment
from dba_ind_partitions dip, dba_part_indexes dpi
where 1=1
and dpi.index_name = dip.index_name
and dpi.table_name ='PTABLE'
order by dip.index_name, dip.partition_name;
 
INDEX_OWNE INDEX_NAME           PARTITION_NAME       STATUS   LOCALI ALIGNMENT
---------- -------------------- -------------------- -------- ------ ------------
IMSI       PT_IX01              P202101              UNUSABLE GLOBAL PREFIXED
IMSI       PT_IX01              P202102              UNUSABLE GLOBAL PREFIXED
IMSI       PT_IX01              P202103              UNUSABLE GLOBAL PREFIXED
IMSI       PT_IX01              P202104              UNUSABLE GLOBAL PREFIXED
IMSI       PT_IX01              P202105              UNUSABLE GLOBAL PREFIXED
IMSI       PT_IX01              P202106              UNUSABLE GLOBAL PREFIXED
IMSI       PT_IX01              P202107              UNUSABLE GLOBAL PREFIXED
IMSI       PT_IX01              P202108              UNUSABLE GLOBAL PREFIXED
IMSI       PT_IX01              P202109              UNUSABLE GLOBAL PREFIXED
IMSI       PT_IX01              P202110              UNUSABLE GLOBAL PREFIXED
IMSI       PT_IX01              P202111              UNUSABLE GLOBAL PREFIXED
IMSI       PT_IX01              P202112              UNUSABLE GLOBAL PREFIXED
IMSI       PT_IX01              P_MAX                UNUSABLE GLOBAL PREFIXED
IMSI       PT_IX02              P202101              USABLE   LOCAL  PREFIXED
IMSI       PT_IX02              P202102              USABLE   LOCAL  PREFIXED
IMSI       PT_IX02              P202103              USABLE   LOCAL  PREFIXED
IMSI       PT_IX02              P202104              USABLE   LOCAL  PREFIXED
IMSI       PT_IX02              P202105              USABLE   LOCAL  PREFIXED
IMSI       PT_IX02              P202106              USABLE   LOCAL  PREFIXED
IMSI       PT_IX02              P202107              USABLE   LOCAL  PREFIXED
IMSI       PT_IX02              P202108              USABLE   LOCAL  PREFIXED
IMSI       PT_IX02              P202109              USABLE   LOCAL  PREFIXED
IMSI       PT_IX02              P2021Q4              UNUSABLE LOCAL  PREFIXED
IMSI       PT_IX02              P_MAX                USABLE   LOCAL  PREFIXED
 
24 rows selected.

글로벌 인덱스의 경우 인덱스의 모든 파티션이 unusable 상태로 변했고
로컬 인덱스의 경우 merge 된 파티션만 unusable 상태로 변했음

 

 

그리고 로컬 인덱스의 경우 P2021Q4 파티션이 자동으로 merge 되었지만
글로벌 인덱스의 경우 P2021Q4 파티션이 자동으로 merge 되지 않음

 

 

글로벌 인덱스 파티션도 merge가 필요한 경우 수동으로 merge 해줘야 할것같은데
명령어를 찾지 못했음

1
2
3
4
5
SQL> alter index PT_IX01 merge partitions P202110, P202111, P202112 into partition P2021Q4;
alter index PT_IX01 merge partitions P202110, P202111, P202112 into partition P2021Q4
                    *
ERROR at line 1:
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option

이 명령은 작동하지 않음

 

 

로컬 인덱스 리빌드는 아래 두가지 명령을 다 사용가능함
PTABLE의 P2021Q4 파티션에 해당되는 모든 로컬 인덱스 rebuild

1
2
3
SQL> alter table PTABLE modify partition P2021Q4 rebuild unusable local indexes;
 
Index altered.

 

 

PTABLE의 P2021Q4 파티션 로컬 인덱스 PT_IX02 만 개별 rebuild

1
2
3
SQL> alter index PT_IX02 rebuild partition P2021Q4 online;
 
Index altered.

 

 

인덱스 상태 재확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
SQL> 
set lines 200 pages 1000
col index_owner for a10
col index_name for a20
col partition_name for a20
select dip.index_owner, dip.index_name, dip.partition_name, dip.status, dpi.locality, dpi.alignment
from dba_ind_partitions dip, dba_part_indexes dpi
where 1=1
and dpi.index_name = dip.index_name
and dpi.table_name = 'PTABLE'
order by dip.index_name, dip.partition_name;
 
INDEX_OWNE INDEX_NAME           PARTITION_NAME       STATUS   LOCALI ALIGNMENT
---------- -------------------- -------------------- -------- ------ ------------
IMSI       PT_IX01              P202101              UNUSABLE GLOBAL PREFIXED
IMSI       PT_IX01              P202102              UNUSABLE GLOBAL PREFIXED
IMSI       PT_IX01              P202103              UNUSABLE GLOBAL PREFIXED
IMSI       PT_IX01              P202104              UNUSABLE GLOBAL PREFIXED
IMSI       PT_IX01              P202105              UNUSABLE GLOBAL PREFIXED
IMSI       PT_IX01              P202106              UNUSABLE GLOBAL PREFIXED
IMSI       PT_IX01              P202107              UNUSABLE GLOBAL PREFIXED
IMSI       PT_IX01              P202108              UNUSABLE GLOBAL PREFIXED
IMSI       PT_IX01              P202109              UNUSABLE GLOBAL PREFIXED
IMSI       PT_IX01              P202110              UNUSABLE GLOBAL PREFIXED
IMSI       PT_IX01              P202111              UNUSABLE GLOBAL PREFIXED
IMSI       PT_IX01              P202112              UNUSABLE GLOBAL PREFIXED
IMSI       PT_IX01              P_MAX                UNUSABLE GLOBAL PREFIXED
IMSI       PT_IX02              P202101              USABLE   LOCAL  PREFIXED
IMSI       PT_IX02              P202102              USABLE   LOCAL  PREFIXED
IMSI       PT_IX02              P202103              USABLE   LOCAL  PREFIXED
IMSI       PT_IX02              P202104              USABLE   LOCAL  PREFIXED
IMSI       PT_IX02              P202105              USABLE   LOCAL  PREFIXED
IMSI       PT_IX02              P202106              USABLE   LOCAL  PREFIXED
IMSI       PT_IX02              P202107              USABLE   LOCAL  PREFIXED
IMSI       PT_IX02              P202108              USABLE   LOCAL  PREFIXED
IMSI       PT_IX02              P202109              USABLE   LOCAL  PREFIXED
IMSI       PT_IX02              P2021Q4              USABLE   LOCAL  PREFIXED
IMSI       PT_IX02              P_MAX                USABLE   LOCAL  PREFIXED
 
24 rows selected.

PT_IX02 로컬 인덱스의 P2021Q4 파티션이 정상적으로 usable 상태로 변함

 

 

PT_IX01 글로벌 인덱스는 현재상태로 병합이 불가능해서 제거 후 재생성하거나
그냥 rebuild 만 진행해서 상태의 파티션 구조로 사용해야 하는듯함

 

 

PT_IX01 글로벌 인덱스 삭제

1
2
3
SQL> drop index PT_IX01;
 
Index dropped.

 

 

글로벌 인덱스 재생성

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SQL> 
create index pt_ix01 on ptable(col1) global 
partition by range (col1)
(
partition p202101 values less than ('20210200'),
partition p202102 values less than ('20210300'),
partition p202103 values less than ('20210400'),
partition p202104 values less than ('20210500'),
partition p202105 values less than ('20210600'),
partition p202106 values less than ('20210700'),
partition p202107 values less than ('20210800'),
partition p202108 values less than ('20210900'),
partition p202109 values less than ('20211000'),
partition p2021Q4 values less than ('20220100'),
partition p_max values less than (maxvalue)
) online;
 
Index created.

 

 

인덱스 상태 재확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
SQL> 
set lines 200 pages 1000
col index_owner for a10
col index_name for a20
col partition_name for a20
select dip.index_owner, dip.index_name, dip.partition_name, dip.status, dpi.locality, dpi.alignment
from dba_ind_partitions dip, dba_part_indexes dpi
where 1=1
and dpi.index_name = dip.index_name
and dpi.table_name = 'PTABLE'
order by dip.index_name, dip.partition_name;
 
INDEX_OWNE INDEX_NAME           PARTITION_NAME       STATUS   LOCALI ALIGNMENT
---------- -------------------- -------------------- -------- ------ ------------
IMSI       PT_IX01              P202101              USABLE   GLOBAL PREFIXED
IMSI       PT_IX01              P202102              USABLE   GLOBAL PREFIXED
IMSI       PT_IX01              P202103              USABLE   GLOBAL PREFIXED
IMSI       PT_IX01              P202104              USABLE   GLOBAL PREFIXED
IMSI       PT_IX01              P202105              USABLE   GLOBAL PREFIXED
IMSI       PT_IX01              P202106              USABLE   GLOBAL PREFIXED
IMSI       PT_IX01              P202107              USABLE   GLOBAL PREFIXED
IMSI       PT_IX01              P202108              USABLE   GLOBAL PREFIXED
IMSI       PT_IX01              P202109              USABLE   GLOBAL PREFIXED
IMSI       PT_IX01              P2021Q4              USABLE   GLOBAL PREFIXED
IMSI       PT_IX01              P_MAX                USABLE   GLOBAL PREFIXED
IMSI       PT_IX02              P202101              USABLE   LOCAL  PREFIXED
IMSI       PT_IX02              P202102              USABLE   LOCAL  PREFIXED
IMSI       PT_IX02              P202103              USABLE   LOCAL  PREFIXED
IMSI       PT_IX02              P202104              USABLE   LOCAL  PREFIXED
IMSI       PT_IX02              P202105              USABLE   LOCAL  PREFIXED
IMSI       PT_IX02              P202106              USABLE   LOCAL  PREFIXED
IMSI       PT_IX02              P202107              USABLE   LOCAL  PREFIXED
IMSI       PT_IX02              P202108              USABLE   LOCAL  PREFIXED
IMSI       PT_IX02              P202109              USABLE   LOCAL  PREFIXED
IMSI       PT_IX02              P2021Q4              USABLE   LOCAL  PREFIXED
IMSI       PT_IX02              P_MAX                USABLE   LOCAL  PREFIXED
 
22 rows selected.

정상적으로 생성됨

 

 

그리고 merge 명령 뒤에 update indexes online 명령을 사용해주면
인덱스들을 자동으로 rebuild해줘서 unusable 상태로 변하지 않고 바로 usable 상태로 사용가능함

 

 

merge 시 update indexes online 구문 사용

1
2
3
SQL> alter table PTABLE merge partitions P202107, P202108, P202109 into partition P2021Q3 update indexes online;
 
Table altered.

 

 

alert log에도 별다른 로그가 남지 않음

1
2
$ vi alert.log
(특이사항 없음)

 

 

파티션 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
SQL>
set lines 200 pages 1000
col owner for a10
col segment_name for a20
col tablespace_name for a20
col partition_name for a20
col segment_type for a20
select owner, tablespace_name, segment_name, partition_name, segment_type, round(bytes/1024/1024) mb
from dba_segments 
where segment_name = 'PTABLE'
and segment_type in ('TABLE', 'TABLE PARTITION','TABLE SUBPARTITION')
order by 3,4;

 
OWNER      TABLESPACE_NAME      SEGMENT_NAME         PARTITION_NAME       SEGMENT_TYPE                 MB
---------- -------------------- -------------------- -------------------- -------------------- ----------
IMSI       USERS                PTABLE               P202101              TABLE PARTITION               8
IMSI       USERS                PTABLE               P202102              TABLE PARTITION               8
IMSI       USERS                PTABLE               P202103              TABLE PARTITION               8
IMSI       USERS                PTABLE               P202104              TABLE PARTITION               8
IMSI       USERS                PTABLE               P202105              TABLE PARTITION               8
IMSI       USERS                PTABLE               P202106              TABLE PARTITION               8
IMSI       USERS                PTABLE               P2021Q3              TABLE PARTITION               8
IMSI       USERS                PTABLE               P2021Q4              TABLE PARTITION               8
IMSI       USERS                PTABLE               P_MAX                TABLE PARTITION              16
 
9 rows selected.

P2021Q3으로 merge됨

 

 

인덱스 상태 재확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
SQL> 
set lines 200 pages 1000
col index_owner for a10
col index_name for a20
col partition_name for a20
select dip.index_owner, dip.index_name, dip.partition_name, dip.status, dpi.locality, dpi.alignment
from dba_ind_partitions dip, dba_part_indexes dpi
where 1=1
and dpi.index_name = dip.index_name
and dpi.table_name = 'PTABLE'
order by dip.index_name, dip.partition_name;
 
INDEX_OWNE INDEX_NAME           PARTITION_NAME       STATUS   LOCALI ALIGNMENT
---------- -------------------- -------------------- -------- ------ ------------
IMSI       PT_IX01              P202101              USABLE   GLOBAL PREFIXED
IMSI       PT_IX01              P202102              USABLE   GLOBAL PREFIXED
IMSI       PT_IX01              P202103              USABLE   GLOBAL PREFIXED
IMSI       PT_IX01              P202104              USABLE   GLOBAL PREFIXED
IMSI       PT_IX01              P202105              USABLE   GLOBAL PREFIXED
IMSI       PT_IX01              P202106              USABLE   GLOBAL PREFIXED
IMSI       PT_IX01              P202107              USABLE   GLOBAL PREFIXED
IMSI       PT_IX01              P202108              USABLE   GLOBAL PREFIXED
IMSI       PT_IX01              P202109              USABLE   GLOBAL PREFIXED
IMSI       PT_IX01              P2021Q4              USABLE   GLOBAL PREFIXED
IMSI       PT_IX01              P_MAX                USABLE   GLOBAL PREFIXED
IMSI       PT_IX02              P202101              USABLE   LOCAL  PREFIXED
IMSI       PT_IX02              P202102              USABLE   LOCAL  PREFIXED
IMSI       PT_IX02              P202103              USABLE   LOCAL  PREFIXED
IMSI       PT_IX02              P202104              USABLE   LOCAL  PREFIXED
IMSI       PT_IX02              P202105              USABLE   LOCAL  PREFIXED
IMSI       PT_IX02              P202106              USABLE   LOCAL  PREFIXED
IMSI       PT_IX02              P2021Q3              USABLE   LOCAL  PREFIXED
IMSI       PT_IX02              P2021Q4              USABLE   LOCAL  PREFIXED
IMSI       PT_IX02              P_MAX                USABLE   LOCAL  PREFIXED
 
20 rows selected.

로컬 인덱스와 글로벌 인덱스 모두 usable 상태임
로컬 인덱스는 자동으로 P2021Q3 파티션으로 병합되었고, 글로벌 인덱스는 병합되지 않음
병합을 위해선 인덱스 재생성이 필요함

 

 

결론 :
파티션 테이블에 merge 작업 시 글로벌 인덱스는 모든 파티션이 unusable 상태로 변경되고 로컬 인덱스는 merge 대상 파티션만 unusable 상태로 변경됨
다만 merge 작업 시 update indexes online 명령을 같이 사용하면 unusable 상태로 변경되지 않고 online 으로 작업 가능함
로컬 인덱스는 자동으로 테이블 파티션 구조와 동일하게 병합되고, 글로벌 인덱스는 병합되지 않아 재생성해서 병합하거나 글로벌 인덱스 파티션 구조는 다르게 가져가야함

참고로 ddl시 unusable 로 변경되는건 데이터가 들어있는 파티션만 해당됨, 파티션이 비어있는 경우 ddl시 변경되지 않음
(Only indexes and index partitions that are not empty are candidates for being marked UNUSABLE. If they are empty, the USABLE/UNUSABLE status is left unchanged.)
https://docs.oracle.com/en/database/oracle/oracle-database/19/vldbg/maintenance-partition-tables-indexes.html#GUID-A226B597-BCF1-49E2-8284-739A99D3F9ED

 

 

참조 : 

https://docs.oracle.com/en/database/oracle/oracle-database/19/vldbg/maintenance-partition-tables-indexes.html
https://docs.oracle.com/en/database/oracle/oracle-database/19/vldbg/index-partitioning.html#GUID-D1E775A0-669B-4E51-8D40-858847B64BEF
https://docs.oracle.com/en/database/oracle/oracle-database/19/vldbg/maintenance-partition-can-be-performed.html#GUID-1D59BD49-CD86-4BFE-9099-D3B8D7FD932A
https://docs.oracle.com/en/database/oracle/oracle-database/19/vldbg/maintenance-partition-tables-indexes.html#GUID-1F3607A1-6790-4C14-9DDA-57B5D84E32CB
https://blog.naver.com/darkturtle/50081249661
https://tuna.tistory.com/138
https://m.blog.naver.com/hanccii/221397362641

오라클 19c 파티션 테이블 DDL 작업 시 인덱스 영향도 확인 ( https://positivemh.tistory.com/1007 )

 

오라클 19c 파티션 테이블 add 작업 시 인덱스 영향도 확인 ( https://positivemh.tistory.com/999 )

오라클 19c 파티션 테이블 drop 작업 시 인덱스 영향도 확인 ( https://positivemh.tistory.com/1000 )

오라클 19c 파티션 테이블 exchange 작업 시 인덱스 영향도 확인 ( https://positivemh.tistory.com/1001 )

오라클 19c 파티션 테이블 merge 작업 시 인덱스 영향도 확인 ( https://positivemh.tistory.com/1002 )

오라클 19c 파티션 테이블 move 작업 시 인덱스 영향도 확인 ( https://positivemh.tistory.com/1003 )

오라클 19c 파티션 테이블 rename 작업 시 인덱스 영향도 확인 ( https://positivemh.tistory.com/1004 )

오라클 19c 파티션 테이블 split 작업 시 인덱스 영향도 확인 ( https://positivemh.tistory.com/1005 )

오라클 19c 파티션 테이블 truncate 작업 시 인덱스 영향도 확인 ( https://positivemh.tistory.com/1006 )