[ORACLE] 테이블 파티션 SPLIT 시 통계정보 null 로 변경됨 테스트

파티션 테이블 환경에서 미래 파티션 추가 시 SPLIT으로 작업하는 경우가 있는데 작업 시 파티션의 통계정보가 null로 변경되는 현상 테스트 입니다.

데이터 없는 max 파티션 split

새로운 data_object_id가 생성되면 split 된 모든 파티션의 통계정보가 null로 변경됨


-- max 파티션 데이터 없음
SELECT count(*) FROM sk.p_tab1 PARTITION(p_max) 

COUNT(*)|
--------+
       0|

1 row(s) fetched.

-- 테이블 통계정보
SELECT 
b.object_id, b.data_object_id, a.owner, table_name, partition_name, a.object_type, num_rows, last_analyzed, global_stats, stattype_locked, stale_stats
FROM dba_tab_statistics a, dba_objects b
WHERE 1=1
AND a.owner = b.owner(+)
AND a.table_name = b.object_name(+)
AND a.partition_name = b.subobject_name(+)
AND a.owner = 'SK'
ORDER BY partition_position NULLS first


OBJECT_ID|DATA_OBJECT_ID|OWNER|TABLE_NAME|PARTITION_NAME|OBJECT_TYPE|NUM_ROWS|LAST_ANALYZED          |GLOBAL_STATS|STATTYPE_LOCKED|STALE_STATS|
---------+--------------+-----+----------+--------------+-----------+--------+-----------------------+------------+---------------+-----------+
         |              |SK   |P_TAB1    |              |TABLE      |    3000|2024-09-16 05:50:46.000|YES         |               |YES        |
    76526|         76557|SK   |P_TAB1    |P1000         |PARTITION  |    1000|2024-09-16 05:49:58.000|YES         |               |NO         |
    76527|         76558|SK   |P_TAB1    |P2000         |PARTITION  |    1000|2024-09-16 05:50:46.000|YES         |               |NO         |
    76528|         76559|SK   |P_TAB1    |P3000         |PARTITION  |    1000|2024-09-16 05:49:58.000|YES         |               |NO         |
    76529|         76593|SK   |P_TAB1    |P_MAX         |PARTITION  |       0|2024-09-16 06:52:59.000|YES         |               |NO         |

5 row(s) fetched.

-- 인덱스 통계정보
SELECT 
b.object_id, b.data_object_id, a.owner, index_name, partition_name, a.object_type, num_rows, last_analyzed, global_stats, stattype_locked, stale_stats
FROM dba_ind_statistics a, dba_objects b
WHERE 1=1
AND a.owner = b.owner(+)
AND a.table_name = b.object_name(+)
AND a.partition_name = b.subobject_name(+)
AND a.owner = 'SK'
ORDER BY partition_position NULLS FIRST


OBJECT_ID|DATA_OBJECT_ID|OWNER|INDEX_NAME|PARTITION_NAME|OBJECT_TYPE|NUM_ROWS|LAST_ANALYZED          |GLOBAL_STATS|STATTYPE_LOCKED|STALE_STATS|
---------+--------------+-----+----------+--------------+-----------+--------+-----------------------+------------+---------------+-----------+
         |              |SK   |P_TAB2_GBL|              |INDEX      |    3000|2024-09-16 05:50:46.000|YES         |               |YES        |
         |              |SK   |P_TAB2_LOC|              |INDEX      |    3000|2024-09-16 05:50:46.000|YES         |               |YES        |
    76526|         76557|SK   |P_TAB2_LOC|P1000         |PARTITION  |    1000|2024-09-16 05:49:58.000|YES         |               |NO         |
    76527|         76558|SK   |P_TAB2_LOC|P2000         |PARTITION  |    1000|2024-09-16 05:50:46.000|YES         |               |NO         |
    76528|         76559|SK   |P_TAB2_LOC|P3000         |PARTITION  |    1000|2024-09-16 05:49:58.000|YES         |               |NO         |
    76529|         76593|SK   |P_TAB2_LOC|P_MAX         |PARTITION  |       0|2024-09-16 06:52:59.000|YES         |               |NO         |

6 row(s) fetched.

-- 파티션 SPLIT
ALTER TABLE sk.P_TAB1 SPLIT PARTITION P_MAX AT (4001) INTO (PARTITION P4000, PARTITION P_MAX) 

0 row(s) modified.

-- 테이블 통계정보
SELECT 
b.object_id, b.data_object_id, a.owner, table_name, partition_name, a.object_type, num_rows, last_analyzed, global_stats, stattype_locked, stale_stats
FROM dba_tab_statistics a, dba_objects b
WHERE 1=1
AND a.owner = b.owner(+)
AND a.table_name = b.object_name(+)
AND a.partition_name = b.subobject_name(+)
AND a.owner = 'SK'
ORDER BY partition_position NULLS first


OBJECT_ID|DATA_OBJECT_ID|OWNER|TABLE_NAME|PARTITION_NAME|OBJECT_TYPE|NUM_ROWS|LAST_ANALYZED          |GLOBAL_STATS|STATTYPE_LOCKED|STALE_STATS|
---------+--------------+-----+----------+--------------+-----------+--------+-----------------------+------------+---------------+-----------+
         |              |SK   |P_TAB1    |              |TABLE      |    3000|2024-09-16 05:50:46.000|YES         |               |YES        |
    76526|         76557|SK   |P_TAB1    |P1000         |PARTITION  |    1000|2024-09-16 05:49:58.000|YES         |               |NO         |
    76527|         76558|SK   |P_TAB1    |P2000         |PARTITION  |    1000|2024-09-16 05:50:46.000|YES         |               |NO         |
    76528|         76559|SK   |P_TAB1    |P3000         |PARTITION  |    1000|2024-09-16 05:49:58.000|YES         |               |NO         |
    76596|         76596|SK   |P_TAB1    |P4000         |PARTITION  |        |                       |NO          |               |           |
    76529|         76597|SK   |P_TAB1    |P_MAX         |PARTITION  |        |                       |NO          |               |           |

6 row(s) fetched.

-- 인덱스 통계정보
SELECT 
b.object_id, b.data_object_id, a.owner, index_name, partition_name, a.object_type, num_rows, last_analyzed, global_stats, stattype_locked, stale_stats
FROM dba_ind_statistics a, dba_objects b
WHERE 1=1
AND a.owner = b.owner(+)
AND a.table_name = b.object_name(+)
AND a.partition_name = b.subobject_name(+)
AND a.owner = 'SK'
ORDER BY partition_position NULLS FIRST


OBJECT_ID|DATA_OBJECT_ID|OWNER|INDEX_NAME|PARTITION_NAME|OBJECT_TYPE|NUM_ROWS|LAST_ANALYZED          |GLOBAL_STATS|STATTYPE_LOCKED|STALE_STATS|
---------+--------------+-----+----------+--------------+-----------+--------+-----------------------+------------+---------------+-----------+
         |              |SK   |P_TAB2_GBL|              |INDEX      |    3000|2024-09-16 05:50:46.000|YES         |               |YES        |
         |              |SK   |P_TAB2_LOC|              |INDEX      |    3000|2024-09-16 05:50:46.000|YES         |               |YES        |
    76526|         76557|SK   |P_TAB2_LOC|P1000         |PARTITION  |    1000|2024-09-16 05:49:58.000|YES         |               |NO         |
    76527|         76558|SK   |P_TAB2_LOC|P2000         |PARTITION  |    1000|2024-09-16 05:50:46.000|YES         |               |NO         |
    76528|         76559|SK   |P_TAB2_LOC|P3000         |PARTITION  |    1000|2024-09-16 05:49:58.000|YES         |               |NO         |
    76596|         76596|SK   |P_TAB2_LOC|P4000         |PARTITION  |        |                       |NO          |               |           |
    76529|         76597|SK   |P_TAB2_LOC|P_MAX         |PARTITION  |        |                       |NO          |               |           |

7 row(s) fetched.

통계정보 LOCK 후 동일 테스트 진행

잠금여부와 상관없음


SELECT 
owner, table_name, partition_name, object_type, num_rows, last_analyzed, global_stats, stattype_locked, stale_stats
FROM dba_tab_statistics
WHERE owner = 'SK'
ORDER BY partition_position NULLS first


OWNER|TABLE_NAME|PARTITION_NAME|OBJECT_TYPE|NUM_ROWS|LAST_ANALYZED          |GLOBAL_STATS|STATTYPE_LOCKED|STALE_STATS|
-----+----------+--------------+-----------+--------+-----------------------+------------+---------------+-----------+
SK   |P_TAB1    |              |TABLE      |    3000|2024-09-16 05:50:46.000|YES         |ALL            |NO         |
SK   |P_TAB1    |P1000         |PARTITION  |    1000|2024-09-16 05:49:58.000|YES         |ALL            |NO         |
SK   |P_TAB1    |P2000         |PARTITION  |    1000|2024-09-16 05:50:46.000|YES         |ALL            |NO         |
SK   |P_TAB1    |P3000         |PARTITION  |    1000|2024-09-16 05:49:58.000|YES         |ALL            |NO         |
SK   |P_TAB1    |P_MAX         |PARTITION  |       0|2024-09-16 06:28:03.000|YES         |ALL            |NO         |

5 row(s) fetched.


SELECT 
owner, index_name, partition_name, object_type, num_rows, last_analyzed, global_stats, stattype_locked, stale_stats
FROM dba_ind_statistics 
WHERE owner = 'SK'
ORDER BY partition_position NULLS FIRST


OWNER|INDEX_NAME|PARTITION_NAME|OBJECT_TYPE|NUM_ROWS|LAST_ANALYZED          |GLOBAL_STATS|STATTYPE_LOCKED|STALE_STATS|
-----+----------+--------------+-----------+--------+-----------------------+------------+---------------+-----------+
SK   |P_TAB2_GBL|              |INDEX      |    3000|2024-09-16 05:50:46.000|YES         |ALL            |NO         |
SK   |P_TAB2_LOC|              |INDEX      |    3000|2024-09-16 05:50:46.000|YES         |ALL            |NO         |
SK   |P_TAB2_LOC|P1000         |PARTITION  |    1000|2024-09-16 05:49:58.000|YES         |ALL            |NO         |
SK   |P_TAB2_LOC|P2000         |PARTITION  |    1000|2024-09-16 05:50:46.000|YES         |ALL            |NO         |
SK   |P_TAB2_LOC|P3000         |PARTITION  |    1000|2024-09-16 05:49:58.000|YES         |ALL            |NO         |
SK   |P_TAB2_LOC|P_MAX         |PARTITION  |       0|2024-09-16 06:28:03.000|YES         |ALL            |NO         |

6 row(s) fetched.

ALTER TABLE sk.P_TAB1 SPLIT PARTITION P_MAX AT (4001) INTO (PARTITION P4000, PARTITION P_MAX) 

0 row(s) modified.


SELECT 
owner, table_name, partition_name, object_type, num_rows, last_analyzed, global_stats, stattype_locked, stale_stats
FROM dba_tab_statistics
WHERE owner = 'SK'
ORDER BY partition_position NULLS first


OWNER|TABLE_NAME|PARTITION_NAME|OBJECT_TYPE|NUM_ROWS|LAST_ANALYZED          |GLOBAL_STATS|STATTYPE_LOCKED|STALE_STATS|
-----+----------+--------------+-----------+--------+-----------------------+------------+---------------+-----------+
SK   |P_TAB1    |              |TABLE      |    3000|2024-09-16 05:50:46.000|YES         |ALL            |NO         |
SK   |P_TAB1    |P1000         |PARTITION  |    1000|2024-09-16 05:49:58.000|YES         |ALL            |NO         |
SK   |P_TAB1    |P2000         |PARTITION  |    1000|2024-09-16 05:50:46.000|YES         |ALL            |NO         |
SK   |P_TAB1    |P3000         |PARTITION  |    1000|2024-09-16 05:49:58.000|YES         |ALL            |NO         |
SK   |P_TAB1    |P4000         |PARTITION  |        |                       |NO          |ALL            |           |
SK   |P_TAB1    |P_MAX         |PARTITION  |        |                       |NO          |ALL            |           |

6 row(s) fetched.


SELECT 
owner, index_name, partition_name, object_type, num_rows, last_analyzed, global_stats, stattype_locked, stale_stats
FROM dba_ind_statistics 
WHERE owner = 'SK'
ORDER BY partition_position NULLS FIRST


OWNER|INDEX_NAME|PARTITION_NAME|OBJECT_TYPE|NUM_ROWS|LAST_ANALYZED          |GLOBAL_STATS|STATTYPE_LOCKED|STALE_STATS|
-----+----------+--------------+-----------+--------+-----------------------+------------+---------------+-----------+
SK   |P_TAB2_GBL|              |INDEX      |    3000|2024-09-16 05:50:46.000|YES         |ALL            |NO         |
SK   |P_TAB2_LOC|              |INDEX      |    3000|2024-09-16 05:50:46.000|YES         |ALL            |NO         |
SK   |P_TAB2_LOC|P1000         |PARTITION  |    1000|2024-09-16 05:49:58.000|YES         |ALL            |NO         |
SK   |P_TAB2_LOC|P2000         |PARTITION  |    1000|2024-09-16 05:50:46.000|YES         |ALL            |NO         |
SK   |P_TAB2_LOC|P3000         |PARTITION  |    1000|2024-09-16 05:49:58.000|YES         |ALL            |NO         |
SK   |P_TAB2_LOC|P4000         |PARTITION  |        |                       |NO          |ALL            |           |
SK   |P_TAB2_LOC|P_MAX         |PARTITION  |        |                       |NO          |ALL            |           |

7 row(s) fetched.

MAX 파티션에 데이터 있는 상태에서 SPLIT

max 파티션의 데이터 신규 파티션으로 이동됨

신규 파티션 data_object_id 재사용되며 테이블 및 인덱스 통계정보 유지됨

MAX 파티션 data_object_id 생성되며 테이블 파티션 통계정보 유지(?) 복사(?) 되었고, 인덱스 파티션의 통계는 null 상태


SELECT count(*) FROM sk.p_tab1 PARTITION(p_max) 

COUNT(*)|
--------+
    1000|

1 row(s) fetched.


SELECT 
b.object_id, b.data_object_id, a.owner, table_name, partition_name, a.object_type, num_rows, last_analyzed, global_stats, stattype_locked, stale_stats
FROM dba_tab_statistics a, dba_objects b
WHERE 1=1
AND a.owner = b.owner(+)
AND a.table_name = b.object_name(+)
AND a.partition_name = b.subobject_name(+)
AND a.owner = 'SK'
ORDER BY partition_position NULLS first


OBJECT_ID|DATA_OBJECT_ID|OWNER|TABLE_NAME|PARTITION_NAME|OBJECT_TYPE|NUM_ROWS|LAST_ANALYZED          |GLOBAL_STATS|STATTYPE_LOCKED|STALE_STATS|
---------+--------------+-----+----------+--------------+-----------+--------+-----------------------+------------+---------------+-----------+
         |              |SK   |P_TAB1    |              |TABLE      |    3000|2024-09-16 05:50:46.000|YES         |               |YES        |
    76526|         76557|SK   |P_TAB1    |P1000         |PARTITION  |    1000|2024-09-16 05:49:58.000|YES         |               |NO         |
    76527|         76558|SK   |P_TAB1    |P2000         |PARTITION  |    1000|2024-09-16 05:50:46.000|YES         |               |NO         |
    76528|         76559|SK   |P_TAB1    |P3000         |PARTITION  |    1000|2024-09-16 05:49:58.000|YES         |               |NO         |
    76529|         76589|SK   |P_TAB1    |P_MAX         |PARTITION  |    1000|2024-09-16 06:50:07.000|YES         |               |NO         |

5 row(s) fetched.


SELECT 
b.object_id, b.data_object_id, a.owner, index_name, partition_name, a.object_type, num_rows, last_analyzed, global_stats, stattype_locked, stale_stats
FROM dba_ind_statistics a, dba_objects b
WHERE 1=1
AND a.owner = b.owner(+)
AND a.table_name = b.object_name(+)
AND a.partition_name = b.subobject_name(+)
AND a.owner = 'SK'
ORDER BY partition_position NULLS FIRST


OBJECT_ID|DATA_OBJECT_ID|OWNER|INDEX_NAME|PARTITION_NAME|OBJECT_TYPE|NUM_ROWS|LAST_ANALYZED          |GLOBAL_STATS|STATTYPE_LOCKED|STALE_STATS|
---------+--------------+-----+----------+--------------+-----------+--------+-----------------------+------------+---------------+-----------+
         |              |SK   |P_TAB2_GBL|              |INDEX      |    3000|2024-09-16 05:50:46.000|YES         |               |YES        |
         |              |SK   |P_TAB2_LOC|              |INDEX      |    3000|2024-09-16 05:50:46.000|YES         |               |YES        |
    76526|         76557|SK   |P_TAB2_LOC|P1000         |PARTITION  |    1000|2024-09-16 05:49:58.000|YES         |               |NO         |
    76527|         76558|SK   |P_TAB2_LOC|P2000         |PARTITION  |    1000|2024-09-16 05:50:46.000|YES         |               |NO         |
    76528|         76559|SK   |P_TAB2_LOC|P3000         |PARTITION  |    1000|2024-09-16 05:49:58.000|YES         |               |NO         |
    76529|         76589|SK   |P_TAB2_LOC|P_MAX         |PARTITION  |    1000|2024-09-16 06:50:07.000|YES         |               |NO         |

6 row(s) fetched.

ALTER TABLE sk.P_TAB1 SPLIT PARTITION P_MAX AT (4001) INTO (PARTITION P4000, PARTITION P_MAX) 

0 row(s) modified.


SELECT 
b.object_id, b.data_object_id, a.owner, table_name, partition_name, a.object_type, num_rows, last_analyzed, global_stats, stattype_locked, stale_stats
FROM dba_tab_statistics a, dba_objects b
WHERE 1=1
AND a.owner = b.owner(+)
AND a.table_name = b.object_name(+)
AND a.partition_name = b.subobject_name(+)
AND a.owner = 'SK'
ORDER BY partition_position NULLS first


OBJECT_ID|DATA_OBJECT_ID|OWNER|TABLE_NAME|PARTITION_NAME|OBJECT_TYPE|NUM_ROWS|LAST_ANALYZED          |GLOBAL_STATS|STATTYPE_LOCKED|STALE_STATS|
---------+--------------+-----+----------+--------------+-----------+--------+-----------------------+------------+---------------+-----------+
         |              |SK   |P_TAB1    |              |TABLE      |    3000|2024-09-16 05:50:46.000|YES         |               |YES        |
    76526|         76557|SK   |P_TAB1    |P1000         |PARTITION  |    1000|2024-09-16 05:49:58.000|YES         |               |NO         |
    76527|         76558|SK   |P_TAB1    |P2000         |PARTITION  |    1000|2024-09-16 05:50:46.000|YES         |               |NO         |
    76528|         76559|SK   |P_TAB1    |P3000         |PARTITION  |    1000|2024-09-16 05:49:58.000|YES         |               |NO         |
    76592|         76589|SK   |P_TAB1    |P4000         |PARTITION  |    1000|2024-09-16 06:50:07.000|YES         |               |NO         |
    76529|         76593|SK   |P_TAB1    |P_MAX         |PARTITION  |    1000|2024-09-16 06:50:07.000|YES         |               |NO         |

6 row(s) fetched.


SELECT 
b.object_id, b.data_object_id, a.owner, index_name, partition_name, a.object_type, num_rows, last_analyzed, global_stats, stattype_locked, stale_stats
FROM dba_ind_statistics a, dba_objects b
WHERE 1=1
AND a.owner = b.owner(+)
AND a.table_name = b.object_name(+)
AND a.partition_name = b.subobject_name(+)
AND a.owner = 'SK'
ORDER BY partition_position NULLS FIRST


OBJECT_ID|DATA_OBJECT_ID|OWNER|INDEX_NAME|PARTITION_NAME|OBJECT_TYPE|NUM_ROWS|LAST_ANALYZED          |GLOBAL_STATS|STATTYPE_LOCKED|STALE_STATS|
---------+--------------+-----+----------+--------------+-----------+--------+-----------------------+------------+---------------+-----------+
         |              |SK   |P_TAB2_GBL|              |INDEX      |    3000|2024-09-16 05:50:46.000|YES         |               |YES        |
         |              |SK   |P_TAB2_LOC|              |INDEX      |    3000|2024-09-16 05:50:46.000|YES         |               |YES        |
    76526|         76557|SK   |P_TAB2_LOC|P1000         |PARTITION  |    1000|2024-09-16 05:49:58.000|YES         |               |NO         |
    76527|         76558|SK   |P_TAB2_LOC|P2000         |PARTITION  |    1000|2024-09-16 05:50:46.000|YES         |               |NO         |
    76528|         76559|SK   |P_TAB2_LOC|P3000         |PARTITION  |    1000|2024-09-16 05:49:58.000|YES         |               |NO         |
    76592|         76589|SK   |P_TAB2_LOC|P4000         |PARTITION  |    1000|2024-09-16 06:50:07.000|YES         |               |NO         |
    76529|         76593|SK   |P_TAB2_LOC|P_MAX         |PARTITION  |        |                       |NO          |               |           |

7 row(s) fetched.

[ORACLE] 테이블 파티션 SPLIT 시 통계정보 null 로 변경됨 테스트

데이터 없는 max 파티션 split

통계정보 LOCK 후 동일 테스트 진행

MAX 파티션에 데이터 있는 상태에서 SPLIT

댓글

댓글 쓰기