通过编写PL/SQL函数实现局部范围扫描
通过Oracle提供的PL/SQL编程语言，可以实现创建过程化SQL函数。在函数中不仅可以存在一个以上的SQL，而且还可以存在多样化的运算、条件处理、循环执行等语句。它依据外部所接受的值或固定的常量来执行必要的数据处理，并返回一个最终的处理结果。因此，我们可以通过编写函数，来处理复杂全局数据范围的运算，并通过函数将这些操作包装起来，从而避免向优化器提供选择使用全局范围扫描的机会，以确保语句按照局部范围扫描来执行。
函数的处理特点是，每次函数只会返回一个处理结果，同时每次只处理对象集合中的一行数据，因此当需要获得多个结果时就必须反复执行，所以如果是大范围数据的批量处理，那么建议还是不要使用函数处理为好，可见在使用函数时一定要注意函数的上述特点，不能随意使用否则无法达到我们期望的效果。
下面通过两个应用实例来说明如何通过函数实现局部范围扫描，以及使用函数实现局部范围扫描的应用场合。
实例1：在前面说明如何利用嵌套视图实现局部范围扫描时，我们通过标量子查询实现了对分组操作的局部范围扫描，但是我们也说明了标量子查询并不是每个版本都提供支持，其实还以通过采用函数的方式来实现局部范围扫描。具体实现方式与步骤如下所示：
（1）编写一个函数实现分组统计：
| Create or replace function Get_avg_Stock(v_start in date, v_end in date, v_product_cd in varchar2) return number is ret_val number(10); begin select sum(stock_qty)/(v_end-v_start) into ret_val from prod_stock where product_cd=v_product_cd and stock_date between v_start and v_end; return ret_val; end Get_avg_Stock; |
（2）使用函数编写SQL语句实现原有功能：
| Select product_cd,product_name, Get_avg_Stock(to_date(‘2010-01-01’,’yyyy-mm-dd’), to_date(‘2010-12-31’,’yyyy-mm-dd’), product_cd) from product where category_cd=’20’; |
通过上两个步骤，实现了将原来操作中的全部范围扫描的包装，并使得整个语句实现了局部范围扫描。
实例二：在进行具有1:M关系的两张表的连接操作时，连接的结果会是M的集合，如果连接的目的是为了实现让M方的表针对从1方的表读出的中间结果进行校验，那么此时就会发生耗时全部范围数据扫描操作，因为为了将连接后的“M”集合简化为原来的“1”集合，需要进行额外的group by、distinct等操作，这大大增加了执行代价，因此尽量不要进行这种性质的连接操作。但当出现这种情况的需求时，可以通过实现函数来实现数据局部范围扫描，提升查询性能。见如下语句：
| Select y.cust_no,y.cust_name,x.bill_tot From (select a.cust_no,sum(b.bill_amt) bill_tot From account a,charge b Where a.acct_no=b.acct_no And a.bill_cd=’FEE’ and b.bill_ym between ‘2010’ and ‘2011’ Grouo by a.cust_no Having sum(b.bill_amt)>5000000) x,customer y Where y.cust_no=x.cust_no and y.cust_type=’IC’ and rownum<=30; |
该语句实现查询在2010年到2011年间，贸易总额在500万以上并且已经付款完成交易的前30位大客户信息。该语句执行过程中，优化器会制定首先执行内部的分组统计子查询，获得查询结果后再将结果与customer表进行连接，最后返回前30为大客户信息。在将子查询与customer表进行关联时，在连接条件y.cust_no=x.cust_no上其实进行的是用交易信息过滤客户信息，而客户信息与交易信息是1:M的关系，因此此时带来了额外的group by、distinct等引发全局范围扫描的操作，致使执行过程中处理了大量不必要的数据，导致执行性能低下。
