联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
在11.2.0.3(从11.2.0.2开始)创建分区表,每个分区默认大小为8M,是由_partition_large_extents参数控制,可以算是11.2.0.2开始的一个新特性,为了减少extent数量,提高分区表性能,而设置的一个参数,默认为true，即分区表的每个extent为8M,这里对于_partition_large_extents为true和false的情况进行了测试
_partition_large_extents=true
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> show parameter deferred_segment_creation
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation boolean TRUE
SQL> show parameter _partition_large_extents;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_partition_large_extents string FALSE
SQL> create table test_com_partition_1
2 (
3 name varchar2(4000) not null,
4 aaaaa number not null,
5 bbbbb varchar2(180) not null,
6 ccccc varchar2(4000),
7 constraint pk_test_com_partition_1 primary key(name)
8 )
9 partition by range(aaaaa) interval (1)
10 subpartition by range (bbbbb)
11 subpartition template
12 (
13 subpartition sp_2008 values less than ('2009') tablespace sp_2008,
14 subpartition sp_2009 values less than ('2010') tablespace sp_2009,
15 subpartition sp_2010 values less than ('2011') tablespace sp_2010,
16 subpartition sp_2011 values less than ('2012') tablespace sp_2011,
17 subpartition sp_2012 values less than ('2013') tablespace sp_2012,
18 subpartition sp_2013 values less than ('2014') tablespace sp_2013,
19 subpartition sp_2014 values less than ('2015') tablespace sp_2014,
20 subpartition sp_2015 values less than ('2016') tablespace sp_2015,
21 subpartition sp_2016 values less than ('2017') tablespace sp_2016,
22 subpartition sp_2017 values less than ('2018') tablespace sp_2017,
23 subpartition sp_2018 values less than ('2019') tablespace sp_2018,
24 subpartition sp_2019 values less than ('2020') tablespace sp_2019,
25 subpartition sp_2020 values less than ('2021') tablespace sp_2020,
26 subpartition sp_2021 values less than ('2022') tablespace sp_2021,
27 subpartition sp_2022 values less than ('2023') tablespace sp_2022,
28 subpartition sp_2023 values less than ('2024') tablespace sp_2023,
29 subpartition sp_2024 values less than ('2025') tablespace sp_2024,
30 subpartition sp_2025 values less than ('2026') tablespace sp_2025,
31 subpartition sp_max values less than (maxvalue) tablespace sp_max
32 )
33 (partition part_init values less than (1))
34 enable row movement;
Table created.
--数据库延迟对象创建
SQL> select TABLESPACE_NAME,sum(bytes)/1024/1024 from dba_segments where tablespace_name
2 like 'SP%' group by TABLESPACE_NAME;
no rows selected
--只插入一个分区1,2013
SQL> insert into test_com_partition_1 values (lpad('xifenfei',3900,'wwww.xifenfei'),1,'2013',
2 rpad('aaafdfafd',4000,'b'));
1 row created.
SQL> commit;
Commit complete.
--所有分区全部都创建了segment
SQL> select TABLESPACE_NAME,sum(bytes)/1024/1024 from dba_segments where tablespace_name like 'SP%'
2 group by TABLESPACE_NAME;
TABLESPACE_NAME SUM(BYTES)/1024/1024
------------------------------ --------------------
SP_2018 8
SP_2022 8
SP_2021 8
SP_2025 8
SP_2011 8
SP_2008 8
SP_MAX 8
SP_2020 8
SP_2012 8
SP_2010 8
SP_2024 8
SP_2019 8
SP_2015 8
SP_2014 8
SP_2013 8
SP_2023 8
SP_2017 8
SP_2016 8
SP_2009 8
19 rows selected.
SQL> begin
2 for i in 3 .. 200 loop
3 insert into test_com_partition_1 values (to_char(i)||lpad('xifenfei',3900,'wwww.xifenfei'),mod(i,5),
'2013',rpad('xifenfei',4000,'www.xifenfei.com'));
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
--只是在2013的分区(1,子分区2013)中插入了对象,但是其他分区也都创建了segment(extent)
SQL> select TABLESPACE_NAME,sum(bytes)/1024/1024 from dba_segments where tablespace_name
2 like 'SP%' group by TABLESPACE_NAME;
TABLESPACE_NAME SUM(BYTES)/1024/1024
------------------------------ --------------------
SP_2018 32
SP_2021 32
SP_2022 32
SP_2008 32
SP_2011 32
SP_2025 32
SP_2010 32
SP_2012 32
SP_2020 32
SP_MAX 32
SP_2015 32
SP_2019 32
SP_2024 32
SP_2013 40
SP_2014 32
SP_2023 32
SP_2009 32
SP_2016 32
SP_2017 32
19 rows selected.
SQL> select PARTITION_NAME,TABLESPACE_NAME from dba_segments where TABLESPACE_NAME='SP_2015';
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SYS_SUBP128 SP_2015
SYS_SUBP148 SP_2015
SYS_SUBP168 SP_2015
SYS_SUBP188 SP_2015
--因为在创建表语句中有partition part_init values less than (1)，隐藏之类对于小于1的分区没有子分区，只有PART_INIT_SP_2013
SQL> select PARTITION_NAME,TABLESPACE_NAME from dba_segments where TABLESPACE_NAME='SP_2013';
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
PART_INIT_SP_2013 SP_2013
SYS_SUBP126 SP_2013
SYS_SUBP146 SP_2013
SYS_SUBP166 SP_2013
SYS_SUBP186 SP_2013
_partition_large_extents=false
SQL> alter system set "_partition_large_extents"=false;
System altered.
SQL> show parameter _partition_large_extents
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_partition_large_extents string FALSE
SQL> drop table test_com_partition_1 purge;
Table dropped.
SQL> alter system set deferred_segment_creation=true;
System altered.
SQL> create table test_com_partition_1
2 (
3 name varchar2(4000) not null,
4 aaaaa number not null,
5 bbbbb varchar2(180) not null,
6 ccccc varchar2(4000),
7 constraint pk_test_com_partition_1 primary key(name)
8 )
9 partition by range(aaaaa) interval (1)
10 subpartition by range (bbbbb)
11 subpartition template
12 (
13 subpartition sp_2008 values less than ('2009') tablespace sp_2008,
14 subpartition sp_2009 values less than ('2010') tablespace sp_2009,
15 subpartition sp_2010 values less than ('2011') tablespace sp_2010,
16 subpartition sp_2011 values less than ('2012') tablespace sp_2011,
17 subpartition sp_2012 values less than ('2013') tablespace sp_2012,
18 subpartition sp_2013 values less than ('2014') tablespace sp_2013,
19 subpartition sp_2014 values less than ('2015') tablespace sp_2014,
20 subpartition sp_2015 values less than ('2016') tablespace sp_2015,
21 subpartition sp_2016 values less than ('2017') tablespace sp_2016,
22 subpartition sp_2017 values less than ('2018') tablespace sp_2017,
23 subpartition sp_2018 values less than ('2019') tablespace sp_2018,
24 subpartition sp_2019 values less than ('2020') tablespace sp_2019,
25 subpartition sp_2020 values less than ('2021') tablespace sp_2020,
26 subpartition sp_2021 values less than ('2022') tablespace sp_2021,
27 subpartition sp_2022 values less than ('2023') tablespace sp_2022,
28 subpartition sp_2023 values less than ('2024') tablespace sp_2023,
29 subpartition sp_2024 values less than ('2025') tablespace sp_2024,
30 subpartition sp_2025 values less than ('2026') tablespace sp_2025,
31 subpartition sp_max values less than (maxvalue) tablespace sp_max
32 )
33 (partition part_init values less than (1))
34 enable row movement;
Table created.
SQL> select TABLESPACE_NAME,sum(bytes)/1024/1024 from dba_segments
2 where tablespace_name like 'SP%' group by TABLESPACE_NAME;
no rows selected
SQL> insert into test_com_partition_1 values (lpad('xifenfei',3900,'wwww.xifenfei'),
2 1,'2013',rpad('aaafdfafd',4000,'b'));
1 row created.
SQL> commit;
Commit complete.
SQL> select TABLESPACE_NAME,sum(bytes)/1024/1024 from dba_segments where tablespace_name
2 like 'SP%' group by TABLESPACE_NAME;
Tablespace SUM(BYTES)/1024/1024
-------------------- --------------------
SP_2018 .0625
SP_2021 .0625
SP_2022 .0625
SP_2008 .0625
SP_2011 .0625
SP_2025 .0625
SP_2010 .0625
SP_2012 .0625
SP_2020 .0625
SP_MAX .0625
SP_2015 .0625
SP_2019 .0625
SP_2024 .0625
SP_2013 .0625
SP_2014 .0625
SP_2023 .0625
SP_2009 .0625
SP_2016 .0625
SP_2017 .0625
19 rows selected.
SQL> select PARTITION_NAME,TABLESPACE_NAME from dba_segments where TABLESPACE_NAME='SP_2015';
Partition Name Tablespace
-------------------- --------------------
SYS_SUBP328 SP_2015
SQL> select PARTITION_NAME,TABLESPACE_NAME from dba_segments where TABLESPACE_NAME='SP_2013';
Partition Name Tablespace
-------------------- --------------------
SYS_SUBP326 SP_2013
SQL> begin
2 for i in 3 .. 2000 loop
3 insert into test_com_partition_1 values (to_char(i)||lpad('xifenfei',3900,'wwww.xifenfei'),
mod(i,5),'2013',rpad('xifenfei',4000,'www.xifenfei.com'));
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> select PARTITION_NAME,TABLESPACE_NAME from dba_segments where TABLESPACE_NAME='SP_2015';
Partition Name Tablespace
-------------------- --------------------
SYS_SUBP328 SP_2015
SYS_SUBP348 SP_2015
SYS_SUBP368 SP_2015
SYS_SUBP388 SP_2015
SQL> select PARTITION_NAME,TABLESPACE_NAME from dba_segments where TABLESPACE_NAME='SP_2013';
Partition Name Tablespace
-------------------- --------------------
PART_INIT_SP_2013 SP_2013
SYS_SUBP326 SP_2013
SYS_SUBP346 SP_2013
SYS_SUBP366 SP_2013
SYS_SUBP386 SP_2013
通过测试证明,设置_partition_large_extents参数确实是能够控制分区表的extent大小,而且对于分区表,deferred_segment_creation虽然为true，但是在一个分区表中如果有一个子分区插入了记录,那么其他子分区会同时创建segment.对于数据量不多，而且数据大量集中在某几个分区,那强烈建议设置_partition_large_extents为false,节约空间.如果数据量较大,而且数据分布较为均匀,建议设置_partition_large_extents为true.另外对于分区的index也有同样的参数为_index_partition_large_extents
加我QQ
参考:【11gR2新特性】Large Partition Extents:_partition_large_extents & _index_partition_large_extents
参考MOS:Initial Extent Size of a Partition Changed to 8MB from 64KB After Upgrade to 11.2.0.2 or Later (Doc ID 1295484.1)