ITPub博客

首页 > Linux操作系统 > Linux操作系统 > SQL调整:‘以空间换性能’调整一例

SQL调整:‘以空间换性能’调整一例

原创 Linux操作系统 作者:husthxd 时间:2004-09-21 00:00:00 0 删除 编辑

如有错误,请指正。




客户抱怨在前台执行的时候很慢,经检查发现问题为查询一个视图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字段属于冗余字段,利用空间上的损失获得了性能上面的提升。


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/6906/viewspace-21541/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
长期从事政务、金融等行业产品研发和架构设计工作,对Oracle、PostgreSQL以及大数据等相关技术有深入研究。现就职于广州云图数据技术有限公司,系统架构师。

注册时间:2007-12-28

  • 博文量
    1155
  • 访问量
    3624486