如何使用Oracle分區(qū)表

這篇文章主要介紹“如何使用Oracle分區(qū)表”，在日常操作中，相信很多人在如何使用Oracle分區(qū)表問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”如何使用Oracle分區(qū)表”的疑惑有所幫助！接下來，請跟著小編一起來學(xué)習(xí)吧！

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

今天我們就來聊一聊分區(qū)表的使用。

分區(qū)類型

范圍分區(qū)

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

1.使用id進行分區(qū)

我們創(chuàng)建一張操作記錄表，有3個字段，分別是id，type和op_time，這里我們使用id對這張表做3個分區(qū)，分別為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) )

創(chuàng)建表成功后，我們插入3條數(shù)據(jù)，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

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

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

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

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

ALTER TABLE t_operate_log DROP PARTITION t_operate_log_4;

2.使用時間進行分區(qū)

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

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

創(chuàng)建表成功后，我們插入3條數(shù)據(jù)，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

列表分區(qū)

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

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

創(chuàng)建表成功后，我們插入4條數(shù)據(jù)，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類型對應(yīng)的數(shù)據(jù)：

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

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

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

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

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

如果每個分區(qū)的數(shù)據(jù)量不大，沒有必須做太多分區(qū)，我們創(chuàng)建分區(qū)時可以減少數(shù)量，如下創(chuàng)建分區(qū)的sql我們把t_operate_log創(chuàng)建成了2個分區(qū)：

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

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

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

HASH分區(qū)

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

如果沒有一個合適的分區(qū)鍵，使用HASH分區(qū)就是一個很好的選擇，HASH分區(qū)的好處是可以讓分區(qū)表數(shù)據(jù)分布均勻。我們還是以上面的表為例，我們使用HASH分區(qū)來創(chuàng)建4個分區(qū)表，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 )

創(chuàng)建成功后我們插入100條數(shù)據(jù)，id是從1~100,我們分別查詢4個分區(qū)表的數(shù)據(jù)，數(shù)據(jù)條數(shù)分別是20、28、25、27。

使用HASH分區(qū)有2個建議：

• 分區(qū)鍵的值最好是連續(xù)的

• 分區(qū)數(shù)量最好是2的n次方，這樣可以對hash運算更加友好(想想java中HashMap構(gòu)造函數(shù)的initialCapacity參數(shù))

注意：

• HASH分區(qū)支持添加操作，比如我們添加一個分區(qū)，sql如下：

ALTER TABLE t_operate_log ADD PARTITION t_operate_log_5;

• 創(chuàng)建后我們查詢t_operate_log_5這張表，發(fā)現(xiàn)也有數(shù)據(jù)，這是因為添加或刪除分區(qū)時，所有數(shù)據(jù)會重新計算HASH值，重新分配到不同的分區(qū)表中。

• HASH分區(qū)是不能刪除的，刪除會報“ORA-14255:不能對范圍、列表，組合范圍或組合列表方式對表進行分區(qū)”

范圍列表組合分區(qū)

在一些復(fù)雜的場景下，我們可以使用范圍和列表組合分區(qū)來進行分區(qū)，比如在前面講范圍分區(qū)和列表分區(qū)的例子，我們做一個范圍列表組合分區(qū)的改進，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做了分區(qū)，然后按照type做了子分區(qū)，這是我插入8條數(shù)據(jù)，每張子分區(qū)表一條，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可以查出每張子分區(qū)表有1條數(shù)據(jù)：

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

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

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

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

ALTER TABLE t_operate_log DROP SUBPARTITION t_operate_log_select_1;

范圍和HASH組合分區(qū)

如果范圍列表組合分區(qū)導(dǎo)致分區(qū)表數(shù)據(jù)不太均衡時，我們可以考慮范圍分區(qū)和HASH分區(qū)來組合使用，看如下的建表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張表的記錄時，發(fā)現(xiàn)并不是每張表中1條數(shù)據(jù)，這是使用了HASH分區(qū)的原因。

列表和HASH組合分區(qū)

我們也可以使用列表和HASH做組合進行分區(qū)，建表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.創(chuàng)建分區(qū)表時可以選擇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可以查看分區(qū)列表和子分區(qū)列表，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.如果表中只有一個分區(qū)，那是不能刪除分區(qū)的，刪除的時候報ORA-14083:無法刪除分區(qū)表的唯一分區(qū)

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

分區(qū)索引創(chuàng)建

Oracle分區(qū)索引有2種，local索引和global索引。

首先我要解釋幾個概念，從官網(wǎng)翻譯：

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. #本地索引 - 單個分區(qū)中的所有索引項只對應(yīng)于單個表分區(qū)。使用LOCAL關(guān)鍵字創(chuàng)建，并且支持分區(qū)獨立性。本地索引使oracle在設(shè)計查詢計劃時更加高效。 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. #全局索引 - 單個分區(qū)中的索引對應(yīng)于多個表分區(qū)。使用GLOBAL關(guān)鍵字創(chuàng)建，不支持分區(qū)獨立性。全局索引只支持范圍分區(qū)，或者分區(qū)方式看起來是均衡的，但Oracle不會利用這種結(jié)構(gòu)。

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. #前綴索引 - 分區(qū)關(guān)鍵字在索引字段的左邊。檢測這種索引比較容易，如果查詢的where條件中包含了分區(qū)鍵，就會消除掉不必要的分區(qū)，不會掃描所有分區(qū)表了。 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. #分前綴索引 - 不支持分區(qū)消除，但在跨分區(qū)查找數(shù)據(jù)時很有效。通常用于創(chuàng)建的索引不是分區(qū)鍵，而這個索引想用在所有子表的情況。

下面我們先來創(chuàng)建local索引，下面的sql我先創(chuàng)建一個范圍分區(qū)，然后創(chuàng)建了一個本地索引：

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) ) #創(chuàng)建本地索引 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索引是針對單個分區(qū)表的索引，無論是普通索引還是唯一索引，這個索引只對單個分區(qū)表有效。

• 創(chuàng)建local索引，可以不加括號后面的語句，但是如果加了必須選擇所有分區(qū)表，否則會報“ORA-14024:LOCAL索引的分區(qū)數(shù)必須等于基礎(chǔ)表的分區(qū)數(shù)”。

下面我們創(chuàng)建一個global索引，sql如下：

CREATE INDEX index_t_operate_log ON t_operate_log (type) GLOBAL

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

對已經(jīng)存在的表分區(qū)

1.創(chuàng)建表

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

創(chuàng)建完成后插入100條數(shù)據(jù)，id為1~100。

2.創(chuàng)建一個分區(qū)表，只有1個分區(qū)

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.把原表數(shù)據(jù)抽取到分區(qū)表

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

這時我們查看t_operate_log_p這個表有100條數(shù)據(jù)。

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

DROP TABLE t_operate_log; RENAME t_operate_log_p TO t_operate_log;

5.把新表拆分成多個分區(qū)表

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個分區(qū):

SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='T_OPERATE_LOG';

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

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

到此，關(guān)于“如何使用Oracle分區(qū)表”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識，請繼續(xù)關(guān)注億速云網(wǎng)站，小編會繼續(xù)努力為大家?guī)砀鄬嵱玫奈恼拢?/p>