分区表: 范围分区、列表分区、HASH分区、组合分区 1.范围分区 drop table range_part_tab purge; create table range_part_tab (id number,deal_date date,area_code number,contents varchar2(4000)) partition by range (deal_date) ( partition p1 values less than (TO_DATE('2012-02-01','YYYY-MM-DD')), partition p2 values less than (TO_DATE('2012-03-01','YYYY-MM-DD')), partition p3 values less than (TO_DATE('2012-04-01','YYYY-MM-DD')), partition p4 values less than (TO_DATE('2012-05-01','YYYY-MM-DD')), partition p5 values less than (TO_DATE('2012-06-01','YYYY-MM-DD')), partition p6 values less than (TO_DATE('2012-07-01','YYYY-MM-DD')), partition p7 values less than (TO_DATE('2012-08-01','YYYY-MM-DD')), partition p8 values less than (TO_DATE('2012-09-01','YYYY-MM-DD')), partition p9 values less than (TO_DATE('2012-10-01','YYYY-MM-DD')), partition p10 values less than (TO_DATE('2012-11-01','YYYY-MM-DD')), partition p11 values less than (TO_DATE('2012-12-01','YYYY-MM-DD')), partition p12 values less than (TO_DATE('2013-01-01','YYYY-MM-DD')), partition p_max values less than (maxvalue) 超出范围的放在此分区,避免出错。总共建立了13个分区。 ) ;
insert into range_part_tab (id,deal_date,area_code,contents) select rownum, to_date(to_char(sysdate-365,'J')+TRUNC(DBMS_RANDOM.VALUE(0,365)),'J'), ceil(dbms_random.value(590,599)), rpad('*',400,'*') from dual connet by rownum<=100000; 插入2012年一整年日期随机数和福建地区号含义(591到599)的随机数记录。 commit; 2.列表分区
drop table list_part_tab purge; create table list_part_tab (id number,deal_date date,area_code number,contents varchar2(4000)) partiton by list(area_code) 列表分区 ( partition p_591 values (591), 仅需 values 即可确定范围,数值也可为多个 partition p_592 values (592), partition p_593 values (593), partition p_594 values (594), partition p_595 values (595), partition p_596 values (596), partition p_597 values (597), partition p_598 values (598), partition p_599 values (599), partition p_other values (DEFAULT), 共10个分区 ,其余分区 ) ; insert into list_part_tab (id,deal_date,area_code,contents) select rownum, to_date(to_char(sysdate-365,'J')+TRUNC(DBMS_RANDOM.VALUES(0,365)),'J'), ceil(dbms_random.value(590,599)), rpad('*',400,'*') from dual connect by rownum<=100000; 插入2012一整年日期随机数和表示福建地区号含义(591到599)的随机数记录 commit;
3.散列分区 ( HASH分区 )
drop table hash_part_tab purge; create table hash_part_tab (id number,deal_date date,area_code number,contents varchar2(4000)) partition by hash(deal_date) 散列分区 partitions 12 分区个数,偶数 ; insert into hash_part_tab(id,deal_date,area_code,contents) select rownum, to_date(to_char(sysdate-365,'J')+TRUNC(DBMS_RANDOM.VALUE(0,365)),'J'), ceil(dbms_random.value(590,599)), rpad('*',400,'*') from dual connect by rownum<=100000; commit;
散列分区没有指定分区名,只指定了分区个数partitions 12
4.组合分区
drop table range_list_part_tab purge; create table range_list_part_tab (id numberm,deal_date date,area_code number,contents varchar2(4000)) partition by range(deal_date) subpartition by list (area_code) subupartition by TEMPLATE (subpartition p_591 values (591), subpartition p_592 values (592), subpartition p_593 values (593), subpartition p_594 values (594), subpartition p_595 values (595), subpartition p_596 values (596), subpartition p_597 values (597), subpartition p_598 values (598), subpartition p_599 values (599), subpartition p_other values (DEFAULT)) ( partition p1 values less than (TO_DATE('2012-02-01','YYYY-MM-DD')), partition p2 values less than (TO_DATE('2012-03-01','YYYY-MM-DD')), partition p3 values less than (TO_DATE('2012-04-01','YYYY-MM-DD')), partition p4 values less than (TO_DATE('2012-05-01','YYYY-MM-DD')), partition p5 values less than (TO_DATE('2012-06-01','YYYY-MM-DD')), partition p6 values less than (TO_DATE('2012-07-01','YYYY-MM-DD')), partition p7 values less than (TO_DATE('2012-08-01','YYYY-MM-DD')), partition p8 values less than (TO_DATE('2012-09-01','YYYY-MM-DD')), partition p9 values less than (TO_DATE('2012-10-01','YYYY-MM-DD')), partition p10 values less than (TO_DATE('2012-11-01','YYYY-MM-DD')), partition p11 values less than (TO_DATE('2012-12-01','YYYY-MM-DD')), partition p12 values less than (TO_DATE('2013-01-01','YYYY-MM-DD')), partition p_max values less than (maxvalue) ) ; insert into range_list_tab(id,deal_date,area_code,contents) select rownum, to_date(to_char(sysdate-365,'J')+TRUNC(DBMS_RANDOM.VALUE(0,365)),'J'), ceil(dbms_random.value(590,599)), rpad('*',400,'*') from dual connect by rownum<=100000; commit;
5.与普通表插入相比
drop table norm_tab purge; create table norm_tab (id number,deal_date date,area_code number,contents varchar2(4000));
insert into norm_tab (id,deal_date,area_code,contents) select rownum, to_date(to_char(sysdate-365,'J')+TRUNC(DBMS_RANDOM.VALUE(0,365)),'J'), ceil(dbms_random.value(590,599)), rpad('*',400,'*') from dual connect by rownum<=100000; commit;
set linesize 666 set pagesize 5000 column segment_name format a20 column partition_name format a20 column segment_type format a20
select segment_name, partition_name, segment_type, bytes/1024/1024, tablespace_name from user_segments where segment_name in('range_part_tab','norm_tab');
结论: 分区表产生多个segment,普通表只产生一个segment。 分区表可以分配在不同的表空间
6.观察HASH分区的段分配情况 set linesize 666 set pagesize 5000 column segment_name format a20 column partition_name format a20 column segment_type format a20 select segment_name, partition_name, segment_type, bytes/1024/1024, tablespace_name from user_segments where segment_name in('HASH_PART_TAB');
hash分区,无法把指定的数据到指定的分区去,不利于索引。 hash算法,均匀分布到不同的分区中,避免查询数据时集中到一个地方、可以精确匹配,无法范围扫描。
7.观察组合分区的段分配的个数 select count(*) from user_segments where segment_name='RANGE_LIST_PART_TAB';
组合分区产生了很多的user_segment 段
|