Oracle-分區表解讀

概述

Oracle-OLAP和OLTP解讀

Oracle-index索引解讀

Oracle-分區表解讀

Oracle-鎖解讀

Oracle-等待事件解讀

Oracle-procedure/cursor解讀

Oracle關于分區的在線文檔

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

對于數據庫中的超大型表，可通過把它的數據分成若干個小表，從而簡化數據庫的管理活動。對于每一個簡化后的小表，我們稱為一個單個的分區。

對于分區的訪問，我們不需要使用特殊的SQL查詢語句或特定的DML語句，而且可以單獨的操作單個分區，而不是整個表。同時可以將不同分區的數據放置到不同的表空間。

Oracle提供分區技術以支持VLDB（VeryLargeDataBase）.

分區表通過對分區列的判斷，把分區列不同的記錄，放到不同的分區中。分區完全對應用透明。

對于外部應用程序來說，雖然存在不同的分區，且數據位于不同的表空間，但邏輯上仍然是一張表。

Oracle的分區表可以包括多個分區，每個分區都是一個獨立的段（SEGMENT），可以存放到不同的表空間中。

查詢時可以通過查詢表來訪問各個分區中的數據，也可以通過在查詢時直接指定分區的方法來進行查詢。

在oracle 10g中最多支持：1024k-1個分區。

什么時候需要分區表？

官網給出的建議：

1、表的大小超過2GB。

2、表中包含歷史數據，新的數據被增加都新的分區中。需要將歷史數據和當前的數據分開單獨處理，比如歷史數據僅僅需要只讀，而當前數據則實現DML

表分區的優缺點

優點

1、改善查詢性能：只需要搜索特定分區，而非整張表，提高查詢速度。

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

3、維護方便：可以單獨備份和恢復每個分區。如果表的某個分區出現故障，需要修復數據，只修復該分區即可; 節約維護時間，單個分區的數據裝載，索引重建，備份，維護等將遠小于整張表的維護時間。

4、均衡I/O：將不同的分區映射到不同的磁盤以平衡I/O，提高并發，改善整個系統性能。

缺點：

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

特點

共性：不同的分區之間必須有相同的邏輯屬性，比如表名，列名，數據類型，約束等，

個性：各個分區可以有不同的物理屬性，比如pctfree, pctused, and tablespaces.

分區獨立性：即使某些分區不可用，其他分區仍然可用。

特殊性：含有LONG、LONGRAW數據類型的表不能進行分區

ORACLE分區類型

Oracle 10g提供了以下幾種分區類型：

（1）范圍分區（range）；

（2）哈希分區（hash）；

（3）列表分區（list）；

（4）范圍－哈希復合分區（range-hash）；

（5）范圍－列表復合分區（range-list）。

范圍分區（range）

范圍分區特性

Range分區是應用范圍比較廣的表分區方式，它是以列的值的范圍來做為分區的劃分條件，將記錄存放到列值所在的range分區中，并且分區鍵經常采用日期。

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

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

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

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

如果某些記錄暫無法預測范圍，可以創建maxvalue分區，所有不在指定范圍內的記錄都會被存儲到maxvalue所在分區中。

創建范圍分區時，必須指定以下內容

分區方法：range

分區列

標識分區邊界的分區描述

如：

create table pdba ( id number, time date) partition by range (time) --創建基于日期的范圍分區并存儲到不同的表空間 ( partition p1 values less than (to_date('2010-10-1', 'yyyy-mm-dd')), partition p2 values less than (to_date('2010-11-1', 'yyyy-mm-dd')), partition p3 values less than (to_date('2010-12-1', 'yyyy-mm-dd')), partition p4 values less than (maxvalue) )

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

create table r --創建基于值范圍的分區，分區子句未指定表空間時則位于缺省的表空間 (a int) partition by range (a) ( partition p1 values less than (10), partition p2 values less than (20), partition p3 values less than (30), partition p4 values less than (maxvalue) ); select * from r partition (p1) --查看分區中的數據

1

2

3

4

5

6

7

8

9

10

11

說明：

partition by 用于指定分區方式 range 表示分區的方式是范圍劃分 partition pn 用于指定分區的名字 values less than 指定分區的上界（上限） 添加分區： ALTER TABLE r add partition p5 values less than (xxx ) tablespace xx; 查看分區表相關信息： SELECT table_name,partition_name,subpartition_count, tablespace_name,user_stats from user_tab_partitions;

1

2

3

4

5

6

7

8

9

10

11

12

13

栗子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 )

1

2

3

4

5

6

7

8

9

10

11

12

13

14

栗子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-2016','DD-MON-YYYY')) TABLESPACEORD_TS01, PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE('01-JUN-2016','DD-MON-YYYY')) TABLESPACE ORD_TS02, PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE('01-JUL-2016','DD-MON-YYYY')) TABLESPACE ORD_TS03 )

1

2

3

4

5

6

7

8

9

10

11

12

13

14

栗子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 );

1

2

3

4

5

6

7

8

9

10

11

哈希分區（hash）

也被成為散列分區。

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

哈希分區為通過指定分區編號來均勻分布數據的一種分區類型，因為通過在I/O設備上進行散列分區，使得這些分區大小一致。

對于那些無法有效劃分范圍的表，可以使用hash分區，這樣對于提高性能還是會有一定的幫助。hash分區會將表中的數據平均分配到你指定的幾個分區中，列所在分區是依據分區列的hash值自動分配，因此你并不能控制也不知道哪條記錄會被放到哪個分區中，hash分區也可以支持多個依賴列。

創建散列分區時，必須指定以下信息

分區方法：hash

分區列

分區數量或單獨的分區描述

分裂、刪除和合并分區不能應用于Hash分區，但是，Hash分區能夠合并和添加。

創建hash分區有兩種方法：一種方法是指定分區數量，另一種方法是指定分區的名字，

但兩者不能同時指定。

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 )

1

2

3

4

5

6

7

8

9

10

11

在這里，我們指定了每個分區的表空間。

簡寫的方式：

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);--表空間的名稱

1

2

3

4

5

6

7

8

9

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

列表分區（list）

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

List分區也需要指定列的值，其分區值必須明確指定，該分區列只能有一個，不能像range或者hash分區那樣同時指定多個列做為分區依賴列，但它的單個分區對應值可以是多個。

在分區時必須確定分區列可能存在的值，一旦插入的列值不在分區范圍內，則插入/更新就會失敗，因此通常建議使用list分區時，要創建一個default分區存儲那些不在指定范圍內的記錄，類似range分區中的maxvalue分區。

List分區時必須指定的以下內容

分區方法：list

分區列

分區描述，每個描述指定一串文字值(值的列表),它們是分區列(它們限定將被包括在分區中的行)的離散值

比如：

在根據某字段，如城市代碼分區時，可以指定default，把非分區規則的數據，全部放到這個default分區。

create table custaddr ( id varchar2(15 byte) not null, areacode varchar2(4 byte) ) partition by list (areacode) ( partition t_list025 values ('025'), partition t_list372 values ('372') , partition t_list510 values ('510'), partition p_other values (default) )

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

栗子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

1

2

3

4

5

6

7

8

9

10

11

12

栗子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 ); )

1

2

3

4

5

6

7

8

9

10

11

12

組合分區

如果某表按照某列分區之后，仍然較大，或者是一些其它的需求，還可以通過分區內再建子分區的方式將分區再分區，即組合分區的方式。

組合分區呢在10g中有兩種：range-hash，range-list。

注意順序，根分區只能是range分區，子分區可以是hash分區或list分區。

組合分區使用range方法分區，在每個子分區中使用hash方法進行再分區。

組合分區比range分區更容易管理，充分使用了hash分區的并行優勢。組合分區支持歷史數據和條塊數據兩者。

如添加新的RANGE分區，同時為DML操作提供更高層的并行性。

創建組合分區時，需要指定如下內容：

分區方法：range

分區列

標識分區邊界的分區描述

子分區方法：hash

子分區列

每個分區的子分區數量，或子分區的描述

范圍－哈希復合分區（range-hash）

這種分區是基于范圍分區和散列分區，表首先按某列進行范圍分區，然后再按某列進行散列分區。

create table xiaogongjiang ( 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 (xgj_space01,xgj_space02,xgj_space03) ( partition part_01 values less than(to_date(‘2006-01-01’,’yyyy-mm-dd’)), partition part_02 values less than(to_date(‘2016-01-01’,’yyyy-mm-dd’)), partition part_03 values less than(maxvalue) );

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

范圍－列表復合分區（range-list）

這種分區是基于范圍分區和列表分區，表首先按某列進行范圍分區，然后再按某列進行列表分區，分區之中的分區被稱為子分區

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 ) )

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

Oracle 11g-Interval Partitioning

數據庫管理員日常要做的一件重復而無聊的工作 比如每隔一天要生成新的24個分區，用以存儲第二天的數據。而在11g中這項工作可以交由Oracle自動完成了，基于Range和List的Interval Partitioning分區類型登場。

在 11g 里的 Interval 創建，這種方法對沒有寫全的分區會自動創建。 比如我

這里只寫了 1 月日期，如果插入的數據有其他月份的，會自動生成對應的分區。

CREATE TABLE TB_INTERVAL PARTITION BY RANGE (time_col) INTERVAL(NUMTOYMINTERVAL(1, 'month')) (PARTITION P0 VALUES LESS THAN (TO_DATE('1-1-2016', 'dd-mm-yyyy')));

1

2

3

4

指定需要Oracle自動創建分區的間隔時間，上面這個例子是1個月，然后至少創建一個基本分區，上面這個例子是在2016-1-1之前的所有數據都在P0分區中，以后每個月的數據都會存放在Oracle自動創建的一個新分區中。

select table_name,partition_name from user_tab_partitions where table_name='TB_INTERVAL';

1

2

select count(*) from TB_INTERVAL partition (p1);

1

創建按月分區的分區表

創建分區表

/* Formatted on 2010/6/10 20:21:12 (QP5 v5.115.810.9015) */ create table intervalpart (c1 number, c3 date) partition by range (c3) interval ( numtoyminterval (1, 'month') ) (partition part1 values less than (to_date ('01/12/2010', 'mm/dd/yyyy')), partition part2 values less than (to_date ('02/12/2010', 'mm/dd/yyyy')) )

1

2

3

4

5

6

7

8

9

注意： 如果在建 Interval 分區表是沒有把所有的分區寫完成，在插入相關數據后會自動生成分區

2. 查看現在表的分區：

SQL> select table_name,partition_name from user_tab_partitions where table_name='INTERVALPART'; TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ INTERVALPART PART1 INTERVALPART PART2

1

2

3

4

5

6

插入測試數據：

SQL> begin 2 for i in 0 .. 11 loop 3 insert into intervalpart values(i,add_months(to_date('2010-1-1','yyyy-mm-dd'),i)); 4 end loop ; 5 commit; 6 end; 7 /

1

2

3

4

5

6

7

8

PL/SQL 過程已成功完成。

補充： add_months() 函數獲取前一個月或者下一個月的月份， 參數中 負數 代

表 往前， 正數 代表 往后。

–上一個月

select to_char(add_months(trunc(sysdate),-1),'yyyymm') from dual;

1

–下一個月

select to_char(add_months(trunc(sysdate),1),'yyyymm') from dual;

1

觀察自動創建的分區：

SQL> select table_name,partition_name from user_tab_partitions where table_name='INTERVALPART'; TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ INTERVALPART PART1 INTERVALPART PART2 INTERVALPART SYS_P22 INTERVALPART SYS_P23 INTERVALPART SYS_P24 INTERVALPART SYS_P25 INTERVALPART SYS_P26 INTERVALPART SYS_P27 INTERVALPART SYS_P28 INTERVALPART SYS_P29 INTERVALPART SYS_P30 INTERVALPART SYS_P31

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

已選擇 12 行。

5. 查看分區內容：

SQL> select * from INTERVALPART; C1 C3 --------- ---------- 1 2010-01-01 0 2010-01-01 1 2010-02-01 2 2010-03-01 3 2010-04-01 4 2010-05-01 5 2010-06-01 6 2010-07-01 7 2010-08-01 8 2010-09-01 9 2010-10-01 10 2010-11-01 11 2010-12-01 已選擇 13 行。 SQL> select * from INTERVALPART partition(part1); C1 C3 --------- ---------- 1 2010-01-01 0 2010-01-01 SQL> select * from INTERVALPART partition(part2); C1 C3 --------- ---------- 1 2010-02-01

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

創建一個以天為間隔的分區表

創建分區表：

SQL> create table dave 2 ( 3 id number, 4 dt date 5 ) 6 partition by range (dt) 7 INTERVAL (NUMTODSINTERVAL(1,'day')) 8 ( 9 partition p100101 values less than (to_date('2010-01-01','yyyy-mm-dd')) 10 );

1

2

3

4

5

6

7

8

9

10

查看表分區：

SQL> select table_name,partition_name from user_tab_partitions where table_name='DAVE'; TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ DAVE P100101

1

2

3

4

插入測試數據：

SQL> begin 2 for i in 1 .. 12 loop 3 insert into dave values(i,trunc(to_date('2010-1-1','yyyy-mm-dd')+i)); 4 end loop; 5 commit; 6 end; 7 /

1

2

3

4

5

6

7

PL/SQL 過程已成功完成。

4. 觀察自動創建的分區：

SQL> select table_name,partition_name from user_tab_partitions where table_name='DAVE'; TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ DAVE P100101 DAVE SYS_P32 DAVE SYS_P33 DAVE SYS_P34 DAVE SYS_P35 DAVE SYS_P36 DAVE SYS_P37 DAVE SYS_P38 DAVE SYS_P39 DAVE SYS_P40 DAVE SYS_P41 DAVE SYS_P42 DAVE SYS_P43

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

已選擇 13 行。

5. 查看分區內容：

SQL> select * from dave partition(SYS_P32); ID DT ---------- ---------- 1 2010-01-02 SQL> select * from dave partition(SYS_P33); ID DT -------- ---------- 2 2010-01-03 SQL> select * from dave partition(SYS_P34); ID DT -------- ---------- 3 2010-01-04 SQL> select * from dave; ID DT -------- ---------- 1 2010-01-02 2 2010-01-03 3 2010-01-04 4 2010-01-05 5 2010-01-06 6 2010-01-07 7 2010-01-08 8 2010-01-09 9 2010-01-10 10 2010-01-11 11 2010-01-12 12 2010-01-13 已選擇 12 行。

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

System Partitioning

系統分區，在這個新的類型中，我們不需要指定任何分區鍵，數據會進入哪個分區完全由應用程序決定，實際上也就是由SQL來決定，終于，我們在Insert語句中可以指定插入哪個分區了。

假設我們創建了下面這張分區表，注意，沒有指定任何分區鍵：

CREATE TABLE systab (c1 integer, c2 integer) PARTITION BY SYSTEM ( PARTITION p1 TABLESPACE tbs_1, PARTITION p2 TABLESPACE tbs_2, PARTITION p3 TABLESPACE tbs_3, PARTITION p4 TABLESPACE tbs_4 );

1

2

3

4

5

6

7

8

現在由SQL語句來指定插入哪個分區：

– 數據插入p1分區

INSERT INTO systab PARTITION (p1) VALUES (4,5);

1

– 數據插入第2個分區，也就是p2分區

INSERT INTO systab PARTITION (2) VALUES (7,8);

1

– 為了實現綁定變量，用pno變量來代替實際分區號，以避免過度解析

INSERT INTO systab PARTITION (:pno) VALUES (9,10);

1

由于System Partitioning的特殊性，所以很明顯，這種類型的分區將不支持Partition Split操作，也不支持create table as select操作。

12C 對表分區維護的增強

Oracle Database 12c對表分區變化比較多，共分為下面幾點

1.在線移動分區:通過MOVE ONLINE關鍵字實現在線分區移動。移動過程中，對表和被移動的分區可以執行查詢操作，

DML語句以及分區的創建和維護操作。整個移動過程對用戶來說是透明的。

2.多個分區同時操作：可以對多個分區同時進行維護操作，如將一年的12個分區合并到一個新的分區中，或者將一個分區

分成多個分區。可以通過FOR語句指定操作的每個語句，對于RANGE分區而言，也可以通過TO來指定處理分區的范圍。

多個分區操作自動并行完成。

3.INTERVAL-REFERENCE分區：把11g的interval分區和reference分區結合，這樣主表自動增加一個分區后，所有字表，

孫子表·····重重孫子表上都會自動隨著外界列數據增加，自動創建新的分區。

4.TRUNCATE和EXCHANGE分區及子分區。五分是TRUNCATE還是EXCHANGE分區，在主表上執行，都可以級聯的作用在字表，

孫子吧·····重重孫子表上同時執行。對于TRUNCATE而言，所有表的TRUNACATE操作在同一個事務中，如果中途失敗，

會回滾到之前的狀態。通過關鍵字CASCADE實現。

5.異步全局索引維護：對于非常大的分區表而言，UPDATE GLOBAL INDEX不再是痛苦。Oracle可以實現了異步維護的

功能，即使是幾億條的記錄的全局索引，在分區維護操作，比如DROP或TRUNCATE后，仍然是VALID狀態,索引不會失效，

不過索引的狀態是包含OBSOLETE數據，當維護操作完成，索引狀態恢復。

6.部分本地和全局索引：Oracle的所有可以在分區級別定義。無論全局索引還是本地所有都可以在分區表的部分分區

上建立，其他分區上則沒有所有。當通過所有列訪問全表數據時，Oracle通過UNION ALL實現，一部分通過索引掃描，

另一部分通過全分區掃描。這可以減少對歷史數據的索引量，增強了靈活性。

具體例子：

1)添加多個新分區：

在12c之前，一次只能添加一個新分區到一個已存在的分區表。在12c中只需要一條單獨的ALTER TABLE ADD PARTITION

命令就可以添加N個新分區。

ALTER TABLE EMP_PART ADD PARTITION PARTITION P4 VALUES LESS THAN(35000) PARTITION P5 VALUES LESS THAN(40000)

1

2

3

同樣，只要MAXVALUE分區不存在，可以添加多個新分區到一個列表和系統分區表

2)刪除、截斷多個分區/子分區

通過在此之前，一次只能刪除/截斷一個分區。12c中通過ALTER TABLE table_name {TRUNCAT|DROP} PARTITIONS

ALTER TABLE EMP_PART DROP PARTITIONS P4,P5; ALTER TABLE EMP_PART TRUNCATE PARTITIONS P4,P5;

1

2

要保持索引更新，使用UPDATE INDEXES或UPDATE GLOBAL INDEXES語句。

ALTER TABLE EMP_PART DROP PARTITIONS P4,P5 UPDATE GLOBAL INDEXES; ALTER TABLE EMP_PART TRUNCATE PARTITIONS P4,P5 UPDATE GLOBAL INDEXES;

1

2

如果沒有使用UPDATE GLOBAL INDEXES更新索引，也可以通過查詢ORPHANED_ENTRIES字段找出是否有索引包含過期的條目

3)將單個分區分割為多個新分區

在此之前是無法單個命令完成這個操作的。

ALTER TABLE EMP_PART SPLIT PARTITIONS p_max INTO (PARTITION P4 VALUES LESS THAN (30000), PARTITION P5 VALUES LESS THAN(40000), PARTITION P_MAX);

1

2

3

4

4)將多個分區合并為一個分區

ALTER TABLE EMP_PART MERGE PARTITIONS P2,P3,P4 INTO PARTITION P_MERGE;

1

如果是連續分區，可以通過TO來

ALTER TABLE EMP_PART MERGE PARTITIONS P2 TO P4 INTO PARTITION P_MERGE;

1

普通表轉分區表方法

將普通表轉換成分區表有 4 種方法：

1. Export/import method

2. Insert with a subquery method

3. Partition exchange method

4. DBMS_REDEFINITION

詳情參考

How to Partition a Non-partitioned Table [ID 1070693.6]

或者

oracle將普通表改為分區表

表分區的相關操作

1.添加分區

添加新的分區有 2 中情況：

（ 1）原分區里邊界是 maxvalue 或者 default。 這種情況下，我們需要把邊界分區 drop 掉，加上新分區后，在添加上新的分區。 或者采用 split，對邊界分區進行拆分。

（ 2）沒有邊界分區的。 這種情況下，直接添加分區就可以了。

以邊界分區添加新分區示例：

（ 1）分區表和索引的信息如下：

SQL> create table custaddr 2 ( 3 id varchar2(15 byte) not null, 4 areacode varchar2(4 byte) 5 ) 6 partition by list (areacode) 7 ( 8 partition t_list556 values ('556') tablespace icd_service, 9 partition p_other values (default)tablespace icd_service 10 );

1

2

3

4

5

6

7

8

9

10

表已創建。

SQL> create index ix_custaddr_id on custaddr(id) 2 local ( 3 partition t_list556 tablespace icd_service, 4 partition p_other tablespace icd_service 5 );

1

2

3

4

5

索引已創建。

（ 2）插入幾條測試數據：

SQL> insert into custaddr values('1','556'); 已創建 1 行。 SQL> insert into custaddr values('2','551'); 已創建 1 行。 SQL> insert into custaddr values('3','555'); 已創建 1 行。 SQL> commit; 提交完成。 SQL> select * from custaddr; ID AREA --------------- ---- 1 556 2 551 3 555 SQL> select * from custaddr partition(t_list556); ID AREA --------------- ---- 1 556 SQL>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

（ 3）刪除 default 分區

sql> alter table custaddr drop partition p_other; 表已更改。 sql> select table_name,partition_name from user_tab_partitions where table_name='CUSTADDR'; table_name partition_name ------------------------------ ------------------------------ custaddr t_list556

1

2

3

4

5

6

7

（ 4）添加新分區

SQL> alter table custaddr add partition t_list551 values('551') tablespace icd_service; 表已更改。 SQL> select table_name,partition_name from user_tab_partitions where table_name='CUSTADDR'; TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ CUSTADDR T_LIST556 CUSTADDR T_LIST551

1

2

3

4

5

6

7

8

（ 5）添加 default 分區

SQL> alter table custaddr add partition p_other values (default) tablespace icd_service; 表已更改。 SQL> select table_name,partition_name from user_tab_partitions where table_name='CUSTADDR'; TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ CUSTADDR T_LIST556 CUSTADDR T_LIST551 CUSTADDR P_OTHER

1

2

3

4

5

6

7

8

9

10

（ 6）對于局部索引， oracle 會自動增加一個局部分區索引。驗證一下：

sql> select owner,index_name,table_name,partitioning_type from dba_part_indexes where index_name='ix_custaddr_id'; owner index_name table_name ---------------------- ------------------------------ ------------------ icd ix_custaddr_id custaddr sql> select index_owner,index_name,partition_name from dba_ind_partitions where index_name='ix_custaddr_id'; index_owner index_name partition_name ------------------------------ ------------------------------ ------------------ icd ix_custaddr_id p_other icd ix_custaddr_id t_list551 icd ix_custaddr_id t_list556

1

2

3

4

5

6

7

8

9

10

11

12

13

分區索引自動創建了。

用 split方法示例

sql> alter table custaddr split partition p_other values('552') into (partition t_list552 tablespace icd_service, partition p_other tablespace icd_service); 表已更改。

1

2

3

–

注意values(‘552’)，如果是 Range 類型的，使用 at， List 使用 Values。

SQL> select table_name,partition_name from user_tab_partitions where table_name='CUSTADDR'; TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ CUSTADDR T_LIST556 CUSTADDR T_LIST551 CUSTADDR T_LIST552 CUSTADDR P_OTHER SQL> select index_owner,index_name,partition_name from dba_ind_partitions where index_name='IX_CUSTADDR_ID'; index_owner index_name partition_name ------------------------------ ------------------------------ ------------------ icd ix_custaddr_id p_other icd ix_custaddr_id t_list551 icd ix_custaddr_id t_list552 icd ix_custaddr_id t_list556

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

注意：分區表會自動維護局部分區索引。全局索引會失效，需要進行 rebuild。

2.刪除分區

alter table T_TRACK drop partition p_2005_04;

1

2

3.添加子分區

alter table T_TRACK modify partition P_2005_01 add subpartition P_2005_01_P1017 values('P1017');

1

2

3

4.刪除子分區

alter table T_TRACK drop subpartition p_2005_01_p1017;

1

5.截斷一個分區表中的一個分區的數據：

alter table sales3 truncate partition sp1 --這種方式會使全局分區索引無效 alter table sales3 truncate partition sp1 update indexes --這種方式全局分區索引不會無效

1

2

3

4

說明：

Truncate 相對 delete 操作很快，數據倉庫中的大量數據的批量數據加載可能

會有用到； 截斷分區同樣會自動維護局部分區索引，同時會使全局索引 unusable,需要重建

6.截斷分區表的子分區

alter table comp truncate subpartition sub1

1

7.截斷帶有約束的分區表

a、禁用約束 alter table sales disable constraint dname_sales1 b、截斷分區 alter table sales truncate partitoin dec c、啟用約束 alter table sales enable constraint dname_sales1

1

2

3

4

5

6

8.查看一個表是不是分區表

select table_name,partitioned from user_tables; TABLE_NAME PAR ------------------------------ --- DEPT NO DEPT3 YES

1

2

3

4

5

9.將一個表的分區從一個表空間移動到另一個表空間

a、查看分區在哪個表空間 SELECT TABLE_OWNER,TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME, SUBPARTITION_COUNT FROM DBA_TAB_PARTITIONS WHERE TABLE_OWNER='SCOTT'; b、移動分區 alter table sales move partiton sp1 tablespace tp; c、檢查是否移動成功 SELECT TABLE_OWNER,TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME, SUBPARTITION_COUNT FROM DBA_TAB_PARTITIONS WHERE TABLE_OWNER='SCOTT'; 移動表空間后，要重建索引，否則索引會變得無效 alter index xxx rebuild

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

注意： 分區移動會自動維護局部分區索引， oracle 不會自動維護全局索引，所以需要我們重新 rebuild 分區索引，具體需要 rebuild 哪些索引，可以通過

dba_part_indexes,dba_ind_partitions 去判斷

SQL> Select index_name,status From user_indexes Where table_name='CUSTADDR'; INDEX_NAME STATUS ------------------------------ -------- IX_CUSTADDR_ID N/A

1

2

3

4

5

10.合并分區：

相鄰的分區可以 merge 為一個分區，新分區的下邊界為原來邊界值較低的分區，上邊界為原來邊界值較高的分區， 原先的局部索引相應也會合并，全局索引會失效，需要 rebuild。

alter table sales3 merge partitons sp1,sp3 into partition sp3

1

合并后的分區名，不能是邊界值較低的那個

11.與分區表相關的數據字典視圖：

DBA_TAB_PARTITIONS DBA_IND_PARTITIONS DBA_TAB_SUBPARTITIONS DBA_IND_SUBPARTITIONS

1

2

3

4

5

Oracle 數據庫

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。