oracle表空間表分區詳解及oracle表分區查詢使用方法
- 高洛峰原創
- 2017-01-06 13:15:361380瀏覽
此文從以下幾個面向來整理關於分區表的概念及操作:
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、每一個分區都必須有一個VALUES LESS THEN子句，它指定了一個不包括在該分區中的上限值。分區鍵的任何值等於或大於這個上限值的記錄都會被加入到下一個高一些的分區。
2、所有分區，除了第一個，都會有一個隱式的下限值，這個值就是此分區的前一個分區的上限值。
3、在最高的分區中，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,
PHONEVARCHAR2(15) NOT NULL,
EMAILVARCHAR2(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
);二.列表分割區：
該分割區的特性是某列的值只有幾個，基於這樣的特點我們可以採用列表分區。
例一
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
);
)三.雜湊分區：
這類分區是在列值上使用雜湊演算法，以決定將行放入哪個分區。當列的值沒有合適的條件時，建議使用雜湊分區。
雜湊分區為透過指定分區編號來均勻分佈資料的一種分區類型，因為透過在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次方，这样可以使得各个分区间数据分布更加均匀。
四.组合范围散列分区
这种分区是基于范围分区和列表分区，表首先按某列进行范围分区，然后再按某列进行列表分区，分区之中的分区被称为子分区。
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
)
)五.复合范围散列分区：
这种分区是基于范围分区和散列分区，表首先按某列进行范围分区，然后再按某列进行散列分区。
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).有关表分区的一些维护性操作：
一、添加分区
以下代码给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');
二、删除分区
以下代码删除了P3表分区：
ALTER TABLE SALES DROP PARTITION P3;
在以下代码删除了P4SUB1子分区：
ALTER TABLE SALES DROP SUBPARTITION P4SUB1;
注意：如果删除的分区是表中唯一的分区，那么此分区将不能被删除，要想删除此分区，必须删除表。
三、截断分区
截断某个分区是指删除某个分区中的数据，并不会删除分区，也不会删除其它分区中的数据。当表中即使只有一个分区时，也可以截断该分区。通过以下代码截断分区：
ALTER TABLE SALES TRUNCATE PARTITION P2;
通过以下代码截断子分区：
ALTER TABLE SALES TRUNCATE SUBPARTITION P2SUB2;
四、合并分区
合并分区是将相邻的分区合并成一个分区，结果分区将采用较高分区的界限，值得注意的是，不能将分区合并到界限较低的分区。以下代码实现了P1 P2分区的合并：
ALTER TABLE SALES MERGE PARTITIONS P1,P2 INTO PARTITION P2;
五、拆分分区
拆分分区将一个分区拆分两个新分区，拆分后原来分区不再存在。注意不能对HASH类型的分区进行拆分。
ALTER TABLE SALES SBLIT PARTITION P2 AT(TO_DATE('2003-02-01','YYYY-MM-DD')) INTO (PARTITION P21,PARTITION P22);
六、接合分区(coalesca)
结合分区是将散列分区中的数据接合到其它分区中，当散列分区中的数据比较大时，可以增加散列分区，然后进行接合，值得注意的是，接合分区只能用于散列分区中。通过以下代码进行接合分区：
ALTER TABLE SALES COALESCA PARTITION;
七、重命名表分区
以下代码将P21更改为P2
ALTER TABLE SALES RENAME PARTITION P21 TO P2;
八、相关查询
跨分区查询
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表空间表分区详解及oracle表分区查询使用方法相关文章请关注PHP中文网！