解析Oracle数据扫描OracleSQL优化引导局部扫描方法（7）

相关链接

解析Oracle数据扫描Oracle SQL优化引导局部扫描方法（1）

http://www.2cto.com/database/201205/130424.html；

解析Oracle数据扫描Oracle SQL优化引导局部扫描方法（2）

http://www.2cto.com/database/201205/130428.html；

解析Oracle数据扫描Oracle SQL优化引导局部扫描方法（3）

http://www.2cto.com/database/201205/130432.html；

解析Oracle数据扫描Oracle SQL优化引导局部扫描方法（4）

http://www.2cto.com/database/201205/130439.html；

解析Oracle数据扫描Oracle SQL优化引导局部扫描方法（5）

http://www.2cto.com/database/201205/130593.html；

解析Oracle数据扫描Oracle SQL优化引导局部扫描方法（6）

http://www.2cto.com/database/201205/130758.html

 

通过编写PL/SQL函数实现局部范围扫描

 

       通过Oracle提供的PL/SQL编程语言，可以实现创建过程化SQL函数。在函数中不仅可以存在一个以上的SQL，而且还可以存在多样化的运算、条件处理、循环执行等语句。它依据外部所接受的值或固定的常量来执行必要的数据处理，并返回一个最终的处理结果。因此，我们可以通过编写函数，来处理复杂全局数据范围的运算，并通过函数将这些操作包装起来，从而避免向优化器提供选择使用全局范围扫描的机会，以确保语句按照局部范围扫描来执行。

 

       函数的处理特点是，每次函数只会返回一个处理结果，同时每次只处理对象集合中的一行数据，因此当需要获得多个结果时就必须反复执行，所以如果是大范围数据的批量处理，那么建议还是不要使用函数处理为好，可见在使用函数时一定要注意函数的上述特点，不能随意使用否则无法达到我们期望的效果。  www.2cto.com  

 

下面通过两个应用实例来说明如何通过函数实现局部范围扫描，以及使用函数实现局部范围扫描的应用场合。

 

实例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;  www.2cto.com  

 

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  www.2cto.com  

 

     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等引发全局范围扫描的操作，致使执行过程中处理了大量不必要的数据，导致执行性能低下。

 

      如果优化器能够制定，首先以客户类型为大客户“IC”的数据集对象，在子查询中查询他们的交易信息，并在达到30名的限度时终止执行，此时就可以实现局部范围数据扫描，同时也可以获得非常好的执行性能。我们仍然可以通过函数来实现这个优化的执行计划，实现步骤如下：

 

（1）    定义函数：

 

Create or replace function Cust_IC_Fee(v_cust_no in varchar2,

 

                                v_start_year  in varchar2,

 

                                v_end_year  in varchar2)

 

return number is

 

ret_var number(10);

 

begin  www.2cto.com  

 

select sum(bill_amt) into ret_var

 

from account a,charge b

 

where a.acct_no=b.acct_no

 

and a.cust_no=v_cust_no and b.bill_cd=’FEE’

 

and b.bill_ym between v_start_year and v_end_year;

 

return ret_val;

 

end Cust_IC_Fee;

 

（2）    使用函数编写SQL语句实现原有功能：

 

Select cust_no,cust_name, Cust_IC_Fee(cust_no,’ 2010’, ‘2011’)

 

From customer

 

Where cust_type=’IC’

 

And Cust_IC_Fee(cust_no,’ 2010’, ‘2011’)>=5000000

 

And rownum<=30;

 

        通过使用函数来重新改写原来的语句实现，实现了优化的执行计划同时也使实现了局部范围数据扫描，执行性能得到了提升，但是当前还是存在一定的缺陷，那就是where条件中的函数虽然和select中的函数相同，但是却需要在执行时分别运行，这是由于函数的执行机制决定的，我们无法左右，在函数重复执行对系统不造成负担的情况下，可以放心使用，但是一旦函数重复执行对系统造成了一定的负担，此时我们就不能再使用这种方法了，而只能寻求其他的解决途径了。

 

一个比较简单的处理方法是运用嵌套视图来改写原来的语句，如下所示：

 

Select cust_no,cust_name,bill_tot

 

From (select rownum,cust_no,cust_name,

 

Cust_IC_Fee(cust_no,’ 2010’, ‘2011’) bill_tot

 

      From customer  www.2cto.com  

 

      Where cust_type=’IC’)

 

Where bill_tot>5000000

 

And rownum<=30;

 

       该语句的玄机在于子查询中的rownum的运用，从前面关于rownum虚拟列特性的讨论中可以知道，子查询的rownum与主查询的rownum代表的意义完全不同，因此当在子查询中使用了rownum，优化器必须要将子查询的执行结果存储在内部临时存储区域中，所以子查询中的函数被执行一次后就将其结果存在了内部临时存储区域内，而此时主查询的查询对象就是存储在内部临时存储区域中的数据集合，因此通过使用嵌套视图实现了只执行一次函数的目的，此时内部子查询和外部主查询都会按照局部范围扫描的方式来运行，因此查询性能会得到很大的提升。

 

       但是如果将内部的rownum去掉，那么执行过程就大不一样了，此时内部子查询的函数可能会被执行多次，因为没有机制来保证内部子查询的结果会被首先保存在内部临时存储区域内，因此函数会被执行的次数可能会与满足cust_type=’IC’条件的记录数目相同。虽然此时内部子查询也可能是局部范围扫描，但此时的局部范围扫描由于不能将结果存放在内部临时存储区域中，而只能直接填充批量数组，同时填充过程中可能还要与外部主查询条件bill_tot>5000000发生交互作用，所以函数可能会被多次调用执行。  www.2cto.com  

 

      可见这种方法的关键在于，设法将内部子查询的结果缓存在内部临时存储区域中，除了使用rownum外还有很多方法可以实现这个目的，如：order by、group by等操作，也同样会驱动子查询的结果被保存在内部临时存储区域中。

 

 

摘自 javacoffe的专栏