转ITPUB:Oracle多层级查询兼容的性能问题

我们在写plsql的时候，由于外部条件的复杂性，在调用接口时，需要考虑到不同层次查询的需求。以购买基金为例，有账户编号、份额类别、基金代码，其中一个账户下有多个份额类别的基金。
假设有两个表：
accoinfo: 账户信息表。有客户编号和基金帐号
tradeinfo: 交易信息表。有基金帐号、份额类别、基金代码、交易日期、交易金额、备注字段
其中，在账户信息表的客户编号字段和交易信息的基金帐号字段上建有索引。
现要求可以根据不同的输入参数，得到不同的统计值，高一级的必填。
比如要统计某个客户下，前收费下所有的基金代码的交易金额。则必须输入客户编号、基金帐号和份额类别。
下面例子是以账户来统计交易次数，其中提供了客户编号和账户编号：
declare
v_custno varchar2(12) := '990000181565';
v_accono varchar2(12) := '880001491234';
v_sharetype varchar2(6) := '';
v_fundcode varchar2(12) := '';
v_count pls_integer := 0;
begin
select count(1) into v_count
from tradeinfo i, accoinfo c
where i.account_no = c.account_no
and c.customer_no = v_custno
and i.account_no = nvl(v_accono, i.account_no)
and i.sharetype = nvl(v_sharetype, i.sharetype);
end;
这种编写方式比较简洁，但实际使用中可能会发现执行速度比想象中的要慢很多。观察执行计划发现是下面这个样子的：
SQL> var v_custno varchar2(12);
SQL> var v_accono varchar2(12);
SQL> var v_sharetype varchar2(1);
SQL> var v_fundcode varchar2(6);
SQL> var v_count number;
SQL> exec :v_custno := '990000181565';
SQL> exec :v_accono := '880001491234';
SQL> select count(1)
2 from tradeinfo i, accoinfo c
3 where i.account_no = c.account_no
4 and c.customer_no = :v_custno
5 and i.account_no = nvl(:v_accono, i.account_no)
6 and i.sharetype = nvl(:v_sharetype, i.sharetype)
7 /
执行计划：
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 9 (12)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
| 2 | CONCATENATION | | | | | |
|* 3 | FILTER | | | | | |
|* 4 | HASH JOIN | | 1 | 26 | 5 (20)| 00:00:01 |
|* 5 | TABLE ACCESS FULL| TRADEINFO | 1 | 10 | 2 (0)| 00:00:01 |
|* 6 | TABLE ACCESS FULL| ACCOINFO | 1 | 16 | 2 (0)| 00:00:01 |
|* 7 | FILTER | | | | | |
|* 8 | HASH JOIN | | 1 | 26 | 5 (20)| 00:00:01 |
|* 9 | TABLE ACCESS FULL| TRADEINFO | 1 | 10 | 2 (0)| 00:00:01 |
|* 10 | TABLE ACCESS FULL| ACCOINFO | 1 | 16 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
下面是谓词逻辑:
---------------------------------------------------
3 - filter(:v_accono IS NULL)
4 - access("I"."ACCOUNT_NO"="C"."ACCOUNT_NO")
5 - filter("I"."SHARETYPE"=NVL(:V_SHARETYPE,"I"."SHARETYPE") AND
"I"."ACCOUNT_NO" IS NOT NULL)
6 - filter("C"."CUSTOMER_NO"=:v_custno)
7 - filter(:v_accono IS NOT NULL)
8 - access("I"."ACCOUNT_NO"="C"."ACCOUNT_NO")
9 - filter("I"."ACCOUNT_NO"=:v_accono AND
"I"."SHARETYPE"=NVL(:V_SHARETYPE,"I"."SHARETYPE"))
10 - filter("C"."CUSTOMER_NO"=:v_custno)
很奇怪的方式，两个表关联，明明可以通过索引来查询，却以一个复杂的计划扫描全表来实现。使得一行的记录需要大量的一致性读和物理读来实现：
----------------------------------------------------------
0 recursive calls
0 db block gets
360297 consistent gets
342956 physical reads
1 rows processed
但是，如果尝试不用绑定变量而是用实际值去执行该SQL语句，却会是另一种情况：
SQL> select count(1)
2 from tradeinfo i, accoinfo c
3 where i.account_no = c.account_no
4 and c.customer_no = '990000181565'
5 and i.account_no = nvl('880001491234', i.account_no)
6 and i.sharetype = nvl('', i.sharetype)
7 /
执行计划：
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 11 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
|* 2 | TABLE ACCESS BY INDEX ROWID | TRADEINFO | 1 | 10 | 3 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 26 | 11 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| ACCOINFO | 2 | 32 | 5 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | ICUSTNO | 2 | | 3 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IACCONO | 1 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
谓词逻辑:
---------------------------------------------------
2 - filter("I"."SHARETYPE"=NVL('',"I"."SHARETYPE"))
5 - access("C"."CUSTOMER_NO"='990000181565')
6 - access("I"."ACCOUNT_NO"="C"."ACCOUNT_NO")
filter("I"."ACCOUNT_NO"=NVL('880001491234',"I"."ACCOUNT_NO"))
代价统计信息：
----------------------------------------------------------
13 recursive calls
0 db block gets
443 consistent gets
4 physical reads
1 rows processed
可以看到，不使用绑定变量后，"consistent gets"和"physical reads"都从
360297 consistent gets
342956 physical reads
下降到：
443 consistent gets
4 physical reads
带来的效益是很明显的。执行速度上，也从原来的几分钟变成了不到1秒。那是什么影响了它的执行计划呢？之前，为了重现这个案例，我也尝试过很多其他的数据方式，但是最终只有在这个案例上重现了。从这个案例中，可以猜测出这种问题可能是其中两个级别的可选择性比较差，重复值很高引起的。那如果对表进行了分析，又会是什么情况呢？
对表做30%的抽样分析：
call dbms_stats.gather_table_stats(user, 'ACCOINFO', null, 30);
call dbms_stats.gather_table_stats(user, 'TRADEINFO', null, 30);
查看分析后的执行计划：
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 41 | 25 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 41 | | |
| 2 | CONCATENATION | | | | | |
|* 3 | FILTER | | | | | |
|* 4 | TABLE ACCESS BY INDEX ROWID | TRADEINFO | 1 | 15 | 5 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 3 | 123 | 15 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| ACCOINFO | 2 | 52 | 5 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | ICUSTNO | 2 | | 3 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | IACCONO | 2 | | 2 (0)| 00:00:01 |
|* 9 | FILTER | | | | | |
|* 10 | TABLE ACCESS BY INDEX ROWID | ACCOINFO | 1 | 26 | 4 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | 1 | 41 | 10 (0)| 00:00:01 |
|* 12 | TABLE ACCESS BY INDEX ROWID| TRADEINFO | 1 | 15 | 6 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | IACCONO | 2 | | 3 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | ICUSTNO | 2 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
以上计划，相对来说，还是比较复杂，但是在执行速度上已经快了很多，基本上符合按索引取数据的基本要求。
统计信息：
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
1 rows processed
在统计信息上也比前两个少了很多。但是由于客户环境的问题，是不允许收集统计信息的（客户的奇怪规定）。所以要优化语句，只能从语句本身来着手了。按照之前的语句，我们只要排除它因为自连接带来的执行计划误导问题，就可以解决此问题了，因此，可以按如下方式修改：
select count(1)
from tradeinfo i, accoinfo c
where i.account_no = c.account_no
and c.customer_no = :v_custno
and (i.account_no = :v_accono or :v_accono is null)
and (i.sharetype = :v_sharetype or :v_sharetype is null)
表结构与数据如下：
drop table tradeinfo;
drop table accoinfo;
create table tradeinfo(
account_no varchar2(12),
sharetype varchar2(1),
fundcode varchar2(6),
trade_date date,
trade_amount number,
remark char(1000)
);
create table accoinfo(customer_no varchar2(12), account_no varchar2(12));
--测试数据
--truncate table tradeinfo
insert into tradeinfo(account_no, sharetype, fundcode, trade_date, trade_amount, remark)
select '88' || lpad(mod(rownum, trunc(dbms_random.value(7,9))*100000), 10, '0'),
chr(round(dbms_random.value(65,66))),
trunc(dbms_random.value(200000, 200100)),
date'2000-1-1' + numtodsinterval(trunc(dbms_random.value(100,200)),'day') + numtoyminterval(trunc(dbms_random.value(1,10)),'year'),
round(dbms_random.value(1000,10000),4), rownum
from dual
connect by rownum <= 2140000
order by dbms_random.normal
--truncate table accoinfo
insert into accoinfo(customer_no, account_no)
select '99' || lpad(mod(rownum, trunc(dbms_random.value(1,4))*250000), 10, '0'), account_no
from (select account_no
from tradeinfo
where account_no is not null
group by account_no)
order by dbms_random.normal
--创建索引
create index iaccono on tradeinfo(account_no) nologging tablespace fund_index;
create index icustno on accoinfo(customer_no) nologging tablespace fund_index;