************************************************************************ ****原文：blog.csdn.net/clark_xu徐长亮的专栏************************************************************************–(1)分区表包含多个分区，，每个分区都是在一个独立的segment上面，可以存放不同的表空间； –(1.1)官方推荐超过2G的表，需要分区表； –(1.2)表中含有历史数据，新的数据添加到最新的分区；例如11个月的数据只是读，最新1个月的数据为修改；需要用分区表； –(1.3)oracle 10g支持1024K-1个分区；–(2)分区表的优点 –2.1 可以对单独的分区进行备份和恢复 –2.2 分散IO–(3)oracle 10g支持的分区 –范围分区range –哈希分区hash –列表分区list –范围哈希复合分区range-hash –范围列表复合分区range-list–(4)range分区create table rangetable(id number(9),time date) partition by range(time)( partition p1 values less than (to_date(‘2010-2-1′,’yyyy-mm-dd’) ), partition p2 values less than (to_date(‘2010-3-1′,’yyyy-mm-dd’) ), partition p3 values less than (to_date(‘2010-4-1′,’yyyy-mm-dd’) ), partition p4 values less than(maxvalue)); –查看分区表的基本信息 select * from user_part_tables; select table_name,partitioning_type,partition_count from user_part_tables where table_name=’RANGETABLE’; –查看表的分区信息:分区名称，默认的extents个数 select * from user_tab_partitions where table_name=’RANGETABLE’; select table_name,high_value,partition_name from user_tab_partitions –拆分分区表：拆分maxvale/default分区表； alter table rangetable split partition p4 at(to_date(‘2010-5-1′,’yyyy-mm-dd’)) into (partition p5,partition p6) alter table rangetable split partition p6 at(to_date(‘2010-7-1′,’yyyy-mm-dd’)) into (partition p6,partition p7) –插入数据 insert into rangetable values(1,to_date(‘2010-01-01′,’yyyy-mm-dd’)); insert into rangetable values(1,to_date(‘2010-02-01′,’yyyy-mm-dd’)); insert into rangetable values(3,to_date(‘2010-03-01′,’yyyy-mm-dd’)); select * from rangetable; –统计segments select partition_name,count(*) from user_extents where segment_name=’RANGETABLE’ group by partition_name;–(5)list分区 –list分区必须制定列值，列植必须明确；要创建default分区来存储不明确的值； create table listtable ( id number(10) not null, areacode varchar2(20) ) partition by list(areacode) ( partition list_025 values(‘025’), partition list_035 values(‘035’), partition list_045 values(‘045’), partition list_055 values(‘055′), partition list_other values (default) ) –插入数据 insert into listtable values(1,’025′); insert into listtable values(2,’035′); insert into listtable values(3,’045′); insert into listtable values(4,’055′); insert into listtable values(5,’075′); –查看分区信息 select * from user_part_tables; select * from listtable; select * from user_tab_partitions where table_name=’LISTTABLE’; –统计segments select partition_name,count(*) from user_extents where segment_name=’LISTTABLE’ group by partition_name;–(6)hash分区 create table hashtable ( id number(9), areacode varchar2(10) ) partition by hash(areacode) partitions 5; –插入数据 insert into hashtable values(1,’025′); insert into hashtable values(2,’035′); insert into hashtable values(3,’045′); insert into hashtable values(4,’055′); insert into hashtable values(5,’075′); commit; –统计segments select partition_name,count(*) from user_extents where segment_name=’HASHTABLE’ group by partition_name; select * from dba_extents where segment_name=’HASHTABLE’;************************************************************************ ****原文：blog.csdn.net/clark_xu徐长亮的专栏************************************************************************
绊脚石乃是进身之阶。
