表分區(轉載)

来源:https://www.cnblogs.com/outmanxiaozhou/archive/2018/12/19/10141594.html
-Advertisement-
Play Games

表分區 從以下幾個方面來整理關於分區表的概念及操作: 1.表空間及分區表的概念 表空間：是一個或多個數據文件的集合，所有的數據對象都存放在指定的表空間中，但主要存放的是表， 所以稱作表空間。 分區表：當表中的數據量不斷增大，查詢數據的速度就會變慢，應用程式的性能就會下降，這時就應該考慮對錶進行分區。 ...


表分區

從以下幾個方面來整理關於分區表的概念及操作:

  1. 表空間及分區表的概念
  2. 表分區的具體作用
  3. 表分區的優缺點
  4. 表分區的幾種類型及操作方法
  5. 對錶分區的維護性操作

 1.表空間及分區表的概念

表空間：是一個或多個數據文件的集合，所有的數據對象都存放在指定的表空間中，但主要存放的是表， 所以稱作表空間。

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

2.表分區的具體作用

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

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

什麼時候使用分區表：

1)   表的大小超過2GB。

2)   表中包含歷史數據，新的數據被增加都新的分區中。

3.表分區的優缺點

優點：

1)   改善查詢性能：對分區對象的查詢可以僅搜索自己關心的分區，提高檢索速度。

2)   增強可用性：如果表的某個分區出現故障，表在其他分區的數據仍然可用；

3)   維護方便：如果表的某個分區出現故障，需要修複數據，只修複該分區即可；

4)   均衡I/O：可以把不同的分區映射到磁碟以平衡I/O，改善整個系統性能。 

缺點：

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

4.表分區的幾種類型及操作方法

1.範圍分區

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

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

1)   每一個分區都必須有一個VALUES LESS THEN子句，它指定了一個不包括在該分區中的上限值。分區鍵的任何值等於或者大於這個上限值的記錄都會被加入到下一個高一些的分區中。

2)   所有分區，除了第一個，都會有一個隱式的下限值，這個值就是此分區的前一個分區的上限值。

3)   在最高的分區中，MAXVALUE被定義。MAXVALUE代表了一個不確定的值。這個值高於其它分區中的任何分區鍵的值，也可以理解為高於任何分區中指定的VALUE LESS THEN的值，同時包括空值。

例1：

假設有一個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
)
複製代碼

例2：按時間劃分

複製代碼 
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')) TABLESPACE ORD_TS01,

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

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

)
複製代碼

例3：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.列表分區

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

例1

複製代碼 
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
)
複製代碼

例2

複製代碼 
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設備上進行散列分區，使得這些分區大小一致。

例1：

複製代碼 
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)
 )
複製代碼

5.有關表分區的一些維護性操作

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;

您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 淺談SQL Server數據優化之若幹要點
    在上篇文章 淺談SQL Server內部運行機制 中，與大家分享了SQL Server內部運行機制，通過上次的分享，相信大家已經能解決如下幾個問題： 1.SQL Server 體繫結構由哪幾部分組成？ 2.SQL Server 體繫結構各模塊之間關係是怎樣的？ 3.SQL Server 體繫結構內部 ...
  • ORA-02030: can only select from fixed tables/views
    有時候給一些普通用戶授予查詢系統對象(例如dynamic performance views)許可權時會遇到“ORA-02030: can only select from fixed tables/views”，如下所示： SQL> grant select on v$session to test... ...
  • MySQL 查看執行計劃
    MySQL 使用 查看 執行計劃，該執行計劃不一定完全正確但是可以參考。 select_type | 說明 : : | : : SIMPLE | 簡單查詢 PRIMARY | 最外層查詢 SUBQUERY | 映射為子查詢 DERIVED | 子查詢 UNION | 聯合 UNION RESULT ...
  • 【HANA系列】SAP HANA XS的JavaScript安全事項
    公眾號：SAP Technical 本文作者：matinal 原文出處：http://www.cnblogs.com/SAPmatinal/ 原文鏈接：【HANA系列】SAP HANA XS使用JavaScript數據交互詳解 前言部分 我們都知道web程式都有潛在的安全隱患問題，那麼SAP HAN ...
  • 【HANA系列】SAP HANA XS使用JavaScript編程詳解
    公眾號：SAP Technical 本文作者：matinal 原文出處：http://www.cnblogs.com/SAPmatinal/ 原文鏈接：【HANA系列】SAP HANA XS使用JavaScript數據交互詳解 前言部分 在第一篇的時候，我們說過如下內容： SAP HANA XS支持 ...
  • 【HANA系列】SAP HANA XS使用JavaScript數據交互詳解
    公眾號：SAP Technical 本文作者：matinal 原文出處：http://www.cnblogs.com/SAPmatinal/ 原文鏈接：【HANA系列】SAP HANA XS使用JavaScript數據交互詳解 前言部分 下麵幾篇文章來詳細瞭解一下SAP HANA XS的內容。 HA ...
  • MySQL資料庫之存儲過程與存儲函數
    存儲過程和存儲函數類似於面向對象程式設計語言中的方法，可以簡化代碼，提高代碼的重用性。本文主要介紹如何創建存儲過程和存儲函數，以及存儲過程與函數的使用、修改、刪除等操作。 ...
  • 20181219-PostgreSQL 流複製監控腳本
    PostgreSQL 流複製監控腳本 https://github.com/AndyYHM/Writing/blob/PostgreSQL/20181219 PostgreSQL%20Stream%20MON.md _整理之前的腳本文檔，搬家至博客園，梳理一下之前寫的shell腳本_ _適用於Pos ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類