SQL調整：‘以空間換效能’調整一例

如有錯誤，請指正。

客戶抱怨在前臺執行的時候很慢，經檢查發現問題為查詢一個檢視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欄位屬於冗餘欄位，利用空間上的損失獲得了效能上面的提升。