SCOTT@test01p> @ver
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
PARTITION_NAME COMPRESS ROWS_PER_BLK
------------------------------ -------- ------------
P1 DISABLED 101.841558
SYS_P421 DISABLED 80
SYS_P422 DISABLED 80
SYS_P423 DISABLED 80
SYS_P424 DISABLED 80
SYS_P425 DISABLED 80
SYS_P426 DISABLED 80
SYS_P427 DISABLED 80
SYS_P428 DISABLED 80
SYS_P429 DISABLED 80
SYS_P430 DISABLED 80
SYS_P431 DISABLED 30.4230769
12 rows selected.
PARTITION_NAME COMPRESS ROWS_PER_BLK
------------------------------ -------- ------------
P1 ENABLED 445.556818
SYS_P421 ENABLED 206.896552
SYS_P422 ENABLED 206.896552
SYS_P423 ENABLED 206.896552
SYS_P424 ENABLED 206.896552
SYS_P425 ENABLED 206.896552
SYS_P426 ENABLED 206.896552
SYS_P427 ENABLED 206.896552
SYS_P428 ENABLED 206.896552
SYS_P429 ENABLED 206.896552
SYS_P430 ENABLED 206.896552
SYS_P431 ENABLED 39.55
--可以发现选择压缩后,每块占用的记录数明显增加。
12 rows selected.
col x new_value p1
select partition_name from user_tab_partitions where partition_position = 4;
PARTITION_NAME
------------------------------
SYS_P423
col x new_value p2
select partition_name from user_tab_partitions where partition_position = 5;
PARTITION_NAME
------------------------------
SYS_P424
PARTITION_NAME COMPRESS ROWS_PER_BLK
------------------------------ -------- ------------
NEW_PAR DISABLED 91.6030534
SYS_P425 ENABLED 206.896552
SYS_P428 ENABLED 206.896552
P1 ENABLED 445.556818
SYS_P427 ENABLED 206.896552
SYS_P422 ENABLED 206.896552
SYS_P429 ENABLED 206.896552
SYS_P430 ENABLED 206.896552
SYS_P421 ENABLED 206.896552
SYS_P426 ENABLED 206.896552
SYS_P431 ENABLED 39.55
11 rows selected.
PARTITION_NAME COMPRESS ROWS_PER_BLK
------------------------------ -------- ------------
P1 ENABLED 445.556818
SYS_P441 ENABLED 206.896552
SYS_P442 ENABLED 206.896552
SYS_P443 ENABLED 206.896552
SYS_P444 ENABLED 206.896552
SYS_P445 ENABLED 206.896552
SYS_P446 ENABLED 206.896552
SYS_P447 ENABLED 206.896552
SYS_P448 ENABLED 206.896552
SYS_P449 ENABLED 206.896552
SYS_P450 ENABLED 206.896552
SYS_P451 ENABLED 39.55
12 rows selected.
col x new_value p1
select partition_name from user_tab_partitions where partition_position = 4;
PARTITION_NAME
------------------------------
SYS_P443
col x new_value p2
select partition_name from user_tab_partitions where partition_position = 5;
PARTITION_NAME
------------------------------
SYS_P444
PARTITION_NAME COMPRESS ROWS_PER_BLK
------------------------------ -------- ------------
SYS_P442 ENABLED 206.896552
P1 ENABLED 445.556818
SYS_P448 ENABLED 206.896552
SYS_P446 ENABLED 206.896552
SYS_P450 ENABLED 206.896552
NEW_PAR ENABLED 300
SYS_P445 ENABLED 206.896552
SYS_P441 ENABLED 206.896552
SYS_P447 ENABLED 206.896552
SYS_P451 ENABLED 39.55
SYS_P449 ENABLED 206.896552
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-772031/,如需转载,请注明出处,否则将追究法律责任。