OS환경 : Oracle Linux 7.6 (64bit)
DB 환경 : Oracle Database 19.3.0.0
방법 : 오라클 19c 파티션 테이블 split 작업 시 인덱스 영향도 확인
오라클에서 파티션 테이블에 split 작업을 할때 발생하는 현상에 대해 확인해봄
샘플 유저 생성
|
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 상태)
max 파티션에 존재하는 데이터 확인
|
1
2
3
4
5
|
SQL> select count(*) from ptable partition (p_max);
COUNT(*)
----------
500384
|
max 파티션에 많은 데이터가 들어있음
p_max파티션을 split 해서 P202201 파티션을 만들 예정
P202201 파티션에 들어가게될 데이터 확인
|
1
2
3
4
5
|
SQL> select count(*) from ptable partition (p_max) where col1 <= '20220200';
COUNT(*)
----------
22504
|
max 파티션 split
|
1
2
3
|
SQL> alter table PTABLE split partition p_max at ('202202') into (partition p202201, partition p_max);
Table altered.
|
alert log 확인
|
1
2
3
|
$ vi alert.log
2024-01-24T15:48:11.237528+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
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
|
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 P202110 USABLE LOCAL PREFIXED
IMSI PT_IX02 P202111 USABLE LOCAL PREFIXED
IMSI PT_IX02 P202112 USABLE LOCAL PREFIXED
IMSI PT_IX02 P202201 UNUSABLE LOCAL PREFIXED
IMSI PT_IX02 P_MAX UNUSABLE LOCAL PREFIXED
27 rows selected.
|
글로벌 인덱스의 경우 인덱스의 모든 파티션이 unusable 상태로 변했고
로컬 인덱스의 경우 max 파티션과 split한 파티션만 unusable 상태로 변했음
그리고 로컬 인덱스의 경우 P202201 파티션이 자동으로 만들어졌지만
글로벌 인덱스의 경우 P202201 파티션이 자동으로 만들어지지 않음
신규 파티션이 필요한 경우 수동으로 split 해줘야함
|
1
2
3
|
SQL> alter index PT_IX01 split partition P_MAX at ('202202') into (partition p202201, partition p_max);
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
41
42
43
44
|
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 P202201 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 P202110 USABLE LOCAL PREFIXED
IMSI PT_IX02 P202111 USABLE LOCAL PREFIXED
IMSI PT_IX02 P202112 USABLE LOCAL PREFIXED
IMSI PT_IX02 P202201 UNUSABLE LOCAL PREFIXED
IMSI PT_IX02 P_MAX UNUSABLE LOCAL PREFIXED
28 rows selected.
|
PT_IX01 인덱스의 P202201 파티션이 정상적으로 생성됨
문제가 발생한 로컬 인덱스 파티션 리빌드
|
1
2
3
|
SQL>
alter index PT_IX02 rebuild partition P202201;
alter index PT_IX02 rebuild partition P_MAX;
|
인덱스 상태 재확인
|
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
43
44
|
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 P202201 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 P202110 USABLE LOCAL PREFIXED
IMSI PT_IX02 P202111 USABLE LOCAL PREFIXED
IMSI PT_IX02 P202112 USABLE LOCAL PREFIXED
IMSI PT_IX02 P202201 USABLE LOCAL PREFIXED
IMSI PT_IX02 P_MAX USABLE LOCAL PREFIXED
28 rows selected.
|
로컬 인덱스 파티션만 usable 상태로 변했음
나머지 인덱스의 파티션들도 모두 rebulid 진행
구문 출력
|
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
|
SQL>
set lines 200 pages 1000
col sql for a70
select 'alter index '|| dip.index_owner || '.' || dip.index_name ||' rebuild partition '|| dip.partition_name || ' online;' "SQL"
from dba_ind_partitions dip, dba_part_indexes dpi
where 1=1
and dpi.index_name = dip.index_name
and dpi.table_name ='PTABLE'
and dip.status = 'UNUSABLE'
order by dip.index_name, dip.partition_name;
SQL
----------------------------------------------------------------------
alter index IMSI.PT_IX01 rebuild partition P202101 online;
alter index IMSI.PT_IX01 rebuild partition P202102 online;
alter index IMSI.PT_IX01 rebuild partition P202103 online;
alter index IMSI.PT_IX01 rebuild partition P202104 online;
alter index IMSI.PT_IX01 rebuild partition P202105 online;
alter index IMSI.PT_IX01 rebuild partition P202106 online;
alter index IMSI.PT_IX01 rebuild partition P202107 online;
alter index IMSI.PT_IX01 rebuild partition P202108 online;
alter index IMSI.PT_IX01 rebuild partition P202109 online;
alter index IMSI.PT_IX01 rebuild partition P202110 online;
alter index IMSI.PT_IX01 rebuild partition P202111 online;
alter index IMSI.PT_IX01 rebuild partition P202112 online;
alter index IMSI.PT_IX01 rebuild partition P202201 online;
alter index IMSI.PT_IX01 rebuild partition P_MAX online;
14 rows selected.
|
해당 쿼리 실행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SQL>
alter index IMSI.PT_IX01 rebuild partition P202101 online;
alter index IMSI.PT_IX01 rebuild partition P202102 online;
alter index IMSI.PT_IX01 rebuild partition P202103 online;
alter index IMSI.PT_IX01 rebuild partition P202104 online;
alter index IMSI.PT_IX01 rebuild partition P202105 online;
alter index IMSI.PT_IX01 rebuild partition P202106 online;
alter index IMSI.PT_IX01 rebuild partition P202107 online;
alter index IMSI.PT_IX01 rebuild partition P202108 online;
alter index IMSI.PT_IX01 rebuild partition P202109 online;
alter index IMSI.PT_IX01 rebuild partition P202110 online;
alter index IMSI.PT_IX01 rebuild partition P202111 online;
alter index IMSI.PT_IX01 rebuild partition P202112 online;
alter index IMSI.PT_IX01 rebuild partition P202201 online;
alter index IMSI.PT_IX01 rebuild partition P_MAX online;
|
파티션 인덱스 재확인
|
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
43
44
|
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
---------- -------------------- -------------------- --------
IMSI PT_IX01 P202101 USABLE
IMSI PT_IX01 P202102 USABLE
IMSI PT_IX01 P202103 USABLE
IMSI PT_IX01 P202104 USABLE
IMSI PT_IX01 P202105 USABLE
IMSI PT_IX01 P202106 USABLE
IMSI PT_IX01 P202107 USABLE
IMSI PT_IX01 P202108 USABLE
IMSI PT_IX01 P202109 USABLE
IMSI PT_IX01 P202110 USABLE
IMSI PT_IX01 P202111 USABLE
IMSI PT_IX01 P202112 USABLE
IMSI PT_IX01 P202201 USABLE
IMSI PT_IX01 P_MAX USABLE
IMSI PT_IX02 P202101 USABLE
IMSI PT_IX02 P202102 USABLE
IMSI PT_IX02 P202103 USABLE
IMSI PT_IX02 P202104 USABLE
IMSI PT_IX02 P202105 USABLE
IMSI PT_IX02 P202106 USABLE
IMSI PT_IX02 P202107 USABLE
IMSI PT_IX02 P202108 USABLE
IMSI PT_IX02 P202109 USABLE
IMSI PT_IX02 P202110 USABLE
IMSI PT_IX02 P202111 USABLE
IMSI PT_IX02 P202112 USABLE
IMSI PT_IX02 P202201 USABLE
IMSI PT_IX02 P_MAX USABLE
28 rows selected.
|
모두 정상화됨
P202201 파티션에 데이터가 잘 이동했는지 확인
|
1
2
3
4
5
|
SQL> select count(*) from ptable partition (P202201) where col1 <= '20220200';
COUNT(*)
----------
22504
|
정상적으로 이동됨
이렇게 글로벌 파티션 인덱스가 존재하는 경우 ddl 작업시 수동으로 rebuild를 해줘야하는 불편함이 생김
이때 split 명령 뒤에 update indexes online 명령을 사용해주면
index를 자동으로 rebuild해줘서 unusable 상태로 변하지 않고 바로 usable 상태로 사용가능함
split 시 update indexes online 구문 사용
|
1
2
3
|
SQL> alter table PTABLE split partition p_max at ('202203') into (partition p202202, partition p_max) 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
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
|
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
---------- -------------------- -------------------- --------
IMSI PT_IX01 P202101 USABLE
IMSI PT_IX01 P202102 USABLE
IMSI PT_IX01 P202103 USABLE
IMSI PT_IX01 P202104 USABLE
IMSI PT_IX01 P202105 USABLE
IMSI PT_IX01 P202106 USABLE
IMSI PT_IX01 P202107 USABLE
IMSI PT_IX01 P202108 USABLE
IMSI PT_IX01 P202109 USABLE
IMSI PT_IX01 P202110 USABLE
IMSI PT_IX01 P202111 USABLE
IMSI PT_IX01 P202112 USABLE
IMSI PT_IX01 P202201 USABLE
IMSI PT_IX01 P_MAX USABLE
IMSI PT_IX02 P202101 USABLE
IMSI PT_IX02 P202102 USABLE
IMSI PT_IX02 P202103 USABLE
IMSI PT_IX02 P202104 USABLE
IMSI PT_IX02 P202105 USABLE
IMSI PT_IX02 P202106 USABLE
IMSI PT_IX02 P202107 USABLE
IMSI PT_IX02 P202108 USABLE
IMSI PT_IX02 P202109 USABLE
IMSI PT_IX02 P202110 USABLE
IMSI PT_IX02 P202111 USABLE
IMSI PT_IX02 P202112 USABLE
IMSI PT_IX02 P202201 USABLE
IMSI PT_IX02 P202202 USABLE
IMSI PT_IX02 P_MAX USABLE
29 rows selected.
|
모두 정상상태임
PT_IX01 글로벌 인덱스에 신규 파티션이 필요한 경우 수동으로 split 해줘야함
|
1
2
3
|
SQL> alter index PT_IX01 split partition P_MAX at ('202203') into (partition p202202, partition p_max);
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
41
42
43
44
45
46
|
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 P202201 USABLE GLOBAL PREFIXED
IMSI PT_IX01 P202202 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 P202201 USABLE LOCAL PREFIXED
IMSI PT_IX02 P202202 USABLE LOCAL PREFIXED
IMSI PT_IX02 P_MAX USABLE LOCAL PREFIXED
30 rows selected.
|
+테스트 하다가 발견한 사항인데 split 구문에서 그냥 online 구문만 추가해줘도 index가 unusable 상태로 변하지 않음을 확인함
split 시 online 구문만 추가 후 실행
|
1
2
3
|
SQL> alter table PTABLE split partition p_max at ('202204') into (partition p202203, partition p_max) online;
Table 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
41
42
43
44
45
46
47
|
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 P202201 USABLE GLOBAL PREFIXED
IMSI PT_IX01 P202202 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 P202201 USABLE LOCAL PREFIXED
IMSI PT_IX02 P202202 USABLE LOCAL PREFIXED
IMSI PT_IX02 P202203 USABLE LOCAL PREFIXED
IMSI PT_IX02 P_MAX USABLE LOCAL PREFIXED
31 rows selected.
|
모두 정상상태임
결론 :
파티션 테이블에 split 작업 시 글로벌 인덱스는 모든 파티션이 unusable 상태로 변경되고 로컬 인덱스는 MAX 파티션과 split 한 파티션만 unusable 상태로 변경됨
다만 split 작업 시 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 )
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 19c 파티션 테이블 DDL 작업 시 인덱스 영향도 확인 (0) | 2024.01.25 |
|---|---|
| 오라클 19c 파티션 테이블 truncate 작업 시 인덱스 영향도 확인 (2) | 2024.01.25 |
| 오라클 19c 파티션 테이블 rename 작업 시 인덱스 영향도 확인 (0) | 2024.01.25 |
| 오라클 19c 파티션 테이블 move 작업 시 인덱스 영향도 확인 (0) | 2024.01.25 |
| 오라클 19c 파티션 테이블 merge 작업 시 인덱스 영향도 확인 (0) | 2024.01.25 |