客户抱怨在前台执行的时候很慢，经检查发现问题为查询一个视图vw_hyb_tbgrjbxx_temp的时候很慢，查询条件是dwbh和yzbz。视图的定义为：
create or replace view vw_hyb_tbgrjbxx
as
select /*+first_rows*/ t1.*,t2.yzbz,t2.grbh_new
from tb_grjbxx t1,hyb_yzbz t2
where t1.grbh = t2.grbh
/

sidb@GDSI-HYQL> l
1* select *from vw_hyb_tbgrjbxx_temp where dwbh = '341004' and yzbz = '0'
sidb@GDSI-HYQL> /

未选定行

已用时间: 00: 00: 04.08

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=455 Card=466 Bytes=1
15102)

1 0 HASH JOIN (Cost=455 Card=466 Bytes=115102)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'TB_GRJBXX' (Cost=19 Ca
rd=466 Bytes=106714)

3 2 INDEX (RANGE SCAN) OF 'IDX_TB_GRJBXX_DWBH' (NON-UNIQUE
) (Cost=7 Card=466)

4 1 TABLE ACCESS (FULL) OF 'HYB_YZBZ' (Cost=403 Card=129281
Bytes=2327058)

Statistics
----------------------------------------------------------
0 recursive calls
5 db block gets
2773 consistent gets
2275 physical reads
112 redo size
3568 bytes sent via SQL*Net to client
372 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

sidb@GDSI-HYQL>

从执行计划可知，表HYB_YZBZ执行了全表扫描，一般的做法是把全表扫描变为通过索引检索数据。在该例中，发现表HYB_YZBZ上列DWBH建有索引，而且HYB_YZBZ的列GRBH/DWBH与TB_GRJBXX的GRBH/DWBH是一一对应的，因而调整视图定义为：
create or replace view vw_hyb_tbgrjbxx_temp
as
select t1.*,t2.yzbz,t2.grbh_new
from tb_grjbxx t1,hyb_yzbz t2
where t1.grbh = t2.grbh
and t1.dwbh = t2.dwbh
/

我们来看看调整后的效果：
sidb@GDSI-HYQL> create or replace view vw_hyb_tbgrjbxx_temp
2 as
3 select t1.*,t2.yzbz,t2.grbh_new
4 from tb_grjbxx t1,hyb_yzbz t2
5 where t1.grbh = t2.grbh
6 and t1.dwbh = t2.dwbh
7 /

视图已建立。

已用时间: 00: 00: 00.01
sidb@GDSI-HYQL> select *from vw_hyb_tbgrjbxx_temp where dwbh = '341004' and yzbz
= '0';

未选定行

已用时间: 00: 00: 00.01

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=27 Card=233 Bytes=60
114)

1 0 HASH JOIN (Cost=27 Card=233 Bytes=60114)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'HYB_YZBZ' (Cost=7 Card
=233 Bytes=6757)

3 2 INDEX (RANGE SCAN) OF 'IDX_HYB_YZBZ_DWBH' (NON-UNIQUE)
(Cost=7 Card=233)

4 1 TABLE ACCESS (BY INDEX ROWID) OF 'TB_GRJBXX' (Cost=19 Ca
rd=466 Bytes=106714)

5 4 INDEX (RANGE SCAN) OF 'IDX_TB_GRJBXX_DWBH' (NON-UNIQUE
) (Cost=7 Card=466)


Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
31 consistent gets
6 physical reads
0 redo size
3568 bytes sent via SQL*Net to client
372 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

sidb@GDSI-HYQL> /

未选定行

已用时间: 00: 00: 00.01

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=27 Card=233 Bytes=60
114)

1 0 HASH JOIN (Cost=27 Card=233 Bytes=60114)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'HYB_YZBZ' (Cost=7 Card
=233 Bytes=6757)

3 2 INDEX (RANGE SCAN) OF 'IDX_HYB_YZBZ_DWBH' (NON-UNIQUE)
(Cost=7 Card=233)

4 1 TABLE ACCESS (BY INDEX ROWID) OF 'TB_GRJBXX' (Cost=19 Ca
rd=466 Bytes=106714)

5 4 INDEX (RANGE SCAN) OF 'IDX_TB_GRJBXX_DWBH' (NON-UNIQUE
) (Cost=7 Card=466)


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
29 consistent gets
0 physical reads
0 redo size
3568 bytes sent via SQL*Net to client
372 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

sidb@GDSI-HYQL>

从统计信息来看
5 db block gets
2773 consistent gets
2275 physical reads
112 redo size
变为
0 db block gets
29 consistent gets
0 physical reads
0 redo size
调整效果明显。

小结：

习惯性的，通常在两个表连接时只需要一个连接条件，而该例从业务规则上入手，通过在表连接上加入适当的‘冗余’连接条件（在该例中是在DWBH上）进行sql调整。从另外一个方面看，HYB_YZBZ中的DWBH字段属于冗余字段，利用空间上的损失获得了性能上面的提升。