熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

Oracle數據庫中分區表的操作方法

2013-11-13 22:10:44  來源: Oracle 

  在大量業務數據處理的項目中可以考慮使用分區表來提高應用系統的性能並方便數據管理本文詳細介紹了分區表的使用
  
  在大型的企業應用或企業級的數據庫應用中要處理的數據量通常可以達到幾十到幾百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_t
From:http://tw.wingwit.com/Article/program/Oracle/201311/18389.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.