联系:手机/微信(+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
参考:【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)