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

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

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

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

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

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

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

0 引 言

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

1 分區(qū)類型

1.1 分區(qū)表類型

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

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

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

4)組合分區(qū)(Subpartition).如果某表按照某列分區(qū)后仍然較大，或者有一些其他的需求，還可以通過(guò)分區(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ū)方式，可以實(shí)現(xiàn)范圍分區(qū)的自動(dòng)化與均衡化.創(chuàng)建的分區(qū)作為元數(shù)據(jù)，只有最開始的分區(qū)是永久分區(qū).隨著數(shù)據(jù)的增加會(huì)分配更多的部分，并自動(dòng)創(chuàng)建新的分區(qū)和本地索引.

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

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

1.2 分區(qū)索引類型

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

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

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

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

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

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

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

表1 ORDERS表

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

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

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

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

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

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

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

2.2 腳本語(yǔ)句

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

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

);

——?jiǎng)?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

);

——?jiǎng)?chuàng)建基于CUSTOMER-NAME的普通索引

CREATE INDEX INDEX-CUSTON ORDERS(CUSTOMER_NAME ASC);

——?jiǎng)?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 實(shí)驗(yàn)結(jié)果與分析

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

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

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

4 結(jié) 語(yǔ)

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

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

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

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

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

[4]朱木秀.ORACLE分區(qū)技術(shù)在移動(dòng)綜合網(wǎng)管系統(tǒng)中的應(yīng)用[J].電腦知識(shí)與技術(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è)級(jí)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ì)算機(jī)軟件工程研究.

TP311.13

A