Oracle數(shù)據(jù)庫(kù)分區(qū)和壓縮技術(shù)應(yīng)用

摘 要：利用oracle分區(qū)和壓縮技術(shù)對(duì)日益膨脹的數(shù)據(jù)庫(kù)空間進(jìn)行優(yōu)化，從而提高查詢效率。本文介紹了分區(qū)的基本原理、原則和方法以及數(shù)據(jù)庫(kù)壓縮技術(shù)。

關(guān)鍵詞：oracle；優(yōu)化；分區(qū)；壓縮

中圖分類號(hào)：TP311.13

隨著電信業(yè)務(wù)發(fā)展，用戶資料和業(yè)務(wù)量數(shù)據(jù)暴增，尤其是生產(chǎn)運(yùn)營(yíng)數(shù)據(jù)集中到省之后，分公司每日要將生產(chǎn)數(shù)據(jù)按全量或增量的方式同步到本地?cái)?shù)據(jù)庫(kù)，每月將各類用戶資料和量收清單在本地做好備份，創(chuàng)建和完善本地?cái)?shù)據(jù)集市。在部分工作時(shí)段，數(shù)據(jù)表訪問(wèn)頻繁，數(shù)據(jù)庫(kù)負(fù)荷超重，嚴(yán)重影響數(shù)據(jù)處理和查詢的效率。根據(jù)實(shí)際情況，主要通過(guò)數(shù)據(jù)表分區(qū)和壓縮來(lái)優(yōu)化oracle數(shù)據(jù)庫(kù)。

1 表分區(qū)

1.1 分區(qū)的基本原理和優(yōu)點(diǎn)

表分區(qū)，是把表里的數(shù)據(jù)在物理上分別存放到不同磁盤，查詢數(shù)據(jù)時(shí)，只需要訪問(wèn)一個(gè)較小片段，而無(wú)需掃描整個(gè)表。進(jìn)行分區(qū)后，邏輯上仍然是一張完整的表，對(duì)于數(shù)據(jù)庫(kù)操作員來(lái)講，在SQL DML命令上，和訪問(wèn)普通表沒(méi)有區(qū)別。但對(duì)于數(shù)據(jù)庫(kù)，由于分別從不同磁盤讀取數(shù)據(jù)，可以有效的降低磁盤的I/O沖突，減少不必要的數(shù)據(jù)流量，從而提高了查詢性能。當(dāng)某個(gè)分區(qū)出現(xiàn)故障時(shí)，只需要修復(fù)該分區(qū)的數(shù)據(jù)，其他分區(qū)的數(shù)據(jù)不會(huì)受影響。分區(qū)也增加了刪除和備份的靈活性，可以獨(dú)立刪除或備份某一分區(qū)的數(shù)據(jù)，大大縮短操作時(shí)間。

1.2 分區(qū)表確定的主要原則

（1）大小超過(guò)2G或者記錄數(shù)超過(guò)千萬(wàn)；（2）對(duì)于經(jīng)常需要維護(hù)，例如按時(shí)間批量刪除的表，對(duì)這類表進(jìn)行分區(qū)，刪除大量數(shù)據(jù)時(shí)可以有效減少系統(tǒng)開銷；（3）表中大部分?jǐn)?shù)據(jù)都是只讀數(shù)據(jù)，通過(guò)分區(qū)可以將只讀數(shù)據(jù)存儲(chǔ)在只讀表空間里，這對(duì)于數(shù)據(jù)庫(kù)的備份也是非常有益的；（4）基于表的大部分查詢應(yīng)用，只訪問(wèn)表中少量的數(shù)據(jù)，可以充分利用分區(qū)來(lái)縮小數(shù)據(jù)查詢范圍。

1.3 分區(qū)的主要方法

分區(qū)的主要方法有：（1）范圍分區(qū)；（2）列表分區(qū)；（3）散列分區(qū)；（4）組合范圍散列分區(qū)。本文主要對(duì)工作中使用較多的范圍分區(qū)和列表分區(qū)做介紹。

（1）范圍分區(qū)：就是對(duì)數(shù)據(jù)表中某個(gè)字段值的范圍進(jìn)行分區(qū)，根據(jù)這個(gè)值的范圍，決定將該數(shù)據(jù)存儲(chǔ)在哪個(gè)分區(qū)上。這種分區(qū)方式最常見，而且多數(shù)是采用日期字段來(lái)分區(qū)。例如業(yè)務(wù)受理訂單表，存放著若干年的受理歷史訂單，而且還不斷增加新紀(jì)錄，可以按照業(yè)務(wù)受理時(shí)間來(lái)分。按照查詢需求，查詢往年數(shù)據(jù)相對(duì)較少，可以一年一個(gè)分區(qū)，當(dāng)年的數(shù)據(jù)訪問(wèn)頻率較高，可以按月分區(qū)。創(chuàng)建分區(qū)表語(yǔ)句：

create table tb_ba（id number（12），…act_date date）

partition by range（act_date）

（partition tb_b_2013 values less than to_date（'20140101'，'yyyymmdd'）） tablespace tbs2013，

partition tb_b_201401 values less than （to_date （'20140201’，’yyyymmdd’））

tablespace tbs201401

…

partition tb_ba_other values less than （maxvalue） tablespace tb_ba_other）；

（2）列表分區(qū)：這種分區(qū)的特點(diǎn)是某一列的值只有幾個(gè)。例如服務(wù)資料表，產(chǎn)品類型基本是固定數(shù)量的，日常查詢頻率較高的只有少數(shù)幾種?？梢詫?duì)移動(dòng)、寬帶、專線各做一個(gè)分區(qū)，其余產(chǎn)品做一個(gè)分區(qū)。

create table tb_c_serv（id number，prod_id number）

partition by list （id）

（partition YD values （3205） tablespace tb_cm1，--移動(dòng)

partition KD values （47） tablespace tb_cm2，--寬帶

…

partition QT values （default） tablespace tb_cm6）；

（3）散列分區(qū)：這類分區(qū)是在列值上使用散列算法，來(lái)確定行放入哪個(gè)分區(qū)，這種分區(qū)分區(qū)大小比較一致，數(shù)據(jù)量均勻。如果列值沒(méi)有適合的條件，可以考慮這種分區(qū)。

（4）組合范圍散列分區(qū)：這類分區(qū)是結(jié)合了范圍分區(qū)和列表分區(qū)，先按某一列做范圍分區(qū)，然后再按某列做列表分區(qū)，相當(dāng)于分區(qū)內(nèi)再做子分區(qū)。

1.4 分區(qū)維護(hù)

刪除分區(qū)：alter table tb_ba drop partition tb_ba_other；

增加分區(qū)：界限應(yīng)高于現(xiàn)有的最后一個(gè)分區(qū)界限。

alter table tb_ba add partition tb_b_yyyymm values less then 日期）表空間；

合并分區(qū)：alter table tb_ba merge partition tb_b_2012，tb_b_2013 into tb_b_2013；

清空分區(qū)數(shù)據(jù)：alter table tb_ba truncate partition tb_b_2012

分區(qū)表及分區(qū)信息分別存放在user_part_tables和user_tab_partitions這兩個(gè)數(shù)據(jù)字典。

2 數(shù)據(jù)庫(kù)壓縮

2.1 數(shù)據(jù)庫(kù)壓縮的優(yōu)點(diǎn)

數(shù)據(jù)表壓縮后，首先節(jié)約了磁盤空間，查詢時(shí)讀取的是壓縮后的數(shù)據(jù)，查詢的數(shù)據(jù)塊會(huì)比未壓縮之前大大減少。減少磁盤I/O節(jié)約下來(lái)的時(shí)間要比在內(nèi)存中解壓縮數(shù)據(jù)所需的時(shí)間多，所以能提高查詢操作的性能。同時(shí)，由于壓縮和解壓都在Oracle內(nèi)部進(jìn)行，外部網(wǎng)絡(luò)只傳輸壓縮后的數(shù)據(jù)塊，從而減少了網(wǎng)絡(luò)負(fù)載。Oracle 11G允許在所有的DML操作包括增、刪、改期間進(jìn)行，所有應(yīng)用都可以啟用壓縮，并使用智能算法保證寫操作時(shí)負(fù)載最小，對(duì)于增、刪、改操作，性能可能會(huì)略有下降，但從獲得的好處來(lái)看是值得的。通信行業(yè)每月量、收這類海量數(shù)據(jù)清單，都會(huì)占用大量空間，導(dǎo)致磁盤空間不時(shí)爆滿，這類清單基本都是只讀的，壓縮勢(shì)在必行。

2.2 常用壓縮命令

壓縮可以在創(chuàng)建表空間、表、索引時(shí)設(shè)置，通常是在常規(guī)語(yǔ)法后加compress，也可以對(duì)現(xiàn)有表空間、表、索引進(jìn)行壓縮。

create table T（id number）compress；--創(chuàng)建壓縮表

alter table T move compress；--將現(xiàn)有表T改為壓縮表

alter table T move nocompress；--取消表T壓縮

alter table T modify partition pmax compress；--壓縮表分區(qū)

alter index IDX1 rebuild compress–壓縮索引

值得說(shuō)明的是，有時(shí)由于sql語(yǔ)句不當(dāng)，導(dǎo)致出現(xiàn)笛卡爾積，臨時(shí)表空間也會(huì)異常暴漲，當(dāng)臨時(shí)表空間不足時(shí)，數(shù)據(jù)庫(kù)運(yùn)算速度就會(huì)異常的慢，當(dāng)臨時(shí)表空間迅速擴(kuò)展到最大空間時(shí)，一般就不會(huì)自動(dòng)清理了，這時(shí)可以執(zhí)行命令縮減臨時(shí)表空間數(shù)據(jù)文件的尺寸。alter database tempfile'/u01/oradata/site/temp01.dbf' resize 10240M；當(dāng)對(duì)表分區(qū)內(nèi)部數(shù)據(jù)進(jìn)行大量UPDATE或delete操作后，最好也要掃描一下分區(qū)內(nèi)部空塊，進(jìn)行磁盤壓縮。

3 結(jié)束語(yǔ)

Oracle數(shù)據(jù)庫(kù)性能優(yōu)化是一個(gè)系統(tǒng)工程，涉及的方面很多，需要開發(fā)和維護(hù)人員在大量的實(shí)踐工作中，不斷總結(jié)和積累經(jīng)驗(yàn)，根據(jù)實(shí)際情況不斷調(diào)整，合理利用數(shù)據(jù)庫(kù)空間，使得數(shù)據(jù)庫(kù)性能優(yōu)越，跑得更快。

參考文獻(xiàn)：

[1]林樹澤.Oracle數(shù)據(jù)庫(kù)管理之道[M].北京：清華大學(xué)出版社，2012.

[2]譚懷遠(yuǎn).讓Oracle跑得更快2-基于海量數(shù)據(jù)的數(shù)據(jù)庫(kù)設(shè)計(jì)與優(yōu)化[M]，北京：電子工業(yè)出版社，2011.

作者簡(jiǎn)介：董曉婷（1979.01-），女，山東壽光人，數(shù)據(jù)分析師，中級(jí)工程師，本科，研究方向：數(shù)據(jù)統(tǒng)計(jì)、分析、挖掘。

作者單位：中國(guó)電信股份有限公司汕頭分公司，廣東汕頭 515041