二.分区上索引部分
一.分区表部分
A>create table t_part(id int)
2 partition by range(id)
3 (
4 partition p1 values less than(100),
5 partition p2 values less than(200),
6 partition p3 values less than(300)
7 )
8 /
Table created.
A>select TABLE_NAME,PARTITION_NAME,COMPRESSION from user_tab_partitions;
TABLE_NAME PARTITION_NAME COMPRESS
------------------------------ ------------------------------ --------
T_PART P1 DISABLED
T_PART P3 DISABLED
T_PART P2 DISABLED
A>create table t_part2(id int)
2 partition by range(id)
3 (
4 partition p1 values less than(100),
5 partition p2 values less than(200) compress,
6 partition p3 values less than(300) compress
7 )
8 /
Table created.
A>select TABLE_NAME,PARTITION_NAME,COMPRESSION from user_tab_partitions order by 1,2;
TABLE_NAME PARTITION_NAME COMPRESS
------------------------------ ------------------------------ --------
T_PART P1 DISABLED
T_PART P2 DISABLED
T_PART P3 DISABLED
T_PART2 P1 DISABLED
T_PART2 P2 ENABLED
T_PART2 P3 ENABLED
6 rows selected.
1.分区默认不压缩
2.分区的压缩属性可以在创建表的时候就指定
A>alter table t_part2 modify partition p1 compress;
Table altered.
A>select TABLE_NAME,PARTITION_NAME,COMPRESSION from user_tab_partitions order by 1,2;
TABLE_NAME PARTITION_NAME COMPRESS
------------------------------ ------------------------------ --------
T_PART P1 DISABLED
T_PART P2 DISABLED
T_PART P3 DISABLED
T_PART2 P1 ENABLED
T_PART2 P2 ENABLED
T_PART2 P3 ENABLED
6 rows selected.
3.分区表创建好以后，可以用alter table xxx modify partition xxx的方式来修改分区的压缩属性
A>select TABLE_NAME,COMPRESSION from user_tables;
TABLE_NAME COMPRESS
------------------------------ --------
T_PART
T_PART2
4.即使分区表中各个分区的属性现在看来都一样，但在查询分区表的压缩属性时，Oracle都显示为空，因为各个分区存在压缩属性不一样的可能性，所以oracle在这留空。而普通的表不管是否压缩都会显式地显示出来
A>create table t(id int);
Table created.
A>create table t_comp(id int) compress;
Table created.
A>select TABLE_NAME,COMPRESSION from user_tables;
TABLE_NAME COMPRESS
------------------------------ --------
T_PART
T_PART2
T_COMP ENABLED
T DISABLED
5.修改分区表的压缩属性会影响到每个分区
A>alter table t_part2 nocompress;
Table altered.
A>select TABLE_NAME,PARTITION_NAME,COMPRESSION from user_tab_partitions order by 1,2;
TABLE_NAME PARTITION_NAME COMPRESS
------------------------------ ------------------------------ --------
T_PART P1 DISABLED
T_PART P2 DISABLED
T_PART P3 DISABLED
T_PART2 P1 DISABLED
T_PART2 P2 DISABLED
T_PART2 P3 DISABLED
6 rows selected.
A>alter table t_part2 compress;
Table altered.
A>select TABLE_NAME,PARTITION_NAME,COMPRESSION from user_tab_partitions order by 1,2;
TABLE_NAME PARTITION_NAME COMPRESS
------------------------------ ------------------------------ --------
T_PART P1 DISABLED
T_PART P2 DISABLED
T_PART P3 DISABLED
T_PART2 P1 ENABLED
T_PART2 P2 ENABLED
T_PART2 P3 ENABLED
6 rows selected.
二.分区上索引部分
A>create index t_part_ind on t_part(id) local;
Index created.
A>select INDEX_NAME,COMPRESSION from user_indexes;
INDEX_NAME COMPRESS
------------------------------ --------
T_PART_IND DISABLED
A>select INDEX_NAME,PARTITION_NAME,COMPRESSION from user_ind_partitions;
INDEX_NAME PARTITION_NAME COMPRESS
------------------------------ ------------------------------ --------
T_PART_IND P1 DISABLED
T_PART_IND P2 DISABLED
T_PART_IND P3 DISABLED
A>create index t_part2_ind on t_part2(id) local compress;
Index created.
A>select INDEX_NAME,COMPRESSION from user_indexes;
INDEX_NAME COMPRESS
------------------------------ --------
T_PART_IND DISABLED
T_PART2_IND ENABLED
A>select INDEX_NAME,PARTITION_NAME,COMPRESSION from user_ind_partitions;
INDEX_NAME PARTITION_NAME COMPRESS
------------------------------ ------------------------------ --------
T_PART_IND P1 DISABLED
T_PART_IND P2 DISABLED
T_PART_IND P3 DISABLED
T_PART2_IND P1 ENABLED
T_PART2_IND P2 ENABLED
T_PART2_IND P3 ENABLED
6 rows selected.
1.分区表上的索引，及各个分区上的索引，其压缩属性默认也是禁用的
2.分区上索引的压缩属性也是继承自分区索引
3.分区表及其分区上索引的压缩属性可以在创建分区索引时就指定
A>alter index t_part2_ind modify partition p1 nocompress;
Index altered.
A>select INDEX_NAME,PARTITION_NAME,COMPRESSION from user_ind_partitions;
INDEX_NAME PARTITION_NAME COMPRESS
------------------------------ ------------------------------ --------
T_PART_IND P1 DISABLED
T_PART_IND P2 DISABLED
T_PART_IND P3 DISABLED
T_PART2_IND P2 ENABLED
T_PART2_IND P3 ENABLED
T_PART2_IND P1 DISABLED
6 rows selected.
4.分区上索引的压缩属性也可以单独修改
A>alter index t_part_ind modify partition p1 nocompress;
Index altered.
A>alter index t_part_ind modify partition p1 compress;
alter index t_part_ind modify partition p1 compress
*
ERROR at line 1:
ORA-28659: COMPRESS must be specified at object level first
A>alter index t_part2_ind modify partition p1 compress;
Index altered.
5.经过比较可以看出，分区上索引的压缩属性修改为"不压缩"都没问题，但修改成"压缩"时有一个分区表报错，原因可以从以下看出来
A>select INDEX_NAME,COMPRESSION from user_indexes;
INDEX_NAME COMPRESS
------------------------------ --------
T_PART_IND DISABLED
T_PART2_IND ENABLED
这就是报错的原因：索引T_PART2_IND的压缩属性已经是ENABLED，所以怎么改都没问题，但索引T_PART_IND目前是DISABLED，如果想让分区表上分区索引变成"ENABLED"，必须先让分区表级索引变成ENABLED，像T_PART2_IND一样才行
A>alter index t_part_ind compress;
alter index t_part_ind compress
*
ERROR at line 1:
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option
试了一下，好像没有这个语法，只能删除重建索引了
A>create index t_part_ind on t_part(id) local compress;
Index created.
A>alter index t_part_ind modify partition p1 compress;
Index altered.
A>alter index t_part_ind modify partition p1 nocompress;
Index altered.
现在就随心所欲了
三.总结
1.无论分区表还是索引，默认都不压缩
2.分区是否压缩在创建时可以单独指定
3.如果对分区表压缩属性进行修改，那分区会继承这一修改后的属性
4.分区表的压缩属性可以修改，但查询不到；索引可以修改也可以看得到
5.分区表级索引的压缩属性只能在创建时指定，分区级索引的压缩属性可以修改，但从"非压缩"改为"压缩"时，要满足分区级索引已启用压缩的前提；反过来，从"压缩"改为"非压缩"则没有要求
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29646619/viewspace-1167377/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/29646619/viewspace-1167377/
994
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
