메뉴 건너뛰기

Korea Oracle User Group

Admin

 

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

 

 

 

위로