函数局部扫描解决在线题库海量数据读取

汪磊

摘要：该文深入分析了局部扫描海量数据库方法，实现了基于函数局部扫描解决在线题库海量数据读取的问题。给出了不同类型利用函数实现局部范圍扫描的SQL语句写法，总结了函数使用需要注意的问题。

关键词：函数；局部扫描；SOL；海量数据

中图分类号：TP311 文献标识码：A 文章编号：1009-3044（2016）29-0014-03

1概述

在数据处理过程中，最苦闷的事情是，很难找到提高大范围数据处理执行速度的对策，处理少量数据时，不论怎样都能够轻易地获得较快的执行速度；但对于大范围的数据，则苦闷不已。那么，如果有完全不受数据处理范围影响而始终能够确保快速执行的方法，则可以很轻松地应对大范围的数据处理了。

2问题的由来

职业院校通用在线考试平台题库，随着近几年的飞速建设，正稳步走向大数据范围，200余门的课程，每门课近千道试题，在组卷的过程中，准确无误的从海量数据中，查询到符合组卷条件要求的试题，需要大量的对比运算。随着题库数据量不断增大，占用CUP的运算时间也不断增加，导制组卷速度的降低。

在线考试平台能否适应海量数据快速查询，就是如何有效地使用SQL语句从数据库中查询所需要数据的问题。为了实现有效地使用SQL语句的目的而需要使用比较有效的方法来编写SQL语句，通过这些有效的方法所编写的SQL语句可以确保要求得到最大限度地满足。由于能够获得相同结果的SQL的表现方式有很多，所以随着所构思的SQL的不同，处理数据的效率也存在着很大的差异。

在处理整体数据的同时寻找更加有效的方法固然重要，但如果只处理整体中的一部分也同样可以获得所期望的结果，应当没有比它更好的方法了，也就是部分范围扫描。也就是不用全部数据就可以获得所期望的结果，在有的应用中，它甚至可以将执行效率提高到整体范围处理的几十陪之多。

3目前数据库查询过程中针对这一问题的解决方案

如果能够有效利用局部范围扫描，那么一直困惑不已的大范围数据处理将不再是难题。但并非所有的情况都能够实现局部范围扫描。目前普遍有以下两种解决方案：

1）利用ROWNUM的局部范围扫描

ROWNUM并不是物理存储在表中的列，而是一种像SYS-DATE或USER等一样可以被使用在任何SQL语句中的虚拟列。在SQL中无端添加ROWNUM查询条件就好在某种程度上束缚了用户所指定的查询条件，一般在大部分的在线查询中，无法预先知道满足用户指定查询条件的最终结果行数。

2）利用嵌套视图的局部范围扫描。

运用嵌套视图来实现局部范围扫描的原理是，将必须使用全部范围扫描的部分捆绑在嵌套视图中，以确保视图之外的部分能够以局部范围扫描的方式来执行。但是SQL除了嵌套视图中的部分之外，其他部分是以局部扫描的方式执行。尽管竭力引导优化按照所期望的方式来生成执行，但优化器不能为SQL制定出所期望的执行计划。

两种解决方案虽然在一定程度上实现了局部扫描，但都有着各自的局限性，很难满足所有情况下的局部扫描。

4利用函数的局部范围扫描

函数的典型特征就是在SQL中插入了过程化的处理内容，这一特征具有非常重要的意义。如果能够将所有的数据处理都视为“集合运算”，则只使用SQL就可以解决很多类型的数据处理问题。可是，由于使用过程化方式来解决问题，使用SQL处理的类型越复杂，需要使用全部范围扫描的情况就会越多。

如何通过使用函数将这部分隐藏起来，从而避免向优化器提供选择使用全部范围扫描的接口，以确保其始终能够以局部范围扫描方式来执行。

1）使用标题子查询来实现局部范围扫描的SQL转换为使用函数来实现

在利用嵌套视图的局部范围扫描时，可以采用在SELECT-List中使用标量子查询，以进一步对其优化。

SELECT a.produet_ed，produet_name，

（SELECT SUM（stock_qty）/（：b2-：b1）

FROM PROD_STOCK b

WHERE b.produet_ed=a.produet_ed

AND b.stoek_date between：b1 and：b2）avg_stock

FROMPRODUCT a

WHERE eategory_ed=20

该方法虽然实现了嵌套视图的局部范围扫描的进一步优化，但由于有些版本中不支持该功能，其通用性不高，所在下面将其转换为使用函数来实现。

尽管在该SQL中添加了一些复杂的过程化处理步骤，但是也没有对局部范围扫描造成任何不良影响。

2）利用局部范围扫描对M集合的处理

将某个表和与其关系为1：M的表进行连接后的结果为M集合，执行连接的目的是为了实现，使用“M”边的表检验从“1”边表中读取的中间结果，为了将连接后的M集合简化为原来的1集合，需要执行额外的GROUPBY、DISTINCT等操作，从而增加了执行代价。

在不执行表连接的情况下，可以通过使用子查询来达到相同的目的。但子查询过于复杂，要求执行多过连接而使得执速度比较低下时，就无法继续使用这种方法。解决此类问题的最佳方法是使用函数。

利用函数既获得了需要的结果，又实现了局部扫描，但WHERE中所使用的函数和SELECT-List中所用的函数虽然相同，但却需要分别执行。从逻辑角度来看，完全没有重复执行两次。但函数的重复执行对系统并不构成负担，则还是可以使用的。

3）特定部分的局部范围扫描函数编写

在实际组卷中，经常会为了提高在线考试对大范围题库数据处理的执行速度而绞尽脑汁。为了提高这种情况下的执行速度，通常使用的方法是提前创建汇总表。但转换一下思维，不将注意力集中到整体的SQL语句中，而采用分而治之的方法，以确保某些特定的部分可以按照局部范围扫描的方式执行，提高执行速度。对于大数据而言，这种方法更加有效。

例如，课程题库拥有12个章节，共计大概3000个题目，准备查询满足条件部分题目信息，并要求按照题目的章节难易程度和题型进行组卷。虽然在查询之前无法知道满足条件的题目个数，但如果按照题目难易程度和题型求汇总，则无法获得最终结果，以及只有等所有的处理结束之后才能对其进行组卷。但是对所要处理的数据行数进行一下简单的计算就会发现，按照这种方式编写的SQL根本不适用于在线查询，其最少处理行数为3000*12*100=3600000行。

认真分析一下，找出其中必须使用全部范围扫描的部分。如果不要求按章节难易程度组卷，则可以采用首先只对将要向界面输出的章节总额求汇总的方法。该方法与一次性对全部章节求汇总的方法相比，可以很大程度地减少所要处理的数据量。除了求每个题型第1章的题目必须按照整体范围进行处理之处，没有必要对其他部分也按照整体范围进行处理，因此，可以先不将12个章节的题目一次性地全部进行处理，而首先应当只处理所有题型第1章的数据，然后再分别处理第一次向界面输出题型的剩下12个章节的数据。使用这种方法，能够在相当大的程度上缩减所要处理的数据量。

利用函数的解决方案如下：

在上面的SQL语句中，首先汇总出满足特定条件的第1章节题目并对其按题型进行排序。虽然这一部分使用了全部范围扫描，但在按照局部范围处理时只汇总了一部题型剩下11个章节的题目。虽然最开始处理第1章节题目数据时总处理行数为500*1*100=50000行，但是如果数组批量每次取10行数据，则在处理10个代理商剩下11个章节数据时的总处理行数却只有10*11*100=1 1000行。

在线应用程序中，按照全部范围扫描的方式处理50000行数据虽然需要一定的开销，但如果创建一个以“SAL_DEPT+SAL_DATE”为聚簇键的聚簇则开销可以得到很大程度的降低。并且在每个处理中，由于每次中处理11000行数据，所以始终可以确保获得较快的执行速度。因此，这个方法不用额外创建汇总表也能够获得较快的执行速度，对大范围数据处理是一个非常有效的解决方案。

5使用函数实现局部扫描过程中的注意事项

函数虽然可以满足过程化数据处理的要求，但是它既然是“函数”就无法摆脱函数本身所具有的特性，它的执行过程是从接受输入变量开始到返回一个最终结果为止。函数的最大缺陷就是每次只返回一个值，即使想一次返回多个值，但由于函数不具备这样的功能，所以為达到目的，函数也只能被连续不断地执行多次，可在某种程度上是无法忍受的，因此就要把将要从函数中输出的多个列连接成一个列来输出。也就是把将要输出的多个列结合成一个列，并将其作为一个常量来返回，然后在SQL中使用SUBSTR函数将这个结合在一起的列分解为原来的多个独立列。使用该方法有如下几个要注意的地方。

第一，为了便于对输出的结合列进行分解，应当在结合时为其指定固定长度。

此时，可以使用TO_CHAR或RPAD函数来实现固定列长度的目的。也可以在结合时为其指定界定符，然后再根据界定符来进行分解。

第二，为了再次分解函数返回值而需要多次描述函数时，可能会导致函数被执行多次。

函数就像表或视图一样，是具有独立功能的对象。所谓的独立不仅指不需要其他对象的帮助就可以独立地输出结果，而且还指在不同的应用中始终以相同的模块出现，同时也实事了共享目的。所谓共享模块是指同一模块必须能够被使用在多个应用程序中。

6结束语

本文的意义就在于，基于函数使用合适的方法控制执行计划来实现部分范围扫描的目的，从而能够获得执行效率的有效提高。