在大型的企業應用或企業級的數據庫應用中要處理的數據量通常可以達到幾十到幾百GB有的甚至可以到TB級雖然存儲介質和數據處理技術的發展也很快但是仍然不能滿足用戶的需求為了使用戶的大量的數據在讀寫操作和查詢中速度更快Oracle提供了對表和索引進行分區的技術以改善大型應用系統的性能
使用分區的優點
·增強可用性如果表的某個分區出現故障表在其他分區的數據仍然可用
·維護方便如果表的某個分區出現故障需要修復數據只修復該分區即可
·均衡I/O可以把不同的分區映射到磁盤以平衡I/O改善整個系統性能
·改善查詢性能對分區對象的查詢可以僅搜索自己關心的分區提高檢索速度
Oracle數據庫提供對表或索引的分區方法有三種
·范圍分區
·Hash分區（散列分區）
·復合分區
下面將以實例的方式分別對這三種分區方法來說明分區表的使用為了測試方便我們先建三個表空間
以下為引用的內容
create tablespace dinya_space
datafile /test/demo/oracle/demodata/dinyadnf size M
create tablespace dinya_space
datafile /test/demo/oracle/demodata/dinyadnf size M
create tablespace dinya_space
datafile /test/demo/oracle/demodata/dinyadnf size M
分區表的創建
范圍分區
范圍分區就是對數據表中的某個值的范圍進行分區根據某個值的范圍決定將該數據存儲在哪個分區上如根據序號分區根據業務記錄的創建日期進行分區等
需求描述有一個物料交易表表名material_transactions該表將來可能有千萬級的數據記錄數要求在建該表的時候使用分區表這時候我們可以使用序號分區三個區每個區中預計存儲三千萬的數據也可以使用日期分區如每五年的數據存儲在一個分區上
根據交易記錄的序號分區建表 以下為引用的內容
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
建表成功根據交易的序號交易ID在三千萬以下的記錄將存儲在第一個表空間dinya_space中分區名為:par_在三千萬到六千萬之間的記錄存儲在第二個表空間
dinya_space中分區名為par_而交易ID在六千萬以上的記錄存儲在第三個表空間dinya_space中分區名為par_
根據交易日期分區建表
以下為引用的內容
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(yyyymmdd))
tablespace dinya_space
partition part_ values less than(to_date(yyyymmdd))
tablespace dinya_space
partition part_ values less than(maxvalue) tablespace dinya_space
);
Table created
這樣我們就分別建了以交易序號和交易日期來分區的分區表每次插入數據的時候系統將根據指定的字段的值來自動將記錄存儲到制定的分區（表空間）中
當然我們還可以根據需求使用兩個字段的范圍分布來分區如partition
by range ( transaction_id transaction_date)
分區條件中的值也做相應的改變請讀者自行測試
Hash分區（散列分區）
散列分區為通過指定分區編號來均勻分布數據的一種分區類型因為通過在I/O設備上進行散列分區使得這些分區大小一致如將物料交易表的數據根據交易ID散列地存放在指定的三個表空間中
以下為引用的內容
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
建表成功此時插入數據系統將按transaction_id將記錄散列地插入三個分區中這裡也就是三個不同的表空間中
復合分區
有時候我們需要根據范圍分區後每個分區內的數據再散列地分布在幾個表空間中這樣我們就要使用復合分區復合分區是先使用范圍分區然後在每個分區內再使用散列分區的一種分區方法如將物料交易的記錄按時間分區然後每個分區中的數據分三個子分區將數據散列地存儲在三個指定的表空間中
以下為引用的內容
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_spacedinya_spacedinya_space)
(
partition part_ values less than(to_date(yyyymmdd))
partition part_ values less than(to_date(yyyymmdd))
partition part_ values less than(maxvalue)
);
Table created
該例中先是根據交易日期進行范圍分區然後根據交易的ID將記錄散列地存儲在三個表空間中
分區表操作
以上了解了三種分區表的建表方法下面將使用實際的數據並針對按日期的范圍分區來測試分區表的數據記錄的操作
插入記錄
以下為引用的內容
SQL> insert into dinya_test values(BOOKSsysdate);
row created
SQL> insert into dinya_test values( BOOKSsysdate+);
row created
SQL> insert into dinya_test values( BOOKSto_date(yyyymmdd));
row created
SQL> insert into dinya_test values( BOOKSto_date(yyyymmdd));
row created
SQL> insert into dinya_test values( BOOKSto_date(yyyymmdd));
row created
SQL> insert into dinya_test values( BOOKSto_date(yyyymmdd));
row created
SQL> commit;
Commit complete
SQL>
按上面的建表結果年前的數據將存儲在第一個分區part_上而年到年的交易數據將存儲在第二個分區part_上年以後的記錄存儲在第三個分區part_上
查詢分區表記錄 以下為引用的內容
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>
從查詢的結果可以看出插入的數據已經根據交易時間范圍存儲在不同的分區中這裡是指定了分區的查詢當然也可以不指定分區直接執行select * from dinya_test查詢全部記錄
在也檢索的數據量很大的時候指定分區會大大提高檢索速度
更新分區表的記錄
以下為引用的內容
SQL> update dinya_test partition(part_) t set em_description=DESK where
ttransaction_id=;
row updated
SQL> commit;
Commit complete
SQL>
這裡將第一個分區中的交易ID=的記錄中的item_description字段更新為DESK可以看到已經成功更新了一條記錄但是當更新的時候指定了分區而根據查詢的記錄不在該分區中時將不會更新數據請看下面的例子 以下為引用的內容
SQL> update dinya_test partition(part_) t set em_description=DESK where
ttransaction_id=;
rows updated
SQL> commit;
Commit complete
SQL>
指定了在第一個分區中更新記錄但是條件中限制交易ID為而查詢全表交易ID為的記錄在第三個分區中這樣該條語句將不會更新記錄
刪除分區表記錄
以下為引用的內容
SQL> delete from dinya_test partition(part_) t where ttransaction_id=;
row deleted
SQL> commit;
Commit complete
SQL>
上面例子刪除了第二個分區part_中的交易記錄ID為的一條記錄和更新數據相同如果指定了分區而條件中的數據又不在該分區中時將不會刪除任何數據
分區表索引的使用
分區表和一般表一樣可以建立索引分區表可以創建局部索引和全局索引當分區中出現許多事務並且要保證所有分區中的數據記錄的唯一性時采用全局索引
局部索引分區的建立
以下為引用的內容
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>
看查詢的執行計劃從下面的執行計劃可以看出系統已經使用了索引
以下為引用的內容
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 (NONUNIQUE) (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>
全局索引分區的建立
全局索引建立時global 子句允許指定索引的范圍值這個范圍值為索引字段的范圍值
以下為引用的內容
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字段建立索引分區當然也可以不指定索引分區名直接對整個表建立索引如
以下為引用的內容
SQL> create index dinya_idx_t on dinya_test(item_id);
Index created
SQL>
同樣的對全局索引根據執行計劃可以看出索引已經可以使用
以下為引用的內容
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 (NONUNIQUE) (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>
分區表的維護
了解了分區表的建立索引的建立表和索引的使用後在應用的還要經常對分區進行維護和管理日常維護和管理的內容包括增加一個分區合並一個分區及刪除分區等等下面以范圍分區為例說明增加合並刪除分區的一般操作
增加一個分區:
以下為引用的內容
SQL> alter table dinya_test
add partition part_ values less than(to_date(yyyymmdd))
tablespace dinya_spa
ce;
Table altered
SQL>
增加一個分區的時候增加的分區的條件必須大於現有分區的最大值否則系統將提示ORA partition bound must collate higher than that of the last partition 錯誤
合並一個分區
以下為引用的內容
SQL> alter table dinya_test merge partitions part_part_ into partition part_;
Table altered
SQL>
在本例中將原有的表的part_分區和part_分區進行了合並合並後的分區為part_如果在合並的時候把合並後的分區定為part_的時候系統將提示ORA cannot reuse lowerbound partition as resulting partition 錯誤
刪除分區
以下為引用的內容
SQL> alter table dinya_test drop partition part_;
Table altered
SQL>
刪除分區表的一個分區後查詢該表的數據時顯示該分區中的數據已全部丟失所以執行刪除分區動作時要慎重確保先備份數據後再執行或將分區合並
總結
需要說明的是本文在舉例說名分區表事務操作的時候都指定了分區因為指定了分區系統在執行的時候則只操作該分區的記錄提高了數據處理的速度不要指定分區直接操作數據也是可以的在分區表上建索引及多索引的使用和非分區表一樣此外因為在維護分區的時候可能對分區的索引會產生一定的影響可能需要在維護之後重建索引相關內容請參考分區表索引部分的文檔本文出自 CTOCOM技術博客
From:http://tw.wingwit.com/Article/program/Oracle/201311/17329.html
