Oracle查询优化技术在标准化服务平台中的应用

洪华军，吴建波

(中国船舶科学研究中心 软件工程技术中心，江苏 无锡 214082)



Oracle查询优化技术在标准化服务平台中的应用

洪华军，吴建波

(中国船舶科学研究中心 软件工程技术中心，江苏 无锡 214082)

系统数据量的增加，使得系统响应时间和系统吞吐率达不到要求，直接关系到系统性能和用户体验，因此对查询进行优化显得至关重要。以标准化服务平台系统应用开发为例，详细阐述了针对特定业务需求背景下的三种Oracle查询优化策略：基于Oracle全文索引，提高题录查询速度；基于Oracle快速刷新物化视图技术，提高年检查询速度；基于Oracle的Job定时任务，自动修改标准状态。

Oracle查询优化；全文索引；物化视图；标准化

0 引言

标准化服务中心为社会提供标准信息服务、标准技术法规服务、通报贸易信息服务、物品编码印制服务、质量检测服务以及经济分析和管理所需的组织机构代码服务。标准化服务平台是标准化服务中心为企业提供标准信息研究、宣贯、咨询、跟踪的综合信息服务平台，可实现标准化服务中心各项业务和服务的网络化、标准化，提高工作效率，增强监督机制，有效利用资源，更好地为企业、社会服务[1]。

本文介绍了在该系统中用到的基于Oracle查询优化技术实现：(1)基于Oracle全文索引，提高题录查询速度；(2)基于Oracle快速刷新物化视图技术，提高年检查询速度；(3)基于Oracle的Job定时任务，自动修改标准状态。

1 基于Oracle全文索引，提高题录查询速度

1.1 业务需求

题录数据是标准化服务平台的基础数据，主要包含国标、行标、企标、国外题录库等，其中基本属性有标准号、标准组织、标准状态、标准性质、发布日期、实施日期、作废日期、中英文名称；对象属性有ICS分类码、国际分类码、替代标准、采用标准、修改件。当前题录库中数据达到1 000多万条，每个月都有新增，题录数据量越来越大。用户按照各属性的数据特征，通过本平台输入查询，查询结果需要专业化，并且系统响应速度要在1 s以内。

1.2 Oracle全文索引

由于题录数据量庞大，且逐月递增。采用如instr和like的一般文本定位方法将导致全表扫描，消耗资源比较昂贵，且实现的搜索功能十分有限。对于庞大的题录数据，需要对其中的各个属性字段进行关键字匹配，肯定会存在非常严重的效率障碍和性能障碍。

基于Oracle的全文索引技术，将段落性的标准号进行逐词分解，并针对单词出现频率、出现位置进行标记，按照单词本身的编码顺序存储为索引文件。如此，在针对标准号关键词进行检索查询时，就不会遍历所有的标准题录，而是根据索引文件进行有序查找，通过有序索引查找标准号关键词，对于海量的标准题录而言，若索引数量为X，则只需要最多Log2X次指针跳转，即可完成搜索，而无须完整遍历整个题录数据表[2]。

全文索引技术也称倒排文档索引技术。由图1可以看到，全文索引中关键词对应的指针指向桶的指针列表头，而该桶的指针列表指向所有包含有同一个关键词的文档。如图1中关键词“GB”、“2012”的指针分别指向一个桶[3]。

图1 全文索引结构图

1.3 查询优化实现

题录查询使用最频繁的是基于模糊查询方式的关键字查询和标准序号查询，如果只建立一般的索引，查询响应时间在10 s以上，完全不能满足用户使用要求。因此需要构建Oracle Text 索引来解决效率问题。

在字段标准序号上创建索引管道(index Pipeline)：

CREATE INDEX

I_STD_STD_NO ON STD(STD_NO);

INDEXTYPE

IS CTXSYS.CONTEXT NOPARALLEL;

将自动生成索引相关的4个表：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出现的位置、次数、Hash值等信息。

如图2[4]所示，Oracle Text 索引将文本打碎分成很多个记号(token)。例如标准号“GB/T 17215.421-2016”将会被分成 GB、T、17215、421、2016这样的记号(token)。通过搜索关键字GB、T、17215、421、2016将能搜索到标准号“GB/T 17215.421-2016”。

图2 Oracle文本索引过程图

由于标准号结构为：标准组织+是否强制执行+ICS分类+年份。用户希望通过标准组织、是否强制执行、ICS分类、年份对标准号进行查询。而这些正好包含在Oracle Text索引打碎分成的记号(token)里面。在索引建立过程中，标准组织“GB”会包括在如下GB row1、GB row2、GB row3等条目中。如GB row1表示GB在行row1中出现过，这样通过反向索引(inverted index)查找标准组织“GB”所对应的行的rowid就可以迅速找到对应的标准。

标准题录数据具有修改少、删除少、增加多的特点。对于题录数据的变化，需要同步和优化索引才能保持查询准确。

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

ctx_ddl.sync_index(‘I_STD_STD_NO’);

优化(optimize):清除DR$I_STD_STD_NO$I表的垃圾，主要是将已经被删除的term从DR$I_STD_STD_NO$I表删除：

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

由于优化索引需要花费很长的时间，不能在修改一条题录后马上进行优化索引。因此采用Oracle的Job定时任务每日零时自动调用运行同步和优化索引的存储过程，保证查询的准确性和提高查询效率。

通过构建全文索引，将查询速度从原来的10 s多减为0.5 s以内。同时，能满足题录查询一些特殊的要求，如：(1)能根据ICS分类码的子码查询需要的标准；(2)输入一类标准的强制标准，能够查到类似的非强制标准；(3)如通过查询“劳动 手套”能查询到含“劳动”且含“手套”的标准。

查询页面如图3所示。

图3 标准查询页面截图

2 基于Oracle快速刷新物化视图技术，提高年检查询速度

2.1 业务需求

企业生产的产品需要符合国家和行业的相关标准，构建标准年检子系统实现年检企业查询，含作废标准企业查询、作废标准替代、年检报告打印等功能。并能统计出年检企业数量、标准总数、企标总数和其他标准总数。

2.2 物化视图

由于题录库数据有1 000多万条，年检企业有1万家，每家企业年检的标准从1～1 000多条不等。每个年检企业的每条标准都需要外联题录库判定是否作废，并计算出该企业是否含作废标准。由于数据量大，通过构建普通视图方式查询很慢，不能达到1 s内响应的要求。

采用物化视图的方式，能预先计算并保存表连接或聚集等耗时较多的操作的结果，在执行查询时，就可以避免进行这些耗时的操作，从而快速得到结果。同时通过刷新物化视图，实现数据正确性和有效性[5]。

2.3 Oracle快速刷新物化视图实现

年检的标准数据来源多，构成的视图数据来源多，数据量大。用户在使用年检系统时，查询数据很慢，系统响应时间在10 s左右。为了提高速度，采用构建物化视图的方式进行查询。

当年检企业库、企业年检标准库发生变化时，物化视图应当刷新。一般有两种方式：完全刷新和快速刷新。快速刷新能够实时反映基表变化，但是操作过程中对视图的约束多，而且严重影响题录库导入速度。因此采用Oracle的Job功能每10 min一次定时完全刷新物化视图，实现视图数据动态维护。

创建物化视图的语句如下：

CREATE materialized VIEW

MV_COMPANY_INSPECTION

refresh force on demand

start with sysdate

NEXT SYSDATE+10/1440

as年检企标的查询语句(略)；

年检系统查询页面如图4所示。

图4 年检系统查询页面截图

3 基于Oracle的Job定时任务，自动修改标准状态

3.1 业务需求

对于每个标准都有发布时间、实施时间、作废时间。当标准从发布到实施，标准状态将从N到A，当该标准作废后，标准状态将变为W。标准状态的变化是整个题录库维护的重中之重，直接影响到题录库的完整性和准确性。

3.2 Oracle的Job定时任务

标准题录数量庞大，如果通过人工修改，将会出现遗漏或者修改错误的情况。因此采用Oracle本身拥有的任务队列管理器(Job Queue)来实现。Job Queue允许用户提前调度和安排某一作业，使Oracle能在指定的时间周期或时间段内自动执行若干次，由于作业在数据库中被执行，因此执行效率很高。

Job Queue能够定制作业的执行时间，并且提供灵活的处理方式，还可以通过配置，安排作业在系统访问量少的时间段内执行，极大地提高了工作效率和均衡系统负荷。对于数据库日常的备份、批量更新、删除和复制、复杂海量数据处理等耗时长、重复性强的工作，就可以利用Job Queue去自动执行以减少工作量[6]。

3.3 自动修改标准状态实现

通过创建Job任务调用修改标准状态的存储过程CHANGE_STD_STATUS，实现如下：

存储过程change_std_status:

--标准状态为A或者N时的处理

IF v_DICT_STANDARD_STATUS=‘A’

OR v_DICT_STANDARD_STATUS=‘N’ THEN

--标准状态为N且实施日期小于当前日期

IF v_DICT_STANDARD_STATUS=‘N’

AND v_DATE_IMPLEMENTATION< sysdate

THEN

BEGIN

--将当前标准状态从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;

--作废日期不为空且作废日期小于当前日期

IF v_DATE_OBSOLETE IS NOT NULL

AND v_DATE_OBSOLETE < sysdate

THEN

BEGIN

--将当前标准状态从A修改为W

UPDATE STD SET

DICT_STANDARD_STATUS=‘W’

WHERE STD.ID = v_STD_ID;

COMMIT;END;

END IF;

执行存储过程CHANGE_STD_STATUS 的Job定时任务:

--每日2点执行该存储过程

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功能，创建定时调度任务，根据实施日期和作废日期来修改标准的状态(N→A, A→W)。

4 结束语

本文采用Oracle全文索引、物化视图、Job定时任务等技术优化并实现了标准化服务平台查询优化功能，并已经投入使用。Oracle查询优化技术需要根据系统需求的特点进行量身分析与定制，将多种优化方式综合，最终达到用户预期目标。

[1] 蔡华利,施慧斌.国内外标准法规比对分析系统设计与实现[J].微型机与应用,2011,30(20):77-80.

[2] 赵伟,张学,廉鑫.全文检索应用开发中的性能优化方法[J].信息与电脑(理论版),2011(4):65-67.

[3] 曾传军.基于索引技术的情报业务系统的应用研究[D].广州：广东工业大学, 2012.

[4] 蔡焰.Oracle 10g HWM原理及性能优化[J].微型机与应用,2013,32(8):1-3.

[5] 赵建平,段慧芬,许利亚,等.XML物化视图远程增量维护在试验任务数据接口同步中的应用[J].计算机系统应用,2013,22(1):162-166.

[6] 王阅晓.Oracle高级队列技术在消息分布处理及定时批处理中的应用设计[J].计算机应用与软件,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查询优化技术在标准化服务平台中的应用[J].微型机与应用，2017,36(13)：94-96,101.

2017-01-10)

洪华军(1984-)，通信作者，男，硕士，工程师，主要研究方向：企业信息化，大数据，软件架构。E-mail：honghuajun9431@163.com。

吴建波(1979-)，男，硕士，高级工程师，主要研究方向：企业信息化，数据工程，软件顶层设计。