Oracle Partition Table Splite 테스트

Oracle Partition Table Splite Test 

인덱스 상태 확인

-- 전체범위 데이터 확인
SELECT count(*) from PARTITION_TEST
WHERE ORDER_DATE BETWEEN '20220101' AND  '20241231'
ORDER BY ORDER_DATE ASC;
 
-- COUNT(*)
100000
 
--MAXVALUE 데이터를 확인 
SELECT count(*) FROM PARTITION_TEST PARTITION(MAXVALUE)
-- COUNT(*)
25827
 
-- 인덱스 확인
SELECT OWNER,INDEX_NAME , INDEX_TYPE ,UNIQUENESS,TABLESPACE_NAME , STATUS  FROM DBA_INDEXES
WHERE TABLE_NAME='PARTITION_TEST';
 
OWNER    INDEX_NAME                INDEX_TYPE        UNIQUENESS        TABLESPACE_NAME    STATUS
TUNING    PARTITION_TEST    NORMAL        UNIQUE            N/A
TUNING    IX_ORDERS_PARTITION_01    NORMAL        NONUNIQUE        N/A
TUNING    IX_ORDERS_PARTITION_02    NORMAL        NONUNIQUE        N/A
TUNING    IX_ORDERS_PARTITION_03    NORMAL        NONUNIQUE        USERS                VALID
 
 
-- IX_ORDERS_PARTITION_01 로컬인덱스
-- IX_ORDERS_PARTITION_02 로컬인덱스
-- IX_ORDERS_PARTITION_03 글로벌 인덱스 
-- ORDERS_PARTITION 유니크인덱스
 
 
-- PARTITION(MAXVALUE) 값 형식 확인하기
SELECT ORDER_DATE , count(*) FROM PARTITION_TEST PARTITION(MAXVALUE)
GROUP BY ORDER_DATE
order by ORDER_DATE;
 
ORDER_DATE                 COUNT(*)  
-------------------------- --------- 
2024/05/12 00:00:00               91
2024/05/20 00:00:00              104
2024/06/22 00:00:00              113
2024/07/11 00:00:00              118
2024/07/24 00:00:00               96
2024/08/11 00:00:00               90
2024/08/13 00:00:00               87
2024/08/22 00:00:00              102
.
.
.
.
152 rows selected.
 
-- 현재 없는 파티션
5~12월
 
PARTITION TEST_202405 VALUES LESS THAN(TO_DATE('20240601', 'YYYYMMDD'))TABLESPACE USERS,
PARTITION TEST_202406 VALUES LESS THAN(TO_DATE('20240701', 'YYYYMMDD'))TABLESPACE USERS,
PARTITION TEST_202407 VALUES LESS THAN(TO_DATE('20240801', 'YYYYMMDD'))TABLESPACE USERS,
PARTITION TEST_202408 VALUES LESS THAN(TO_DATE('20240901', 'YYYYMMDD'))TABLESPACE USERS,
PARTITION TEST_202409 VALUES LESS THAN(TO_DATE('20241001', 'YYYYMMDD'))TABLESPACE USERS,
PARTITION TEST_202410 VALUES LESS THAN(TO_DATE('20241101', 'YYYYMMDD'))TABLESPACE USERS,
PARTITION TEST_202411 VALUES LESS THAN(TO_DATE('20241201', 'YYYYMMDD'))TABLESPACE USERS,
PARTITION TEST_202412 VALUES LESS THAN(TO_DATE('20250101', 'YYYYMMDD'))TABLESPACE USERS,
 
-- 파티셔닝 스플릿 수행
ALTER TABLE PARTITION_TEST
SPLIT PARTITION MAXVALUE 
INTO
(
PARTITION TEST_202405 VALUES LESS THAN(TO_DATE('20240601', 'YYYYMMDD'))TABLESPACE USERS,
PARTITION TEST_202406 VALUES LESS THAN(TO_DATE('20240701', 'YYYYMMDD'))TABLESPACE USERS,
PARTITION TEST_202407 VALUES LESS THAN(TO_DATE('20240801', 'YYYYMMDD'))TABLESPACE USERS,
PARTITION TEST_202408 VALUES LESS THAN(TO_DATE('20240901', 'YYYYMMDD'))TABLESPACE USERS,
PARTITION TEST_202409 VALUES LESS THAN(TO_DATE('20241001', 'YYYYMMDD'))TABLESPACE USERS,
PARTITION TEST_202410 VALUES LESS THAN(TO_DATE('20241101', 'YYYYMMDD'))TABLESPACE USERS,
PARTITION TEST_202411 VALUES LESS THAN(TO_DATE('20241201', 'YYYYMMDD'))TABLESPACE USERS,
PARTITION TEST_202412 VALUES LESS THAN(TO_DATE('20250101', 'YYYYMMDD'))TABLESPACE USERS,
PARTITION MAXVALUE  
)
UPDATE GLOBAL INDEXES -- ONLINE; -- GLOBAL INDEX 리빌드 작업과 동일
 
-- Session2 LOCK 유무확인
SELECT vo.session_id,do.object_name, do.owner, do.object_type,do.owner, 
       vo.xidusn, vo.locked_mode 
FROM v$locked_object vo , dba_objects do 
WHERE vo.object_id = do.object_id
 
SESSION_ID    OBJECT_NAME     OWNER    OBJECT_TYPE    OWNER        XIDUSN        LOCKED_MODE
384    OBJ$               SYS        TABLE                SYS        6            3
384    PARTITION_TEST    TUNING        TABLE PARTITION    TUNING        6            6
384    PARTITION_TEST    TUNING        TABLE                TUNING        6            3
 
Partition Split 수행 시 해당 오브젝트는 LOCK 상태이다. Online 옵션 권장
-- MAXVALUE split상태확인
SELECT count(*) FROM PARTITION_TEST PARTITION(MAXVALUE)
-- COUNT(*)
0 
 
-- 글로벌 인덱스 상태확인
SELECT OWNER,INDEX_NAME , INDEX_TYPE ,UNIQUENESS,TABLESPACE_NAME , STATUS  FROM DBA_INDEXES
WHERE TABLE_NAME='PARTITION_TEST';
 
-- 글로벌 인덱스 정상 확인
OWNER    INDEX_NAME            INDEX_TYPE    UNIQUENESS    TABLESPACE_NAME STATUS
TUNING    PARTITION_TEST            NORMAL        UNIQUE        N/A
TUNING    IX_ORDERS_PARTITION_01    NORMAL        NONUNIQUE        N/A
TUNING    IX_ORDERS_PARTITION_02    NORMAL        NONUNIQUE        N/A
TUNING    IX_ORDERS_PARTITION_03    NORMAL        NONUNIQUE    USERS    VALID
 
-- 로컬 인덱스 비정상 상태확인
SELECT INDEX_OWNER , INDEX_NAME , PARTITION_NAME , STATUS FROM DBA_IND_PARTITIONS
WHERE INDEX_OWNER = 'TUNING'
AND INDEX_NAME IN ('PARTITION_TEST' ,'IX_ORDERS_PARTITION_01','IX_ORDERS_PARTITION_02','IX_ORDERS_PARTITION_03')
AND STATUS = 'UNUSABLE';
 
-- 로컬 인덱스 비정상 확인
INDEX_OWNER     INDEX_NAME              PARTITION_NAME            STATUS
TUNING            IX_ORDERS_PARTITION_01    TEST_202405            UNUSABLE
TUNING            IX_ORDERS_PARTITION_01    TEST_202406            UNUSABLE
TUNING            IX_ORDERS_PARTITION_01    TEST_202407            UNUSABLE
TUNING            IX_ORDERS_PARTITION_01    TEST_202408            UNUSABLE
TUNING            IX_ORDERS_PARTITION_01    TEST_202409            UNUSABLE
TUNING            IX_ORDERS_PARTITION_01    TEST_202410            UNUSABLE
TUNING            IX_ORDERS_PARTITION_01    TEST_202411            UNUSABLE
TUNING            IX_ORDERS_PARTITION_01    TEST_202412            UNUSABLE
TUNING            IX_ORDERS_PARTITION_02    TEST_202405            UNUSABLE
TUNING            IX_ORDERS_PARTITION_02    TEST_202406            UNUSABLE
TUNING            IX_ORDERS_PARTITION_02    TEST_202407            UNUSABLE
TUNING            IX_ORDERS_PARTITION_02    TEST_202408            UNUSABLE
TUNING            IX_ORDERS_PARTITION_02    TEST_202409            UNUSABLE
TUNING            IX_ORDERS_PARTITION_02    TEST_202410            UNUSABLE
TUNING            IX_ORDERS_PARTITION_02    TEST_202411            UNUSABLE
TUNING            IX_ORDERS_PARTITION_02    TEST_202412            UNUSABLE
TUNING            PARTITION_TEST            TEST_202405            UNUSABLE
TUNING            PARTITION_TEST            TEST_202406            UNUSABLE
TUNING            PARTITION_TEST            TEST_202407            UNUSABLE
TUNING            PARTITION_TEST            TEST_202408            UNUSABLE
TUNING            PARTITION_TEST            TEST_202409            UNUSABLE
TUNING            PARTITION_TEST            TEST_202410            UNUSABLE
TUNING            PARTITION_TEST            TEST_202411            UNUSABLE
TUNING            PARTITION_TEST            TEST_202412            UNUSABLE
스플릿 수행된 파티션 테이블 LOCAL 인덱스 상태가 UNUSABLE 상태확인

 

-- 로컬 인덱스 리빌드 수행
 
SELECT 'alter index '||index_name||' rebuild partition '|| partition_name ||' online;' FROM DBA_IND_PARTITIONS
WHERE INDEX_OWNER = 'TUNING'
AND INDEX_NAME IN ('PARTITION_TEST' ,'IX_ORDERS_PARTITION_01','IX_ORDERS_PARTITION_02','IX_ORDERS_PARTITION_03')
AND STATUS = 'UNUSABLE'
GROUP BY index_name ,partition_name
ORDER BY 1;
 
alter index IX_ORDERS_PARTITION_01 rebuild partition TEST_202405 online;
alter index IX_ORDERS_PARTITION_01 rebuild partition TEST_202406 online;
alter index IX_ORDERS_PARTITION_01 rebuild partition TEST_202407 online;
alter index IX_ORDERS_PARTITION_01 rebuild partition TEST_202408 online;
alter index IX_ORDERS_PARTITION_01 rebuild partition TEST_202409 online;
alter index IX_ORDERS_PARTITION_01 rebuild partition TEST_202410 online;
alter index IX_ORDERS_PARTITION_01 rebuild partition TEST_202411 online;
alter index IX_ORDERS_PARTITION_01 rebuild partition TEST_202412 online;
alter index IX_ORDERS_PARTITION_02 rebuild partition TEST_202405 online;
alter index IX_ORDERS_PARTITION_02 rebuild partition TEST_202406 online;
alter index IX_ORDERS_PARTITION_02 rebuild partition TEST_202407 online;
alter index IX_ORDERS_PARTITION_02 rebuild partition TEST_202408 online;
alter index IX_ORDERS_PARTITION_02 rebuild partition TEST_202409 online;
alter index IX_ORDERS_PARTITION_02 rebuild partition TEST_202410 online;
alter index IX_ORDERS_PARTITION_02 rebuild partition TEST_202411 online;
alter index IX_ORDERS_PARTITION_02 rebuild partition TEST_202412 online;
alter index PARTITION_TEST rebuild partition TEST_202405 online;
alter index PARTITION_TEST rebuild partition TEST_202406 online;
alter index PARTITION_TEST rebuild partition TEST_202407 online;
alter index PARTITION_TEST rebuild partition TEST_202408 online;
alter index PARTITION_TEST rebuild partition TEST_202409 online;
alter index PARTITION_TEST rebuild partition TEST_202410 online;
alter index PARTITION_TEST rebuild partition TEST_202411 online;
alter index PARTITION_TEST rebuild partition TEST_202412 online;
 
 
SELECT INDEX_OWNER, INDEX_NAME, PARTITION_NAME, STATUS FROM DBA_IND_PARTITIONS
WHERE INDEX_OWNER = 'TUNING'
AND INDEX_NAME IN ('PARTITION_TEST','IX_ORDERS_PARTITION_01','IX_ORDERS_PARTITION_02','IX_ORDERS_PARTITION_03')
AND STATUS = 'UNUSABLE';
 
--리빌드 대상 0 ROWS