Range Partition을 사용할 경우 연말이 되거나 혹은 지난 PM 때 다음해를 준비하는 Partition 작업을 해야할 때가 있다.
부지런한 DBA의 경우 매달 향후 2-3 개월 정도의 파티션을 만들고 주기적으로 작업을 하기도 한다.
이런 작업으로 인해 인덱스 등이 영향을 받아 성능이 안 좋아지는 사례가 종종 발생한다.
이는 수행 전 작업으로 인한 영향도에 대해서 충분히 고려하고 넘어가지 못해 발생하는 경우라 할 수 있다.
작업에 대한 내용, 발생할 부분에 대해 충분히 인지하고 케이스 별로 확인 후 작업에 대해 준비를 해야할 것이다.
이에 파티션 작업에 대한 여러 케이스에 대해서 테스트를 진행하여 경우를 살펴 보았다.
1. 테스트 환경 생성
2. Partition 추가
3. Partition을 중간 삽입형태로 추가 테스트
4. Partition Truncate 테스트
5. Partition Drop 테스트
6. Partition Split 테스트(Wide 분포의 데이터를 지닌 Partition이 split 대상인 경우)
7. Partition Split 테스트(split 대상 Partition에 우편향 데이터만 들어 있어 split 후 우측 파티션에만 데이터가 들어가는 경우)
8. Partition Split 테스트(split 대상 Partition에 좌편향 데이터만 들어 있어 split 후 좌측 파티션에만 데이터가 들어가는 경우)
9. Partition Exchange 테스트
10. MAX Value를 갖는 Partition 추가, 삭제
결론
status가 Unusable로 변경되는 경우는 Partition Segment의 Row가 Row Movement 액션이 발생할 거라 여겨지는 작업일 경우
인덱스에 저장되는 rowid가 변경되어 Unusable 상태로 변경되게 된다.
1. 테스트 환경 생성
테스트 Table, Index를 생성하고 Test 용 데이터를 생성한다.
PARTITION_TEST_PK - Local Partition Index
PARTITION_TEST_IDX01 - Global Index
SQL> CREATE TABLE PARTITION_TEST
2 (
3 YYYYMM VARCHAR2(6)
4 ,TEST_COL1 NUMBER
5 ,TEST_COL2 NUMBER
6 )
7 PARTITION BY RANGE (YYYYMM)
8 (
9 PARTITION YYYYMM_201501 VALUES LESS THAN ('201502') TABLESPACE USERS,
10 PARTITION YYYYMM_201502 VALUES LESS THAN ('201503') TABLESPACE USERS,
11 PARTITION YYYYMM_201503 VALUES LESS THAN ('201504') TABLESPACE USERS,
12 PARTITION YYYYMM_201504 VALUES LESS THAN ('201505') TABLESPACE USERS,
13 PARTITION YYYYMM_201505 VALUES LESS THAN ('201506') TABLESPACE USERS,
14 PARTITION YYYYMM_201506 VALUES LESS THAN ('201507') TABLESPACE USERS,
15 PARTITION YYYYMM_201507 VALUES LESS THAN ('201508') TABLESPACE USERS,
16 PARTITION YYYYMM_201508 VALUES LESS THAN ('201509') TABLESPACE USERS,
17 PARTITION YYYYMM_201509 VALUES LESS THAN ('201510') TABLESPACE USERS,
18 PARTITION YYYYMM_201510 VALUES LESS THAN ('201511') TABLESPACE USERS,
19 PARTITION YYYYMM_201511 VALUES LESS THAN ('201512') TABLESPACE USERS,
20 PARTITION YYYYMM_201512 VALUES LESS THAN ('201513') TABLESPACE USERS
21 );
Table created.
Elapsed: 00:00:00.13
SQL> CREATE INDEX PARTITION_TEST_PK ON PARTITION_TEST(YYYYMM, TEST_COL1) LOCAL;
Index created.
Elapsed: 00:00:00.05
SQL> CREATE INDEX PARTITION_TEST_IDX01 ON PARTITION_TEST(TEST_COL2);
Index created.
Elapsed: 00:00:00.01
SQL> ALTER TABLE PARTITION_TEST
2 ADD CONSTRAINT PARTITION_TEST_PK PRIMARY KEY(YYYYMM, TEST_COL1)
3 USING INDEX LOCAL;
Table altered.
Elapsed: 00:00:00.12
SQL> INSERT INTO PARTITION_TEST SELECT '201501', LEVEL, MOD(LEVEL, 3) FROM DUAL CONNECT BY LEVEL <= 1000;
1000 rows created.
Elapsed: 00:00:00.05
SQL> INSERT INTO PARTITION_TEST SELECT '201502', LEVEL, MOD(LEVEL, 3) FROM DUAL CONNECT BY LEVEL <= 1000;
1000 rows created.
Elapsed: 00:00:00.04
SQL> INSERT INTO PARTITION_TEST SELECT '201503', LEVEL, MOD(LEVEL, 3) FROM DUAL CONNECT BY LEVEL <= 1000;
1000 rows created.
Elapsed: 00:00:00.05
SQL> INSERT INTO PARTITION_TEST SELECT '201504', LEVEL, MOD(LEVEL, 3) FROM DUAL CONNECT BY LEVEL <= 1000;
1000 rows created.
Elapsed: 00:00:00.05
SQL> INSERT INTO PARTITION_TEST SELECT '201505', LEVEL, MOD(LEVEL, 3) FROM DUAL CONNECT BY LEVEL <= 1000;
1000 rows created.
Elapsed: 00:00:00.03
SQL> INSERT INTO PARTITION_TEST SELECT '201506', LEVEL, MOD(LEVEL, 3) FROM DUAL CONNECT BY LEVEL <= 1000;
1000 rows created.
Elapsed: 00:00:00.04
SQL> INSERT INTO PARTITION_TEST SELECT '201507', LEVEL, MOD(LEVEL, 3) FROM DUAL CONNECT BY LEVEL <= 1000;
1000 rows created.
Elapsed: 00:00:00.04
SQL> INSERT INTO PARTITION_TEST SELECT '201508', LEVEL, MOD(LEVEL, 3) FROM DUAL CONNECT BY LEVEL <= 1000;
1000 rows created.
Elapsed: 00:00:00.04
SQL> INSERT INTO PARTITION_TEST SELECT '201509', LEVEL, MOD(LEVEL, 3) FROM DUAL CONNECT BY LEVEL <= 1000;
1000 rows created.
Elapsed: 00:00:00.04
SQL> INSERT INTO PARTITION_TEST SELECT '201510', LEVEL, MOD(LEVEL, 3) FROM DUAL CONNECT BY LEVEL <= 1000;
1000 rows created.
Elapsed: 00:00:00.04
SQL> INSERT INTO PARTITION_TEST SELECT '201511', LEVEL, MOD(LEVEL, 3) FROM DUAL CONNECT BY LEVEL <= 1000;
1000 rows created.
Elapsed: 00:00:00.04
QL> INSERT INTO PARTITION_TEST SELECT '201512', LEVEL, MOD(LEVEL, 3) FROM DUAL CONNECT BY LEVEL <= 1000;
1000 rows created.
Elapsed: 00:00:00.04
QL> COMMIT;
Commit complete.
Elapsed: 00:00:00.01
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'DB_MONITORING', TABNAME => 'PARTITION_TEST', CASCADE => TRUE)
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.46
SQL> SELECT TABLE_NAME
2 ,INDEX_NAME
3 ,INDEX_TYPE
4 ,STATUS
5 ,PARTITIONED
6 ,DEGREE
7 ,GLOBAL_STATS
8 FROM DBA_INDEXES
9 WHERE INDEX_NAME LIKE 'PARTITION%';
TABLE_NAME INDEX_NAME INDEX_TYPE STATUS PAR DEGREE GLO
------------------ --------------------- ------------ -------- --- ------- ---
PARTITION_TEST PARTITION_TEST_PK NORMAL N/A YES 1 YES
PARTITION_TEST PARTITION_TEST_IDX01 NORMAL VALID NO 1 YES
2 rows selected.
Elapsed: 00:00:00.14
SQL> SELECT INDEX_NAME
2 ,PARTITION_NAME
3 ,HIGH_VALUE
4 ,STATUS
5 ,GLOBAL_STATS
6 FROM DBA_IND_PARTITIONS
7 WHERE INDEX_NAME LIKE 'PARTITION%';
INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS GLO
------------------- --------------- ------------ -------- ---
PARTITION_TEST_PK YYYYMM_201501 '201502' USABLE YES
PARTITION_TEST_PK YYYYMM_201502 '201503' USABLE YES
PARTITION_TEST_PK YYYYMM_201503 '201504' USABLE YES
PARTITION_TEST_PK YYYYMM_201504 '201505' USABLE YES
PARTITION_TEST_PK YYYYMM_201505 '201506' USABLE YES
PARTITION_TEST_PK YYYYMM_201506 '201507' USABLE YES
PARTITION_TEST_PK YYYYMM_201507 '201508' USABLE YES
PARTITION_TEST_PK YYYYMM_201508 '201509' USABLE YES
PARTITION_TEST_PK YYYYMM_201509 '201510' USABLE YES
PARTITION_TEST_PK YYYYMM_201510 '201511' USABLE YES
PARTITION_TEST_PK YYYYMM_201511 '201512' USABLE YES
PARTITION_TEST_PK YYYYMM_201512 '201513' USABLE YES
12 rows selected.
Elapsed: 00:00:00.24
SQL> SELECT TABLE_NAME
2 ,STATUS
3 ,NUM_ROWS
4 ,DEGREE
5 ,PARTITIONED
6 ,GLOBAL_STATS
7 FROM DBA_TABLES
8 WHERE TABLE_NAME = 'PARTITION_TEST';
TABLE_NAME STATUS NUM_ROWS DEGREE PAR GLO
--------------- -------- ---------- ------------ --- ---
PARTITION_TEST VALID 12000 1 YES YES
1 row selected.
Elapsed: 00:00:00.02
SQL> SELECT TABLE_NAME
2 ,PARTITION_NAME
3 ,HIGH_VALUE
4 ,TABLESPACE_NAME
5 ,NUM_ROWS
6 ,BLOCKS
7 ,GLOBAL_STATS
8 FROM DBA_TAB_PARTITIONS
9 WHERE TABLE_NAME = 'PARTITION_TEST'
10 ORDER BY PARTITION_NAME;
TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME NUM_ROWS BLOCKS GLO
---------------- --------------- ---------- ---------------- ---------- ---------- ---
PARTITION_TEST YYYYMM_201501 '201502' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201502 '201503' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201503 '201504' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201504 '201505' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201505 '201506' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201506 '201507' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201507 '201508' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201508 '201509' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201509 '201510' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201510 '201511' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201511 '201512' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201512 '201513' USERS 1000 46 YES
12 rows selected.
Elapsed: 00:00:00.11
2. Partition 추가
Partition을 추가하는 작업을 수행한다. 일반적인 수행 방식이다.
추가한 Partition의 Local Index가 생성되었지만 통계수집을 하지 않아 Global Index에 통계 정보가 없는 것을 확인할 수 있다.
SQL> ALTER TABLE PARTITION_TEST ADD PARTITION YYYYMM_201601 VALUES LESS THAN ('201602') TABLESPACE USERS;
Table altered.
Elapsed: 00:00:00.08
SQL> @tt
TABLE_NAME INDEX_NAME INDEX_TYPE STATUS PAR DEGREE GLO
-------------------- ------------------------- ------------ -------- --- ------ ---
PARTITION_TEST PARTITION_TEST_PK NORMAL N/A YES 1 YES
PARTITION_TEST PARTITION_TEST_IDX01 NORMAL VALID NO 1 YES
2 rows selected.
Elapsed: 00:00:00.05
INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS GLO
------------------------- -------------------- --------------- -------- ---
PARTITION_TEST_PK YYYYMM_201501 '201502' USABLE YES
PARTITION_TEST_PK YYYYMM_201502 '201503' USABLE YES
PARTITION_TEST_PK YYYYMM_201503 '201504' USABLE YES
PARTITION_TEST_PK YYYYMM_201504 '201505' USABLE YES
PARTITION_TEST_PK YYYYMM_201505 '201506' USABLE YES
PARTITION_TEST_PK YYYYMM_201506 '201507' USABLE YES
PARTITION_TEST_PK YYYYMM_201507 '201508' USABLE YES
PARTITION_TEST_PK YYYYMM_201508 '201509' USABLE YES
PARTITION_TEST_PK YYYYMM_201509 '201510' USABLE YES
PARTITION_TEST_PK YYYYMM_201510 '201511' USABLE YES
PARTITION_TEST_PK YYYYMM_201511 '201512' USABLE YES
PARTITION_TEST_PK YYYYMM_201512 '201513' USABLE YES
PARTITION_TEST_PK YYYYMM_201601 '201602' USABLE NO
13 rows selected.
Elapsed: 00:00:00.07
TABLE_NAME STATUS NUM_ROWS DEGREE PAR GLO
-------------------- -------- ---------- ------ --- ---
PARTITION_TEST VALID 12000 1 YES YES
1 row selected.
Elapsed: 00:00:00.01
TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME NUM_ROWS BLOCKS GLO
-------------------- -------------------- --------------- --------------- ---------- ---------- ---
PARTITION_TEST YYYYMM_201501 '201502' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201502 '201503' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201503 '201504' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201504 '201505' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201505 '201506' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201506 '201507' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201507 '201508' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201508 '201509' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201509 '201510' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201510 '201511' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201511 '201512' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201512 '201513' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201601 '201602' USERS NO
13 rows selected.
Elapsed: 00:00:00.05
3. Partition을 중간 삽입형태로 추가 테스트
ORA-14074 에러가 발생하며 추가할 수 없다.
Partition 추가는 최종 파티션의 상위로만 추가가 가능하다.
SQL> ALTER TABLE PARTITION_TEST ADD PARTITION YYYYMM_201412 VALUES LESS THAN ('201413') TABLESPACE USERS;
ALTER TABLE PARTITION_TEST ADD PARTITION YYYYMM_201412 VALUES LESS THAN ('201413') TABLESPACE USERS
*
ERROR at line 1:
ORA-14074: partition bound must collate higher than that of the last partition
Elapsed: 00:00:00.00
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'DB_MONITORING', TABNAME => 'PARTITION_TEST', CASCADE => TRUE)
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.71
4. Partition Truncate 테스트
Partition 작업 중 Truncate 작업에 대해 테스트를 진행해 본다.
Local Partition Index의 경우 문제가 없지만 Global Index의 경우 status가 Unusable로 변경되며 관련 SQL 문장이 Full Table Scan으로 변경된다.
또한 해당 인덱스를 타도록 힌트를 사용한 문장의 경우 해당 인덱스의 상태가 Unusable로 사용할 수 없다고 에러가 발생하게 된다.
Global Index의 경우 Rebuild를 해야만 한다. 이때 가급적 기존에 사용하던 통계 정보를 먼저 백업을 해 두고 작업 후 백업한 통계로
import 해 주는 것이 기존 SQL 문장의 성능에 변화를 줄일 수 있다. 통계 값이 크게 영향이 없다면 어차피 Index Rebuild 할 때 새로 수집되기
때문에 그냥 두어도 무방하다.
SQL> ALTER TABLE PARTITION_TEST TRUNCATE PARTITION YYYYMM_201510; Table truncated. Elapsed: 00:00:00.15 SQL> @tt TABLE_NAME INDEX_NAME INDEX_TYPE STATUS PAR DEGREE GLO -------------------- ------------------------- ------------ -------- --- ------ --- PARTITION_TEST PARTITION_TEST_PK NORMAL N/A YES 1 YES PARTITION_TEST PARTITION_TEST_IDX01 NORMAL UNUSABLE NO 1 YES 2 rows selected. Elapsed: 00:00:00.05 INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS GLO ------------------------- -------------------- --------------- -------- --- PARTITION_TEST_PK YYYYMM_201501 '201502' USABLE YES PARTITION_TEST_PK YYYYMM_201502 '201503' USABLE YES PARTITION_TEST_PK YYYYMM_201503 '201504' USABLE YES PARTITION_TEST_PK YYYYMM_201504 '201505' USABLE YES PARTITION_TEST_PK YYYYMM_201505 '201506' USABLE YES PARTITION_TEST_PK YYYYMM_201506 '201507' USABLE YES PARTITION_TEST_PK YYYYMM_201507 '201508' USABLE YES PARTITION_TEST_PK YYYYMM_201508 '201509' USABLE YES PARTITION_TEST_PK YYYYMM_201509 '201510' USABLE YES PARTITION_TEST_PK YYYYMM_201510 '201511' USABLE YES PARTITION_TEST_PK YYYYMM_201511 '201512' USABLE YES PARTITION_TEST_PK YYYYMM_201512 '201513' USABLE YES PARTITION_TEST_PK YYYYMM_201601 '201602' USABLE YES 13 rows selected. Elapsed: 00:00:00.07 TABLE_NAME STATUS NUM_ROWS DEGREE PAR GLO -------------------- -------- ---------- ------ --- --- PARTITION_TEST VALID 12000 1 YES YES 1 row selected. Elapsed: 00:00:00.01 TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME NUM_ROWS BLOCKS GLO -------------------- -------------------- --------------- --------------- ---------- ---------- --- PARTITION_TEST YYYYMM_201501 '201502' USERS 1000 46 YES PARTITION_TEST YYYYMM_201502 '201503' USERS 1000 46 YES PARTITION_TEST YYYYMM_201503 '201504' USERS 1000 46 YES PARTITION_TEST YYYYMM_201504 '201505' USERS 1000 46 YES PARTITION_TEST YYYYMM_201505 '201506' USERS 1000 46 YES PARTITION_TEST YYYYMM_201506 '201507' USERS 1000 46 YES PARTITION_TEST YYYYMM_201507 '201508' USERS 1000 46 YES PARTITION_TEST YYYYMM_201508 '201509' USERS 1000 46 YES PARTITION_TEST YYYYMM_201509 '201510' USERS 1000 46 YES PARTITION_TEST YYYYMM_201510 '201511' USERS 1000 46 YES PARTITION_TEST YYYYMM_201511 '201512' USERS 1000 46 YES PARTITION_TEST YYYYMM_201512 '201513' USERS 1000 46 YES PARTITION_TEST YYYYMM_201601 '201602' USERS 0 0 YES 13 rows selected. Elapsed: 00:00:00.04 SQL> SELECT /*+ INDEX(A PARTITION_TEST_IDX01) */ * 2 FROM PARTITION_TEST A 3 WHERE TEST_COL2 = 0; SELECT /*+ INDEX(A PARTITION_TEST_IDX01) */ * * ERROR at line 1: ORA-01502: index 'DB_MONITORING.PARTITION_TEST_IDX01' or partition of such index is in unusable state Elapsed: 00:00:00.00 SQL> ALTER INDEX PARTITION_TEST_IDX01 REBUILD; Index altered. Elapsed: 00:00:00.05 SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'DB_MONITORING', TABNAME => 'PARTITION_TEST', CASCADE => TRUE) PL/SQL procedure successfully completed. Elapsed: 00:00:01.05 SQL> @tt TABLE_NAME INDEX_NAME INDEX_TYPE STATUS PAR DEGREE GLO -------------------- ------------------------- ------------ -------- --- ------ --- PARTITION_TEST PARTITION_TEST_PK NORMAL N/A YES 1 YES PARTITION_TEST PARTITION_TEST_IDX01 NORMAL VALID NO 1 YES 2 rows selected. Elapsed: 00:00:00.05 INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS GLO ------------------------- -------------------- --------------- -------- --- PARTITION_TEST_PK YYYYMM_201501 '201502' USABLE YES PARTITION_TEST_PK YYYYMM_201502 '201503' USABLE YES PARTITION_TEST_PK YYYYMM_201503 '201504' USABLE YES PARTITION_TEST_PK YYYYMM_201504 '201505' USABLE YES PARTITION_TEST_PK YYYYMM_201505 '201506' USABLE YES PARTITION_TEST_PK YYYYMM_201506 '201507' USABLE YES PARTITION_TEST_PK YYYYMM_201507 '201508' USABLE YES PARTITION_TEST_PK YYYYMM_201508 '201509' USABLE YES PARTITION_TEST_PK YYYYMM_201509 '201510' USABLE YES PARTITION_TEST_PK YYYYMM_201510 '201511' USABLE YES PARTITION_TEST_PK YYYYMM_201511 '201512' USABLE YES PARTITION_TEST_PK YYYYMM_201512 '201513' USABLE YES PARTITION_TEST_PK YYYYMM_201601 '201602' USABLE YES 13 rows selected. Elapsed: 00:00:00.07 TABLE_NAME STATUS NUM_ROWS DEGREE PAR GLO -------------------- -------- ---------- ------ --- --- PARTITION_TEST VALID 11000 1 YES YES 1 row selected. Elapsed: 00:00:00.01 TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME NUM_ROWS BLOCKS GLO -------------------- -------------------- --------------- --------------- ---------- ---------- --- PARTITION_TEST YYYYMM_201501 '201502' USERS 1000 46 YES PARTITION_TEST YYYYMM_201502 '201503' USERS 1000 46 YES PARTITION_TEST YYYYMM_201503 '201504' USERS 1000 46 YES PARTITION_TEST YYYYMM_201504 '201505' USERS 1000 46 YES PARTITION_TEST YYYYMM_201505 '201506' USERS 1000 46 YES PARTITION_TEST YYYYMM_201506 '201507' USERS 1000 46 YES PARTITION_TEST YYYYMM_201507 '201508' USERS 1000 46 YES PARTITION_TEST YYYYMM_201508 '201509' USERS 1000 46 YES PARTITION_TEST YYYYMM_201509 '201510' USERS 1000 46 YES PARTITION_TEST YYYYMM_201510 '201511' USERS 0 0 YES PARTITION_TEST YYYYMM_201511 '201512' USERS 1000 46 YES PARTITION_TEST YYYYMM_201512 '201513' USERS 1000 46 YES PARTITION_TEST YYYYMM_201601 '201602' USERS 0 0 YES 13 rows selected. Elapsed: 00:00:00.04 SQL> INSERT INTO PARTITION_TEST SELECT '201510', LEVEL, MOD(LEVEL, 3) FROM DUAL CONNECT BY LEVEL <= 1000; 1000 rows created. Elapsed: 00:00:00.07 SQL> COMMIT; Commit complete. Elapsed: 00:00:00.00 SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'DB_MONITORING', TABNAME => 'PARTITION_TEST', CASCADE => TRUE) PL/SQL procedure successfully completed. Elapsed: 00:00:01.17
5. Partition Drop 테스트
Partition Truncate 테스트 때와 동일하다고 보면 된다.
SQL> ALTER TABLE PARTITION_TEST DROP PARTITION YYYYMM_201510; Table altered. Elapsed: 00:00:00.07 SQL> @tt TABLE_NAME INDEX_NAME INDEX_TYPE STATUS PAR DEGREE GLO -------------------- ------------------------- ------------ -------- --- ------ --- PARTITION_TEST PARTITION_TEST_PK NORMAL N/A YES 1 YES PARTITION_TEST PARTITION_TEST_IDX01 NORMAL UNUSABLE NO 1 YES 2 rows selected. Elapsed: 00:00:00.05 INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS GLO ------------------------- -------------------- --------------- -------- --- PARTITION_TEST_PK YYYYMM_201501 '201502' USABLE YES PARTITION_TEST_PK YYYYMM_201502 '201503' USABLE YES PARTITION_TEST_PK YYYYMM_201503 '201504' USABLE YES PARTITION_TEST_PK YYYYMM_201504 '201505' USABLE YES PARTITION_TEST_PK YYYYMM_201505 '201506' USABLE YES PARTITION_TEST_PK YYYYMM_201506 '201507' USABLE YES PARTITION_TEST_PK YYYYMM_201507 '201508' USABLE YES PARTITION_TEST_PK YYYYMM_201508 '201509' USABLE YES PARTITION_TEST_PK YYYYMM_201509 '201510' USABLE YES PARTITION_TEST_PK YYYYMM_201511 '201512' USABLE YES PARTITION_TEST_PK YYYYMM_201512 '201513' USABLE YES PARTITION_TEST_PK YYYYMM_201601 '201602' USABLE YES 12 rows selected. Elapsed: 00:00:00.06 TABLE_NAME STATUS NUM_ROWS DEGREE PAR GLO -------------------- -------- ---------- ------ --- --- PARTITION_TEST VALID 12000 1 YES YES 1 row selected. Elapsed: 00:00:00.02 TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME NUM_ROWS BLOCKS GLO -------------------- -------------------- --------------- --------------- ---------- ---------- --- PARTITION_TEST YYYYMM_201501 '201502' USERS 1000 46 YES PARTITION_TEST YYYYMM_201502 '201503' USERS 1000 46 YES PARTITION_TEST YYYYMM_201503 '201504' USERS 1000 46 YES PARTITION_TEST YYYYMM_201504 '201505' USERS 1000 46 YES PARTITION_TEST YYYYMM_201505 '201506' USERS 1000 46 YES PARTITION_TEST YYYYMM_201506 '201507' USERS 1000 46 YES PARTITION_TEST YYYYMM_201507 '201508' USERS 1000 46 YES PARTITION_TEST YYYYMM_201508 '201509' USERS 1000 46 YES PARTITION_TEST YYYYMM_201509 '201510' USERS 1000 46 YES PARTITION_TEST YYYYMM_201511 '201512' USERS 1000 46 YES PARTITION_TEST YYYYMM_201512 '201513' USERS 1000 46 YES PARTITION_TEST YYYYMM_201601 '201602' USERS 0 0 YES 12 rows selected. Elapsed: 00:00:00.04 SQL> ALTER INDEX PARTITION_TEST_IDX01 REBUILD; Index altered. Elapsed: 00:00:00.05 SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'DB_MONITORING', TABNAME => 'PARTITION_TEST', CASCADE => TRUE) PL/SQL procedure successfully completed. Elapsed: 00:00:01.07 SQL> @tt TABLE_NAME INDEX_NAME INDEX_TYPE STATUS PAR DEGREE GLO -------------------- ------------------------- ------------ -------- --- ------ --- PARTITION_TEST PARTITION_TEST_PK NORMAL N/A YES 1 YES PARTITION_TEST PARTITION_TEST_IDX01 NORMAL VALID NO 1 YES 2 rows selected. Elapsed: 00:00:00.06 INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS GLO ------------------------- -------------------- --------------- -------- --- PARTITION_TEST_PK YYYYMM_201501 '201502' USABLE YES PARTITION_TEST_PK YYYYMM_201502 '201503' USABLE YES PARTITION_TEST_PK YYYYMM_201503 '201504' USABLE YES PARTITION_TEST_PK YYYYMM_201504 '201505' USABLE YES PARTITION_TEST_PK YYYYMM_201505 '201506' USABLE YES PARTITION_TEST_PK YYYYMM_201506 '201507' USABLE YES PARTITION_TEST_PK YYYYMM_201507 '201508' USABLE YES PARTITION_TEST_PK YYYYMM_201508 '201509' USABLE YES PARTITION_TEST_PK YYYYMM_201509 '201510' USABLE YES PARTITION_TEST_PK YYYYMM_201511 '201512' USABLE YES PARTITION_TEST_PK YYYYMM_201512 '201513' USABLE YES PARTITION_TEST_PK YYYYMM_201601 '201602' USABLE YES 12 rows selected. Elapsed: 00:00:00.06 TABLE_NAME STATUS NUM_ROWS DEGREE PAR GLO -------------------- -------- ---------- ------ --- --- PARTITION_TEST VALID 11000 1 YES YES 1 row selected. Elapsed: 00:00:00.02 TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME NUM_ROWS BLOCKS GLO -------------------- -------------------- --------------- --------------- ---------- ---------- --- PARTITION_TEST YYYYMM_201501 '201502' USERS 1000 46 YES PARTITION_TEST YYYYMM_201502 '201503' USERS 1000 46 YES PARTITION_TEST YYYYMM_201503 '201504' USERS 1000 46 YES PARTITION_TEST YYYYMM_201504 '201505' USERS 1000 46 YES PARTITION_TEST YYYYMM_201505 '201506' USERS 1000 46 YES PARTITION_TEST YYYYMM_201506 '201507' USERS 1000 46 YES PARTITION_TEST YYYYMM_201507 '201508' USERS 1000 46 YES PARTITION_TEST YYYYMM_201508 '201509' USERS 1000 46 YES PARTITION_TEST YYYYMM_201509 '201510' USERS 1000 46 YES PARTITION_TEST YYYYMM_201511 '201512' USERS 1000 46 YES PARTITION_TEST YYYYMM_201512 '201513' USERS 1000 46 YES PARTITION_TEST YYYYMM_201601 '201602' USERS 0 0 YES 12 rows selected. Elapsed: 00:00:00.03
6. Partition Split 테스트(Wide 분포의 데이터를 지닌 Partition이 split 대상인 경우)
Partition을 Split 하는데 쪼개지는 양쪽 파티션으로 데이터들이 나누어서 들어가는 경우를 이야기 한다.
이 경우 쪼개져서 생성된 2개의 Partition의 Local Index 2개 모두 status가 Unusable로 변경된다. 또한 Global Index 역시 Unusable 상태가 된다.
역시 Unusable Index 모두를 Rebuild 해 줘야 한다.
SQL> SELECT COUNT( *)
2 FROM PARTITION_TEST
3 WHERE YYYYMM = '201510';
COUNT(*)
----------
0
1 row selected.
Elapsed: 00:00:00.00
SQL> INSERT INTO PARTITION_TEST SELECT '201510', LEVEL, MOD(LEVEL, 3) FROM DUAL CONNECT BY LEVEL <= 1000;
1000 rows created.
Elapsed: 00:00:00.04
SQL> COMMIT;
Commit complete.
Elapsed: 00:00:00.00
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'DB_MONITORING', TABNAME => 'PARTITION_TEST', CASCADE => TRUE)
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.98
SQL> @tt
TABLE_NAME INDEX_NAME INDEX_TYPE STATUS PAR DEGREE GLO
-------------------- ------------------------- ------------ -------- --- ------ ---
PARTITION_TEST PARTITION_TEST_PK NORMAL N/A YES 1 YES
PARTITION_TEST PARTITION_TEST_IDX01 NORMAL VALID NO 1 YES
2 rows selected.
Elapsed: 00:00:00.05
INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS GLO
------------------------- -------------------- --------------- -------- ---
PARTITION_TEST_PK YYYYMM_201501 '201502' USABLE YES
PARTITION_TEST_PK YYYYMM_201502 '201503' USABLE YES
PARTITION_TEST_PK YYYYMM_201503 '201504' USABLE YES
PARTITION_TEST_PK YYYYMM_201504 '201505' USABLE YES
PARTITION_TEST_PK YYYYMM_201505 '201506' USABLE YES
PARTITION_TEST_PK YYYYMM_201506 '201507' USABLE YES
PARTITION_TEST_PK YYYYMM_201507 '201508' USABLE YES
PARTITION_TEST_PK YYYYMM_201508 '201509' USABLE YES
PARTITION_TEST_PK YYYYMM_201509 '201510' USABLE YES
PARTITION_TEST_PK YYYYMM_201511 '201512' USABLE YES
PARTITION_TEST_PK YYYYMM_201512 '201513' USABLE YES
PARTITION_TEST_PK YYYYMM_201601 '201602' USABLE YES
12 rows selected.
Elapsed: 00:00:00.07
TABLE_NAME STATUS NUM_ROWS DEGREE PAR GLO
-------------------- -------- ---------- ------ --- ---
PARTITION_TEST VALID 12000 1 YES YES
1 row selected.
Elapsed: 00:00:00.01
TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME NUM_ROWS BLOCKS GLO
-------------------- -------------------- --------------- --------------- ---------- ---------- ---
PARTITION_TEST YYYYMM_201501 '201502' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201502 '201503' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201503 '201504' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201504 '201505' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201505 '201506' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201506 '201507' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201507 '201508' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201508 '201509' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201509 '201510' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201511 '201512' USERS 2000 46 YES
PARTITION_TEST YYYYMM_201512 '201513' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201601 '201602' USERS 0 0 YES
12 rows selected.
Elapsed: 00:00:00.04
SQL> ALTER TABLE PARTITION_TEST
2 SPLIT PARTITION YYYYMM_201511 AT ('201511') INTO(PARTITION YYYYMM_201510 TABLESPACE USERS, PARTITION YYYYMM_201511 TABLESPACE USERS);
Table altered.
Elapsed: 00:00:00.41
SQL> @tt
TABLE_NAME INDEX_NAME INDEX_TYPE STATUS PAR DEGREE GLO
-------------------- ------------------------- ------------ -------- --- ------ ---
PARTITION_TEST PARTITION_TEST_PK NORMAL N/A YES 1 YES
PARTITION_TEST PARTITION_TEST_IDX01 NORMAL UNUSABLE NO 1 YES
2 rows selected.
Elapsed: 00:00:00.09
INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS GLO
------------------------- -------------------- --------------- -------- ---
PARTITION_TEST_PK YYYYMM_201501 '201502' USABLE YES
PARTITION_TEST_PK YYYYMM_201502 '201503' USABLE YES
PARTITION_TEST_PK YYYYMM_201503 '201504' USABLE YES
PARTITION_TEST_PK YYYYMM_201504 '201505' USABLE YES
PARTITION_TEST_PK YYYYMM_201505 '201506' USABLE YES
PARTITION_TEST_PK YYYYMM_201506 '201507' USABLE YES
PARTITION_TEST_PK YYYYMM_201507 '201508' USABLE YES
PARTITION_TEST_PK YYYYMM_201508 '201509' USABLE YES
PARTITION_TEST_PK YYYYMM_201509 '201510' USABLE YES
PARTITION_TEST_PK YYYYMM_201510 '201511' UNUSABLE NO
PARTITION_TEST_PK YYYYMM_201511 '201512' UNUSABLE NO
PARTITION_TEST_PK YYYYMM_201512 '201513' USABLE YES
PARTITION_TEST_PK YYYYMM_201601 '201602' USABLE YES
13 rows selected.
Elapsed: 00:00:00.07
TABLE_NAME STATUS NUM_ROWS DEGREE PAR GLO
-------------------- -------- ---------- ------ --- ---
PARTITION_TEST VALID 12000 1 YES YES
1 row selected.
Elapsed: 00:00:00.06
TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME NUM_ROWS BLOCKS GLO
-------------------- -------------------- --------------- --------------- ---------- ---------- ---
PARTITION_TEST YYYYMM_201501 '201502' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201502 '201503' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201503 '201504' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201504 '201505' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201505 '201506' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201506 '201507' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201507 '201508' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201508 '201509' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201509 '201510' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201510 '201511' USERS NO
PARTITION_TEST YYYYMM_201511 '201512' USERS NO
PARTITION_TEST YYYYMM_201512 '201513' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201601 '201602' USERS 0 0 YES
13 rows selected.
Elapsed: 00:00:00.11
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'DB_MONITORING', TABNAME => 'PARTITION_TEST', CASCADE => TRUE)
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.33
SQL> @tt
TABLE_NAME INDEX_NAME INDEX_TYPE STATUS PAR DEGREE GLO
-------------------- ------------------------- ------------ -------- --- ------ ---
PARTITION_TEST PARTITION_TEST_PK NORMAL N/A YES 1 YES
PARTITION_TEST PARTITION_TEST_IDX01 NORMAL UNUSABLE NO 1 YES
2 rows selected.
Elapsed: 00:00:00.06
INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS GLO
------------------------- -------------------- --------------- -------- ---
PARTITION_TEST_PK YYYYMM_201501 '201502' USABLE YES
PARTITION_TEST_PK YYYYMM_201502 '201503' USABLE YES
PARTITION_TEST_PK YYYYMM_201503 '201504' USABLE YES
PARTITION_TEST_PK YYYYMM_201504 '201505' USABLE YES
PARTITION_TEST_PK YYYYMM_201505 '201506' USABLE YES
PARTITION_TEST_PK YYYYMM_201506 '201507' USABLE YES
PARTITION_TEST_PK YYYYMM_201507 '201508' USABLE YES
PARTITION_TEST_PK YYYYMM_201508 '201509' USABLE YES
PARTITION_TEST_PK YYYYMM_201509 '201510' USABLE YES
PARTITION_TEST_PK YYYYMM_201510 '201511' UNUSABLE YES
PARTITION_TEST_PK YYYYMM_201511 '201512' UNUSABLE YES
PARTITION_TEST_PK YYYYMM_201512 '201513' USABLE YES
PARTITION_TEST_PK YYYYMM_201601 '201602' USABLE YES
13 rows selected.
Elapsed: 00:00:00.07
TABLE_NAME STATUS NUM_ROWS DEGREE PAR GLO
-------------------- -------- ---------- ------ --- ---
PARTITION_TEST VALID 12000 1 YES YES
1 row selected.
Elapsed: 00:00:00.01
TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME NUM_ROWS BLOCKS GLO
-------------------- -------------------- --------------- --------------- ---------- ---------- ---
PARTITION_TEST YYYYMM_201501 '201502' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201502 '201503' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201503 '201504' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201504 '201505' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201505 '201506' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201506 '201507' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201507 '201508' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201508 '201509' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201509 '201510' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201510 '201511' USERS 1000 21 YES
PARTITION_TEST YYYYMM_201511 '201512' USERS 1000 21 YES
PARTITION_TEST YYYYMM_201512 '201513' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201601 '201602' USERS 0 0 YES
13 rows selected.
Elapsed: 00:00:00.04
SQL> ALTER INDEX PARTITION_TEST_IDX01 REBUILD;
Index altered.
Elapsed: 00:00:00.05
SQL> ALTER INDEX PARTITION_TEST_PK REBUILD PARTITION YYYYMM_201510;
Index altered.
Elapsed: 00:00:00.02
SQL> ALTER INDEX PARTITION_TEST_PK REBUILD PARTITION YYYYMM_201511;
Index altered.
Elapsed: 00:00:00.02
SQL> @tt
TABLE_NAME INDEX_NAME INDEX_TYPE STATUS PAR DEGREE GLO
-------------------- ------------------------- ------------ -------- --- ------ ---
PARTITION_TEST PARTITION_TEST_PK NORMAL N/A YES 1 YES
PARTITION_TEST PARTITION_TEST_IDX01 NORMAL VALID NO 1 NO
2 rows selected.
Elapsed: 00:00:00.05
INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS GLO
------------------------- -------------------- --------------- -------- ---
PARTITION_TEST_PK YYYYMM_201501 '201502' USABLE YES
PARTITION_TEST_PK YYYYMM_201502 '201503' USABLE YES
PARTITION_TEST_PK YYYYMM_201503 '201504' USABLE YES
PARTITION_TEST_PK YYYYMM_201504 '201505' USABLE YES
PARTITION_TEST_PK YYYYMM_201505 '201506' USABLE YES
PARTITION_TEST_PK YYYYMM_201506 '201507' USABLE YES
PARTITION_TEST_PK YYYYMM_201507 '201508' USABLE YES
PARTITION_TEST_PK YYYYMM_201508 '201509' USABLE YES
PARTITION_TEST_PK YYYYMM_201509 '201510' USABLE YES
PARTITION_TEST_PK YYYYMM_201510 '201511' USABLE YES
PARTITION_TEST_PK YYYYMM_201511 '201512' USABLE YES
PARTITION_TEST_PK YYYYMM_201512 '201513' USABLE YES
PARTITION_TEST_PK YYYYMM_201601 '201602' USABLE YES
13 rows selected.
Elapsed: 00:00:00.07
TABLE_NAME STATUS NUM_ROWS DEGREE PAR GLO
-------------------- -------- ---------- ------ --- ---
PARTITION_TEST VALID 12000 1 YES YES
1 row selected.
Elapsed: 00:00:00.01
TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME NUM_ROWS BLOCKS GLO
-------------------- -------------------- --------------- --------------- ---------- ---------- ---
PARTITION_TEST YYYYMM_201501 '201502' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201502 '201503' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201503 '201504' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201504 '201505' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201505 '201506' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201506 '201507' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201507 '201508' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201508 '201509' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201509 '201510' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201510 '201511' USERS 1000 21 YES
PARTITION_TEST YYYYMM_201511 '201512' USERS 1000 21 YES
PARTITION_TEST YYYYMM_201512 '201513' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201601 '201602' USERS 0 0 YES
13 rows selected.
Elapsed: 00:00:00.04
7. Partition Split 테스트(split 대상 Partition에 우편향 데이터만 들어 있어 split 후 우측 파티션에만 데이터가 들어가는 경우)
데이터의 분포가 한쪽으로 치우쳐 있어 Partition Split를 했지만 한쪽 Partition으로만 들어갈 경우를 이야기 한다.
이 경우 Global Index의 상태는 변화가 없으며 Local Index도 변화가 없다. 단지 쪼개겨 새로 생긴 Partition중 데이터가 들어있지 않은 곳의
통계정보가 없을 뿐이다.
이 결과는 Split 되기 전 파티션의 정보가 우편향 데이터와 함께 우측 또는 위측 Partition으로 모두 이동되었음을 의미한다.
아래 테스트 결과를 통해 확인할 수 있다.
SQL> ALTER TABLE PARTITION_TEST DROP PARTITION YYYYMM_201510;
Table altered.
Elapsed: 00:00:00.05
SQL> SELECT COUNT( *)
2 FROM PARTITION_TEST
3 WHERE YYYYMM = '201510';
COUNT(*)
----------
0
1 row selected.
Elapsed: 00:00:00.00
SQL> @tt
TABLE_NAME INDEX_NAME INDEX_TYPE STATUS PAR DEGREE GLO
-------------------- ------------------------- ------------ -------- --- ------ ---
PARTITION_TEST PARTITION_TEST_PK NORMAL N/A YES 1 YES
PARTITION_TEST PARTITION_TEST_IDX01 NORMAL UNUSABLE NO 1 NO
2 rows selected.
Elapsed: 00:00:00.05
INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS GLO
------------------------- -------------------- --------------- -------- ---
PARTITION_TEST_PK YYYYMM_201501 '201502' USABLE YES
PARTITION_TEST_PK YYYYMM_201502 '201503' USABLE YES
PARTITION_TEST_PK YYYYMM_201503 '201504' USABLE YES
PARTITION_TEST_PK YYYYMM_201504 '201505' USABLE YES
PARTITION_TEST_PK YYYYMM_201505 '201506' USABLE YES
PARTITION_TEST_PK YYYYMM_201506 '201507' USABLE YES
PARTITION_TEST_PK YYYYMM_201507 '201508' USABLE YES
PARTITION_TEST_PK YYYYMM_201508 '201509' USABLE YES
PARTITION_TEST_PK YYYYMM_201509 '201510' USABLE YES
PARTITION_TEST_PK YYYYMM_201511 '201512' USABLE YES
PARTITION_TEST_PK YYYYMM_201512 '201513' USABLE YES
PARTITION_TEST_PK YYYYMM_201601 '201602' USABLE YES
12 rows selected.
Elapsed: 00:00:00.07
TABLE_NAME STATUS NUM_ROWS DEGREE PAR GLO
-------------------- -------- ---------- ------ --- ---
PARTITION_TEST VALID 12000 1 YES YES
1 row selected.
Elapsed: 00:00:00.01
TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME NUM_ROWS BLOCKS GLO
-------------------- -------------------- --------------- --------------- ---------- ---------- ---
PARTITION_TEST YYYYMM_201501 '201502' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201502 '201503' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201503 '201504' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201504 '201505' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201505 '201506' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201506 '201507' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201507 '201508' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201508 '201509' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201509 '201510' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201511 '201512' USERS 1000 21 YES
PARTITION_TEST YYYYMM_201512 '201513' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201601 '201602' USERS 0 0 YES
12 rows selected.
Elapsed: 00:00:00.03
SQL> ALTER INDEX PARTITION_TEST_IDX01 REBUILD;
Index altered.
Elapsed: 00:00:00.04
SQL> @tt
TABLE_NAME INDEX_NAME INDEX_TYPE STATUS PAR DEGREE GLO
-------------------- ------------------------- ------------ -------- --- ------ ---
PARTITION_TEST PARTITION_TEST_PK NORMAL N/A YES 1 YES
PARTITION_TEST PARTITION_TEST_IDX01 NORMAL VALID NO 1 NO
2 rows selected.
Elapsed: 00:00:00.05
INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS GLO
------------------------- -------------------- --------------- -------- ---
PARTITION_TEST_PK YYYYMM_201501 '201502' USABLE YES
PARTITION_TEST_PK YYYYMM_201502 '201503' USABLE YES
PARTITION_TEST_PK YYYYMM_201503 '201504' USABLE YES
PARTITION_TEST_PK YYYYMM_201504 '201505' USABLE YES
PARTITION_TEST_PK YYYYMM_201505 '201506' USABLE YES
PARTITION_TEST_PK YYYYMM_201506 '201507' USABLE YES
PARTITION_TEST_PK YYYYMM_201507 '201508' USABLE YES
PARTITION_TEST_PK YYYYMM_201508 '201509' USABLE YES
PARTITION_TEST_PK YYYYMM_201509 '201510' USABLE YES
PARTITION_TEST_PK YYYYMM_201511 '201512' USABLE YES
PARTITION_TEST_PK YYYYMM_201512 '201513' USABLE YES
PARTITION_TEST_PK YYYYMM_201601 '201602' USABLE YES
12 rows selected.
Elapsed: 00:00:00.07
TABLE_NAME STATUS NUM_ROWS DEGREE PAR GLO
-------------------- -------- ---------- ------ --- ---
PARTITION_TEST VALID 11000 1 YES YES
1 row selected.
Elapsed: 00:00:00.01
TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME NUM_ROWS BLOCKS GLO
-------------------- -------------------- --------------- --------------- ---------- ---------- ---
PARTITION_TEST YYYYMM_201501 '201502' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201502 '201503' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201503 '201504' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201504 '201505' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201505 '201506' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201506 '201507' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201507 '201508' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201508 '201509' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201509 '201510' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201511 '201512' USERS 1000 21 YES
PARTITION_TEST YYYYMM_201512 '201513' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201601 '201602' USERS 0 0 YES
12 rows selected.
Elapsed: 00:00:00.04
SQL> ALTER TABLE PARTITION_TEST
2 SPLIT PARTITION YYYYMM_201511 AT ('201511') INTO(PARTITION YYYYMM_201510 TABLESPACE USERS, PARTITION YYYYMM_201511 TABLESPACE USERS);
Table altered.
Elapsed: 00:00:00.03
SQL> @tt
TABLE_NAME INDEX_NAME INDEX_TYPE STATUS PAR DEGREE GLO
-------------------- ------------------------- ------------ -------- --- ------ ---
PARTITION_TEST PARTITION_TEST_PK NORMAL N/A YES 1 YES
PARTITION_TEST PARTITION_TEST_IDX01 NORMAL VALID NO 1 NO
2 rows selected.
Elapsed: 00:00:00.06
INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS GLO
------------------------- -------------------- --------------- -------- ---
PARTITION_TEST_PK YYYYMM_201501 '201502' USABLE YES
PARTITION_TEST_PK YYYYMM_201502 '201503' USABLE YES
PARTITION_TEST_PK YYYYMM_201503 '201504' USABLE YES
PARTITION_TEST_PK YYYYMM_201504 '201505' USABLE YES
PARTITION_TEST_PK YYYYMM_201505 '201506' USABLE YES
PARTITION_TEST_PK YYYYMM_201506 '201507' USABLE YES
PARTITION_TEST_PK YYYYMM_201507 '201508' USABLE YES
PARTITION_TEST_PK YYYYMM_201508 '201509' USABLE YES
PARTITION_TEST_PK YYYYMM_201509 '201510' USABLE YES
PARTITION_TEST_PK YYYYMM_201510 '201511' USABLE NO
PARTITION_TEST_PK YYYYMM_201511 '201512' USABLE YES
PARTITION_TEST_PK YYYYMM_201512 '201513' USABLE YES
PARTITION_TEST_PK YYYYMM_201601 '201602' USABLE YES
13 rows selected.
Elapsed: 00:00:00.06
TABLE_NAME STATUS NUM_ROWS DEGREE PAR GLO
-------------------- -------- ---------- ------ --- ---
PARTITION_TEST VALID 11000 1 YES YES
1 row selected.
Elapsed: 00:00:00.02
TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME NUM_ROWS BLOCKS GLO
-------------------- -------------------- --------------- --------------- ---------- ---------- ---
PARTITION_TEST YYYYMM_201501 '201502' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201502 '201503' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201503 '201504' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201504 '201505' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201505 '201506' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201506 '201507' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201507 '201508' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201508 '201509' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201509 '201510' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201510 '201511' USERS NO
PARTITION_TEST YYYYMM_201511 '201512' USERS 1000 21 YES
PARTITION_TEST YYYYMM_201512 '201513' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201601 '201602' USERS 0 0 YES
13 rows selected.
Elapsed: 00:00:00.04
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'DB_MONITORING', TABNAME => 'PARTITION_TEST', CASCADE => TRUE)
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.04
SQL> @tt
TABLE_NAME INDEX_NAME INDEX_TYPE STATUS PAR DEGREE GLO
-------------------- ------------------------- ------------ -------- --- ------ ---
PARTITION_TEST PARTITION_TEST_PK NORMAL N/A YES 1 YES
PARTITION_TEST PARTITION_TEST_IDX01 NORMAL VALID NO 1 YES
2 rows selected.
Elapsed: 00:00:00.05
INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS GLO
------------------------- -------------------- --------------- -------- ---
PARTITION_TEST_PK YYYYMM_201501 '201502' USABLE YES
PARTITION_TEST_PK YYYYMM_201502 '201503' USABLE YES
PARTITION_TEST_PK YYYYMM_201503 '201504' USABLE YES
PARTITION_TEST_PK YYYYMM_201504 '201505' USABLE YES
PARTITION_TEST_PK YYYYMM_201505 '201506' USABLE YES
PARTITION_TEST_PK YYYYMM_201506 '201507' USABLE YES
PARTITION_TEST_PK YYYYMM_201507 '201508' USABLE YES
PARTITION_TEST_PK YYYYMM_201508 '201509' USABLE YES
PARTITION_TEST_PK YYYYMM_201509 '201510' USABLE YES
PARTITION_TEST_PK YYYYMM_201510 '201511' USABLE YES
PARTITION_TEST_PK YYYYMM_201511 '201512' USABLE YES
PARTITION_TEST_PK YYYYMM_201512 '201513' USABLE YES
PARTITION_TEST_PK YYYYMM_201601 '201602' USABLE YES
13 rows selected.
Elapsed: 00:00:00.07
TABLE_NAME STATUS NUM_ROWS DEGREE PAR GLO
-------------------- -------- ---------- ------ --- ---
PARTITION_TEST VALID 11000 1 YES YES
1 row selected.
Elapsed: 00:00:00.02
TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME NUM_ROWS BLOCKS GLO
-------------------- -------------------- --------------- --------------- ---------- ---------- ---
PARTITION_TEST YYYYMM_201501 '201502' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201502 '201503' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201503 '201504' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201504 '201505' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201505 '201506' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201506 '201507' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201507 '201508' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201508 '201509' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201509 '201510' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201510 '201511' USERS 0 0 YES
PARTITION_TEST YYYYMM_201511 '201512' USERS 1000 21 YES
PARTITION_TEST YYYYMM_201512 '201513' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201601 '201602' USERS 0 0 YES
13 rows selected.
Elapsed: 00:00:00.03
8. Partition Split 테스트(split 대상 Partition에 좌편향 데이터만 들어 있어 split 후 좌측 파티션에만 데이터가 들어가는 경우)
7번의 Case와 반대라고 생각하면 된다.
SQL> ALTER TABLE PARTITION_TEST DROP PARTITION YYYYMM_201510;
Table altered.
Elapsed: 00:00:00.13
SQL> DELETE FROM PARTITION_TEST
2 WHERE YYYYMM = '201511';
1000 rows deleted.
Elapsed: 00:00:00.04
SQL> INSERT INTO PARTITION_TEST SELECT '201510', LEVEL, MOD(LEVEL, 3) FROM DUAL CONNECT BY LEVEL <= 1000;
1000 rows created.
Elapsed: 00:00:00.05
SQL> COMMIT;
Commit complete.
Elapsed: 00:00:00.00
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'DB_MONITORING', TABNAME => 'PARTITION_TEST', CASCADE => TRUE)
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.44
SQL> @tt
TABLE_NAME INDEX_NAME INDEX_TYPE STATUS PAR DEGREE GLO
-------------------- ------------------------- ------------ -------- --- ------ ---
PARTITION_TEST PARTITION_TEST_PK NORMAL N/A YES 1 YES
PARTITION_TEST PARTITION_TEST_IDX01 NORMAL VALID NO 1 YES
2 rows selected.
Elapsed: 00:00:00.10
INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS GLO
------------------------- -------------------- --------------- -------- ---
PARTITION_TEST_PK YYYYMM_201501 '201502' USABLE YES
PARTITION_TEST_PK YYYYMM_201502 '201503' USABLE YES
PARTITION_TEST_PK YYYYMM_201503 '201504' USABLE YES
PARTITION_TEST_PK YYYYMM_201504 '201505' USABLE YES
PARTITION_TEST_PK YYYYMM_201505 '201506' USABLE YES
PARTITION_TEST_PK YYYYMM_201506 '201507' USABLE YES
PARTITION_TEST_PK YYYYMM_201507 '201508' USABLE YES
PARTITION_TEST_PK YYYYMM_201508 '201509' USABLE YES
PARTITION_TEST_PK YYYYMM_201509 '201510' USABLE YES
PARTITION_TEST_PK YYYYMM_201511 '201512' USABLE YES
PARTITION_TEST_PK YYYYMM_201512 '201513' USABLE YES
PARTITION_TEST_PK YYYYMM_201601 '201602' USABLE YES
12 rows selected.
Elapsed: 00:00:00.12
TABLE_NAME STATUS NUM_ROWS DEGREE PAR GLO
-------------------- -------- ---------- ------ --- ---
PARTITION_TEST VALID 11000 1 YES YES
1 row selected.
Elapsed: 00:00:00.06
TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME NUM_ROWS BLOCKS GLO
-------------------- -------------------- --------------- --------------- ---------- ---------- ---
PARTITION_TEST YYYYMM_201501 '201502' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201502 '201503' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201503 '201504' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201504 '201505' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201505 '201506' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201506 '201507' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201507 '201508' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201508 '201509' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201509 '201510' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201511 '201512' USERS 1000 64 YES
PARTITION_TEST YYYYMM_201512 '201513' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201601 '201602' USERS 0 0 YES
12 rows selected.
Elapsed: 00:00:00.11
SQL> SELECT COUNT( *)
2 FROM PARTITION_TEST
3 WHERE YYYYMM = '201510';
COUNT(*)
----------
1000
1 row selected.
Elapsed: 00:00:00.01
SQL> SELECT COUNT( *)
2 FROM PARTITION_TEST
3 WHERE YYYYMM = '201511';
COUNT(*)
----------
0
1 row selected.
Elapsed: 00:00:00.00
SQL> ALTER TABLE PARTITION_TEST
2 SPLIT PARTITION YYYYMM_201511 AT ('201511') INTO(PARTITION YYYYMM_201510 TABLESPACE USERS, PARTITION YYYYMM_201511 TABLESPACE USERS);
Table altered.
Elapsed: 00:00:00.08
SQL> @tt
TABLE_NAME INDEX_NAME INDEX_TYPE STATUS PAR DEGREE GLO
-------------------- ------------------------- ------------ -------- --- ------ ---
PARTITION_TEST PARTITION_TEST_PK NORMAL N/A YES 1 YES
PARTITION_TEST PARTITION_TEST_IDX01 NORMAL VALID NO 1 YES
2 rows selected.
Elapsed: 00:00:00.06
INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS GLO
------------------------- -------------------- --------------- -------- ---
PARTITION_TEST_PK YYYYMM_201501 '201502' USABLE YES
PARTITION_TEST_PK YYYYMM_201502 '201503' USABLE YES
PARTITION_TEST_PK YYYYMM_201503 '201504' USABLE YES
PARTITION_TEST_PK YYYYMM_201504 '201505' USABLE YES
PARTITION_TEST_PK YYYYMM_201505 '201506' USABLE YES
PARTITION_TEST_PK YYYYMM_201506 '201507' USABLE YES
PARTITION_TEST_PK YYYYMM_201507 '201508' USABLE YES
PARTITION_TEST_PK YYYYMM_201508 '201509' USABLE YES
PARTITION_TEST_PK YYYYMM_201509 '201510' USABLE YES
PARTITION_TEST_PK YYYYMM_201510 '201511' USABLE YES
PARTITION_TEST_PK YYYYMM_201511 '201512' USABLE NO
PARTITION_TEST_PK YYYYMM_201512 '201513' USABLE YES
PARTITION_TEST_PK YYYYMM_201601 '201602' USABLE YES
13 rows selected.
Elapsed: 00:00:00.06
TABLE_NAME STATUS NUM_ROWS DEGREE PAR GLO
-------------------- -------- ---------- ------ --- ---
PARTITION_TEST VALID 11000 1 YES YES
1 row selected.
Elapsed: 00:00:00.02
TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME NUM_ROWS BLOCKS GLO
-------------------- -------------------- --------------- --------------- ---------- ---------- ---
PARTITION_TEST YYYYMM_201501 '201502' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201502 '201503' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201503 '201504' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201504 '201505' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201505 '201506' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201506 '201507' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201507 '201508' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201508 '201509' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201509 '201510' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201510 '201511' USERS 1000 64 YES
PARTITION_TEST YYYYMM_201511 '201512' USERS 1000 64 YES
PARTITION_TEST YYYYMM_201512 '201513' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201601 '201602' USERS 0 0 YES
13 rows selected.
Elapsed: 00:00:00.04
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'DB_MONITORING', TABNAME => 'PARTITION_TEST', CASCADE => TRUE)
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.17
SQL> @tt
TABLE_NAME INDEX_NAME INDEX_TYPE STATUS PAR DEGREE GLO
-------------------- ------------------------- ------------ -------- --- ------ ---
PARTITION_TEST PARTITION_TEST_PK NORMAL N/A YES 1 YES
PARTITION_TEST PARTITION_TEST_IDX01 NORMAL VALID NO 1 YES
2 rows selected.
Elapsed: 00:00:00.05
INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS GLO
------------------------- -------------------- --------------- -------- ---
PARTITION_TEST_PK YYYYMM_201501 '201502' USABLE YES
PARTITION_TEST_PK YYYYMM_201502 '201503' USABLE YES
PARTITION_TEST_PK YYYYMM_201503 '201504' USABLE YES
PARTITION_TEST_PK YYYYMM_201504 '201505' USABLE YES
PARTITION_TEST_PK YYYYMM_201505 '201506' USABLE YES
PARTITION_TEST_PK YYYYMM_201506 '201507' USABLE YES
PARTITION_TEST_PK YYYYMM_201507 '201508' USABLE YES
PARTITION_TEST_PK YYYYMM_201508 '201509' USABLE YES
PARTITION_TEST_PK YYYYMM_201509 '201510' USABLE YES
PARTITION_TEST_PK YYYYMM_201510 '201511' USABLE YES
PARTITION_TEST_PK YYYYMM_201511 '201512' USABLE YES
PARTITION_TEST_PK YYYYMM_201512 '201513' USABLE YES
PARTITION_TEST_PK YYYYMM_201601 '201602' USABLE YES
13 rows selected.
Elapsed: 00:00:00.07
TABLE_NAME STATUS NUM_ROWS DEGREE PAR GLO
-------------------- -------- ---------- ------ --- ---
PARTITION_TEST VALID 11000 1 YES YES
1 row selected.
Elapsed: 00:00:00.02
TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME NUM_ROWS BLOCKS GLO
-------------------- -------------------- --------------- --------------- ---------- ---------- ---
PARTITION_TEST YYYYMM_201501 '201502' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201502 '201503' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201503 '201504' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201504 '201505' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201505 '201506' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201506 '201507' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201507 '201508' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201508 '201509' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201509 '201510' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201510 '201511' USERS 1000 64 YES
PARTITION_TEST YYYYMM_201511 '201512' USERS 0 0 YES
PARTITION_TEST YYYYMM_201512 '201513' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201601 '201602' USERS 0 0 YES
13 rows selected.
Elapsed: 00:00:00.04
9. Partition Exchange 테스트
Exchange 경우 Exchange 할 테이블에도 Partition 테이블에 있는 제약조건과 인덱스가 존재해야 한다.
또한 각 컬럼의 데이터 타입도 일치해야 한다. 그렇지 않을 경우 에러가 발생한다.
또, Global Index의 경우 Unusable이 되며 exchange 한 Partition의 Local Index또한 Unusable이 된다. Rebuild 해 줘야 한다.
SQL> INSERT INTO PARTITION_TEST SELECT '201511', LEVEL, MOD(LEVEL, 3) FROM DUAL CONNECT BY LEVEL <= 1000;
1000 rows created.
Elapsed: 00:00:00.04
SQL> ALTER TABLE PARTITION_TEST DROP PARTITION YYYYMM_201510;
Table altered.
Elapsed: 00:00:00.04
SQL> ALTER INDEX PARTITION_TEST_IDX01 REBUILD;
Index altered.
Elapsed: 00:00:00.08
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'DB_MONITORING', TABNAME => 'PARTITION_TEST', CASCADE => TRUE)
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.97
SQL> @tt
TABLE_NAME INDEX_NAME INDEX_TYPE STATUS PAR DEGREE GLO
-------------------- ------------------------- ------------ -------- --- ------ ---
PARTITION_TEST PARTITION_TEST_PK NORMAL N/A YES 1 YES
PARTITION_TEST PARTITION_TEST_IDX01 NORMAL VALID NO 1 YES
2 rows selected.
Elapsed: 00:00:00.06
INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS GLO
------------------------- -------------------- --------------- -------- ---
PARTITION_TEST_PK YYYYMM_201501 '201502' USABLE YES
PARTITION_TEST_PK YYYYMM_201502 '201503' USABLE YES
PARTITION_TEST_PK YYYYMM_201503 '201504' USABLE YES
PARTITION_TEST_PK YYYYMM_201504 '201505' USABLE YES
PARTITION_TEST_PK YYYYMM_201505 '201506' USABLE YES
PARTITION_TEST_PK YYYYMM_201506 '201507' USABLE YES
PARTITION_TEST_PK YYYYMM_201507 '201508' USABLE YES
PARTITION_TEST_PK YYYYMM_201508 '201509' USABLE YES
PARTITION_TEST_PK YYYYMM_201509 '201510' USABLE YES
PARTITION_TEST_PK YYYYMM_201511 '201512' USABLE YES
PARTITION_TEST_PK YYYYMM_201512 '201513' USABLE YES
PARTITION_TEST_PK YYYYMM_201601 '201602' USABLE YES
12 rows selected.
Elapsed: 00:00:00.06
TABLE_NAME STATUS NUM_ROWS DEGREE PAR GLO
-------------------- -------- ---------- ------ --- ---
PARTITION_TEST VALID 11000 1 YES YES
1 row selected.
Elapsed: 00:00:00.02
TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME NUM_ROWS BLOCKS GLO
-------------------- -------------------- --------------- --------------- ---------- ---------- ---
PARTITION_TEST YYYYMM_201501 '201502' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201502 '201503' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201503 '201504' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201504 '201505' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201505 '201506' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201506 '201507' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201507 '201508' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201508 '201509' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201509 '201510' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201511 '201512' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201512 '201513' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201601 '201602' USERS 0 0 YES
12 rows selected.
Elapsed: 00:00:00.04
SQL> CREATE TABLE PARTITION_TEST_EXCHANGE
2 (
3 YYYYMM VARCHAR2(6)
4 ,TEST_COL1 NUMBER
5 ,TEST_COL2 NUMBER
6 );
Table created.
Elapsed: 00:00:00.04
SQL> INSERT INTO PARTITION_TEST_EXCHANGE SELECT '201510', LEVEL, MOD(LEVEL, 3) FROM DUAL CONNECT BY LEVEL <= 1000;
1000 rows created.
Elapsed: 00:00:00.06
SQL> COMMIT;
Commit complete.
Elapsed: 00:00:00.00
SQL> ALTER TABLE PARTITION_TEST
2 SPLIT PARTITION YYYYMM_201511 AT ('201511') INTO(PARTITION YYYYMM_201510 TABLESPACE USERS, PARTITION YYYYMM_201511 TABLESPACE USERS);
Table altered.
Elapsed: 00:00:00.07
SQL> @tt
TABLE_NAME INDEX_NAME INDEX_TYPE STATUS PAR DEGREE GLO
-------------------- ------------------------- ------------ -------- --- ------ ---
PARTITION_TEST PARTITION_TEST_PK NORMAL N/A YES 1 YES
PARTITION_TEST PARTITION_TEST_IDX01 NORMAL UNUSABLE NO 1 YES
2 rows selected.
Elapsed: 00:00:00.05
INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS GLO
------------------------- -------------------- --------------- -------- ---
PARTITION_TEST_PK YYYYMM_201501 '201502' USABLE YES
PARTITION_TEST_PK YYYYMM_201502 '201503' USABLE YES
PARTITION_TEST_PK YYYYMM_201503 '201504' USABLE YES
PARTITION_TEST_PK YYYYMM_201504 '201505' USABLE YES
PARTITION_TEST_PK YYYYMM_201505 '201506' USABLE YES
PARTITION_TEST_PK YYYYMM_201506 '201507' USABLE YES
PARTITION_TEST_PK YYYYMM_201507 '201508' USABLE YES
PARTITION_TEST_PK YYYYMM_201508 '201509' USABLE YES
PARTITION_TEST_PK YYYYMM_201509 '201510' USABLE YES
PARTITION_TEST_PK YYYYMM_201510 '201511' UNUSABLE NO
PARTITION_TEST_PK YYYYMM_201511 '201512' UNUSABLE NO
PARTITION_TEST_PK YYYYMM_201512 '201513' USABLE YES
PARTITION_TEST_PK YYYYMM_201601 '201602' USABLE YES
13 rows selected.
Elapsed: 00:00:00.07
TABLE_NAME STATUS NUM_ROWS DEGREE PAR GLO
-------------------- -------- ---------- ------ --- ---
PARTITION_TEST VALID 11000 1 YES YES
1 row selected.
Elapsed: 00:00:00.02
TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME NUM_ROWS BLOCKS GLO
-------------------- -------------------- --------------- --------------- ---------- ---------- ---
PARTITION_TEST YYYYMM_201501 '201502' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201502 '201503' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201503 '201504' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201504 '201505' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201505 '201506' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201506 '201507' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201507 '201508' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201508 '201509' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201509 '201510' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201510 '201511' USERS NO
PARTITION_TEST YYYYMM_201511 '201512' USERS NO
PARTITION_TEST YYYYMM_201512 '201513' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201601 '201602' USERS 0 0 YES
13 rows selected.
Elapsed: 00:00:00.04
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'DB_MONITORING', TABNAME => 'PARTITION_TEST', CASCADE => TRUE)
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.15
SQL> @tt
TABLE_NAME INDEX_NAME INDEX_TYPE STATUS PAR DEGREE GLO
-------------------- ------------------------- ------------ -------- --- ------ ---
PARTITION_TEST PARTITION_TEST_PK NORMAL N/A YES 1 YES
PARTITION_TEST PARTITION_TEST_IDX01 NORMAL UNUSABLE NO 1 YES
2 rows selected.
Elapsed: 00:00:00.06
INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS GLO
------------------------- -------------------- --------------- -------- ---
PARTITION_TEST_PK YYYYMM_201501 '201502' USABLE YES
PARTITION_TEST_PK YYYYMM_201502 '201503' USABLE YES
PARTITION_TEST_PK YYYYMM_201503 '201504' USABLE YES
PARTITION_TEST_PK YYYYMM_201504 '201505' USABLE YES
PARTITION_TEST_PK YYYYMM_201505 '201506' USABLE YES
PARTITION_TEST_PK YYYYMM_201506 '201507' USABLE YES
PARTITION_TEST_PK YYYYMM_201507 '201508' USABLE YES
PARTITION_TEST_PK YYYYMM_201508 '201509' USABLE YES
PARTITION_TEST_PK YYYYMM_201509 '201510' USABLE YES
PARTITION_TEST_PK YYYYMM_201510 '201511' UNUSABLE YES
PARTITION_TEST_PK YYYYMM_201511 '201512' UNUSABLE YES
PARTITION_TEST_PK YYYYMM_201512 '201513' USABLE YES
PARTITION_TEST_PK YYYYMM_201601 '201602' USABLE YES
13 rows selected.
Elapsed: 00:00:00.06
TABLE_NAME STATUS NUM_ROWS DEGREE PAR GLO
-------------------- -------- ---------- ------ --- ---
PARTITION_TEST VALID 12000 1 YES YES
1 row selected.
Elapsed: 00:00:00.02
TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME NUM_ROWS BLOCKS GLO
-------------------- -------------------- --------------- --------------- ---------- ---------- ---
PARTITION_TEST YYYYMM_201501 '201502' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201502 '201503' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201503 '201504' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201504 '201505' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201505 '201506' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201506 '201507' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201507 '201508' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201508 '201509' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201509 '201510' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201510 '201511' USERS 1000 21 YES
PARTITION_TEST YYYYMM_201511 '201512' USERS 1000 21 YES
PARTITION_TEST YYYYMM_201512 '201513' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201601 '201602' USERS 0 0 YES
13 rows selected.
Elapsed: 00:00:00.04
SQL> DELETE FROM PARTITION_TEST
2 WHERE YYYYMM = '201510';
DELETE FROM PARTITION_TEST
*
ERROR at line 1:
ORA-01502: index 'DB_MONITORING.PARTITION_TEST_PK' or partition of such index is in unusable state
Elapsed: 00:00:00.01
SQL> ALTER INDEX PARTITION_TEST_IDX01 REBUILD;
Index altered.
Elapsed: 00:00:00.07
SQL> ALTER INDEX PARTITION_TEST_PK REBUILD PARTITION YYYYMM_201510;
Index altered.
Elapsed: 00:00:00.02
SQL> ALTER INDEX PARTITION_TEST_PK REBUILD PARTITION YYYYMM_201511;
Index altered.
Elapsed: 00:00:00.02
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'DB_MONITORING', TABNAME => 'PARTITION_TEST', CASCADE => TRUE)
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.16
SQL> @tt
TABLE_NAME INDEX_NAME INDEX_TYPE STATUS PAR DEGREE GLO
-------------------- ------------------------- ------------ -------- --- ------ ---
PARTITION_TEST PARTITION_TEST_PK NORMAL N/A YES 1 YES
PARTITION_TEST PARTITION_TEST_IDX01 NORMAL VALID NO 1 YES
2 rows selected.
Elapsed: 00:00:00.06
INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS GLO
------------------------- -------------------- --------------- -------- ---
PARTITION_TEST_PK YYYYMM_201501 '201502' USABLE YES
PARTITION_TEST_PK YYYYMM_201502 '201503' USABLE YES
PARTITION_TEST_PK YYYYMM_201503 '201504' USABLE YES
PARTITION_TEST_PK YYYYMM_201504 '201505' USABLE YES
PARTITION_TEST_PK YYYYMM_201505 '201506' USABLE YES
PARTITION_TEST_PK YYYYMM_201506 '201507' USABLE YES
PARTITION_TEST_PK YYYYMM_201507 '201508' USABLE YES
PARTITION_TEST_PK YYYYMM_201508 '201509' USABLE YES
PARTITION_TEST_PK YYYYMM_201509 '201510' USABLE YES
PARTITION_TEST_PK YYYYMM_201510 '201511' USABLE YES
PARTITION_TEST_PK YYYYMM_201511 '201512' USABLE YES
PARTITION_TEST_PK YYYYMM_201512 '201513' USABLE YES
PARTITION_TEST_PK YYYYMM_201601 '201602' USABLE YES
13 rows selected.
Elapsed: 00:00:00.07
TABLE_NAME STATUS NUM_ROWS DEGREE PAR GLO
-------------------- -------- ---------- ------ --- ---
PARTITION_TEST VALID 12000 1 YES YES
1 row selected.
Elapsed: 00:00:00.02
TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME NUM_ROWS BLOCKS GLO
-------------------- -------------------- --------------- --------------- ---------- ---------- ---
PARTITION_TEST YYYYMM_201501 '201502' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201502 '201503' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201503 '201504' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201504 '201505' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201505 '201506' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201506 '201507' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201507 '201508' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201508 '201509' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201509 '201510' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201510 '201511' USERS 1000 21 YES
PARTITION_TEST YYYYMM_201511 '201512' USERS 1000 21 YES
PARTITION_TEST YYYYMM_201512 '201513' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201601 '201602' USERS 0 0 YES
13 rows selected.
Elapsed: 00:00:00.04
SQL> DELETE FROM PARTITION_TEST
2 WHERE YYYYMM = '201510';
1000 rows deleted.
Elapsed: 00:00:00.04
SQL> COMMIT;
Commit complete.
Elapsed: 00:00:00.01
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'DB_MONITORING', TABNAME => 'PARTITION_TEST', CASCADE => TRUE)
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.99
SQL> @tt
TABLE_NAME INDEX_NAME INDEX_TYPE STATUS PAR DEGREE GLO
-------------------- ------------------------- ------------ -------- --- ------ ---
PARTITION_TEST PARTITION_TEST_PK NORMAL N/A YES 1 YES
PARTITION_TEST PARTITION_TEST_IDX01 NORMAL VALID NO 1 YES
2 rows selected.
Elapsed: 00:00:00.06
INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS GLO
------------------------- -------------------- --------------- -------- ---
PARTITION_TEST_PK YYYYMM_201501 '201502' USABLE YES
PARTITION_TEST_PK YYYYMM_201502 '201503' USABLE YES
PARTITION_TEST_PK YYYYMM_201503 '201504' USABLE YES
PARTITION_TEST_PK YYYYMM_201504 '201505' USABLE YES
PARTITION_TEST_PK YYYYMM_201505 '201506' USABLE YES
PARTITION_TEST_PK YYYYMM_201506 '201507' USABLE YES
PARTITION_TEST_PK YYYYMM_201507 '201508' USABLE YES
PARTITION_TEST_PK YYYYMM_201508 '201509' USABLE YES
PARTITION_TEST_PK YYYYMM_201509 '201510' USABLE YES
PARTITION_TEST_PK YYYYMM_201510 '201511' USABLE YES
PARTITION_TEST_PK YYYYMM_201511 '201512' USABLE YES
PARTITION_TEST_PK YYYYMM_201512 '201513' USABLE YES
PARTITION_TEST_PK YYYYMM_201601 '201602' USABLE YES
13 rows selected.
Elapsed: 00:00:00.06
TABLE_NAME STATUS NUM_ROWS DEGREE PAR GLO
-------------------- -------- ---------- ------ --- ---
PARTITION_TEST VALID 11000 1 YES YES
1 row selected.
Elapsed: 00:00:00.02
TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME NUM_ROWS BLOCKS GLO
-------------------- -------------------- --------------- --------------- ---------- ---------- ---
PARTITION_TEST YYYYMM_201501 '201502' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201502 '201503' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201503 '201504' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201504 '201505' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201505 '201506' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201506 '201507' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201507 '201508' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201508 '201509' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201509 '201510' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201510 '201511' USERS 0 0 YES
PARTITION_TEST YYYYMM_201511 '201512' USERS 1000 21 YES
PARTITION_TEST YYYYMM_201512 '201513' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201601 '201602' USERS 0 0 YES
13 rows selected.
Elapsed: 00:00:00.04
SQL> ALTER TABLE PARTITION_TEST EXCHANGE PARTITION YYYYMM_201510 WITH TABLE PARTITION_TEST_EXCHANGE;
ALTER TABLE PARTITION_TEST EXCHANGE PARTITION YYYYMM_201510 WITH TABLE PARTITION_TEST_EXCHANGE
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
Elapsed: 00:00:00.28
SQL> desc PARTITION_TEST_EXCHANGE
Name Null? Type
----------------------------------------------------------------------------------------------------------- -------- ------------------------------------------------------------------------
YYYYMM VARCHAR2(6)
TEST_COL1 NUMBER
TEST_COL2 NUMBER
SQL> select count(*) from PARTITION_TEST_EXCHANGE;
COUNT(*)
----------
1000
1 row selected.
Elapsed: 00:00:00.00
SQL> desc PARTITION_TEST
Name Null? Type
----------------------------------------------------------------------------------------------------------- -------- ------------------------------------------------------------------------
YYYYMM NOT NULL VARCHAR2(6)
TEST_COL1 NOT NULL NUMBER
TEST_COL2 NUMBER
SQL> ALTER TABLE PARTITION_TEST_EXCHANGE MODIFY (YYYYMM NOT NULL, TEST_COL1 NOT NULL);
Table altered.
Elapsed: 00:00:00.09
SQL> desc PARTITION_TEST_EXCHANGE
Name Null? Type
----------------------------------------------------------------------------------------------------------- -------- ------------------------------------------------------------------------
YYYYMM NOT NULL VARCHAR2(6)
TEST_COL1 NOT NULL NUMBER
TEST_COL2 NUMBER
SQL> ALTER TABLE PARTITION_TEST EXCHANGE PARTITION YYYYMM_201510 WITH TABLE PARTITION_TEST_EXCHANGE;
ALTER TABLE PARTITION_TEST EXCHANGE PARTITION YYYYMM_201510 WITH TABLE PARTITION_TEST_EXCHANGE
*
ERROR at line 1:
ORA-14130: UNIQUE constraints mismatch in ALTER TABLE EXCHANGE PARTITION
Elapsed: 00:00:00.02
SQL> CREATE UNIQUE INDEX PARTITION_TEST_EXCHANGE_PK ON PARTITION_TEST_EXCHANGE(YYYYMM, TEST_COL1);
Index created.
Elapsed: 00:00:00.06
SQL> ALTER TABLE PARTITION_TEST_EXCHANGE
2 ADD CONSTRAINT PARTITION_TEST_EXCHANGE_PK PRIMARY KEY(YYYYMM, TEST_COL1)
3 USING INDEX;
Table altered.
Elapsed: 00:00:00.06
SQL> ALTER TABLE PARTITION_TEST EXCHANGE PARTITION YYYYMM_201510 WITH TABLE PARTITION_TEST_EXCHANGE;
Table altered.
Elapsed: 00:00:00.59
SQL> @tt
TABLE_NAME INDEX_NAME INDEX_TYPE STATUS PAR DEGREE GLO
-------------------- ------------------------- ------------ -------- --- ------ ---
PARTITION_TEST PARTITION_TEST_PK NORMAL N/A YES 1 YES
PARTITION_TEST PARTITION_TEST_IDX01 NORMAL UNUSABLE NO 1 YES
PARTITION_TEST_EXCHA PARTITION_TEST_EXCHANGE_P NORMAL UNUSABLE NO 1 NO
NGE K
3 rows selected.
Elapsed: 00:00:00.05
INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS GLO
------------------------- -------------------- --------------- -------- ---
PARTITION_TEST_PK YYYYMM_201501 '201502' USABLE YES
PARTITION_TEST_PK YYYYMM_201502 '201503' USABLE YES
PARTITION_TEST_PK YYYYMM_201503 '201504' USABLE YES
PARTITION_TEST_PK YYYYMM_201504 '201505' USABLE YES
PARTITION_TEST_PK YYYYMM_201505 '201506' USABLE YES
PARTITION_TEST_PK YYYYMM_201506 '201507' USABLE YES
PARTITION_TEST_PK YYYYMM_201507 '201508' USABLE YES
PARTITION_TEST_PK YYYYMM_201508 '201509' USABLE YES
PARTITION_TEST_PK YYYYMM_201509 '201510' USABLE YES
PARTITION_TEST_PK YYYYMM_201510 '201511' UNUSABLE YES
PARTITION_TEST_PK YYYYMM_201511 '201512' USABLE YES
PARTITION_TEST_PK YYYYMM_201512 '201513' USABLE YES
PARTITION_TEST_PK YYYYMM_201601 '201602' USABLE YES
13 rows selected.
Elapsed: 00:00:00.07
TABLE_NAME STATUS NUM_ROWS DEGREE PAR GLO
-------------------- -------- ---------- ------ --- ---
PARTITION_TEST VALID 11000 1 YES YES
1 row selected.
Elapsed: 00:00:00.02
TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME NUM_ROWS BLOCKS GLO
-------------------- -------------------- --------------- --------------- ---------- ---------- ---
PARTITION_TEST YYYYMM_201501 '201502' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201502 '201503' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201503 '201504' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201504 '201505' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201505 '201506' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201506 '201507' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201507 '201508' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201508 '201509' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201509 '201510' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201510 '201511' USERS 0 21 YES
PARTITION_TEST YYYYMM_201511 '201512' USERS 1000 21 YES
PARTITION_TEST YYYYMM_201512 '201513' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201601 '201602' USERS 0 0 YES
13 rows selected.
Elapsed: 00:00:00.03
SQL> ALTER INDEX PARTITION_TEST_IDX01 REBUILD;
Index altered.
Elapsed: 00:00:00.55
SQL> ALTER INDEX PARTITION_TEST_PK REBUILD PARTITION YYYYMM_201510;
Index altered.
Elapsed: 00:00:00.03
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'DB_MONITORING', TABNAME => 'PARTITION_TEST', CASCADE => TRUE)
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.95
SQL> @tt
TABLE_NAME INDEX_NAME INDEX_TYPE STATUS PAR DEGREE GLO
-------------------- ------------------------- ------------ -------- --- ------ ---
PARTITION_TEST PARTITION_TEST_PK NORMAL N/A YES 1 YES
PARTITION_TEST PARTITION_TEST_IDX01 NORMAL VALID NO 1 YES
PARTITION_TEST_EXCHA PARTITION_TEST_EXCHANGE_P NORMAL UNUSABLE NO 1 NO
NGE K
3 rows selected.
Elapsed: 00:00:00.06
INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS GLO
------------------------- -------------------- --------------- -------- ---
PARTITION_TEST_PK YYYYMM_201501 '201502' USABLE YES
PARTITION_TEST_PK YYYYMM_201502 '201503' USABLE YES
PARTITION_TEST_PK YYYYMM_201503 '201504' USABLE YES
PARTITION_TEST_PK YYYYMM_201504 '201505' USABLE YES
PARTITION_TEST_PK YYYYMM_201505 '201506' USABLE YES
PARTITION_TEST_PK YYYYMM_201506 '201507' USABLE YES
PARTITION_TEST_PK YYYYMM_201507 '201508' USABLE YES
PARTITION_TEST_PK YYYYMM_201508 '201509' USABLE YES
PARTITION_TEST_PK YYYYMM_201509 '201510' USABLE YES
PARTITION_TEST_PK YYYYMM_201510 '201511' USABLE YES
PARTITION_TEST_PK YYYYMM_201511 '201512' USABLE YES
PARTITION_TEST_PK YYYYMM_201512 '201513' USABLE YES
PARTITION_TEST_PK YYYYMM_201601 '201602' USABLE YES
13 rows selected.
Elapsed: 00:00:00.07
TABLE_NAME STATUS NUM_ROWS DEGREE PAR GLO
-------------------- -------- ---------- ------ --- ---
PARTITION_TEST VALID 12000 1 YES YES
1 row selected.
Elapsed: 00:00:00.02
TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME NUM_ROWS BLOCKS GLO
-------------------- -------------------- --------------- --------------- ---------- ---------- ---
PARTITION_TEST YYYYMM_201501 '201502' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201502 '201503' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201503 '201504' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201504 '201505' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201505 '201506' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201506 '201507' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201507 '201508' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201508 '201509' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201509 '201510' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201510 '201511' USERS 1000 21 YES
PARTITION_TEST YYYYMM_201511 '201512' USERS 1000 21 YES
PARTITION_TEST YYYYMM_201512 '201513' USERS 1000 46 YES
PARTITION_TEST YYYYMM_201601 '201602' USERS 0 0 YES
13 rows selected.
Elapsed: 00:00:00.04
10. MAX Value를 갖는 Partition 추가, 삭제
마지막 파티션 뒤에 Max value를 갖는 파티션에 대해서 추가 삭제한 테스트 이다.
SQL> ALTER TABLE PARTITION_TEST ADD PARTITION YYYYMM_MAX VALUES LESS THAN (MAXVALUE) TABLESPACE USERS; Table altered. Elapsed: 00:00:00.11 SQL> @tt TABLE_NAME INDEX_NAME INDEX_TYPE STATUS PAR DEGREE GLO -------------------- ------------------------- ------------ -------- --- ------ --- PARTITION_TEST PARTITION_TEST_PK NORMAL N/A YES 1 YES PARTITION_TEST PARTITION_TEST_IDX01 NORMAL VALID NO 1 YES PARTITION_TEST_EXCHA PARTITION_TEST_EXCHANGE_P NORMAL UNUSABLE NO 1 NO NGE K 3 rows selected. Elapsed: 00:00:00.06 INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS GLO ------------------------- -------------------- --------------- -------- --- PARTITION_TEST_PK YYYYMM_201501 '201502' USABLE YES PARTITION_TEST_PK YYYYMM_201502 '201503' USABLE YES PARTITION_TEST_PK YYYYMM_201503 '201504' USABLE YES PARTITION_TEST_PK YYYYMM_201504 '201505' USABLE YES PARTITION_TEST_PK YYYYMM_201505 '201506' USABLE YES PARTITION_TEST_PK YYYYMM_201506 '201507' USABLE YES PARTITION_TEST_PK YYYYMM_201507 '201508' USABLE YES PARTITION_TEST_PK YYYYMM_201508 '201509' USABLE YES PARTITION_TEST_PK YYYYMM_201509 '201510' USABLE YES PARTITION_TEST_PK YYYYMM_201510 '201511' USABLE YES PARTITION_TEST_PK YYYYMM_201511 '201512' USABLE YES PARTITION_TEST_PK YYYYMM_201512 '201513' USABLE YES PARTITION_TEST_PK YYYYMM_201601 '201602' USABLE YES PARTITION_TEST_PK YYYYMM_MAX MAXVALUE USABLE NO 14 rows selected. Elapsed: 00:00:00.07 TABLE_NAME STATUS NUM_ROWS DEGREE PAR GLO -------------------- -------- ---------- ------ --- --- PARTITION_TEST VALID 12000 1 YES YES 1 row selected. Elapsed: 00:00:00.02 TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME NUM_ROWS BLOCKS GLO -------------------- -------------------- --------------- --------------- ---------- ---------- --- PARTITION_TEST YYYYMM_201501 '201502' USERS 1000 46 YES PARTITION_TEST YYYYMM_201502 '201503' USERS 1000 46 YES PARTITION_TEST YYYYMM_201503 '201504' USERS 1000 46 YES PARTITION_TEST YYYYMM_201504 '201505' USERS 1000 46 YES PARTITION_TEST YYYYMM_201505 '201506' USERS 1000 46 YES PARTITION_TEST YYYYMM_201506 '201507' USERS 1000 46 YES PARTITION_TEST YYYYMM_201507 '201508' USERS 1000 46 YES PARTITION_TEST YYYYMM_201508 '201509' USERS 1000 46 YES PARTITION_TEST YYYYMM_201509 '201510' USERS 1000 46 YES PARTITION_TEST YYYYMM_201510 '201511' USERS 1000 21 YES PARTITION_TEST YYYYMM_201511 '201512' USERS 1000 21 YES PARTITION_TEST YYYYMM_201512 '201513' USERS 1000 46 YES PARTITION_TEST YYYYMM_201601 '201602' USERS 0 0 YES PARTITION_TEST YYYYMM_MAX MAXVALUE USERS NO 14 rows selected. Elapsed: 00:00:00.04 SQL> ALTER TABLE PARTITION_TEST DROP PARTITION YYYYMM_MAX; Table altered. Elapsed: 00:00:00.38 SQL> @tt TABLE_NAME INDEX_NAME INDEX_TYPE STATUS PAR DEGREE GLO -------------------- ------------------------- ------------ -------- --- ------ --- PARTITION_TEST PARTITION_TEST_PK NORMAL N/A YES 1 YES PARTITION_TEST PARTITION_TEST_IDX01 NORMAL VALID NO 1 YES PARTITION_TEST_EXCHA PARTITION_TEST_EXCHANGE_P NORMAL UNUSABLE NO 1 NO NGE K 3 rows selected. Elapsed: 00:00:00.13 INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS GLO ------------------------- -------------------- --------------- -------- --- PARTITION_TEST_PK YYYYMM_201501 '201502' USABLE YES PARTITION_TEST_PK YYYYMM_201502 '201503' USABLE YES PARTITION_TEST_PK YYYYMM_201503 '201504' USABLE YES PARTITION_TEST_PK YYYYMM_201504 '201505' USABLE YES PARTITION_TEST_PK YYYYMM_201505 '201506' USABLE YES PARTITION_TEST_PK YYYYMM_201506 '201507' USABLE YES PARTITION_TEST_PK YYYYMM_201507 '201508' USABLE YES PARTITION_TEST_PK YYYYMM_201508 '201509' USABLE YES PARTITION_TEST_PK YYYYMM_201509 '201510' USABLE YES PARTITION_TEST_PK YYYYMM_201510 '201511' USABLE YES PARTITION_TEST_PK YYYYMM_201511 '201512' USABLE YES PARTITION_TEST_PK YYYYMM_201512 '201513' USABLE YES PARTITION_TEST_PK YYYYMM_201601 '201602' USABLE YES 13 rows selected. Elapsed: 00:00:01.51 TABLE_NAME STATUS NUM_ROWS DEGREE PAR GLO -------------------- -------- ---------- ------ --- --- PARTITION_TEST VALID 12000 1 YES YES 1 row selected. Elapsed: 00:00:00.07 TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME NUM_ROWS BLOCKS GLO -------------------- -------------------- --------------- --------------- ---------- ---------- --- PARTITION_TEST YYYYMM_201501 '201502' USERS 1000 46 YES PARTITION_TEST YYYYMM_201502 '201503' USERS 1000 46 YES PARTITION_TEST YYYYMM_201503 '201504' USERS 1000 46 YES PARTITION_TEST YYYYMM_201504 '201505' USERS 1000 46 YES PARTITION_TEST YYYYMM_201505 '201506' USERS 1000 46 YES PARTITION_TEST YYYYMM_201506 '201507' USERS 1000 46 YES PARTITION_TEST YYYYMM_201507 '201508' USERS 1000 46 YES PARTITION_TEST YYYYMM_201508 '201509' USERS 1000 46 YES PARTITION_TEST YYYYMM_201509 '201510' USERS 1000 46 YES PARTITION_TEST YYYYMM_201510 '201511' USERS 1000 21 YES PARTITION_TEST YYYYMM_201511 '201512' USERS 1000 21 YES PARTITION_TEST YYYYMM_201512 '201513' USERS 1000 46 YES PARTITION_TEST YYYYMM_201601 '201602' USERS 0 0 YES 13 rows selected. Elapsed: 00:00:00.16 SQL> DROP TABLE PARTITION_TEST_EXCHANGE PURGE; Table dropped. Elapsed: 00:00:00.51 SQL> ALTER TABLE PARTITION_TEST ADD PARTITION YYYYMM_MAX VALUES LESS THAN (MAXVALUE) TABLESPACE USERS; Table altered. Elapsed: 00:00:00.12 SQL> INSERT INTO PARTITION_TEST SELECT '201601', LEVEL, MOD(LEVEL, 3) FROM DUAL CONNECT BY LEVEL <= 1000; 1000 rows created. Elapsed: 00:00:00.17 SQL> INSERT INTO PARTITION_TEST SELECT '201602', LEVEL, MOD(LEVEL, 3) FROM DUAL CONNECT BY LEVEL <= 1000; 1000 rows created. Elapsed: 00:00:00.06 SQL> COMMIT; Commit complete. Elapsed: 00:00:00.01 SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'DB_MONITORING', TABNAME => 'PARTITION_TEST', CASCADE => TRUE) PL/SQL procedure successfully completed. Elapsed: 00:00:04.13 SQL> @tt TABLE_NAME INDEX_NAME INDEX_TYPE STATUS PAR DEGREE GLO -------------------- ------------------------- ------------ -------- --- ------ --- PARTITION_TEST PARTITION_TEST_PK NORMAL N/A YES 1 YES PARTITION_TEST PARTITION_TEST_IDX01 NORMAL VALID NO 1 YES 2 rows selected. Elapsed: 00:00:00.12 INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS GLO ------------------------- -------------------- --------------- -------- --- PARTITION_TEST_PK YYYYMM_201501 '201502' USABLE YES PARTITION_TEST_PK YYYYMM_201502 '201503' USABLE YES PARTITION_TEST_PK YYYYMM_201503 '201504' USABLE YES PARTITION_TEST_PK YYYYMM_201504 '201505' USABLE YES PARTITION_TEST_PK YYYYMM_201505 '201506' USABLE YES PARTITION_TEST_PK YYYYMM_201506 '201507' USABLE YES PARTITION_TEST_PK YYYYMM_201507 '201508' USABLE YES PARTITION_TEST_PK YYYYMM_201508 '201509' USABLE YES PARTITION_TEST_PK YYYYMM_201509 '201510' USABLE YES PARTITION_TEST_PK YYYYMM_201510 '201511' USABLE YES PARTITION_TEST_PK YYYYMM_201511 '201512' USABLE YES PARTITION_TEST_PK YYYYMM_201512 '201513' USABLE YES PARTITION_TEST_PK YYYYMM_201601 '201602' USABLE YES PARTITION_TEST_PK YYYYMM_MAX MAXVALUE USABLE YES 14 rows selected. Elapsed: 00:00:00.12 TABLE_NAME STATUS NUM_ROWS DEGREE PAR GLO -------------------- -------- ---------- ------ --- --- PARTITION_TEST VALID 14000 1 YES YES 1 row selected. Elapsed: 00:00:00.06 TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME NUM_ROWS BLOCKS GLO -------------------- -------------------- --------------- --------------- ---------- ---------- --- PARTITION_TEST YYYYMM_201501 '201502' USERS 1000 46 YES PARTITION_TEST YYYYMM_201502 '201503' USERS 1000 46 YES PARTITION_TEST YYYYMM_201503 '201504' USERS 1000 46 YES PARTITION_TEST YYYYMM_201504 '201505' USERS 1000 46 YES PARTITION_TEST YYYYMM_201505 '201506' USERS 1000 46 YES PARTITION_TEST YYYYMM_201506 '201507' USERS 1000 46 YES PARTITION_TEST YYYYMM_201507 '201508' USERS 1000 46 YES PARTITION_TEST YYYYMM_201508 '201509' USERS 1000 46 YES PARTITION_TEST YYYYMM_201509 '201510' USERS 1000 46 YES PARTITION_TEST YYYYMM_201510 '201511' USERS 1000 21 YES PARTITION_TEST YYYYMM_201511 '201512' USERS 1000 21 YES PARTITION_TEST YYYYMM_201512 '201513' USERS 1000 46 YES PARTITION_TEST YYYYMM_201601 '201602' USERS 1000 46 YES PARTITION_TEST YYYYMM_MAX MAXVALUE USERS 1000 46 YES 14 rows selected. Elapsed: 00:00:00.12 SQL> ALTER TABLE PARTITION_TEST DROP PARTITION YYYYMM_MAX; Table altered. Elapsed: 00:00:00.08 SQL> @tt TABLE_NAME INDEX_NAME INDEX_TYPE STATUS PAR DEGREE GLO -------------------- ------------------------- ------------ -------- --- ------ --- PARTITION_TEST PARTITION_TEST_PK NORMAL N/A YES 1 YES PARTITION_TEST PARTITION_TEST_IDX01 NORMAL UNUSABLE NO 1 YES 2 rows selected. Elapsed: 00:00:00.06 INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS GLO ------------------------- -------------------- --------------- -------- --- PARTITION_TEST_PK YYYYMM_201501 '201502' USABLE YES PARTITION_TEST_PK YYYYMM_201502 '201503' USABLE YES PARTITION_TEST_PK YYYYMM_201503 '201504' USABLE YES PARTITION_TEST_PK YYYYMM_201504 '201505' USABLE YES PARTITION_TEST_PK YYYYMM_201505 '201506' USABLE YES PARTITION_TEST_PK YYYYMM_201506 '201507' USABLE YES PARTITION_TEST_PK YYYYMM_201507 '201508' USABLE YES PARTITION_TEST_PK YYYYMM_201508 '201509' USABLE YES PARTITION_TEST_PK YYYYMM_201509 '201510' USABLE YES PARTITION_TEST_PK YYYYMM_201510 '201511' USABLE YES PARTITION_TEST_PK YYYYMM_201511 '201512' USABLE YES PARTITION_TEST_PK YYYYMM_201512 '201513' USABLE YES PARTITION_TEST_PK YYYYMM_201601 '201602' USABLE YES 13 rows selected. Elapsed: 00:00:00.06 TABLE_NAME STATUS NUM_ROWS DEGREE PAR GLO -------------------- -------- ---------- ------ --- --- PARTITION_TEST VALID 14000 1 YES YES 1 row selected. Elapsed: 00:00:00.02 TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME NUM_ROWS BLOCKS GLO -------------------- -------------------- --------------- --------------- ---------- ---------- --- PARTITION_TEST YYYYMM_201501 '201502' USERS 1000 46 YES PARTITION_TEST YYYYMM_201502 '201503' USERS 1000 46 YES PARTITION_TEST YYYYMM_201503 '201504' USERS 1000 46 YES PARTITION_TEST YYYYMM_201504 '201505' USERS 1000 46 YES PARTITION_TEST YYYYMM_201505 '201506' USERS 1000 46 YES PARTITION_TEST YYYYMM_201506 '201507' USERS 1000 46 YES PARTITION_TEST YYYYMM_201507 '201508' USERS 1000 46 YES PARTITION_TEST YYYYMM_201508 '201509' USERS 1000 46 YES PARTITION_TEST YYYYMM_201509 '201510' USERS 1000 46 YES PARTITION_TEST YYYYMM_201510 '201511' USERS 1000 21 YES PARTITION_TEST YYYYMM_201511 '201512' USERS 1000 21 YES PARTITION_TEST YYYYMM_201512 '201513' USERS 1000 46 YES PARTITION_TEST YYYYMM_201601 '201602' USERS 1000 46 YES 13 rows selected. Elapsed: 00:00:00.03
| 번호 | 제목 | 글쓴이 | 날짜 | 조회 수 |
|---|---|---|---|---|
| 14 | Schema Password 복사 하기 | Talros | 2016.10.05 | 3487 |
| 13 | Block Cleanout(블럭 클린아웃) | 명품관 | 2016.09.23 | 1797 |
| 12 | Fixed Table에 대한 권한은 직접적으로 부여되지 않는다. | 명품관 | 2016.08.26 | 2945 |
| 11 | Oracle Archive log 사용량 확인(GV$ARCHIVED_LOG) [1] | 에밀리오 | 2016.08.04 | 11894 |
| 10 | Oracle Resource Limit를 이용한 간단한 Parameter Check (GV$RESOURCE_LIMIT) | 에밀리오 | 2016.07.15 | 2468 |
| 9 | Oracle VMSTAT을 이용한 CPU 사용량 활용법 | 에밀리오 | 2016.07.14 | 1113 |
| 8 | Kill Session Script (GV$SESSION) | 에밀리오 | 2016.07.12 | 2350 |
| 7 | Active Session History를 이용한 TOP SQL 분석 (GV$ACTIVE_SESSION_HISTORY) | 에밀리오 | 2016.07.12 | 1419 |
| 6 | DB에 생성된 ROLE의 생성 정보 확인 [2] | 명품관 | 2016.05.31 | 2404 |
| 5 | 내가 돌린 SQL ID 찾기 [1] | Talros | 2016.05.12 | 9031 |
| 4 | 테이블 컬럼의 Default 값에 대한 흔적은 Dictionary에 계속 남게 된다. | 명품관 | 2016.04.05 | 1729 |
| 3 | Result Cache(oracle11g NF) [6] | ocm10gr2 | 2016.03.14 | 384 |
| 2 | Alert Log를 SQL 사용하여 보기 (X$DBGALERTEXT) [1] | 에밀리오 | 2016.01.28 | 2304 |
| » | Partition 추가의 계절 - Range Partition 추가시 알아 두어야할 부분 [1] | 명품관 | 2015.12.03 | 2551 |
4. Partition Truncate 테스트 실습 도중에
ALTER INDEX PARTITION_TEST_IDX01 REBUILD; 이후 dbms_stats 패키지를 돌리는 것을 확인 할 수 있습니다.
혹시 dbms_stats 패키지는 왜 돌리는 것인지 알 수 있을까요?
global index 에만 문제가 있으므로 rebuild 만으로도 괜찮지 않을까요?
글 잘 봤습니다 ^^