Oracle分區(qū)表和分區(qū)索引在VLDB中的研究

趙衛(wèi)東， 劉永紅， 鄢 濤， 于 曦

(1.成都大學(xué) 模式識別與智能信息處理四川省高校重點實驗室， 四川 成都 610106;2.成都大學(xué) 信息科學(xué)與工程學(xué)院， 四川 成都 610106)

Oracle分區(qū)表和分區(qū)索引在VLDB中的研究

趙衛(wèi)東1,2， 劉永紅1,2， 鄢 濤1,2， 于 曦1,2

(1.成都大學(xué) 模式識別與智能信息處理四川省高校重點實驗室， 四川 成都 610106;2.成都大學(xué) 信息科學(xué)與工程學(xué)院， 四川 成都 610106)

分區(qū)表和分區(qū)索引功能是數(shù)據(jù)庫管理中的關(guān)鍵技術(shù)之一，是海量數(shù)據(jù)庫管理(Very Large Databases，VLDB)中一個重要的性能提升機(jī)制.分區(qū)技術(shù)的原理是將邏輯上的一個大表拆分成多個獨立的物理分區(qū)來存儲，從而提高數(shù)據(jù)I/O性能.面對大數(shù)據(jù)的存儲，幾乎所有Oracle數(shù)據(jù)庫都用分區(qū)技術(shù)來提高查詢數(shù)據(jù)的性能.通過分析一個項目案例，說明如何綜合使用分區(qū)表和分區(qū)索引來提高大數(shù)據(jù)的查詢速度.

Oracle;分區(qū)表;分區(qū)索引;VLDB

0 引 言

分區(qū)是構(gòu)建千兆字節(jié)數(shù)據(jù)系統(tǒng)或超高可用性系統(tǒng)的關(guān)鍵工具.Oracle分區(qū)工具將表或者索引物理分割成多個小單元.應(yīng)用時，從邏輯上看是一個表或者一個索引，而物理上卻都是獨立的對象，并且都可以被獨立管理[1-4]，為數(shù)據(jù)庫維護(hù)和各類應(yīng)用程序的編寫帶來了極大的好處.通常，分區(qū)可以大大提高某些查詢以及維護(hù)操作的性能,還可以極大簡化常見的管理任務(wù).分區(qū)功能將表、索引或索引組織表進(jìn)一步細(xì)分為段，每個分區(qū)有自己的名稱，可以選擇自己的存儲特性，以便能存儲在不同的物理介質(zhì)中.從數(shù)據(jù)庫管理員的角度，分區(qū)后的對象具有多個段，這些段既可被集體管理，也可被單獨管理，這就使數(shù)據(jù)庫管理員在管理分區(qū)后的對象時有相當(dāng)大的靈活性.如果把表的不同分區(qū)分配到不同的磁盤，還可以平衡磁盤I/O操作，減少磁盤爭用.然而，從應(yīng)用程序的角度，分區(qū)后的表與非分區(qū)表完全相同，訪問數(shù)據(jù)的SQL語句無需任何修改，即用戶訪問數(shù)據(jù)時感覺不到分區(qū)的存在，這正是Oracle數(shù)據(jù)庫系統(tǒng)的優(yōu)越性所在.

1 分區(qū)類型

1.1 分區(qū)表類型

1)范圍分區(qū)(Range).是應(yīng)用范圍比較廣的表分區(qū)方式，以列值的范圍作為分區(qū)的劃分條件，將記錄存放到列值所在的Range分區(qū)中.

2)散列分區(qū)(Hash).如果指定為Hash分區(qū)，Oracle會將表中的數(shù)據(jù)自動平均分配到指定的幾個分區(qū)中，因此并不能控制也不知道哪條記錄會被放到哪個分區(qū).Hash分區(qū)可以支持多個依賴列.對于那些無法有效劃分范圍的表，可以使用Hash分區(qū).

3)列表分區(qū)(List).List也需要指定列的值，但不同于Range分區(qū)的范圍式列值，其分區(qū)值必須明確指定.也不同于Hash分區(qū)，List分區(qū)通過明確指定分區(qū)值，將記錄存儲在預(yù)設(shè)的分區(qū).List分區(qū)的分區(qū)列只能有一個.

4)組合分區(qū)(Subpartition).如果某表按照某列分區(qū)后仍然較大，或者有一些其他的需求，還可以通過分區(qū)內(nèi)再建子分區(qū)的方式將分區(qū)再進(jìn)行分區(qū)，即為組合分區(qū)的方式.要注意順序，根分區(qū)只能是Range分區(qū)，子分區(qū)可以是Hash分區(qū)或List分區(qū).

5)Interval分區(qū).是Range分區(qū)的增強(qiáng)分區(qū)方式，可以實現(xiàn)范圍分區(qū)的自動化與均衡化.創(chuàng)建的分區(qū)作為元數(shù)據(jù)，只有最開始的分區(qū)是永久分區(qū).隨著數(shù)據(jù)的增加會分配更多的部分，并自動創(chuàng)建新的分區(qū)和本地索引.

6)引用分區(qū)(Reference).是指子表沒有本身的分區(qū)屬性，通過外鍵參照父表分區(qū)進(jìn)行分區(qū).這在有父子關(guān)系的多表分區(qū)存儲中有重要的應(yīng)用價值.

7)虛擬列分區(qū)(Generated).Oracle允許將某一列或者多列設(shè)計為一個虛擬列.數(shù)據(jù)庫對這個虛擬列進(jìn)行分區(qū)存儲.當(dāng)一個表中某個屬性不能直接轉(zhuǎn)為范圍或者列表時，虛擬列則可以表示為某個范圍，從而進(jìn)行分區(qū).

1.2 分區(qū)索引類型

索引和表一樣，需要占用磁盤空間.一般來說，如果索引所對應(yīng)的表的數(shù)據(jù)量非常大，比如幾百萬甚至上千萬條數(shù)據(jù)，則索引也會占用很大的空間，因此建議對索引進(jìn)行分區(qū)[1].

索引分區(qū)的類型有2類：本地分區(qū)索引與全局分區(qū)索引.

1)本地分區(qū)索引(Local Partitioned Indexes)，也叫局部索引.本地分區(qū)形式與表分區(qū)完全相同，依賴列也相同.數(shù)據(jù)倉庫系統(tǒng)中大多數(shù)分區(qū)實現(xiàn)使用的就是局部索引[2].

2)全局分區(qū)索引(Global Partitioned Indexes)，可以是Range分區(qū)或Hash分區(qū).既可建于分區(qū)表上，又可創(chuàng)建于非分區(qū)表上[3]，即全局索引是完全獨立于分區(qū)表的，因此它需要更多的維護(hù)操作.使用全局分區(qū)索引時需要注意索引分區(qū)數(shù)跟表分區(qū)數(shù)可能不同[4].全局分區(qū)最適合于聯(lián)機(jī)事務(wù)處理過程(On-Line Transaction Processing，OLTP)系統(tǒng)來直接訪問特定的記錄[5].

2 分區(qū)應(yīng)用

2.1 分區(qū)表和分區(qū)索引的應(yīng)用

假定一個大型企業(yè)有一個超大數(shù)據(jù)量的訂單表，數(shù)據(jù)在1億行以上.訂單表中有3個基本屬性：訂單ID(ORDER-ID),訂單日期(ORDER-DATE)與客戶姓名(CUST-NAME).在日常查詢中，通常需要根據(jù)這3個屬性值之一或者組合查詢訂單數(shù)據(jù).

表1 ORDERS表

如表1所示，通常情況下企業(yè)的銷售活動是按日期遞增進(jìn)行的，所以訂單表應(yīng)按日期分區(qū)存儲(最常見是按年度或者月度分區(qū))，這樣就使大量的數(shù)據(jù)按日期分類集中存儲在不同的數(shù)據(jù)段中.按日期查詢時，只需要查詢某個分區(qū)的數(shù)據(jù)，跳過其他不在日期范圍的數(shù)據(jù)段，從而大大加快了數(shù)據(jù)的查詢速度.

數(shù)據(jù)分區(qū)存儲不會自動將數(shù)據(jù)排序.如果需要在查詢時排序，那么還要加上索引，這就是在表2中給ORDER-DATE加上B-樹索引的原因.大數(shù)據(jù)情況下，普通索引會占用大量的存儲空間，因此需要將索引按分區(qū)存儲，這就是“分區(qū)索引”技術(shù)(見圖1).一般來講，只需要對當(dāng)年或者當(dāng)月數(shù)據(jù)進(jìn)行排序，所以設(shè)置ORDER-DATE為本地范圍分區(qū)即可.沒有必要對全部數(shù)據(jù)排序，如果需要，排序時間長一點也是可以接受的.

圖1 基于ORDER-DATE的本地分區(qū)索引(Range)

對于ORDER-ID主鍵，由于它與ORDER-DATE屬性無關(guān)，因此不能設(shè)計為本地分區(qū)存儲，只能設(shè)計為全局分區(qū)方式存儲.由于ORDER-ID的范圍難以確定，所以最好采用Hash分區(qū)方式由系統(tǒng)自動平均分配到各分區(qū)中，如圖2所示.

圖2 基于ORDER-ID的全局分區(qū)索引(Hash)

對于CUST-NAME(客戶姓名)屬性，由于不是主要查詢屬性，可將其設(shè)置為普通B-樹索引，見圖3.

圖3 基于CUST-NAME的全局非分區(qū)索引(B-Tree)

2.2 腳本語句

本研究的實驗環(huán)境中，使用2個表空間存儲ORDERS分區(qū)表以及表的分區(qū)索引.這2個表空間是USERS和USERS02.實際的SQL語句如下：

CREATE TABLE ORDERS

(

ORDER-ID NUMBER(10,0) NOT NULL,

ORDER-DATE DATE NOT NULL,

CUSTOMER-NAME VARCHAR2(40 BYTE) NOT NULL

)

PARTITION BY RANGE (ORDER-DATE)

(

PARTITION PARTITION-BEFORE-2016 VALUES LESS THAN(TO-DATE(′2016-01-01′,′YYYY-MM-DD′,′NLS-CALENDAR=GREGORIAN′)) TABLESPACE USERS,

PARTITION PARTITION-BEFORE-2017 VALUES LESS THAN(TO-DATE(′2017-01-01′,′YYYY-MM-DD′,′NLS-CALENDAR=GREGORIAN′)) TABLESPACE USERS02

);

——創(chuàng)建基于ORDER-DATE的本地分區(qū)索引INDEX-DATE：

CREATE INDEX INDEX-DATE ON ORDERS(ORDER-DATE ASC)

LOCAL

(

PARTITION PARTITION-BEFORE-2016 TABLESPACE USERS,

PARTITION PARTITION-BEFORE-2017 TABLESPACE USERS02

);

——創(chuàng)建基于CUSTOMER-NAME的普通索引

CREATE INDEX INDEX-CUSTON ORDERS(CUSTOMER_NAME ASC);

——創(chuàng)建基于ORDER-ID的全局分區(qū)索引(Hash)

CREATE UNIQUE INDEX ORDERS-PK ON ORDERS(ORDER-ID ASC)

GLOBAL PARTITION BY HASH(ORDER-ID)

(

PARTITION INDEX-PARTITION1 TABLESPACE USERS,

PARTITION INDEX-PARTITION2 TABLESPACE USERS02

);

ALTER TABLE ORDERS ADD CONSTRAINT ORDERS-PK PRIMARY KEY(ORDER-ID)

USING INDEX ORDERS-PK ENABLE;

3 實驗結(jié)果與分析

為了測試以上分區(qū)方式，插入1億條ORDERS訂單記錄.分2個表空間，每個表空間各存儲1個表的分區(qū)和1個索引分區(qū).表2記錄了3個典型SQL語句的執(zhí)行跟蹤過程和執(zhí)行計劃.從實際執(zhí)行時間看，查詢單條特定訂單的語句執(zhí)行時間都在0.01 s以內(nèi).如果分區(qū)得當(dāng)，這個速度不會隨數(shù)據(jù)量的增加而顯著減慢，非常高效快捷.

注意，表2中的變量d1與d2必須在同一年范圍內(nèi)，否則“操作”中可能是全表搜索，而不進(jìn)行分區(qū)搜索，即不會使用“PARTITION RANGE SINGLE”.

表2 3條SQL語句的執(zhí)行計劃及分析

4 結(jié) 語

分區(qū)表可以增加系統(tǒng)可用性，并改善系統(tǒng)性能[5].分區(qū)設(shè)計時，首先要考慮表的數(shù)據(jù)量是否足夠大，然后分析使用哪個屬性進(jìn)行分區(qū)、分區(qū)類型的選用及分區(qū)范圍的粗細(xì)度等因素，最后為每個分區(qū)分配1個表空間和數(shù)據(jù)文件.合理分配分區(qū)表可以極大提高數(shù)據(jù)的查詢性能.如果有一些表不適合設(shè)計為分區(qū)表，也可以考慮將索引單獨設(shè)計為全局索引分區(qū).索引分區(qū)和表分區(qū)可以有不同的存儲表空間.

請注意，分區(qū)表也有其局限性.主要局限性是，根據(jù)查詢表的非分區(qū)屬性值查詢時，由于數(shù)據(jù)分散在不同分區(qū)，查詢性能反而可能受影響.另外，對已經(jīng)存在的非分區(qū)表不能直接轉(zhuǎn)化為分區(qū)表,只能使用Oracle提供的在線重定義表功能.分區(qū)表也使得數(shù)據(jù)遷移工作變得異常復(fù)雜.

[1]谷長勇,王彬,陳杰,等.Oracle11g權(quán)威指南[M].北京:電子工業(yè)出版社,2008.

[2]Thomas Kyte.Oracle Database 9i/10g/11g編程藝術(shù)[M].蘇金國，王小振，譯.北京:人民郵電出版社,2011.

[3]吳潔明，周錦．基于Oracle數(shù)據(jù)庫SQL查詢語句優(yōu)化規(guī)則的研究[J]．陜西理工學(xué)院學(xué)報(自然科學(xué)版)，2013，29(4)：34-38．

[4]朱木秀.ORACLE分區(qū)技術(shù)在移動綜合網(wǎng)管系統(tǒng)中的應(yīng)用[J].電腦知識與技術(shù) 2011,7(33):8137-8140.

[5]曹彤,程君.Oracle分區(qū)技術(shù)在LIS中的應(yīng)用研究[J].醫(yī)院數(shù)字化，2012,33(4):60-61.

[6]Ian Abramson,Michael Abbey，Michelle Malcher,等.專業(yè)級Oracle Database 12c安裝、配置與維護(hù)[M].盧濤，李穎，譯.北京：清華大學(xué)出版社，2014.

Research on Oracle Partitioned Table and Partitioned Index in VLDB

ZHAOWeidong1,2,LIUYonghong1,2,YANTao1,2,YUXi1,2

(1.Key Laboratory of Pattern Recognition and Intelligent Information Processing, Chengdu 610106, China;2.School of Information Science and Engineering, Chengdu University, Chengdu 610106, China)

Partitioned table and partitioned index are the key technologies in database management.They serve as important mechanisms to improve the performance of Very Large Databases(VLDB).The principle of partition technology is to separate a large table of logic into a plurality of independent physical partitions to store,and thus to improve the performance of data I/O.In the face of a large number of data storage,almost all of the Oracle database use partition technology to improve the performance of data query.This article analyzes a project case and then illustrates how to use the partitioned table and partitioned index to improve the query speed of large data.

Oracle;partitioned table;partitioned index;VLDB

1004-5422(2016)04-0358-04

2016-11-03.

趙衛(wèi)東(1968 — )， 男， 碩士， 副教授， 從事計算機(jī)軟件工程研究.

TP311.13

A