sql优化案例（标量子查询改写）

sql执行频繁，单次执行时间存在性能问题。
sql如下：
select t.cons_no,
t.cons_name,
t.elec_addr,
t.contract_cap t_contract_cap,
(select b.tg_id
from s_mp_scheme b
where exists (select 1
from s_mp_app c
where b.mp_scheme_id = c.mp_scheme_id
and c.app_no = t.app_no)
and rownum = 1) tg_id
from s_app t
where t.app_no = '2xxx7';

Elapsed: 00:00:04.27

Execution Plan
----------------------------------------------------------
Plan hash value: 631761334

 

 

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM=1)
2 - filter( EXISTS (SELECT 0 FROM "SGPM"."S_MP_APP" "C" WHERE "C"."MP_SCHEME_
ID"=:B1 AND

"C"."APP_NO"=:B2))
4 - filter("C"."APP_NO"=:B1)
5 - access("C"."MP_SCHEME_ID"=:B1)
7 - access("T"."APP_NO"='2xxx7')

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

从上面的Statistics我们发现一致读很多。
执行计划S_MP_SCHEME也未能使用高效的扫描方式。
由列的统计信息知道s_app表上的app_no选择性很高，返回的是少量的数据集。
问题很明显：
(select b.tg_id
from s_mp_scheme b
where exists (select 1
from s_mp_app c
where b.mp_scheme_id = c.mp_scheme_id
and c.app_no = t.app_no)
and rownum = 1)
这段标量子查询导致性能问题，通过sql改写解决标量子查询的问题。

 

sql改写后：
select a.cons_no,
a.cons_name,
a.elec_addr,
a.contract_cap t_contract_cap,
d.tg_id
from s_app a
left join (
select c.app_no,b.mp_scheme_id,max(tg_id) tg_id from s_mp_scheme b,s_mp_app c
where b.mp_scheme_id = c.mp_scheme_id
group by b.mp_scheme_id,c.app_no
) d
on d.app_no = a.app_no
where a.app_no = '2xxx7';
Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 2090481196

 

 

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("D"."APP_NO"(+)="A"."APP_NO")
3 - access("A"."APP_NO"='2xxx7')
11 - access("C"."APP_NO"='2xxx7')
12 - access("B"."MP_SCHEME_ID"="ITEM_1")

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

原先的sqlconsistent gets为759983，改写后只有7。
在生产环境中，标量子查询很容易出现性能问题，掌握对此类查询改写的方式很重要。

改写标量子查询能让表的关联方式更加多样化，有助于产生更优的执行计划。