解析Oracle数据扫描Oracle SQL优化引导局部扫描方法（7）(二)
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的专栏