聊聊怎樣使用Oracle分區表

作者： jinjunzhu 2021-01-20 08:07:52

單表的數據量如果太大，會影響到讀寫性能。我們可以使用分庫分表來解決單表的性能問題。Oracle的分區表是將一張大表在物理上分成幾張較小的表，從邏輯上來看仍然是一張完整的表。

本文轉載自微信公眾號「 jinjunzhu」，作者 jinjunzhu 。轉載本文請聯系 jinjunzhu公眾號。

單表的數據量如果太大，會影響到讀寫性能。我們可以使用分庫分表來解決單表的性能問題。Oracle的分區表是將一張大表在物理上分成幾張較小的表，從邏輯上來看仍然是一張完整的表。這樣每次DML操作可以只考慮其中一張分區表。oracle建議單表大小超過2GB時就使用分區表。

今天我們就來聊一聊分區表的使用。

分區類型

范圍分區

范圍分區是最常用的分區方法，它使用分區鍵來作為劃分分區的依據，分區鍵可以使用時間、id等易于比較的字段。

1.使用id進行分區

我們創建一張操作記錄表，有3個字段，分別是id，type和op_time，這里我們使用id對這張表做3個分區，分別為t_operate_log_1(id<100)，t_operate_log_2(100<=id<200)，t_operate_log_3(id>=200),建表sql如下：

create table t_operate_log 
( 
  id   number(7), 
  type varchar(1), 
  op_time date 
) 
PARTITION BY RANGE(id) 
( 
 partition t_operate_log_1 values less than (100), 
 partition t_operate_log_2 values less than (200), 
 partition t_operate_log_3 values less than (300) 
)

創建表成功后，我們插入3條數據，id分別是1，100，200，sql如下：

insert into t_operate_log values(1, '1', sysdate); 
insert into t_operate_log values(100, '1', sysdate); 
insert into t_operate_log values(200, '1', sysdate);

下面這3條sql分別可以查到id是1，100，200的這3條記錄：

select * from t_operate_log partition(t_operate_log_1); 
select * from t_operate_log partition(t_operate_log_2); 
select * from t_operate_log partition(t_operate_log_3);

下面這個sql可以查到t_operate_log中所有記錄：

select * from t_operate_log

注意：我們可以用下面命令再添加一個分區：

ALTER TABLE t_operate_log ADD PARTITION t_operate_log_4 VALUES LESS THAN(400);

但是要注意一點，如果一個分區的范圍是maxvalue(比如把300替換成maxvalue)，添加分區會失敗。

我們也可以用下面命令刪除一個分區：

ALTER TABLE t_operate_log DROP PARTITION t_operate_log_4;

2.使用時間進行分區

我們還是使用上面的表進行試驗，這次我們使用op_time字段做3個分區，分別為：

t_operate_log_1(op_time時間小于2019-01-17) 
 
t_operate_log_2(2019-01-17<=id<2020-01-17)，t_operate_log_3(id>=2020-01-17)

建表sql如下：

create table t_operate_log 
( 
  id   number(7), 
  type varchar(1), 
  op_time date 
) 
PARTITION BY RANGE(op_time) 
( 
 partition t_operate_log_1 values less than (to_date('2019-01-17','yyyy-MM-dd')), 
 partition t_operate_log_2 values less than (to_date('2020-01-17','yyyy-MM-dd')), 
 partition t_operate_log_3 values less than (maxvalue) 
)

創建表成功后，我們插入3條數據，id分別是1，2，3，時間分別是2019-01-16、2019-01-17、2020-01-17：

insert into t_operate_log values(1, '1', to_date('2019-01-16','yyyy-MM-dd')); 
insert into t_operate_log values(2, '1', to_date('2019-01-17','yyyy-MM-dd')); 
insert into t_operate_log values(3, '1', to_date('2020-01-17','yyyy-MM-dd'));

下面這3條sql分別可以查到id是1，2，3的這3條記錄：

select * from t_operate_log partition(t_operate_log_1); 
select * from t_operate_log partition(t_operate_log_2); 
select * from t_operate_log partition(t_operate_log_3);

下面這個sql可以查到t_operate_log中所有記錄：

select * from t_operate_log

列表分區

列表分區的使用場景是表中的某一列只有固定幾個值，比如上面的操作日志表，假如我們的type有4個類型：add、edit、delete、query，我們建立分區表如下：

create table t_operate_log 
( 
  id   number(7), 
  type varchar2(10), 
  op_time date 
) 
PARTITION BY list(type) 
( 
 partition t_operate_log_add values('add'), 
 partition t_operate_log_delete values('delete'), 
 partition t_operate_log_edit values('edit'), 
 partition t_operate_log_query values('query') 
)

創建表成功后，我們插入4條數據，type分別為add，delete，edit，query

insert into t_operate_log values(1, 'add', to_date('2019-01-16','yyyy-MM-dd')); 
insert into t_operate_log values(2, 'delete', to_date('2019-01-16','yyyy-MM-dd')); 
insert into t_operate_log values(3, 'edit', to_date('2020-01-16','yyyy-MM-dd')); 
insert into t_operate_log values(4, 'query', to_date('2020-01-16','yyyy-MM-dd'));

下面的4條sql分別可以查出每一種type類型對應的數據：

select * from t_operate_log partition(t_operate_log_add); 
select * from t_operate_log partition(t_operate_log_delete); 
select * from t_operate_log partition(t_operate_log_edit); 
select * from t_operate_log partition(t_operate_log_query);

注意：我們可以給列表分區增加元素，比如我們在t_operate_log_query這個分區表中增加一個元素"select",sql如下：

ALTER TABLE t_operate_log MODIFY PARTITION t_operate_log_query ADD VALUES('select');

也可以給列表分區刪除元素，比如我們在t_operate_log_query這個分區表中刪除元素"select",sql如下：

ALTER TABLE t_operate_log MODIFY PARTITION t_operate_log_query DROP VALUES('select');

如果每個分區的數據量不大，沒有必須做太多分區，我們創建分區時可以減少數量，如下創建分區的sql我們把t_operate_log創建成了2個分區：

create table t_operate_log 
( 
  id   number(7), 
  type varchar2(10), 
  op_time date 
) 
PARTITION BY list(type) 
( 
 partition t_operate_log_add_del values('add','delete'), 
 partition t_operate_log_edit_query values('edit','query') 
)

創建成功后我們還是使用上面的insert語句插入4條數據，成功后我們用下面的sql查詢，分別可以查詢出2條數據：

#查詢出type是add和delete的數據 
select * from t_operate_log partition(t_operate_log_add_del); 
#查詢出type是edit和query的數據 
select * from t_operate_log partition(t_operate_log_edit_query);

HASH分區

范圍分區和列表分區都使用了某一個字段來做分區鍵，使用這個字段的值作為分區的依據，使用簡單。但是有一個問題就是分區鍵的區分度要大，不然容易存在分區數據量嚴重不均勻的情況。

如果沒有一個合適的分區鍵，使用HASH分區就是一個很好的選擇，HASH分區的好處是可以讓分區表數據分布均勻。我們還是以上面的表為例，我們使用HASH分區來創建4個分區表，sql如下：

create table t_operate_log 
( 
  id   number(7), 
  type varchar2(10), 
  op_time date 
) 
PARTITION BY hash(id) 
( 
 partition t_operate_log_1, 
 partition t_operate_log_2, 
 partition t_operate_log_3, 
 partition t_operate_log_4 
)

創建成功后我們插入100條數據，id是從1~100,我們分別查詢4個分區表的數據，數據條數分別是20、28、25、27。

使用HASH分區有2個建議：

分區鍵的值最好是連續的
分區數量最好是2的n次方，這樣可以對hash運算更加友好(想想java中HashMap構造函數的initialCapacity參數)

注意：

HASH分區支持添加操作，比如我們添加一個分區，sql如下：

ALTER TABLE t_operate_log ADD PARTITION t_operate_log_5;

創建后我們查詢t_operate_log_5這張表，發現也有數據，這是因為添加或刪除分區時，所有數據會重新計算HASH值，重新分配到不同的分區表中。
HASH分區是不能刪除的，刪除會報“ORA-14255:不能對范圍、列表，組合范圍或組合列表方式對表進行分區”

范圍列表組合分區

在一些復雜的場景下，我們可以使用范圍和列表組合分區來進行分區，比如在前面講范圍分區和列表分區的例子，我們做一個范圍列表組合分區的改進，sql如下：

create table t_operate_log 
( 
  id   number(7), 
  type varchar2(10), 
  op_time date 
) 
PARTITION BY RANGE(op_time) SUBPARTITION BY LIST (type)   
(   
   PARTITION t_operate_log_time_1 VALUES LESS THAN(to_date('2021-01-16','yyyy-MM-dd')) 
  (        
     SUBPARTITION t_operate_log_add_1 values('add'), 
     SUBPARTITION t_operate_log_delete_1 values('delete'), 
     SUBPARTITION t_operate_log_edit_1 values('edit'), 
     SUBPARTITION t_operate_log_query_1 values('query') 
  ),    
   PARTITION t_operate_log_time_2 VALUES LESS THAN (to_date('2021-01-17','yyyy-MM-dd')) 
  (    
       SUBPARTITION t_operate_log_add_2 values('add'), 
     SUBPARTITION t_operate_log_delete_2 values('delete'), 
     SUBPARTITION t_operate_log_edit_2 values('edit'), 
     SUBPARTITION t_operate_log_query_2 values('query') 
  )    
)

上面我按照op_time做了分區，然后按照type做了子分區，這是我插入8條數據，每張子分區表一條，sql如下：

insert into t_operate_log values(1, 'add', to_date('2021-01-15','yyyy-MM-dd')); 
insert into t_operate_log values(2, 'delete', to_date('2021-01-15','yyyy-MM-dd')); 
insert into t_operate_log values(3, 'edit', to_date('2021-01-15','yyyy-MM-dd')); 
insert into t_operate_log values(4, 'query', to_date('2021-01-15','yyyy-MM-dd')); 
insert into t_operate_log values(5, 'add', to_date('2021-01-16','yyyy-MM-dd')); 
insert into t_operate_log values(6, 'delete', to_date('2021-01-16','yyyy-MM-dd')); 
insert into t_operate_log values(7, 'edit', to_date('2021-01-16','yyyy-MM-dd')); 
insert into t_operate_log values(8, 'query', to_date('2021-01-16','yyyy-MM-dd'));

然后我用下面的sql可以查出每張子分區表有1條數據：

select * from t_operate_log SUBPARTITION(t_operate_log_add_1); 
select * from t_operate_log SUBPARTITION(t_operate_log_delete_1); 
select * from t_operate_log SUBPARTITION(t_operate_log_edit_1); 
select * from t_operate_log SUBPARTITION(t_operate_log_query_1); 
select * from t_operate_log SUBPARTITION(t_operate_log_add_2); 
select * from t_operate_log SUBPARTITION(t_operate_log_delete_2); 
select * from t_operate_log SUBPARTITION(t_operate_log_edit_2); 
select * from t_operate_log SUBPARTITION(t_operate_log_query_2);

注意：我們可以添加子分區，比如我們給t_operate_log_time_1這個分區添加一個子分區，列表分區的type字段值是'select',sql如下：

ALTER TABLE t_operate_log MODIFY PARTITION t_operate_log_time_1 ADD SUBPARTITION t_operate_log_select_1 values('select');

我們也可以刪除子分區，比如刪除type是'select'的這個分區，sql如下：

ALTER TABLE t_operate_log DROP SUBPARTITION t_operate_log_select_1;

范圍和HASH組合分區

如果范圍列表組合分區導致分區表數據不太均衡時，我們可以考慮范圍分區和HASH分區來組合使用，看如下的建表sql:

create table t_operate_log 
( 
  id   number(7), 
  type varchar2(10), 
  op_time date 
) 
PARTITION BY RANGE(op_time) SUBPARTITION BY HASH (id)   
(   
   PARTITION t_operate_log_time_1 VALUES LESS THAN(to_date('2021-01-16','yyyy-MM-dd')) 
  (        
     SUBPARTITION t_operate_log_1, 
     SUBPARTITION t_operate_log_2, 
     SUBPARTITION t_operate_log_3, 
     SUBPARTITION t_operate_log_4 
  ),    
   PARTITION t_operate_log_time_2 VALUES LESS THAN (to_date('2021-01-17','yyyy-MM-dd')) 
  (    
     SUBPARTITION t_operate_log_5, 
     SUBPARTITION t_operate_log_6, 
     SUBPARTITION t_operate_log_7, 
     SUBPARTITION t_operate_log_8 
  )    
)

我們用下面的sql插入8條記錄：

insert into t_operate_log values(1, 'add', to_date('2021-01-15','yyyy-MM-dd')); 
insert into t_operate_log values(2, 'delete', to_date('2021-01-15','yyyy-MM-dd')); 
insert into t_operate_log values(3, 'edit', to_date('2021-01-15','yyyy-MM-dd')); 
insert into t_operate_log values(4, 'query', to_date('2021-01-15','yyyy-MM-dd')); 
insert into t_operate_log values(5, 'add', to_date('2021-01-16','yyyy-MM-dd')); 
insert into t_operate_log values(6, 'delete', to_date('2021-01-16','yyyy-MM-dd')); 
insert into t_operate_log values(7, 'edit', to_date('2021-01-16','yyyy-MM-dd')); 
insert into t_operate_log values(8, 'query', to_date('2021-01-16','yyyy-MM-dd'));

我們分別查詢這8張表的記錄時，發現并不是每張表中1條數據，這是使用了HASH分區的原因。

列表和HASH組合分區

我們也可以使用列表和HASH做組合進行分區，建表sql如下：

create table t_operate_log 
( 
  id   number(7), 
  type varchar2(10), 
  op_time date 
) 
PARTITION BY list(type) SUBPARTITION BY HASH (id)   
(   
   PARTITION t_operate_log_type_add VALUES('add') 
  (        
     SUBPARTITION t_operate_log_add_1, 
     SUBPARTITION t_operate_log_add_2 
  ),    
   PARTITION t_operate_log_type_delete VALUES('delete') 
  (    
     SUBPARTITION t_operate_log_delete_1, 
     SUBPARTITION t_operate_log_delete_2 
  ) ,   
   PARTITION t_operate_log_type_edit VALUES('edit') 
  (        
     SUBPARTITION t_operate_log_edit_1, 
     SUBPARTITION t_operate_log_edit_2 
  ),    
   PARTITION t_operate_log_type_query VALUES('query') 
  (    
     SUBPARTITION t_operate_log_query_1, 
     SUBPARTITION t_operate_log_query_2 
  ) 
)

注意事項

1.創建分區表時可以選擇TABLESPACE，比如下面的sql：

create table t_operate_log 
( 
  id   number(7), 
  type varchar2(10), 
  op_time date 
) 
PARTITION BY list(type) 
( 
 partition t_operate_log_add_del values('add','delete') TABLESPACE LOG, 
 partition t_operate_log_edit_query values('edit','query') TABLESPACE LOG 
)

2.使用下面sql可以查看分區列表和子分區列表，TABLE_NAME要大寫：

SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='T_OPERATE_LOG'; 
SELECT * FROM USER_TAB_SUBPARTITIONS WHERE TABLE_NAME='T_OPERATE_LOG';

3.如果表中只有一個分區，那是不能刪除分區的，刪除的時候報ORA-14083:無法刪除分區表的唯一分區

4.除了添加和刪除，分區還支持合并、拆分、重命名等多個操作，感興趣的可以自己研究。

分區索引創建

Oracle分區索引有2種，local索引和global索引。

首先我要解釋幾個概念，從官網翻譯：

Local - All index entries in a single partition will correspond to a single table partition (equipartitioned). They are created with the LOCAL keyword and support partition independance. Equipartioning allows oracle to be more efficient whilst devising query plans. 
#本地索引 - 單個分區中的所有索引項只對應于單個表分區。使用LOCAL關鍵字創建，并且支持分區獨立性。本地索引使oracle在設計查詢計劃時更加高效。 
Global - Index in a single partition may correspond to multiple table partitions. They are created with the GLOBAL keyword and do not support partition independance. Global indexes can only be range partitioned and may be partitioned in such a fashion that they look equipartitioned, but Oracle will not take advantage of this structure. 
#全局索引 - 單個分區中的索引對應于多個表分區。使用GLOBAL關鍵字創建，不支持分區獨立性。全局索引只支持范圍分區，或者分區方式看起來是均衡的，但Oracle不會利用這種結構。

Prefixed - The partition key is the leftmost column(s) of the index. Probing this type of index is less costly. If a query specifies the partition key in the where clause partition pruning is possible, that is, not all partitions will be searched. 
#前綴索引 - 分區關鍵字在索引字段的左邊。檢測這種索引比較容易，如果查詢的where條件中包含了分區鍵，就會消除掉不必要的分區，不會掃描所有分區表了。 
Non-Prefixed - Does not support partition pruning, but is effective in accessing data that spans multiple partitions. Often used for indexing a column that is not the tables partition key, when you would like the index to be partitioned on the same key as the underlying table. 
#分前綴索引 - 不支持分區消除，但在跨分區查找數據時很有效。通常用于創建的索引不是分區鍵，而這個索引想用在所有子表的情況。

下面我們先來創建local索引，下面的sql我先創建一個范圍分區，然后創建了一個本地索引：

create table t_operate_log 
( 
  id   number(7), 
  type varchar(1), 
  op_time date 
) 
PARTITION BY RANGE(id) 
( 
 partition t_operate_log_1 values less than (100), 
 partition t_operate_log_2 values less than (200), 
 partition t_operate_log_3 values less than (300) 
) 
#創建本地索引 
create index index_t_operate_log on t_operate_log(type) local 
( 
 partition t_operate_log_1, 
 partition t_operate_log_2, 
 partition t_operate_log_3 
)

這里有幾點說明：

local索引是針對單個分區表的索引，無論是普通索引還是唯一索引，這個索引只對單個分區表有效。
創建local索引，可以不加括號后面的語句，但是如果加了必須選擇所有分區表，否則會報“ORA-14024:LOCAL索引的分區數必須等于基礎表的分區數”。

下面我們創建一個global索引，sql如下：

CREATE INDEX index_t_operate_log ON t_operate_log (type) GLOBAL

注意:oracle不支持全局非前綴索引。

對已經存在的表分區

1.創建表

create table t_operate_log 
( 
  id   number(7), 
  type varchar(1), 
  op_time date 
)

創建完成后插入100條數據，id為1~100。

2.創建一個分區表，只有1個分區

create table t_operate_log_p 
 
( 
 
id number(7), 
 
type varchar(1), 
 
op_time date 
 
) 
 
PARTITION BY RANGE(id) 
 
( 
 
partition t_operate_log_1 values less than (101) 
 
)

3.把原表數據抽取到分區表

ALTER TABLE t_operate_log_p 
  EXCHANGE PARTITION t_operate_log_1 
  WITH TABLE t_operate_log 
  WITHOUT VALIDATION;

這時我們查看t_operate_log_p這個表有100條數據。

4.刪除原有表，把新的表改名為原來的表

DROP TABLE t_operate_log; 
RENAME t_operate_log_p TO t_operate_log;

5.把新表拆分成多個分區表

ALTER TABLE t_operate_log SPLIT PARTITION t_operate_log_1 AT (id) 
INTO (PARTITION t_operate_log_2, 
      PARTITION t_operate_log_3);

6.驗證

使用下面sql我們可以看到有2個分區:

SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='T_OPERATE_LOG';

使用下面sql我們可以看到，第一個分區有49條，第二個分區有51條，可見我們分區用的id=50的記錄分到了第二個分區表：

select * from t_operate_log PARTITION(t_operate_log_2);

select * from t_operate_log PARTITION(t_operate_log_3);

參考鏈接：

https://oracle-base.com/articles/8i/partitioned-tables-and-indexes

責任編輯：武曉燕來源： jinjunzhu

oracle 分區單表