【oracle】分區表:range分區,list分區,hash分區,rangehash
************************************************************************ ****原文: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 徐長亮的專欄************************************************************************