oracle表分區和索引分區

一 分區表技術概述

創新互聯是專業的修文網站建設公司，修文接單;提供網站制作、成都網站制作,網頁設計,網站設計,建網站,PHP網站建設等專業做網站服務;采用PHP框架,可快速的進行修文網站開發網頁制作和功能擴展;專業做搜索引擎喜愛的網站,專業的做網站團隊,希望更多企業前來合作!

二 分區索引技術概述

⑴ 本地前綴分區索引

適用場景：

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

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

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

缺點：

主要體現在數據的高可用性方面

當DROP分區后、全局分區索引則全部INVALID、除非REBULID

但數據量越大、重建索引的時間越長

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

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

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

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

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 列作為分區鍵創建按月自增分區；

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

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

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

oracle根據多字段創建分區表

最近有業務場景需要用多個字段做分區表，數據量比較大，保存時間也較長，經過學習與實踐，算是基本完成，以下內容為實踐樣例：

---建表語句

create table t_table

(

areacode varchar2(10),

appdate date,

text varchar(10)

)

partition by range(appdate)--根據字段 appdate 創建主分區

interval(numtoyminterval(1,'MONTH')) --主分區按 月 自動創建分區

subpartition by list(areacode) --再按 地區 創建子分區

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

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分區，之后的自動分區

)

---模擬寫入測試數據

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');

--查詢數據

select * from t_table;

--查詢主分區數據

select *from t_table partition (mainpartition1);

--查詢子分區數據

select *from t_table subpartition (mainpartition1_sub1);

--查看自動創建的主分區

select * from user_tab_partitions where table_name='T_TABLE'

ORACLE表分區

一.表分區策略

1.識別大表

采用ANALYZE TABLE語句進行分析，然后查詢數據字典獲得相應的數據量。

2.大表如何分區

可根據月份，季度以及年份等進行分區；

3.分區的表空間規劃

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

二.創建表分區

a.創建范圍分區的關鍵字是'RANGE'

1.范圍分區

create table ware_retail_part --創建一個描述商品零售的數據表

(

id integer primary key,--銷售編號

retail_date date,--銷售日期

ware_name varchar2(50)--商品名稱

)

partition by range(retail_date)

(

--2011年第一個季度為part_01分區

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

--2011年第二個季度為part_02分區

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

--2011年第三個季度為part_03分區

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

--2011年第四個季度為part_04分區

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

);

2.創建散列分區

3.組合分區：

4.interval 分區

三.創建索引分區

索引分區分為本地索引分區和全局索引分區，全局索引不反應基礎表的結構，要分區只能進行范圍分區。

創建索引分區要參照表分區

四.分區技術簡介

優點：

1.減少維護工作量

2.增強數據的可用性

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

4.提高查詢速度

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

五,管理表分區

1.添加表分區

ALTER TABLE...ALTER PARATITION

2.合并表分區

3.刪除分區

ALTER TABLE...DROP PARTITION

刪除分區時，里面的數據也會被刪除。

-創建表和分區

create table sales--創建一個銷售記錄表

(

id number primary key,--記錄編號

goodsname varchar2(10),--商品名

saledate date--銷售日期

)

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

(

--第一季度數據

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

--第二季度數據

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

--第三季度數據

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

--第四季度數據

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

);

--創建局部索引

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

);

--并入分區

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

--重建局部索引

alter table sales modify partition part_sea4 rebuild unusable local indexes;

六.管理索引分區

刪除索引：DROP PARTITION

重建分區：REBUILT PARTITION

更名索引分區：RENAME PARTITION

分割索引分區：SPLIT PARTITION

oracle11g自動分區

在Oracle10g中，沒有定義間隔分區，只能通過范圍分區實現間隔分區功能，如果要實現自動創建分區，只能通過創建JOB或者scheduler來實現；而在11g中，Oracle直接提供了間隔分區功能，大大簡化了間隔分區的實現。

----注：oracle11g雖然可以自動分區，但是分區的名字不能自定義，對于需要定時刪除分區時沒法處理，不如通過時間范圍來手工分區。詳見

create table HIP_LOG_NODE_Part

(

ID?????????????????? VARCHAR2(32)???????? not null,

RECORD_TIME????????? DATE

)tablespace TB_HIP_LOG_NODE

PARTITION BY RANGE (RECORD_TIME) interval (numtoyminterval(1, 'month'))

STORE IN (TB_HIP_LOG_NODE)

(

partition hip_log_node_partition values less than (to_date('2019-08-01 00:00','yyyy-MM-dd HH24:mi')) tablespace TB_HIP_LOG_NODE

);

1、Oracle11g有間隔分區功能，對于使用Range分區的可以按年，月，日來自動生成分區。

2、2019-08-01前的數據（包含8月份的數據）會放入hip_log_node_partition?分區，8月1日后的數據每月只要有數據，就會自動創建一個分區。也就是從9月開始，開始新建分區。

3、interval函數--將數值按標準換算為日期

numtodsinterval、numtodsinterval函數,將數字轉成年月，時分秒

詳見：

4、查看表分區 select table_name,partition_name from user_tab_partitions where table_name='INTERVAL_SALES';

5、插入數據再次查看分區，詳見：

6、修改分區、合并分區、拆分分區，詳見 ：

7、創建索引（分區索引、全局索引） :

非分區字段創建主鍵，則創建主鍵local索引時必須加上分區字段

ALTER TABLE TEST ADD CONSTRAINT PK_TEST PRIMARY KEY (主鍵字段,分區字段) USING INDEX LOCAL;

8、oracle 10g創建表分區

9、刪除

1.不保留，直接刪除：

alter table table_name drop/truncate partition partition_name;

具體用drop還是truncate，得你自己衡量，drop的話原來的分區和數據直接就沒有了，truncate的話，只是數據沒有了，分區還在。

oracle對現有大表進行分區

有很多種方法可以做分區表轉換，常見的有：

1、CTAS，在建分區表時就把源表數據插進去

2、建分區表，從源表導出，再導入分區表，10g可以用數據泵

3、在線重定義，不影響業務，但速度慢些

2億數據如果按平均行長70也就是十幾g，不會很慢，只要存儲不太差，估計個把小時怎么也完事了。在線重定義比較慢，但一晚上也沒問題，30g的搞過6，7個小時。