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

OS환경 : Oracle Linux 7.6 (64bit)

 

DB 환경 : Oracle Database 19.3.0.0

 

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

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

 

 

샘플 유저 생성

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

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') );   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   := '2021'||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

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   12 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

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   25 rows selected.

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

 

 

파티션 add

1 2 3

SQL> alter table PTABLE add partition P202201 values less than ('20220200');   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

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              P202201              USABLE   LOCAL  PREFIXED   26 rows selected.

add시 특이사항 없음, 모두 정상상태임

로컬 인덱스는 자동으로 인덱스도 생성되었지만 글로벌 인덱스는 생성되지 않음(테이블 파티션 구조와 별개로 동작하기 때문)

 

 

PT_IX01 글로벌 인덱스에 신규 파티션이 필요한 경우 수동으로 split 해줘야함

1 2 3

SQL> alter index PT_IX01 split partition P_MAX at ('202202') into (partition p202201, partition p_max);   Index altered.

 

 

결론 : 파티션 테이블에 add 작업 시 글로벌 인덱스와 로컬 인덱스 모두 unusable 상태로 변경되지 않음, online 으로 작업 가능함

 

 

참조 : 

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 )