oracle分區表

標籤：物理   命令   sel   to_date   ESS   modify   drop   div   而且   

何時該分區

1. 大於2GB的表
2. 含有1000萬條記錄以上的表，表中含有的資料越多，SQL操作的執行速度就會越慢。
3. 將會含有大量資料的表。
4. 強行拆分後可利於並行操作的表。
5. 含有需要定期歸檔日誌或刪除部分的表

分區表的優點

1. 由於將資料分散到各個區中，減少損壞的可能性

2. 可以對單獨的分區進行備份和恢複

3. 可以將分區映射到不同的物理磁碟上，來分散I/O

4. 提高可管理性，可用性

分區表策略分類

範圍 --> 根據日期,數值或字元建立分區
列表 --> 根據列表值建立分區
散列 --> 在沒有明顯分區鍵的情況下，以均分方式建立分區
組合 --> 組合使用多種分區方式
間隔 --> 當新分區索引值超出現存最大範圍時，通過自動分配新分區擴充定界分割
引用 --> 根據父表列為子表建立分區
虛擬列分區 --> 在虛擬列上建立分區
系統 --> 根據插入資料的應用程式建立分區

常用的分區類型為：範圍、列表和散列

 查詢資料量排行前十的表

一條可靠的原則就是大於2GB的表就可以應用資料分割函數。運行下面的查詢命令可以顯示資料庫中佔用空間的排名情況：select * from (select owner,segment_name,segment_type,partition_name,sum(bytes)/1024/1024 meg_totfrom dba_segments group by owner,segment_name,segment_type,partition_nameorder by sum(extents) desc)where rownum <=10 

 定界分割

-- 建立一個普通表的語句create table person1 (id int primary key, name varchar2(20), birth date);-- 資料將會在同一個資料表空間同一個段內insert into person1 values (1, ‘sss‘, sysdate); -- 建立一個分區表-- 這裡是按照生日進行定界分割-- 語句的基本格式就是在普通建表的語句上，增加 partition by 語句塊create table person2 (name varchar2(20), birth date)partition by range (birth)(  partition p1 values less than (to_date(‘19950101‘,‘yyyymmdd‘)),  -- ‘values less than‘  partition p2 values less than (to_date(‘20000101‘,‘yyyymmdd‘)),  partition p3 values less than (maxvalue)                         -- 預設分區);-- 插入，資料會根據分區的情況進入不同的分區內insert into person2 values (‘張三‘, to_date(‘19940707‘));insert into person2 values (‘李四‘, to_date(‘19980707‘));insert into person2 values (‘王五‘, to_date(‘20040707‘));-- 查詢表中所有資料select * from person2;-- 查詢特定分區上資料select * from person2 partition (p3);  -- 可以為不同的分區指定不同的資料表空間-- 沒有指定資料表空間的分區，使用使用者的預設資料表空間-- 所以，一個表內的資料可以存在於不同資料表空間裡，也就是可以存放在不同資料檔案中，不同磁碟上-- 因此，分區表能增強資料的安全性create table person3 (name varchar2(20), birth date)partition by range (birth)(  partition p1 values less than (to_date(‘19950101‘,‘yyyymmdd‘)) tablespace system,  partition p2 values less than (to_date(‘20000101‘,‘yyyymmdd‘)) tablespace sysaux,  partition p3 values less than (maxvalue) tablespace users);  -- 可以在其他類型上進行定界分割-- 也可以在多個欄位上進行定界分割create table person4 (name varchar2(20), birth date, score number)partition by range (birth, score)(  partition p1 values less than (to_date(‘19900101‘,‘yyyymmdd‘), 60),  partition p2 values less than (to_date(‘19900101‘,‘yyyymmdd‘), 90),  partition p3 values less than (to_date(‘19990101‘,‘yyyymmdd‘), 60),  partition p4 values less than (to_date(‘19990101‘,‘yyyymmdd‘), 90),  partition p5 values less than (maxvalue, maxvalue));

 列表分區

-- 如果是生日的這樣的欄位，資料是連續的，應該使用分為分區create table person (name varchar2(20), birth date)partition by range(birth)(  partition p1 values less than (to_date(‘19900101‘, ‘yyyymmdd‘)) tablespace users,  partition p2 values less than (maxvalue));insert into person values (‘aaa‘, to_date(‘19871212‘, ‘yyyymmdd‘));select * from person partition (p1);  /* where birth between 1987 and 1990 where sex in (‘男‘, ‘女‘)*/ -- 但是像性別、民族等欄位，更適合使用的是列表分區-- 下面一個例子，使用性別作為分區欄位，男的一個區，女的一個區create table person2 (name varchar2(20), sex varchar(10))partition by list (sex)(    partition p1 values (‘男‘),    partition p2 values (‘女‘));insert into person2 values (‘aaa‘, ‘男‘);insert into person2 values (‘bbb‘, ‘女‘);insert into person2 values (‘ccc‘, ‘未知‘);  -- 報錯select * from person2 partition (p2); -- 預設分區的寫法create table person3 (name varchar2(20), sex varchar(10))partition by list (sex)(    partition p1 values (‘男‘),    partition p2 values (‘女‘),    partition p3 values (default));insert into person3 values (‘ccc‘, ‘未知‘);select * from person3 partition (p3);  -- 可以為每個分區指定資料表空間create table person3 (name varchar2(20), sex varchar(10))partition by list (sex)(    partition p1 values (‘男‘) tablespace users,    partition p2 values (‘女‘) tablespace system,    partition p3 values (default));

 雜湊分割

-- 雜湊分割-- 主要用在一些比較離散，不好分類的資料上，比如產品名字-- 讓 oracle 使用雜湊演算法自動計算資料的分區 -- 建立語句，非常簡單create table person4 (name varchar2(20), sex varchar2(10))partition by hash (name)(  partition p1,  partition p2 tablespace users);insert into person4 values (‘aaa‘, ‘男‘);insert into person4 values (‘收款‘, ‘男‘);select * from person4 partition (p1); -- 上面的語句可以進一步簡化為：create table person5 (name varchar2(20), sex varchar2(10))partition by hash (name)partitions 5; -- 為每個分區指定資料表空間create table person6 (name varchar2(20), sex varchar2(10))partition by hash (name)partitions 3 store in (users, system, sysaux);

 範圍與雜湊組合

-- 先按照生日，將資料分為三個區-- 然後在每個分區內，又按照雜湊演算法分成了三個區-- 這樣就保證了每個分區內的資料盡量的少，而且分區進行平衡create table person7 (name varchar2(20), birth date)partition by range (birth)subpartition by hash (name) subpartitions 3(  partition p1 values less than (to_date(‘19900101‘, ‘yyyymmdd‘)),  partition p2 values less than (to_date(‘20000101‘, ‘yyyymmdd‘)),  partition p3 values less than (maxvalue));

 非分區錶轉分區表

 12c以前非分區表需要轉換為分區, 如果不停業務的話可以使用線上重定義,只有在表進行切換的時候會有短暫的鎖表。 12c 中alter table online clause 實現了表上現有的索引有效,又不停業務。

-- oracle 12ccreate table person7 (name varchar2(20), birth date);--將非分區錶轉換為分區表，使用雜湊alter table person7 modifypartition by hash (name)(  partition ph1,  partition ph2) onlineupdate indexes;

操作分區

-- 添加分區alter table person add partition p9 values less than (MAXVALUE);alter table person add partition p9 values (1, 2);   -- 針對 list 分區alter table person add partition;                    -- 針對 hash 分區 -- 刪除分區alter table person drop partition p3; -- 刪除分區內資料alter table person truncate partition p3; -- 合并相鄰分區alter table person merge partitions p2, p3 into partition p8; -- 拆分分區alter table person split partition p2 at (3000) into (partition p3, partition p14);     -- 定界分割的拆分alter table person split partition p2 values (1,2) into (partition p3, partition p4);   -- 列表分區的拆分alter table person split partition p2 into (partition p3 values (1, 2), partition p4 values (3), partition p5);   -- 列表分區的拆分 -- 重新命名分區alter table person rename partition p2 to p12;

 

oracle分區表