何時用，如何建Oracle分區(qū)表

一、什么場景、時間適合用分區(qū)表？

創(chuàng)新互聯(lián)專注于縉云網(wǎng)站建設(shè)服務(wù)及定制，我們擁有豐富的企業(yè)做網(wǎng)站經(jīng)驗。 熱誠為您提供縉云營銷型網(wǎng)站建設(shè)，縉云網(wǎng)站制作、縉云網(wǎng)頁設(shè)計、縉云網(wǎng)站官網(wǎng)定制、小程序設(shè)計服務(wù)，打造縉云網(wǎng)絡(luò)公司原創(chuàng)品牌,更為您提供縉云網(wǎng)站排名全網(wǎng)營銷落地服務(wù)。

1、大數(shù)據(jù)量的表，比如大于2GB。一方面2GB文件對于32位OS是一個上限，另外備份時間長。

2、包括歷史數(shù)據(jù)的表，比如最新的數(shù)據(jù)放入到最新的分區(qū)中。典型的例子：歷史表，只有當(dāng)前月份的數(shù)據(jù)可以被修改，而其他月份只能read-only

就我實際工作中了解到的幾種情況：

全國法人單位名錄庫約有700-800萬條記錄，字段約有100個?？砂此鶎偈⌒姓^(qū)劃、指標(biāo)枚舉等字段建立分區(qū)表。

北京統(tǒng)計宏觀數(shù)據(jù)庫，在村一級采用一維表存儲，隨著指標(biāo)增多，記錄可達千萬至億條級。

上海市房管局系統(tǒng)的權(quán)屬數(shù)據(jù)，按照市中心及19個區(qū)縣，共劃分了20個分區(qū)，以期提高數(shù)據(jù)的存取效率。

二、分區(qū)表有什么優(yōu)勢？

1、增強可用性：如果表的某個分區(qū)出現(xiàn)故障，表在其他分區(qū)的數(shù)據(jù)仍然可用；

2、維護方便：如果表的某個分區(qū)出現(xiàn)故障，需要修復(fù)數(shù)據(jù)，只修復(fù)該分區(qū)即可；

3、均衡I/O：可以把不同的分區(qū)映射到磁盤以平衡I/O，改善整個系統(tǒng)性能；

4、改善查詢性能：對分區(qū)對象的查詢可以僅搜索自己關(guān)心的分區(qū)，提高檢索速度。

oracle根據(jù)多字段創(chuàng)建分區(qū)表

最近有業(yè)務(wù)場景需要用多個字段做分區(qū)表，數(shù)據(jù)量比較大，保存時間也較長，經(jīng)過學(xué)習(xí)與實踐，算是基本完成，以下內(nèi)容為實踐樣例：

---建表語句

create table t_table

(

areacode varchar2(10),

appdate date,

text varchar(10)

)

partition by range(appdate)--根據(jù)字段 appdate 創(chuàng)建主分區(qū)

interval(numtoyminterval(1,'MONTH')) --主分區(qū)按 月 自動創(chuàng)建分區(qū)

subpartition by list(areacode) --再按 地區(qū) 創(chuàng)建子分區(qū)

subpartition template( --指定明確的子分區(qū)信息

subpartition sub1 values('101'),

subpartition sub2 values('201'),

subpartition sub3 values('301')

)

(

partition mainpartition1 values less than(to_date('2019-04-01','yyyy-mm-dd'))--2019年4月1日前的放入mainpartition1分區(qū)，之后的自動分區(qū)

)

---模擬寫入測試數(shù)據(jù)

insert into t_table values('101',to_date('2019-03-03','yyyy-mm-dd'),'a');

insert into t_table values('101',to_date('2019-02-03','yyyy-mm-dd'),'a');

insert into t_table values('101',to_date('2019-04-03','yyyy-mm-dd'),'a');

insert into t_table values('201',to_date('2019-03-03','yyyy-mm-dd'),'a');

insert into t_table values('201',to_date('2019-05-03','yyyy-mm-dd'),'a');

insert into t_table values('301',to_date('2019-04-01','yyyy-mm-dd'),'a');

--查詢數(shù)據(jù)

select * from t_table;

--查詢主分區(qū)數(shù)據(jù)

select *from t_table partition (mainpartition1);

--查詢子分區(qū)數(shù)據(jù)

select *from t_table subpartition (mainpartition1_sub1);

--查看自動創(chuàng)建的主分區(qū)

select * from user_tab_partitions where table_name='T_TABLE'

Oracle創(chuàng)建分區(qū)表操作

1、創(chuàng)建語句

create table p(id number)

partition by range(id)

(partition p1 values less than(100) tablespace t1,

partition p2 values less than(200) tablespace t2,

partition p3 values less than(300) tablespace t3);

2、添加分區(qū)

alter table p add partition p4 values less than (400) tablespace t4;

3、清除分區(qū)數(shù)據(jù)

alter table p trunc partition p1;

4、刪除分區(qū)

alter table p drop partition p1;

ORACLE表分區(qū)

一.表分區(qū)策略

1.識別大表

采用ANALYZE TABLE語句進行分析，然后查詢數(shù)據(jù)字典獲得相應(yīng)的數(shù)據(jù)量。

2.大表如何分區(qū)

可根據(jù)月份，季度以及年份等進行分區(qū)；

3.分區(qū)的表空間規(guī)劃

要對每個表空間的大小進行估計

二.創(chuàng)建表分區(qū)

a.創(chuàng)建范圍分區(qū)的關(guān)鍵字是'RANGE'

1.范圍分區(qū)

create table ware_retail_part --創(chuàng)建一個描述商品零售的數(shù)據(jù)表

(

id integer primary key,--銷售編號

retail_date date,--銷售日期

ware_name varchar2(50)--商品名稱

)

partition by range(retail_date)

(

--2011年第一個季度為part_01分區(qū)

partition par_01 values less than(to_date('2011-04-01','yyyy-mm-dd')) tablespace TEMP01,

--2011年第二個季度為part_02分區(qū)

partition par_02 values less than(to_date('2011-07-01','yyyy-mm-dd')) tablespace TEMP01,

--2011年第三個季度為part_03分區(qū)

partition par_03 values less than(to_date('2011-10-01','yyyy-mm-dd')) tablespace TEMP01,

--2011年第四個季度為part_04分區(qū)

partition par_04 values less than(to_date('2012-01-01','yyyy-mm-dd')) tablespace TEMP01

);

2.創(chuàng)建散列分區(qū)

3.組合分區(qū)：

4.interval 分區(qū)

三.創(chuàng)建索引分區(qū)

索引分區(qū)分為本地索引分區(qū)和全局索引分區(qū)，全局索引不反應(yīng)基礎(chǔ)表的結(jié)構(gòu)，要分區(qū)只能進行范圍分區(qū)。

創(chuàng)建索引分區(qū)要參照表分區(qū)

四.分區(qū)技術(shù)簡介

優(yōu)點：

1.減少維護工作量

2.增強數(shù)據(jù)的可用性

3.均衡I/O,提升性能

4.提高查詢速度

5.分區(qū)對用戶保持透明，用戶感覺不到分區(qū)的存在。

五,管理表分區(qū)

1.添加表分區(qū)

ALTER TABLE...ALTER PARATITION

2.合并表分區(qū)

3.刪除分區(qū)

ALTER TABLE...DROP PARTITION

刪除分區(qū)時，里面的數(shù)據(jù)也會被刪除。

-創(chuàng)建表和分區(qū)

create table sales--創(chuàng)建一個銷售記錄表

(

id number primary key,--記錄編號

goodsname varchar2(10),--商品名

saledate date--銷售日期

)

partition by range(saledate)--按照日期分區(qū)

(

--第一季度數(shù)據(jù)

partition part_sea1 values less than(to_date('2011-04-01','yyyy-mm-dd')) tablespace tbsp_1,

--第二季度數(shù)據(jù)

partition part_sea2 values less than(to_date('2011-07-01','yyyy-mm-dd')) tablespace tbsp_2,

--第三季度數(shù)據(jù)

partition part_sea3 values less than(to_date('2011-10-01','yyyy-mm-dd')) tablespace tbsp_1,

--第四季度數(shù)據(jù)

partition part_sea4 values less than(to_date('2012-01-01','yyyy-mm-dd')) tablespace tbsp_2

);

--創(chuàng)建局部索引

create index index_3_4 on sales(saledate)

local(

partition part_seal tablespace tbsp_1,

partition part_sea2 tablespace tbsp_2,

partition part_sea3 tablespace tbsp_1,

partition part_sea4 tablespace tbsp_2

);

--并入分區(qū)

alter table sales merge partitions part_sea3,part_sea4 into partition part_sea4;

--重建局部索引

alter table sales modify partition part_sea4 rebuild unusable local indexes;

六.管理索引分區(qū)

刪除索引：DROP PARTITION

重建分區(qū)：REBUILT PARTITION

更名索引分區(qū)：RENAME PARTITION

分割索引分區(qū)：SPLIT PARTITION

oracle 表分區(qū)

范圍(range)分區(qū):

一::對于分區(qū)表,如果where條件種沒有分區(qū)列,那么oracle會掃描所有的分區(qū),然后做PARTITION RANGE

ALL 操作,這樣成本將比未分區(qū)的全表掃描稍微高點,因為需要合并各個分區(qū).

二:范圍分區(qū)可以用values less than (maxvalue)增加一個默認(rèn)分區(qū),maxvalue 常量表示該分區(qū)用來存放所有其

他分區(qū)無法存放的記錄,

三:范圍分區(qū)可以對各種謂詞做分區(qū)消除,包括=,,,等比hash,和list分區(qū)要靈活

oracle表分區(qū)和索引分區(qū)

一 分區(qū)表技術(shù)概述

二 分區(qū)索引技術(shù)概述

⑴ 本地前綴分區(qū)索引

適用場景：

如果歷史數(shù)據(jù)整理非常頻繁、而且不能承受全局分區(qū)索引重建的長時間帶來的索引不可用

同時、日常交易性能尚能接受、則建議設(shè)計為本地非前綴分區(qū)索引

注意：本地分區(qū)索引不能保證唯一性（除非分區(qū)鍵是約束的一部分）

缺點：

主要體現(xiàn)在數(shù)據(jù)的高可用性方面

當(dāng)DROP分區(qū)后、全局分區(qū)索引則全部INVALID、除非REBULID

但數(shù)據(jù)量越大、重建索引的時間越長

一般來講，如果需要將數(shù)據(jù)按照某個值邏輯聚集，多采用范圍分區(qū)。如基于時間數(shù)據(jù)的按“年”、“月”等分區(qū)就是很典型的例子。在許多情況下，范圍分區(qū)都能利用到分區(qū)消除特性（ = = = between…and 等篩選條件下）。

如果在表里無法找到一個合適的屬性來按這個屬性完成范圍分區(qū)，但你又想享受分區(qū)帶來的性能與可用性的提升，則可以考慮使用散列分區(qū)。（適合使用 = IN 等篩選條件）

如果數(shù)據(jù)中有一列或有一組離散值，且按這一列進行分區(qū)很有意義，則這樣的數(shù)據(jù)就很適合采用列表分區(qū)。

如果某些數(shù)據(jù)邏輯上可以進行范圍分區(qū)，但是得到的范圍分區(qū)還是太大，不能有效管理，則可以考慮使用組合分區(qū)（范圍分區(qū)+hash 或范圍分區(qū)+列表分區(qū)）。

create table products_table

(

id number(2),

name varchar2(50),

sale_date date

)

partition by range(sale_date)

interval (numtoyminterval(1,'month'))

(

partition p_month_1 values less than (to_date('2016-01-01','yyyy-mm-dd'))

)

如圖，取 products_table 中的 sale_date 列作為分區(qū)鍵創(chuàng)建按月自增分區(qū)；

所有銷售時間在 ‘2016-01-01’之前的記錄都會被放入 p_month_1 分區(qū)；

銷售時間在‘2016-01-01’之后的記錄在插入時Oracle會自動創(chuàng)建記錄所屬月的分區(qū)；

比如當(dāng)有銷售時間分別為 2016年1月20日 與 2016年2月20日 的兩條記錄插入時，Oracle會分別創(chuàng)建一個上限值為 ‘2016-01-31’的分區(qū)和一個上限值為‘2016-02-29’的分區(qū)來存儲這兩條記錄