Oracle查询分区是否压缩,分区表及其索引的压缩属性

一.分区表部分

二.分区上索引部分

一.分区表部分

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.分区表级索引的压缩属性只能在创建时指定，分区级索引的压缩属性可以修改，但从"非压缩"改为"压缩"时，要满足分区级索引已启用压缩的前提；反过来，从"压缩"改为"非压缩"则没有要求