在大型的企業(yè)應(yīng)用或企業(yè)級的數(shù)據(jù)庫應(yīng)用中 要處理的數(shù)據(jù)量通?？梢赃_到幾十到幾百GB 有的甚至可以到TB級 雖然存儲介質(zhì)和數(shù)據(jù)處理技術(shù)的發(fā)展也很快 但是仍然不能滿足用戶的需求 為了使用戶的大量的數(shù)據(jù)在讀寫操作和查詢中速度更快 Oracle提供了對表和索引進行分區(qū)的技術(shù) 以改善大型應(yīng)用系統(tǒng)的性能
讓客戶滿意是我們工作的目標(biāo)，不斷超越客戶的期望值來自于我們對這個行業(yè)的熱愛。我們立志把好的技術(shù)通過有效、簡單的方式提供給客戶，將通過不懈努力成為客戶在信息化領(lǐng)域值得信任、有價值的長期合作伙伴，公司提供的服務(wù)項目有：國際域名空間、網(wǎng)站空間、營銷軟件、網(wǎng)站建設(shè)、霞浦網(wǎng)站維護、網(wǎng)站推廣。
使用分區(qū)的優(yōu)點
·增強可用性 如果表的某個分區(qū)出現(xiàn)故障 表在其他分區(qū)的數(shù)據(jù)仍然可用
·維護方便 如果表的某個分區(qū)出現(xiàn)故障 需要修復(fù)數(shù)據(jù) 只修復(fù)該分區(qū)即可
·均衡I/O 可以把不同的分區(qū)映射到磁盤以平衡I/O 改善整個系統(tǒng)性能
·改善查詢性能 對分區(qū)對象的查詢可以僅搜索自己關(guān)心的分區(qū) 提高檢索速度
Oracle數(shù)據(jù)庫提供對表或索引的分區(qū)方法有三種
·范圍分區(qū)
·Hash分區(qū)（散列分區(qū)）
·復(fù)合分區(qū)
下面將以實例的方式分別對這三種分區(qū)方法來說明分區(qū)表的使用 為了測試方便 我們先建三個表空間
以下為引用的內(nèi)容
create tablespace dinya_space
datafile /test/demo/oracle/demodata/dinya dnf size M
create tablespace dinya_space
datafile /test/demo/oracle/demodata/dinya dnf size M
create tablespace dinya_space
datafile /test/demo/oracle/demodata/dinya dnf size M
分區(qū)表的創(chuàng)建
范圍分區(qū)
范圍分區(qū)就是對數(shù)據(jù)表中的某個值的范圍進行分區(qū) 根據(jù)某個值的范圍 決定將該數(shù)據(jù)存儲在哪個分區(qū)上 如根據(jù)序號分區(qū) 根據(jù)業(yè)務(wù)記錄的創(chuàng)建日期進行分區(qū)等
需求描述 有一個物料交易表 表名 material_transactions 該表將來可能有千萬級的數(shù)據(jù)記錄數(shù) 要求在建該表的時候使用分區(qū)表 這時候我們可以使用序號分區(qū)三個區(qū) 每個區(qū)中預(yù)計存儲三千萬的數(shù)據(jù) 也可以使用日期分區(qū) 如每五年的數(shù)據(jù)存儲在一個分區(qū)上
根據(jù)交易記錄的序號分區(qū)建表 以下為引用的內(nèi)容
SQL create table dinya_test
(
transaction_id number primary key
item_id number( ) not null
item_description varchar ( )
transaction_date date not null
)
partition by range (transaction_id)
(
partition part_ values less than( ) tablespace dinya_space
partition part_ values less than( ) tablespace dinya_space
partition part_ values less than(maxvalue) tablespace dinya_space
);
Table created
建表成功 根據(jù)交易的序號 交易ID在三千萬以下的記錄將存儲在第一個表空間dinya_space 中 分區(qū)名為:par_ 在三千萬到六千萬之間的記錄存儲在第二個表空間
dinya_space 中 分區(qū)名為 par_ 而交易ID在六千萬以上的記錄存儲在第三個表空間dinya_space 中 分區(qū)名為par_
根據(jù)交易日期分區(qū)建表
以下為引用的內(nèi)容
SQL create table dinya_test
(
transaction_id number primary key
item_id number( ) not null
item_description varchar ( )
transaction_date date not null
)
partition by range (transaction_date)
(
partition part_ values less than(to_date( yyyy mm dd ))
tablespace dinya_space
partition part_ values less than(to_date( yyyy mm dd ))
tablespace dinya_space
partition part_ values less than(maxvalue) tablespace dinya_space
);
Table created
這樣我們就分別建了以交易序號和交易日期來分區(qū)的分區(qū)表 每次插入數(shù)據(jù)的時候 系統(tǒng)將根據(jù)指定的字段的值來自動將記錄存儲到制定的分區(qū)（表空間）中
當(dāng)然 我們還可以根據(jù)需求 使用兩個字段的范圍分布來分區(qū) 如partition
by range ( transaction_id transaction_date)
分區(qū)條件中的值也做相應(yīng)的改變 請讀者自行測試
Hash分區(qū)（散列分區(qū)）
散列分區(qū)為通過指定分區(qū)編號來均勻分布數(shù)據(jù)的一種分區(qū)類型 因為通過在I/O設(shè)備上進行散列分區(qū) 使得這些分區(qū)大小一致 如將物料交易表的數(shù)據(jù)根據(jù)交易ID散列地存放在指定的三個表空間中
以下為引用的內(nèi)容
SQL create table dinya_test
(
transaction_id number primary key
item_id number( ) not null
item_description varchar ( )
transaction_date date
)
partition by hash(transaction_id)
(
partition part_ tablespace dinya_space
partition part_ tablespace dinya_space
partition part_ tablespace dinya_space
);
Table created
建表成功 此時插入數(shù)據(jù) 系統(tǒng)將按transaction_id將記錄散列地插入三個分區(qū)中 這里也就是三個不同的表空間中
復(fù)合分區(qū)
有時候我們需要根據(jù)范圍分區(qū)后 每個分區(qū)內(nèi)的數(shù)據(jù)再散列地分布在幾個表空間中 這樣我們就要使用復(fù)合分區(qū) 復(fù)合分區(qū)是先使用范圍分區(qū) 然后在每個分區(qū)內(nèi)再使用散列分區(qū)的一種分區(qū)方法 如將物料交易的記錄按時間分區(qū) 然后每個分區(qū)中的數(shù)據(jù)分三個子分區(qū) 將數(shù)據(jù)散列地存儲在三個指定的表空間中
以下為引用的內(nèi)容
SQL create table dinya_test
(
transaction_id number primary key
item_id number( ) not null
item_description varchar ( )
transaction_date date
)
partition by range(transaction_date)subpartition by hash(transaction_id)
subpartitions store in (dinya_space dinya_space dinya_space )
(
partition part_ values less than(to_date( yyyy mm dd ))
partition part_ values less than(to_date( yyyy mm dd ))
partition part_ values less than(maxvalue)
);
Table created
該例中 先是根據(jù)交易日期進行范圍分區(qū) 然后根據(jù)交易的ID將記錄散列地存儲在三個表空間中
分區(qū)表操作
以上了解了三種分區(qū)表的建表方法 下面將使用實際的數(shù)據(jù)并針對按日期的范圍分區(qū)來測試分區(qū)表的數(shù)據(jù)記錄的操作
插入記錄
以下為引用的內(nèi)容
SQL insert into dinya_test values( BOOKS sysdate);
row created
SQL insert into dinya_test values( BOOKS sysdate+ );
row created
SQL insert into dinya_test values( BOOKS to_date( yyyy mm dd ));
row created
SQL insert into dinya_test values( BOOKS to_date( yyyy mm dd ));
row created
SQL insert into dinya_test values( BOOKS to_date( yyyy mm dd ));
row created
SQL insert into dinya_test values( BOOKS to_date( yyyy mm dd ));
row created
SQL mit;
Commit plete
SQL
按上面的建表結(jié)果 年前的數(shù)據(jù)將存儲在第一個分區(qū)part_ 上 而 年到 年的交易數(shù)據(jù)將存儲在第二個分區(qū)part_ 上 年以后的記錄存儲在第三個分區(qū)part_ 上
查詢分區(qū)表記錄 以下為引用的內(nèi)容
SQL select * from dinya_test partition(part_ );
TRANSACTION_ID ITEM_ID ITEM_DESCRIPTION TRANSACTION_DATE
BOOKS : :
BOOKS : :
SQL
SQL select * from dinya_test partition(part_ );
TRANSACTION_ID ITEM_ID ITEM_DESCRIPTION TRANSACTION_DATE
BOOKS
BOOKS
SQL
SQL select * from dinya_test partition(part_ );
TRANSACTION_ID ITEM_ID ITEM_DESCRIPTION TRANSACTION_DATE
BOOKS
BOOKS
SQL
從查詢的結(jié)果可以看出 插入的數(shù)據(jù)已經(jīng)根據(jù)交易時間范圍存儲在不同的分區(qū)中 這里是指定了分區(qū)的查詢 當(dāng)然也可以不指定分區(qū) 直接執(zhí)行select * from dinya_test查詢?nèi)坑涗?/p>
在也檢索的數(shù)據(jù)量很大的時候 指定分區(qū)會大大提高檢索速度
更新分區(qū)表的記錄
以下為引用的內(nèi)容
SQL update dinya_test partition(part_ ) t set em_description= DESK where
t transaction_id= ;
row updated
SQL mit;
Commit plete
SQL
這里將第一個分區(qū)中的交易ID= 的記錄中的item_description字段更新為 DESK 可以看到已經(jīng)成功更新了一條記錄 但是當(dāng)更新的時候指定了分區(qū) 而根據(jù)查詢的記錄不在該分區(qū)中時 將不會更新數(shù)據(jù) 請看下面的例子 以下為引用的內(nèi)容
SQL update dinya_test partition(part_ ) t set em_description= DESK where
t transaction_id= ;
rows updated
SQL mit;
Commit plete
SQL
指定了在第一個分區(qū)中更新記錄 但是條件中限制交易ID為 而查詢?nèi)?交易ID為 的記錄在第三個分區(qū)中 這樣該條語句將不會更新記錄
刪除分區(qū)表記錄
以下為引用的內(nèi)容
SQL delete from dinya_test partition(part_ ) t where t transaction_id= ;
row deleted
SQL mit;
Commit plete
SQL
上面例子刪除了第二個分區(qū)part_ 中的交易記錄ID為 的一條記錄 和更新數(shù)據(jù)相同 如果指定了分區(qū) 而條件中的數(shù)據(jù)又不在該分區(qū)中時 將不會刪除任何數(shù)據(jù)
分區(qū)表索引的使用
分區(qū)表和一般表一樣可以建立索引 分區(qū)表可以創(chuàng)建局部索引和全局索引 當(dāng)分區(qū)中出現(xiàn)許多事務(wù)并且要保證所有分區(qū)中的數(shù)據(jù)記錄的唯一性時采用全局索引
局部索引分區(qū)的建立
以下為引用的內(nèi)容
SQL create index dinya_idx_t on dinya_test(item_id)
local
(
partition idx_ tablespace dinya_space
partition idx_ tablespace dinya_space
partition idx_ tablespace dinya_space
);
Index created
SQL
看查詢的執(zhí)行計劃 從下面的執(zhí)行計劃可以看出 系統(tǒng)已經(jīng)使用了索引
以下為引用的內(nèi)容
SQL select * from dinya_test partition(part_ ) t where em_id= ;
Execution Plan
SELECT STATEMENT Optimizer=CHOOSE (Cost= Card= Bytes= )
TABLE ACCESS (BY LOCAL INDEX ROWID) OF DINYA_TEST (Cost=
Card= Bytes= )
INDEX (RANGE SCAN) OF DINYA_IDX_T (NON UNIQUE) (Cost=
Card= )
Statistics
recursive calls
db block gets
consistent gets
physical reads
redo size
bytes sent via SQL*Net to client
bytes received via SQL*Net from client
SQL*Net roundtrips to/from client
sorts (memory)
sorts (disk)
rows processed
SQL
全局索引分區(qū)的建立
全局索引建立時global 子句允許指定索引的范圍值 這個范圍值為索引字段的范圍值
以下為引用的內(nèi)容
SQL create index dinya_idx_t on dinya_test(item_id)
global partition by range(item_id)
(
partition idx_ values less than ( ) tablespace dinya_space
partition idx_ values less than ( ) tablespace dinya_space
partition idx_ values less than (maxvalue) tablespace dinya_space
);
Index created
SQL
本例中對表的item_id字段建立索引分區(qū) 當(dāng)然也可以不指定索引分區(qū)名直接對整個表建立索引 如
以下為引用的內(nèi)容
SQL create index dinya_idx_t on dinya_test(item_id);
Index created
SQL
同樣的 對全局索引根據(jù)執(zhí)行計劃可以看出索引已經(jīng)可以使用
以下為引用的內(nèi)容
SQL select * from dinya_test t where em_id= ;
Execution Plan
SELECT STATEMENT Optimizer=CHOOSE (Cost= Card= Bytes= )
TABLE ACCESS (BY GLOBAL INDEX ROWID) OF DINYA_TEST (Cost
= Card= Bytes= )
INDEX (RANGE SCAN) OF DINYA_IDX_T (NON UNIQUE) (Cost=
Card= )
Statistics
recursive calls
db block gets
consistent gets
physical reads
redo size
bytes sent via SQL*Net to client
bytes received via SQL*Net from client
SQL*Net roundtrips to/from client
sorts (memory)
sorts (disk)
rows processed
SQL
分區(qū)表的維護
了解了分區(qū)表的建立 索引的建立 表和索引的使用后 在應(yīng)用的還要經(jīng)常對分區(qū)進行維護和管理 日常維護和管理的內(nèi)容包括 增加一個分區(qū) 合并一個分區(qū)及刪除分區(qū)等等 下面以范圍分區(qū)為例說明增加 合并 刪除分區(qū)的一般操作
增加一個分區(qū):
以下為引用的內(nèi)容
SQL alter table dinya_test
add partition part_ values less than(to_date( yyyy mm dd ))
tablespace dinya_spa
ce ;
Table altered
SQL
增加一個分區(qū)的時候 增加的分區(qū)的條件必須大于現(xiàn)有分區(qū)的最大值 否則系統(tǒng)將提示ORA partition bound must collate higher than that of the last partition 錯誤
合并一個分區(qū)
以下為引用的內(nèi)容
SQL alter table dinya_test merge partitions part_ part_ into partition part_ ;
Table altered
SQL
在本例中將原有的表的part_ 分區(qū)和part_ 分區(qū)進行了合并 合并后的分區(qū)為part_ 如果在合并的時候把合并后的分區(qū)定為part_ 的時候 系統(tǒng)將提示ORA cannot reuse lower bound partition as resulting partition 錯誤
刪除分區(qū)
以下為引用的內(nèi)容
SQL alter table dinya_test drop partition part_ ;
Table altered
SQL
刪除分區(qū)表的一個分區(qū)后 查詢該表的數(shù)據(jù)時顯示 該分區(qū)中的數(shù)據(jù)已全部丟失 所以執(zhí)行刪除分區(qū)動作時要慎重 確保先備份數(shù)據(jù)后再執(zhí)行 或?qū)⒎謪^(qū)合并
總結(jié)
lishixinzhi/Article/program/Oracle/201311/17329
一.表分區(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
增加分區(qū)表的分區(qū)
ALTER TABLE AA ADD PARTITION SYS_P421 VALUES LESS THAN(TO_DATE('2017-05-21 00:00:00','YYYY-MM-DD HH24:MI:SS')) TABLESPACE USERS;
如果是分區(qū)表可以添加分區(qū)，如果不是分區(qū)表，可以把表轉(zhuǎn)換成分區(qū)表，再增加分區(qū)。
根據(jù)數(shù)據(jù)表字段值的范圍進行分區(qū)。
創(chuàng)建完分區(qū)表后向表中添加一些數(shù)據(jù)，declarenamevarchar2(10)。fractionnumber(5)。gradenumber(5)。inumber(8)：=1。begin。foriin1。100000LOOP。當(dāng)我們的查詢語句不指定分區(qū)的時候，如果分區(qū)字段出現(xiàn)在where條件之后，Oracle會自動根據(jù)字段值的范圍掃描響應(yī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;
重慶分公司
重慶分公司
