Oracle查詢優(yōu)化技術(shù)在標(biāo)準(zhǔn)化服務(wù)平臺(tái)中的應(yīng)用

洪華軍，吳建波

(中國船舶科學(xué)研究中心 軟件工程技術(shù)中心，江蘇 無錫 214082)

?

Oracle查詢優(yōu)化技術(shù)在標(biāo)準(zhǔn)化服務(wù)平臺(tái)中的應(yīng)用

洪華軍，吳建波

(中國船舶科學(xué)研究中心 軟件工程技術(shù)中心，江蘇 無錫 214082)

系統(tǒng)數(shù)據(jù)量的增加，使得系統(tǒng)響應(yīng)時(shí)間和系統(tǒng)吞吐率達(dá)不到要求，直接關(guān)系到系統(tǒng)性能和用戶體驗(yàn)，因此對(duì)查詢進(jìn)行優(yōu)化顯得至關(guān)重要。以標(biāo)準(zhǔn)化服務(wù)平臺(tái)系統(tǒng)應(yīng)用開發(fā)為例，詳細(xì)闡述了針對(duì)特定業(yè)務(wù)需求背景下的三種Oracle查詢優(yōu)化策略：基于Oracle全文索引，提高題錄查詢速度；基于Oracle快速刷新物化視圖技術(shù)，提高年檢查詢速度；基于Oracle的Job定時(shí)任務(wù)，自動(dòng)修改標(biāo)準(zhǔn)狀態(tài)。

Oracle查詢優(yōu)化；全文索引；物化視圖；標(biāo)準(zhǔn)化

0 引言

標(biāo)準(zhǔn)化服務(wù)中心為社會(huì)提供標(biāo)準(zhǔn)信息服務(wù)、標(biāo)準(zhǔn)技術(shù)法規(guī)服務(wù)、通報(bào)貿(mào)易信息服務(wù)、物品編碼印制服務(wù)、質(zhì)量檢測服務(wù)以及經(jīng)濟(jì)分析和管理所需的組織機(jī)構(gòu)代碼服務(wù)。標(biāo)準(zhǔn)化服務(wù)平臺(tái)是標(biāo)準(zhǔn)化服務(wù)中心為企業(yè)提供標(biāo)準(zhǔn)信息研究、宣貫、咨詢、跟蹤的綜合信息服務(wù)平臺(tái)，可實(shí)現(xiàn)標(biāo)準(zhǔn)化服務(wù)中心各項(xiàng)業(yè)務(wù)和服務(wù)的網(wǎng)絡(luò)化、標(biāo)準(zhǔn)化，提高工作效率，增強(qiáng)監(jiān)督機(jī)制，有效利用資源，更好地為企業(yè)、社會(huì)服務(wù)[1]。

本文介紹了在該系統(tǒng)中用到的基于Oracle查詢優(yōu)化技術(shù)實(shí)現(xiàn)：(1)基于Oracle全文索引，提高題錄查詢速度；(2)基于Oracle快速刷新物化視圖技術(shù)，提高年檢查詢速度；(3)基于Oracle的Job定時(shí)任務(wù)，自動(dòng)修改標(biāo)準(zhǔn)狀態(tài)。

1 基于Oracle全文索引，提高題錄查詢速度

1.1 業(yè)務(wù)需求

題錄數(shù)據(jù)是標(biāo)準(zhǔn)化服務(wù)平臺(tái)的基礎(chǔ)數(shù)據(jù)，主要包含國標(biāo)、行標(biāo)、企標(biāo)、國外題錄庫等，其中基本屬性有標(biāo)準(zhǔn)號(hào)、標(biāo)準(zhǔn)組織、標(biāo)準(zhǔn)狀態(tài)、標(biāo)準(zhǔn)性質(zhì)、發(fā)布日期、實(shí)施日期、作廢日期、中英文名稱；對(duì)象屬性有ICS分類碼、國際分類碼、替代標(biāo)準(zhǔn)、采用標(biāo)準(zhǔn)、修改件。當(dāng)前題錄庫中數(shù)據(jù)達(dá)到1 000多萬條，每個(gè)月都有新增，題錄數(shù)據(jù)量越來越大。用戶按照各屬性的數(shù)據(jù)特征，通過本平臺(tái)輸入查詢，查詢結(jié)果需要專業(yè)化，并且系統(tǒng)響應(yīng)速度要在1 s以內(nèi)。

1.2 Oracle全文索引

由于題錄數(shù)據(jù)量龐大，且逐月遞增。采用如instr和like的一般文本定位方法將導(dǎo)致全表掃描，消耗資源比較昂貴，且實(shí)現(xiàn)的搜索功能十分有限。對(duì)于龐大的題錄數(shù)據(jù)，需要對(duì)其中的各個(gè)屬性字段進(jìn)行關(guān)鍵字匹配，肯定會(huì)存在非常嚴(yán)重的效率障礙和性能障礙。

基于Oracle的全文索引技術(shù)，將段落性的標(biāo)準(zhǔn)號(hào)進(jìn)行逐詞分解，并針對(duì)單詞出現(xiàn)頻率、出現(xiàn)位置進(jìn)行標(biāo)記，按照單詞本身的編碼順序存儲(chǔ)為索引文件。如此，在針對(duì)標(biāo)準(zhǔn)號(hào)關(guān)鍵詞進(jìn)行檢索查詢時(shí)，就不會(huì)遍歷所有的標(biāo)準(zhǔn)題錄，而是根據(jù)索引文件進(jìn)行有序查找，通過有序索引查找標(biāo)準(zhǔn)號(hào)關(guān)鍵詞，對(duì)于海量的標(biāo)準(zhǔn)題錄而言，若索引數(shù)量為X，則只需要最多Log2X次指針跳轉(zhuǎn)，即可完成搜索，而無須完整遍歷整個(gè)題錄數(shù)據(jù)表[2]。

全文索引技術(shù)也稱倒排文檔索引技術(shù)。由圖1可以看到，全文索引中關(guān)鍵詞對(duì)應(yīng)的指針指向桶的指針列表頭，而該桶的指針列表指向所有包含有同一個(gè)關(guān)鍵詞的文檔。如圖1中關(guān)鍵詞“GB”、“2012”的指針分別指向一個(gè)桶[3]。

圖1 全文索引結(jié)構(gòu)圖

1.3 查詢優(yōu)化實(shí)現(xiàn)

題錄查詢使用最頻繁的是基于模糊查詢方式的關(guān)鍵字查詢和標(biāo)準(zhǔn)序號(hào)查詢，如果只建立一般的索引，查詢響應(yīng)時(shí)間在10 s以上，完全不能滿足用戶使用要求。因此需要構(gòu)建Oracle Text 索引來解決效率問題。

在字段標(biāo)準(zhǔn)序號(hào)上創(chuàng)建索引管道(index Pipeline)：

CREATE INDEX

I_STD_STD_NO ON STD(STD_NO);

INDEXTYPE

IS CTXSYS.CONTEXT NOPARALLEL;

將自動(dòng)生成索引相關(guān)的4個(gè)表：DR$I_STD_STD_NO$I，DR$I_STD_STD_NO$K，DR$I_STD_STD_NO$N，DR$I_STD_STD_NO$R。記錄表意單元term出現(xiàn)的位置、次數(shù)、Hash值等信息。

如圖2[4]所示，Oracle Text 索引將文本打碎分成很多個(gè)記號(hào)(token)。例如標(biāo)準(zhǔn)號(hào)“GB/T 17215.421-2016”將會(huì)被分成 GB、T、17215、421、2016這樣的記號(hào)(token)。通過搜索關(guān)鍵字GB、T、17215、421、2016將能搜索到標(biāo)準(zhǔn)號(hào)“GB/T 17215.421-2016”。

圖2 Oracle文本索引過程圖

由于標(biāo)準(zhǔn)號(hào)結(jié)構(gòu)為：標(biāo)準(zhǔn)組織+是否強(qiáng)制執(zhí)行+ICS分類+年份。用戶希望通過標(biāo)準(zhǔn)組織、是否強(qiáng)制執(zhí)行、ICS分類、年份對(duì)標(biāo)準(zhǔn)號(hào)進(jìn)行查詢。而這些正好包含在Oracle Text索引打碎分成的記號(hào)(token)里面。在索引建立過程中，標(biāo)準(zhǔn)組織“GB”會(huì)包括在如下GB row1、GB row2、GB row3等條目中。如GB row1表示GB在行row1中出現(xiàn)過，這樣通過反向索引(inverted index)查找標(biāo)準(zhǔn)組織“GB”所對(duì)應(yīng)的行的rowid就可以迅速找到對(duì)應(yīng)的標(biāo)準(zhǔn)。

標(biāo)準(zhǔn)題錄數(shù)據(jù)具有修改少、刪除少、增加多的特點(diǎn)。對(duì)于題錄數(shù)據(jù)的變化，需要同步和優(yōu)化索引才能保持查詢準(zhǔn)確。

同步(sync):將新的term保存到DR$I_STD_STD_NO$I表：

ctx_ddl.sync_index(‘I_STD_STD_NO’);

優(yōu)化(optimize):清除DR$I_STD_STD_NO$I表的垃圾，主要是將已經(jīng)被刪除的term從DR$I_STD_STD_NO$I表刪除：

ctx_ddl.optimize_index(‘I_STD_STD_NO’,‘FULL’);

由于優(yōu)化索引需要花費(fèi)很長的時(shí)間，不能在修改一條題錄后馬上進(jìn)行優(yōu)化索引。因此采用Oracle的Job定時(shí)任務(wù)每日零時(shí)自動(dòng)調(diào)用運(yùn)行同步和優(yōu)化索引的存儲(chǔ)過程，保證查詢的準(zhǔn)確性和提高查詢效率。

通過構(gòu)建全文索引，將查詢速度從原來的10 s多減為0.5 s以內(nèi)。同時(shí)，能滿足題錄查詢一些特殊的要求，如：(1)能根據(jù)ICS分類碼的子碼查詢需要的標(biāo)準(zhǔn)；(2)輸入一類標(biāo)準(zhǔn)的強(qiáng)制標(biāo)準(zhǔn)，能夠查到類似的非強(qiáng)制標(biāo)準(zhǔn)；(3)如通過查詢“勞動(dòng) 手套”能查詢到含“勞動(dòng)”且含“手套”的標(biāo)準(zhǔn)。

查詢頁面如圖3所示。

圖3 標(biāo)準(zhǔn)查詢頁面截圖

2 基于Oracle快速刷新物化視圖技術(shù)，提高年檢查詢速度

2.1 業(yè)務(wù)需求

企業(yè)生產(chǎn)的產(chǎn)品需要符合國家和行業(yè)的相關(guān)標(biāo)準(zhǔn)，構(gòu)建標(biāo)準(zhǔn)年檢子系統(tǒng)實(shí)現(xiàn)年檢企業(yè)查詢，含作廢標(biāo)準(zhǔn)企業(yè)查詢、作廢標(biāo)準(zhǔn)替代、年檢報(bào)告打印等功能。并能統(tǒng)計(jì)出年檢企業(yè)數(shù)量、標(biāo)準(zhǔn)總數(shù)、企標(biāo)總數(shù)和其他標(biāo)準(zhǔn)總數(shù)。

2.2 物化視圖

由于題錄庫數(shù)據(jù)有1 000多萬條，年檢企業(yè)有1萬家，每家企業(yè)年檢的標(biāo)準(zhǔn)從1～1 000多條不等。每個(gè)年檢企業(yè)的每條標(biāo)準(zhǔn)都需要外聯(lián)題錄庫判定是否作廢，并計(jì)算出該企業(yè)是否含作廢標(biāo)準(zhǔn)。由于數(shù)據(jù)量大，通過構(gòu)建普通視圖方式查詢很慢，不能達(dá)到1 s內(nèi)響應(yīng)的要求。

采用物化視圖的方式，能預(yù)先計(jì)算并保存表連接或聚集等耗時(shí)較多的操作的結(jié)果，在執(zhí)行查詢時(shí)，就可以避免進(jìn)行這些耗時(shí)的操作，從而快速得到結(jié)果。同時(shí)通過刷新物化視圖，實(shí)現(xiàn)數(shù)據(jù)正確性和有效性[5]。

2.3 Oracle快速刷新物化視圖實(shí)現(xiàn)

年檢的標(biāo)準(zhǔn)數(shù)據(jù)來源多，構(gòu)成的視圖數(shù)據(jù)來源多，數(shù)據(jù)量大。用戶在使用年檢系統(tǒng)時(shí)，查詢數(shù)據(jù)很慢，系統(tǒng)響應(yīng)時(shí)間在10 s左右。為了提高速度，采用構(gòu)建物化視圖的方式進(jìn)行查詢。

當(dāng)年檢企業(yè)庫、企業(yè)年檢標(biāo)準(zhǔn)庫發(fā)生變化時(shí)，物化視圖應(yīng)當(dāng)刷新。一般有兩種方式：完全刷新和快速刷新?？焖偎⑿履軌?qū)崟r(shí)反映基表變化，但是操作過程中對(duì)視圖的約束多，而且嚴(yán)重影響題錄庫導(dǎo)入速度。因此采用Oracle的Job功能每10 min一次定時(shí)完全刷新物化視圖，實(shí)現(xiàn)視圖數(shù)據(jù)動(dòng)態(tài)維護(hù)。

創(chuàng)建物化視圖的語句如下：

CREATE materialized VIEW

MV_COMPANY_INSPECTION

refresh force on demand

start with sysdate

NEXT SYSDATE+10/1440

as年檢企標(biāo)的查詢語句(略)；

年檢系統(tǒng)查詢頁面如圖4所示。

圖4 年檢系統(tǒng)查詢頁面截圖

3 基于Oracle的Job定時(shí)任務(wù)，自動(dòng)修改標(biāo)準(zhǔn)狀態(tài)

3.1 業(yè)務(wù)需求

對(duì)于每個(gè)標(biāo)準(zhǔn)都有發(fā)布時(shí)間、實(shí)施時(shí)間、作廢時(shí)間。當(dāng)標(biāo)準(zhǔn)從發(fā)布到實(shí)施，標(biāo)準(zhǔn)狀態(tài)將從N到A，當(dāng)該標(biāo)準(zhǔn)作廢后，標(biāo)準(zhǔn)狀態(tài)將變?yōu)閃。標(biāo)準(zhǔn)狀態(tài)的變化是整個(gè)題錄庫維護(hù)的重中之重，直接影響到題錄庫的完整性和準(zhǔn)確性。

3.2 Oracle的Job定時(shí)任務(wù)

標(biāo)準(zhǔn)題錄數(shù)量龐大，如果通過人工修改，將會(huì)出現(xiàn)遺漏或者修改錯(cuò)誤的情況。因此采用Oracle本身擁有的任務(wù)隊(duì)列管理器(Job Queue)來實(shí)現(xiàn)。Job Queue允許用戶提前調(diào)度和安排某一作業(yè)，使Oracle能在指定的時(shí)間周期或時(shí)間段內(nèi)自動(dòng)執(zhí)行若干次，由于作業(yè)在數(shù)據(jù)庫中被執(zhí)行，因此執(zhí)行效率很高。

Job Queue能夠定制作業(yè)的執(zhí)行時(shí)間，并且提供靈活的處理方式，還可以通過配置，安排作業(yè)在系統(tǒng)訪問量少的時(shí)間段內(nèi)執(zhí)行，極大地提高了工作效率和均衡系統(tǒng)負(fù)荷。對(duì)于數(shù)據(jù)庫日常的備份、批量更新、刪除和復(fù)制、復(fù)雜海量數(shù)據(jù)處理等耗時(shí)長、重復(fù)性強(qiáng)的工作，就可以利用Job Queue去自動(dòng)執(zhí)行以減少工作量[6]。

3.3 自動(dòng)修改標(biāo)準(zhǔn)狀態(tài)實(shí)現(xiàn)

通過創(chuàng)建Job任務(wù)調(diào)用修改標(biāo)準(zhǔn)狀態(tài)的存儲(chǔ)過程CHANGE_STD_STATUS，實(shí)現(xiàn)如下：

存儲(chǔ)過程change_std_status:

--標(biāo)準(zhǔn)狀態(tài)為A或者N時(shí)的處理

IF v_DICT_STANDARD_STATUS=‘A’

OR v_DICT_STANDARD_STATUS=‘N’ THEN

--標(biāo)準(zhǔn)狀態(tài)為N且實(shí)施日期小于當(dāng)前日期

IF v_DICT_STANDARD_STATUS=‘N’

AND v_DATE_IMPLEMENTATION< sysdate

THEN

BEGIN

--將當(dāng)前標(biāo)準(zhǔn)狀態(tài)從N修改為A

UPDATE STD SET

DICT_STANDARD_STATUS=‘A’

WHERE STD.ID = v_STD_ID;

COMMIT;

v_DICT_STANDARD_STATUS:=‘A’;

END;

END IF;

END IF;

--作廢日期不為空且作廢日期小于當(dāng)前日期

IF v_DATE_OBSOLETE IS NOT NULL

AND v_DATE_OBSOLETE < sysdate

THEN

BEGIN

--將當(dāng)前標(biāo)準(zhǔn)狀態(tài)從A修改為W

UPDATE STD SET

DICT_STANDARD_STATUS=‘W’

WHERE STD.ID = v_STD_ID;

COMMIT;END;

END IF;

執(zhí)行存儲(chǔ)過程CHANGE_STD_STATUS 的Job定時(shí)任務(wù):

--每日2點(diǎn)執(zhí)行該存儲(chǔ)過程

DECLARE

X NUMBER;

BEGIN

SYS.DBMS_JOB.SUBMIT

( Job => X, what =>‘CHANGE_STD_STATUS;’,next_date =>to_date(‘04-04-2016 02:00:00’, ‘dd/mm /yyyy hh24:mi:ss’),interval =>‘TRUNC(SYSDATE+1)+2/24’,no_parse => FALSE);

SYS.DBMS_OUTPUT.PUT_LINE(‘Job Number is:’|| to_char(x));

COMMIT;

END;

通過Oracle的Job功能，創(chuàng)建定時(shí)調(diào)度任務(wù)，根據(jù)實(shí)施日期和作廢日期來修改標(biāo)準(zhǔn)的狀態(tài)(N→A, A→W)。

4 結(jié)束語

本文采用Oracle全文索引、物化視圖、Job定時(shí)任務(wù)等技術(shù)優(yōu)化并實(shí)現(xiàn)了標(biāo)準(zhǔn)化服務(wù)平臺(tái)查詢優(yōu)化功能，并已經(jīng)投入使用。Oracle查詢優(yōu)化技術(shù)需要根據(jù)系統(tǒng)需求的特點(diǎn)進(jìn)行量身分析與定制，將多種優(yōu)化方式綜合，最終達(dá)到用戶預(yù)期目標(biāo)。

[1] 蔡華利,施慧斌.國內(nèi)外標(biāo)準(zhǔn)法規(guī)比對(duì)分析系統(tǒng)設(shè)計(jì)與實(shí)現(xiàn)[J].微型機(jī)與應(yīng)用,2011,30(20):77-80.

[2] 趙偉,張學(xué),廉鑫.全文檢索應(yīng)用開發(fā)中的性能優(yōu)化方法[J].信息與電腦(理論版),2011(4):65-67.

[3] 曾傳軍.基于索引技術(shù)的情報(bào)業(yè)務(wù)系統(tǒng)的應(yīng)用研究[D].廣州：廣東工業(yè)大學(xué), 2012.

[4] 蔡焰.Oracle 10g HWM原理及性能優(yōu)化[J].微型機(jī)與應(yīng)用,2013,32(8):1-3.

[5] 趙建平,段慧芬,許利亞,等.XML物化視圖遠(yuǎn)程增量維護(hù)在試驗(yàn)任務(wù)數(shù)據(jù)接口同步中的應(yīng)用[J].計(jì)算機(jī)系統(tǒng)應(yīng)用,2013,22(1):162-166.

[6] 王閱曉.Oracle高級(jí)隊(duì)列技術(shù)在消息分布處理及定時(shí)批處理中的應(yīng)用設(shè)計(jì)[J].計(jì)算機(jī)應(yīng)用與軟件,2012(11):232-235,280.

Application of Oracle query optimization technology in standardized service platform

Hong Huajun, Wu Jianbo

(Software Engineering and Technology Center, China Ship Science Research Center, Wuxi 214082, China)

The increment of system data volumes makes response time of the system and throughput can not meet the requirements. It is related to system performance and user experience directly, so the query optimization is critical. Based on standardized application development platform system, according to the background of the specific business requirements, this thesis expounds three Oracle query optimization strategies: based on Oracle full-text indexing to improve index query speed; based on fast refresh materialized view of Oracle technology to improve the query speed of annual inspection; based on Oracle job timing task to modify the standard state automatically.

Oracle query optimization; full text index; materialized views; standardized

TP311.131

A

10.19358/j.issn.1674- 7720.2017.13.029

洪華軍，吳建波.Oracle查詢優(yōu)化技術(shù)在標(biāo)準(zhǔn)化服務(wù)平臺(tái)中的應(yīng)用[J].微型機(jī)與應(yīng)用，2017,36(13)：94-96,101.

2017-01-10)

洪華軍(1984-)，通信作者，男，碩士，工程師，主要研究方向：企業(yè)信息化，大數(shù)據(jù)，軟件架構(gòu)。E-mail：honghuajun9431@163.com。

吳建波(1979-)，男，碩士，高級(jí)工程師，主要研究方向：企業(yè)信息化，數(shù)據(jù)工程，軟件頂層設(shè)計(jì)。