首頁 > 軟體

Oracle表分割區詳解

2022-05-26 22:00:58

一、 表空間及分割區表的概念

表空間：

是一個或多個資料檔案的集合，所有的資料物件都存放在指定的表空間中，但主要存放的是表， 所以稱作表空間。

分割區表：

當表中的資料量不斷增大，查詢資料的速度就會變慢，應用程式的效能就會下降，這時就應該考慮對錶進行分割區。表進行分割區後，邏輯上表仍然是一張完整的表，只是將表中的資料在物理上存放到多個表空間(物理檔案上)，這樣查詢資料時，不至於每次都掃描整張表。

二、表分割區的具體作用

Oracle的表分割區功能通過改善可管理性、效能和可用性，從而為各式應用程式帶來了極大的好處。通常，分割區可以使某些查詢以及維護操作的效能大大提高。此外,分割區還可以極大簡化常見的管理任務，分割區是構建千兆位元組資料系統或超高可用性系統的關鍵工具。

分割區功能能夠將表、索引或索引組織表進一步細分為段，這些資料庫物件的段叫做分割區。每個分割區有自己的名稱，還可以選擇自己的儲存特性。從資料庫管理員的角度來看，一個分割區後的物件具有多個段，這些段既可進行集體管理，也可單獨管理，這就使資料庫管理員在管理分割區後的物件時有相當大的靈活性。但是，從應用程式的角度來看，分割區後的表與非分割區表完全相同，使用 SQL DML 命令存取分割區後的表時，無需任何修改。

什麼時候使用分割區表：

1、表的大小超過2GB。

2、表中包含歷史資料，新的資料被增加都新的分割區中。

三、表分割區的優缺點

優點：

  • 改善查詢效能：對分割區物件的查詢可以僅搜尋自己關心的分割區，提高檢索速度。
  • 增強可用性：如果表的某個分割區出現故障，表在其他分割區的資料仍然可用；
  • 維護方便：如果表的某個分割區出現故障，需要修復資料，只修復該分割區即可；
  • 均衡I/O：可以把不同的分割區對映到磁碟以平衡I/O，改善整個系統效能。

缺點：

分割區表相關：已經存在的表沒有方法可以直接轉化為分割區表。不過 Oracle 提供了線上重定義表的功能。

四、表分割區的幾種型別及操作方法

1、範圍分割區：

範圍分割區將資料基於範圍對映到每一個分割區，這個範圍是你在建立分割區時指定的分割區鍵決定的。這種分割區方式是最為常用的，並且分割區鍵經常採用日期。舉個例子：你可能會將銷售資料按照月份進行分割區。

當使用範圍分割區時，請考慮以下幾個規則：

  • 每一個分割區都必須有一個VALUES LESS THEN子句，它指定了一個不包括在該分割區中的上限值。分割區鍵的任何值等於或者大於這個上限值的記錄都會被加入到下一個高一些的分割區中。
  • 所有分割區，除了第一個，都會有一個隱式的下限值，這個值就是此分割區的前一個分割區的上限值。
  • 在最高的分割區中，MAXVALUE被定義。MAXVALUE代表了一個不確定的值。這個值高於其它分割區中的任何分割區鍵的值，也可以理解為高於任何分割區中指定的VALUE LESS THEN的值，同時包括空值。

例一：

假設有一個CUSTOMER表，表中有資料200000行，我們將此表通過CUSTOMER_ID進行分割區，每個分割區儲存100000行，我們將每個分割區儲存到單獨的表空間中，這樣資料檔案就可以跨越多個物理磁碟。下面是建立表和分割區的程式碼，如下：

CREATE TABLE CUSTOMER 
( 
    CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY, 
    FIRST_NAME  VARCHAR2(30) NOT NULL, 
    LAST_NAME   VARCHAR2(30) NOT NULL, 
    PHONE        VARCHAR2(15) NOT NULL, 
    EMAIL        VARCHAR2(80), 
    STATUS       CHAR(1) 
) 
PARTITION BY RANGE (CUSTOMER_ID) 
( 
    PARTITION CUS_PART1 VALUES LESS THAN (100000) TABLESPACE CUS_TS01, 
    PARTITION CUS_PART2 VALUES LESS THAN (200000) TABLESPACE CUS_TS02 
)

例二：按時間劃分

CREATE TABLE ORDER_ACTIVITIES 
( 
    ORDER_ID      NUMBER(7) NOT NULL, 
    ORDER_DATE    DATE, 
    TOTAL_AMOUNT NUMBER, 
    CUSTOTMER_ID NUMBER(7), 
    PAID           CHAR(1) 
) 
PARTITION BY RANGE (ORDER_DATE) 
( 

  PARTITION ORD_ACT_PART01 VALUES LESS THAN (TO_DATE('01- MAY -2003','DD-MON-YYYY')) TABLESPACEORD_TS01,

  PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE('01-JUN-2003','DD-MON-YYYY')) TABLESPACE ORD_TS02, 

  PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE('01-JUL-2003','DD-MON-YYYY')) TABLESPACE ORD_TS03 

)

例三：MAXVALUE

CREATE TABLE RangeTable
( 
  idd   INT PRIMARY KEY , 
  iNAME VARCHAR(10), 
  grade INT  
) 
PARTITION  BY  RANGE (grade) 
( 
      PARTITION  part1 VALUES  LESS  THEN (1000) TABLESPACE  Part1_tb, 
      PARTITION  part2 VALUES  LESS  THEN (MAXVALUE) TABLESPACE  Part2_tb 
);

2、列表分割區：

該分割區的特點是某列的值只有幾個，基於這樣的特點我們可以採用列表分割區。

例一

CREATE TABLE PROBLEM_TICKETS 
( 
    PROBLEM_ID   NUMBER(7) NOT NULL PRIMARY KEY, 
    DESCRIPTION  VARCHAR2(2000), 
    CUSTOMER_ID  NUMBER(7) NOT NULL, 
    DATE_ENTERED DATE NOT NULL, 
    STATUS       VARCHAR2(20) 
) 
PARTITION BY LIST (STATUS) 
( 
      PARTITION PROB_ACTIVE   VALUES ('ACTIVE') TABLESPACE PROB_TS01, 
      PARTITION PROB_INACTIVE VALUES ('INACTIVE') TABLESPACE PROB_TS02

);

例二

CREATE  TABLE  ListTable
( 
    id    INT  PRIMARY  KEY , 
    name  VARCHAR (20), 
    area  VARCHAR (10) 
) 
PARTITION  BY  LIST (area) 
( 
    PARTITION  part1 VALUES ('guangdong','beijing') TABLESPACE  Part1_tb, 
    PARTITION  part2 VALUES ('shanghai','nanjing')  TABLESPACE  Part2_tb 
);

3、雜湊分割區：

這類分割區是在列值上使用雜湊演演算法，以確定將行放入哪個分割區中。當列的值沒有合適的條件時，建議使用雜湊分割區。

雜湊分割區為通過指定分割區編號來均勻分佈資料的一種分割區型別，因為通過在I/O裝置上進行雜湊分割區，使得這些分割區大小一致。

例一：

CREATE TABLE HASH_TABLE 
( 
  COL NUMBER(8), 
  INF VARCHAR2(100) 
) 
PARTITION BY HASH (COL) 
( 
  PARTITION PART01 TABLESPACE HASH_TS01, 
  PARTITION PART02 TABLESPACE HASH_TS02, 
  PARTITION PART03 TABLESPACE HASH_TS03 
)

簡寫：

CREATE TABLE emp
(
    empno NUMBER (4),
    ename VARCHAR2 (30),
    sal   NUMBER 
)
PARTITION BY  HASH (empno) PARTITIONS 8
STORE IN (emp1,emp2,emp3,emp4,emp5,emp6,emp7,emp8);

hash分割區最主要的機制是根據hash演演算法來計算具體某條紀錄應該插入到哪個分割區中,hash演演算法中最重要的是hash函數，Oracle中如果你要使用hash分割區，只需指定分割區的數量即可。建議分割區的數量採用2的n次方，這樣可以使得各個分割區間資料分佈更加均勻。

4、複合（範圍列表）分割區

這種分割區是基於範圍分割區和列表分割區，表首先按某列進行範圍分割區，然後再按某列進行列表分割區，分割區之中的分割區被稱為子分割區。

CREATE TABLE SALES 
( 

PRODUCT_ID VARCHAR2(5), 

SALES_DATE DATE, 

SALES_COST NUMBER(10),

STATUS VARCHAR2(20) 

)

PARTITION BY RANGE(SALES_DATE) SUBPARTITION BY LIST (STATUS) 

(

   PARTITION P1 VALUES LESS THAN(TO_DATE('2003-01-01','YYYY-MM-DD'))TABLESPACE rptfact2009 
          ( 
              SUBPARTITION P1SUB1 VALUES ('ACTIVE') TABLESPACE rptfact2009, 
              SUBPARTITION P1SUB2 VALUES ('INACTIVE') TABLESPACE rptfact2009 
          ), 
   PARTITION P2 VALUES LESS THAN (TO_DATE('2003-03-01','YYYY-MM-DD')) TABLESPACE rptfact2009 
          ( 
              SUBPARTITION P2SUB1 VALUES ('ACTIVE') TABLESPACE rptfact2009, 
              SUBPARTITION P2SUB2 VALUES ('INACTIVE') TABLESPACE rptfact2009 
          ) 
)

5、複合（範圍雜湊）分割區：

這種分割區是基於範圍分割區和雜湊分割區，表首先按某列進行範圍分割區，然後再按某列進行雜湊分割區。

create table dinya_test 
 ( 
 transaction_id number primary key, 
 item_id number(8) not null, 
 item_description varchar2(300), 
 transaction_date date 
 ) 
 partition by range(transaction_date)subpartition by hash(transaction_id)  subpartitions 3 store in (dinya_space01,dinya_space02,dinya_space03) 
 ( 
     partition part_01 values less than(to_date(‘2006-01-01','yyyy-mm-dd')), 
     partition part_02 values less than(to_date(‘2010-01-01','yyyy-mm-dd')), 
     partition part_03 values less than(maxvalue) 
 );

五、有關表分割區的一些維護性操作：

1、新增分割區

以下程式碼給SALES表新增了一個P3分割區

ALTER TABLE SALES ADD PARTITION P3 VALUES LESS THAN(TO_DATE('2003-06-01','YYYY-MM-DD'));

注意：以上新增的分割區界限應該高於最後一個分割區界限。

以下程式碼給SALES表的P3分割區新增了一個P3SUB1子分割區

ALTER TABLE SALES MODIFY PARTITION P3 ADD SUBPARTITION P3SUB1 VALUES('COMPLETE');

2、刪除分割區

以下程式碼刪除了P3表分割區：

ALTER TABLE SALES DROP PARTITION P3;

在以下程式碼刪除了P4SUB1子分割區：

ALTER TABLE SALES DROP SUBPARTITION P4SUB1;

注意：如果刪除的分割區是表中唯一的分割區，那麼此分割區將不能被刪除，要想刪除此分割區，必須刪除表。

3、截斷分割區

截斷某個分割區是指刪除某個分割區中的資料，並不會刪除分割區，也不會刪除其它分割區中的資料。當表中即使只有一個分割區時，也可以截斷該分割區。通過以下程式碼截斷分割區：

ALTER TABLE SALES TRUNCATE PARTITION P2;

通過以下程式碼截斷子分割區：

ALTER TABLE SALES TRUNCATE SUBPARTITION P2SUB2;

4、合併分割區

合併分割區是將相鄰的分割區合併成一個分割區，結果分割區將採用較高分割區的界限，值得注意的是，不能將分割區合併到界限較低的分割區。以下程式碼實現了P1 P2分割區的合併：

ALTER TABLE SALES MERGE PARTITIONS P1,P2 INTO PARTITION P2;

5、拆分分割區

拆分分割區將一個分割區拆分兩個新分割區，拆分後原來分割區不再存在。注意不能對HASH型別的分割區進行拆分。

ALTER TABLE SALES SBLIT PARTITION P2 AT(TO_DATE('2003-02-01','YYYY-MM-DD')) INTO (PARTITION P21,PARTITION P22);

6、接合分割區(coalesca)

結合分割區是將雜湊分割區中的資料接合到其它分割區中，當雜湊分割區中的資料比較大時，可以增加雜湊分割區，然後進行接合，值得注意的是，接合分割區只能用於雜湊分割區中。通過以下程式碼進行接合分割區：

ALTER TABLE SALES COALESCA PARTITION;

7、重新命名錶分割區

以下程式碼將P21更改為P2

ALTER TABLE SALES RENAME PARTITION P21 TO P2;

8、相關查詢

跨分割區查詢

select sum( *) from 
(select count(*) cn from t_table_SS PARTITION (P200709_1) 
union all 
select count(*) cn from t_table_SS PARTITION (P200709_2)
);

查詢表上有多少分割區

SELECT * FROM useR_TAB_PARTITIONS WHERE TABLE_NAME='tableName'

查詢索引資訊

select object_name,object_type,tablespace_name,sum(value) 
from v$segment_statistics 
where statistic_name IN ('physical reads','physical write','logical reads')and object_type='INDEX' 
group by object_name,object_type,tablespace_name 
order by 4 desc

其他查詢

--顯示資料庫所有分割區表的資訊： 

select * from DBA_PART_TABLES 

--顯示當前使用者可存取的所有分割區表資訊: 

select * from ALL_PART_TABLES 

--顯示當前使用者所有分割區表的資訊： 

select * from USER_PART_TABLES 

--顯示錶分割區資訊 顯示資料庫所有分割區表的詳細分割區資訊： 

select * from DBA_TAB_PARTITIONS 

--顯示當前使用者可存取的所有分割區表的詳細分割區資訊： 

select * from ALL_TAB_PARTITIONS 

--顯示當前使用者所有分割區表的詳細分割區資訊： 

select * from USER_TAB_PARTITIONS 

--顯示子分割區資訊 顯示資料庫所有組合分割區表的子分割區資訊： 

select * from DBA_TAB_SUBPARTITIONS 

--顯示當前使用者可存取的所有組合分割區表的子分割區資訊： 

select * from ALL_TAB_SUBPARTITIONS 

--顯示當前使用者所有組合分割區表的子分割區資訊： 

select * from USER_TAB_SUBPARTITIONS 

--顯示分割區列 顯示資料庫所有分割區表的分割區列資訊： 

select * from DBA_PART_KEY_COLUMNS 

--顯示當前使用者可存取的所有分割區表的分割區列資訊： 

select * from ALL_PART_KEY_COLUMNS 

--顯示當前使用者所有分割區表的分割區列資訊： 

select * from USER_PART_KEY_COLUMNS 

--顯示子分割區列 顯示資料庫所有分割區表的子分割區列資訊： 

select * from DBA_SUBPART_KEY_COLUMNS 

--顯示當前使用者可存取的所有分割區表的子分割區列資訊： 

select * from ALL_SUBPART_KEY_COLUMNS 

--顯示當前使用者所有分割區表的子分割區列資訊： 

select * from USER_SUBPART_KEY_COLUMNS 

--怎樣查詢出oracle資料庫中所有的的分割區表 

select * from user_tables a where a.partitioned='YES' 

--刪除一個表的資料是 

truncate table table_name; 

--刪除分割區表一個分割區的資料是 

alter table table_name truncate partition p5;

到此這篇關於Oracle表分割區的文章就介紹到這了。希望對大家的學習有所幫助，也希望大家多多支援it145.com。


相關文章

  • 德漢姆中學就讀條件 德漢姆中學就讀條件

    <em>Mac</em>Book项目 2009年学校开始实施<em>Mac</em>Book项目,所有师生配备一本<em>Mac</em>Book,并同步更新了校园无线网络。学校每周进行电脑技术更新,每月发送技术支持资料,极大改变了教学及学习方式。因此2011

    2021-06-01 09:32:01

  • 黑科技的輕簡出行，告別「電量焦慮症」：Anker氮化鎵超能充系列 黑科技的輕簡出行，告別「電量焦慮症」：Anker氮化鎵超能充系列

    综合看Anker超能充系列的性价比很高，并且与不仅和iPhone12/苹果<em>Mac</em>Book很配，而且适合多设备充电需求的日常使用或差旅场景，不管是安卓还是Switch同样也能用得上它，希望这次分享能给准备购入充电器的小伙伴们有所

    2021-06-01 09:31:42

  • 吳亦凡廠牌首秀，L4WUDU居然忘詞了，Rapper和明星比還是有差距！ 吳亦凡廠牌首秀，L4WUDU居然忘詞了，Rapper和明星比還是有差距！

    除了L4WUDU与吴亦凡已经多次共事，成为了明面上的厂牌成员，吴亦凡还曾带领20XXCLUB全队参加2020年的一场音乐节，这也是20XXCLUB首次全员合照，王嗣尧Turbo、陈彦希Regi、<em>Mac</em> Ova Seas、林渝植等人全部出场。然而让

    2021-06-01 09:31:34

  • IPFS、Chia、Bzz和ICP挖礦該怎麼選？哪一個更有優勢？ IPFS、Chia、Bzz和ICP挖礦該怎麼選？哪一個更有優勢？

    目前应用IPFS的机构：1 谷歌<em>浏览器</em>支持IPFS分布式协议 2 万维网 (历史档案博物馆)数据库 3 火狐<em>浏览器</em>支持 IPFS分布式协议 4 EOS 等数字货币数据存储 5 美国国会图书馆，历史资料永久保存在 IPFS 6 加

    2021-06-01 09:31:24

  • 有哪些事是買了雪佛蘭後才知道的？美事偷著樂，開拓者車主隨聊 有哪些事是買了雪佛蘭後才知道的？美事偷著樂，開拓者車主隨聊

    开拓者的车机是兼容苹果和<em>安卓</em>，虽然我不怎么用，但确实兼顾了我家人的很多需求：副驾的门板还配有解锁开关，有的时候老婆开车，下车的时候偶尔会忘记解锁，我在副驾驶可以自己开门：第二排设计很好，不仅配置了一个很大的

    2021-06-01 09:30:48

  • iPhone12在618最新定價，跌價幅度超過1400元，還等iPhone13嗎？ iPhone12在618最新定價，跌價幅度超過1400元，還等iPhone13嗎？

    不仅是<em>安卓</em>手机，苹果手机的降价力度也是前所未有了，iPhone12也“跳水价”了，发布价是6799元，如今已经跌至5308元，降价幅度超过1400元，最新定价确认了。iPhone12是苹果首款5G手机，同时也是全球首款5nm芯片的智能机，它

    2021-06-01 09:30:45

IT145.com E-mail:sddin#qq.com