记录一次大表分区引起存储空间膨胀使用的问题

有这样一个表A(id number(10,0), bid number(10,0),userid varchar2(20),dat1 timestamp,group_id number(10,0);

上面有8亿数据，47G左右，表上没有任何索引，平常处理数据都是使用group_id来过滤，group_id有15000个左右，每年增长几千个。

我们知道oracle分区表最多支持1048575个分区，每年增长几千个，需要100年才会达到上限，因此我们确定的分区方案是按group_id作为分区键，做间隔为1的间隔分区表。

结果是我们把数据迁移到分区表后，发现分区表占用空间达到了160G，比原来大了2倍多，差点儿把表空间给撑到告警，有点儿心惊胆战，事后对此事做个总结：

为什么空间会比非分区表大那么多，原因是忽略了分区表默认分区大小。

Oracle11G新特性：分区表分区默认segment大小64k变为8M

从11.2.0.2开始创建分区表,每个分区默认大小为8M,是由_partition_large_extents参数控制,可以算是11.2.0.2开始的一个新特性,为了减少extent数量,提高分区表性能,而引入的一个参数,默认为true，即分区表的每个extent为8M。

如果分区键列上数据分布有倾斜，那数据量少的分区上将会造成很大的空间浪费，试想8M的空间上只存储几条数据，如果有5000个分区这样，那会浪费多大的空间。

同样地索引分区受参数_index_partition_large_extents的控制，而当_index_partition_large_extents为true时，创建分区索引时，默认分区大小为8m，而创建普通索引默认大小为64k。_index_partition_large_extents默认为false。

因此在以数值类型的列作为分区键时，在计划做成间隔分区时，需要考虑以下因素：

1. 查询3个参数的设置：

(1)查询_partition_large_extents、_index_partition_large_extents的设置，这两个参数为隐藏参数，查询脚本如下：

SELECT i.ksppinm name, CV.ksppstvl VALUE

  FROM sys.x$ksppi i, sys.x$ksppcv CV

 WHERE i.inst_id = USERENV('Instance')

   AND CV.inst_id = USERENV('Instance')

   AND i.indx = CV.indx

   AND i.ksppinm in

       ('_partition_large_extents', '_index_partition_large_extents');

(2)查询参数deferred_segment_creation

这个参数为延迟段创建，如果为true，表示创建表的分区时，如果该分区不插入数据，将不会分配段空间。

2. 如果_partition_large_extents、_index_partition_large_extents均为true，要考虑该分区键值得分布。如果存在倾斜，建议创建表时指定storage 属性，默认分区大小设置小点儿。如果数据量比较大，且分布比较均匀，可以使用默认设置。

创建分区表指定storage属性参考如下：

SCOTT@PROD1>create table t1220(id number(10),col1 varchar2(20),group_id number(10))

   storage (initial 64k next 1m)

   partition by range(group_id)

   interval (1)

  (partition p12200 values less than (0),

  partition p12201 values less than (4) );

Table created.

SCOTT@PROD1>select segment_name,partition_name,bytes/1024 kb,blocks from user_segments where segment_name='T1220';

no rows selected

SCOTT@PROD1>insert into t1220 values(1,'aa',5);

1 row created.

SCOTT@PROD1>commit;

Commit complete.

SCOTT@PROD1>select segment_name,partition_name,bytes/1024 kb,blocks from user_segments where segment_name='T1220';

SEGMENT_NAME         PARTITION_NAME                      KB        BLOCKS

--------------------          ------------------------------ -                 ---------      ----------

T1220                SYS_P141                               64          8

发现插入一条数据后，段分配的第一个extent，有8个块，占64KB的空间。

当然建表时没有指定storage属性，表建成后也是可以修改默认属性的。

SCOTT@PROD1>alter table t1220 modify default attributes storage (initial 128k next 1m);

Table altered.

SCOTT@PROD1>insert into t1220 values(2,'bb',6);

1 row created.

SCOTT@PROD1>commit;

Commit complete.

SCOTT@PROD1>select segment_name,partition_name,bytes/1024 kb,blocks from user_segments where segment_name='T1220';

SEGMENT_NAME         PARTITION_NAME                         KB     BLOCKS

-------------------- ------------------------------ ---------- ----------

T1220                SYS_P141                               64          8

T1220                SYS_P142                              128         16

对于日期类型列为分区键，如果按月分区，总分区数也比较有限，可以不用考虑太多，而且一般这种分区方式数据分布也比较均匀。

另外：SYS用户下对象是不支持延迟段功能的。

SYS@PROD1>create table t1220_1(id number(10),col1 varchar2(20),group_id number(10))

  2  storage (initial 64k next 1m)

  3  partition by range(group_id)

  4  interval (1)

  5  (partition p122010 values less than (0) segment creation deferred,

  6   partition p122011 values less than (4) segment creation deferred

  7  );

create table t1220_1(id number(10),col1 varchar2(20),group_id number(10))

*

ERROR at line 1:

ORA-14223: Deferred segment creation is not supported for this table