原来的语句是这样的：
select sum(sl0000) from xstfxps2 where
dhao00 in (
select dhao00 from xstfxps1 where trunc(ywrq00)=trunc(sysdate)
and khdm00=’500000003913′);
已用时间: 00: 02: 49.04
execution plan
———————————————————-
0 select statement optimizer=choose
1 0 sort (aggregate)
2 1 nested loops
3 2 table access (full) of ‘xstfxps2’
4 2 table access (by index rowid) of ‘xstfxps1’
5 4 index (unique scan) of ‘xstfxps1_pk’ (unique)
statistics
———————————————————-
0 recursive calls
0 db block gets
17355138 consistent gets
34141 physical reads
2912 redo size
198 bytes sent via sql*net to client
275 bytes received via sql*net from client
2 sql*net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
我们看到统计信息里面进行了17355138次逻辑读，34141次物理io，这是相当吓人的数字。在执行计划里面我们看到表xstfxps2来了一次全表扫描。
我们首先看一下这两张表总的数据量：
sql> select count(*) from xstfxps2;
count(*)
———-
5585018
我们这里看到xstfxps2这张表有5585018条记录。
sql> select count(*) from xstfxps1;
count(*)
———-
702121
两张表的表结构如下所示：
sql> desc xstfxps1
name type nullable default comments
—— ———— ——– ——- ——–
dhao00 number(8)
lhdh00 number(8) y
fldh00 number(8) y
fplb00 varchar2(2) y
ywrq00 date y
ywry00 varchar2(8) y
shrq00 date y
xsqrrq date y
xsqrry varchar2(8) y
khdm00 varchar2(12)
xkzh00 varchar2(12)
ckdm00 varchar2(2) y
thckdm varchar2(2) y
xsfsdm varchar2(2) y
fxrydm varchar2(4) y
shrydm varchar2(4) y
shbj00 varchar2(1) ‘n’
fxbj00 varchar2(1) ‘n’
skbj00 varchar2(2) y
fkdm00 varchar2(2) y
sql> desc xstfxps2
name type nullable default comments
—— ———— ——– ——- ——–
dhao00 number(8)
spdm00 varchar2(8)
djia00 number(7,2) 0
fxsl00 number y 0
sl0000 number y 0
thsl00 number y 0
je0000 number y 0
se0000 number y
fpbbh0 varchar2(11) y
fphao0 varchar2(10) y
rbdh00 number(8) y
其中xstfxps1的客户订单的表头，保存订单的客户信息、订货日期等信息。xstfxps2是订单的表体，详细记录了客户订单的商品、价格、数量等信息。
调整的第一步是把子查询提取出来，再看语句的执行计划。通常来说，如果语句能够避免子查询的使用，就尽量不用子查询。因为子查询的开销是相当昂贵的。改写后的语句如下：
select sum(sl0000)
from xstfxps2 a,(select dhao00 from xstfxps1 where trunc(ywrq00)=trunc(sysdate)
and khdm00=’500000003913′) b
where a.dhao00=b.dhao00;
已用时间: 00: 00: 03.05
execution plan
———————————————————-
0 select statement optimizer=choose
1 0 sort (aggregate)
2 1 table access (by index rowid) of ‘xstfxps2’
3 2 nested loops
4 3 table access (full) of ‘xstfxps1’
5 3 index (range scan) of ‘xstfxps2_pk’ (unique)
statistics
———————————————————-
0 recursive calls
0 db block gets
11974 consistent gets
225 physical reads
832 redo size
211 bytes sent via sql*net to client
275 bytes received via sql*net from client
2 sql*net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
我们可以看到逻辑io由原来的17355138次下降到11974次，有了数量级的提升。执行时间也有原来将近3分钟下降到现在的3秒多一些。很显然性能有了大幅的提升。不过我们看到执行计划里面表xstfxps1还是有一个全表扫描存在。通常来说我们应该尽量避免全表扫描的存在，尤其对于大表，应该建立合适的索引以避免fts的产生。我们来看这两张表的索引信息：
select index_name,column_name from dba_ind_columns where table_name like ‘xstfxps%’
index_name column_name
—————————— ———————————–
xstfxps1_pk dhao00
xstfxps2_pk dhao00
xstfxps2_pk spdm00
我们看到这两张表除了主键约束外都没有建另外的索引。根据语句的查询情况，我们建立了如下的复合索引：
create index idx_xstfxps1_khdm00_ywrq00 on xstfxps1(khdm00,ywrq00) tablespace indx;
为了使用索引，我们必须对原来的日期字段的条件进行一些调整。因为有个trunc()函数的存在，语句将不会使用到索引。我们只要明白trunc(ywrq00)=trunc(sysdate)事实上等同于ywrq00大于trunc(sysdate)，小于trunc(sysdate+1)减去一秒，我们就有了比较好的办法来处理
这个条件。最终改写后的语句如下：
select sum(sl0000)
from xstfxps2 a, xstfxps1 b
where a.dhao00=b.dhao00
and b.khdm00=’500000003913′
and b.ywrq00 between trunc(sysdate)
and trunc(sysdate)+1-1/(24*60*60);
execution plan
———————————————————-
0 select statement optimizer=choose
1 0 sort (aggregate)
2 1 table access (by index rowid) of ‘xstfxps2’
3 2 nested loops
4 3 table access (by index rowid) of ‘xstfxps1’
5 4 index (range scan) of ‘idx_xstfxps1_khdm00_ywrq00’
(non-unique)
6 3 index (range scan) of ‘xstfxps2_pk’ (unique)
statistics
———————————————————-
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
210 bytes sent via sql*net to client
275 bytes received via sql*net from client
2 sql*net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
我们这时候看逻辑io已经降为3次，语句的执行计划也符合我们的调整目标，创建的索引产生了比较大的效果。这条语句的调整至此告一段落。