热门搜索
分布式
数据库一体机
云数据库
建设银行
保险核心
密集交易
登录
注册
SQL优化案例——反向改写标量子查询
技术分享/
文章详情
/
SQL优化案例——反向改写标量子查询
PYZ
2025/12/31
121
0
0
问题SQL
select
*
from ( select TMP_PAGE.*,
ROWNUM as ROW_ID
from ( select *
from (select dev_ip as oltip,
olt_pon_port_cuid as oltpon,
firstpos as lightinfo,
t.cvlan as cvlan,
t.svlan as svlan,
t.logicid as loid,
nvl(nvl(( select decode(obd.pos_level,'二级', '2','一级', '1')
from l_obd obd
where obd.cuid = p.access_device ), ( select decode(obd.pos_level, '二级', '2','一级', '1')
from l_obd obd
where obd.int_id = p.access_device )), '2') as lightlevel,
p.access_device as lightid,
k.city_id as city_id,
k.country_id as county_id,
p.product_code,
o.service_type,
p.access_port,
p.access_device,
o.crm_task_id
from yqy_kd k
left join t_activate t
on k.sheetid = t.related_sheet_cuid
and t.activate_state = '22'
and t.access_type = '1'
inner join t_product p
on k.account = p.product_code
inner join t_order o
on o.cuid = p.related_order_cuid
where k.account = '876' ) s
where rownum = 1 ) TMP_PAGE
where ROWNUM <= 10 )
where ROW_ID > 0;
1 #NSET2: [4395, 1, 960]
2 #PIPE2: [4395, 1, 960]
3 #PIPE2: [4395, 1, 960]
4 #PRJT2: [4395, 1, 960]; exp_num(16), is_atom(FALSE)
5 #SLCT2: [4395, 1, 960]; DMTEMPVIEW_889254675.ROW_ID > var5
6 #PRJT2: [4395, 1, 960]; exp_num(16), is_atom(FALSE)
7 #RN: [4395, 1, 960]
8 #PRJT2: [4395, 1, 960]; exp_num(15), is_atom(FALSE)
9 #TOPN2: [4395, 1, 960]; top_num(10)
10 #PRJT2: [4395, 1, 960]; exp_num(15), is_atom(FALSE)
11 #TOPN2: [4395, 1, 960]; top_num(1)
12 #NEST LOOP INNER JOIN2: [4395, 144682, 960]
13 #HASH LEFT JOIN2: [4375, 142352, 624]; key_num(1), partition_keys_num(0), ret_null(0), mix(0), KEY(K.SHEETID=T.RELATED_SHEET_CUID)
14 #BLKUP2: [207, 142352, 336]; IDX_YQY_ZHICHA_KD_TDT_2(K)
15 #SSEK2: [207, 142352, 336]; scan_type(ASC), IDX_YQY_ZHICHA_KD_TDT_2(yqy_kd as K), scan_range['876','876'], is_global(0)
16 #SLCT2: [3401, 7414543, 288]; (T.ACTIVATE_STATE = '22' AND T.ACCESS_TYPE = '1') SLCT_PUSHDOWN(TRUE)
17 #CSCN2: [3401, 19608061, 288]; INDEX33567764(t_activate as T) NEED_SLCT(TRUE); btr_scan(1)
18 #NEST LOOP INDEX JOIN2: [1, 1, 336]
19 #BLKUP2: [1, 1, 192]; INDEX_PRODUCT_CODE_SF(P)
20 #SSEK2: [1, 1, 192]; scan_type(ASC), INDEX_PRODUCT_CODE_SF(t_product as P), scan_range['876','876'], is_global(0)
21 #CSEK2: [1, 1, 48]; scan_type(UNIQUE), INDEX33587856(t_order as O), scan_range[P.RELATED_ORDER_CUID,P.RELATED_ORDER_CUID]
22 #SPL2: [1, 1, 96]; key_num(1), spool_num(1), is_atom(TRUE), has_var(1), sites(-)
23 #PRJT2: [1, 1, 96]; exp_num(1), is_atom(TRUE)
24 #BLKUP2: [1, 1, 96]; IDX_LCZG_OBD_ID(OBD)
25 #SSEK2: [1, 1, 96]; scan_type(ASC), IDX_LCZG_OBD_ID(l_obd as OBD), scan_range[var4,var4], is_global(0)
26 #SPL2: [1, 1, 96]; key_num(1), spool_num(0), is_atom(TRUE), has_var(1), sites(-)
27 #PRJT2: [1, 1, 96]; exp_num(1), is_atom(TRUE)
28 #SSEK2: [1, 1, 96]; scan_type(ASC), IDX_LCZG_OBD_20251223_01(l_obd as OBD), scan_range[(var3,min),(var3,max)), is_global(0)
问题分析
从执行计划可以看出问题点主要在于12-21步的表关联，首先K表和T表的hash关联，T表有近2000w数据量，而K表在与T表关联后，还需要和P表和O表的结果集进行#NEST LOOP INNER JOIN，相当于产生了笛卡尔积，因此导致SQL执行不出来。
优化方案
- 经过排查发现K表和P、O表关联后的结果集很少，因此可以让这三张表提前关联；
- T表的关联字段t.related_sheet_cuid上存在索引，并且字段过滤性很高；
- T表是与K表进行左关联，因此可以在K、P、O关联后获取少量结果集，再关联T表进行连接推入，左关联可以改写为标量子查询；
改写后
select
*
from ( select TMP_PAGE.*,
ROWNUM as ROW_ID
from (select *
from (select dev_ip as oltip,
olt_pon_port_cuid as oltpon,
firstpos as lightinfo,
(select t.cvlan from t_activate t where k.sheetid = t.related_sheet_cuid and t.activate_state = '22' and t.access_type = '1') as cvlan,
(select t.svlan from t_activate t where k.sheetid = t.related_sheet_cuid and t.activate_state = '22' and t.access_type = '1') as svlan,
(select t.logicid from t_activate t where k.sheetid = t.related_sheet_cuid and t.activate_state = '22' and t.access_type = '1') as loid,
nvl(nvl(( select decode(obd.pos_level,'二级', '2', '一级', '1')
from l_obd obd
where obd.cuid = p.access_device ),
( select decode(obd.pos_level, '二级', '2','一级', '1')
from l_obd obd
where obd.int_id = p.access_device )), '2') as lightlevel,
p.access_device as lightid,
k.city_id as city_id,
k.country_id as county_id,
p.product_code,
o.service_type,
p.access_port,
p.access_device,
o.crm_task_id
from yqy_kd k
inner join t_product p
on k.account = p.product_code
inner join t_order o
on o.cuid = p.related_order_cuid
where k.account = '876' ) s
where rownum = 1 ) TMP_PAGE
where ROWNUM <= 10 )
where ROW_ID > 0;
1 #NSET2: [227, 1, 672]
2 #PIPE2: [227, 1, 672]
3 #PIPE2: [227, 1, 672]
4 #PIPE2: [227, 1, 672]
5 #PIPE2: [227, 1, 672]
6 #PIPE2: [227, 1, 672]
7 #PRJT2: [227, 1, 672]; exp_num(16), is_atom(FALSE)
8 #SLCT2: [227, 1, 672]; DMTEMPVIEW_893184376.ROW_ID > var8
9 #PRJT2: [227, 1, 672]; exp_num(16), is_atom(FALSE)
10 #RN: [227, 1, 672]
11 #PRJT2: [227, 1, 672]; exp_num(15), is_atom(FALSE)
12 #TOPN2: [227, 1, 672]; top_num(10)
13 #PRJT2: [227, 1, 672]; exp_num(15), is_atom(FALSE)
14 #TOPN2: [227, 1, 672]; top_num(1)
15 #NEST LOOP INNER JOIN2: [227, 144682, 672]
16 #BLKUP2: [207, 142352, 336]; IDX_YQY_ZHICHA_KD_TDT_2(K)
17 #SSEK2: [207, 142352, 336]; scan_type(ASC), IDX_YQY_ZHICHA_KD_TDT_2(yqy_kd as K), scan_range['876','876'], is_global(0)
18 #NEST LOOP INDEX JOIN2: [1, 1, 336]
19 #BLKUP2: [1, 1, 192]; INDEX_PRODUCT_CODE_SF(P)
20 #SSEK2: [1, 1, 192]; scan_type(ASC), INDEX_PRODUCT_CODE_SF(t_product as P), scan_range['876','876'], is_global(0)
21 #CSEK2: [1, 1, 48]; scan_type(UNIQUE), INDEX33587856(t_order as O), scan_range[P.RELATED_ORDER_CUID,P.RELATED_ORDER_CUID]
22 #SPL2: [1, 1, 96]; key_num(1), spool_num(4), is_atom(TRUE), has_var(1), sites(-)
23 #PRJT2: [1, 1, 96]; exp_num(1), is_atom(TRUE)
24 #BLKUP2: [1, 1, 96]; IDX_LCZG_OBD_ID(OBD)
25 #SSEK2: [1, 1, 96]; scan_type(ASC), IDX_LCZG_OBD_ID(l_obd as OBD), scan_range[var7,var7], is_global(0)
26 #SPL2: [1, 1, 96]; key_num(1), spool_num(3), is_atom(TRUE), has_var(1), sites(-)
27 #PRJT2: [1, 1, 96]; exp_num(1), is_atom(TRUE)
28 #SSEK2: [1, 1, 96]; scan_type(ASC), IDX_LCZG_OBD_20251223_01(l_obd as OBD), scan_range[(var6,min),(var6,max)), is_global(0)
29 #SPL2: [1, 1, 192]; key_num(1), spool_num(2), is_atom(TRUE), has_var(1), sites(-)
30 #PRJT2: [1, 1, 192]; exp_num(1), is_atom(TRUE)
31 #SLCT2: [1, 1, 192]; (T.ACTIVATE_STATE = '22' AND T.ACCESS_TYPE = '1')
32 #BLKUP2: [1, 1, 192]; IDX_PRECOVER_ACTIVATE_ST_CUID(T)
33 #SSEK2: [1, 1, 192]; scan_type(ASC), IDX_PRECOVER_ACTIVATE_ST_CUID(t_activate as T), scan_range[var5,var5], is_global(0)
34 #SPL2: [1, 1, 192]; key_num(1), spool_num(1), is_atom(TRUE), has_var(1), sites(-)
35 #PRJT2: [1, 1, 192]; exp_num(1), is_atom(TRUE)
36 #SLCT2: [1, 1, 192]; (T.ACTIVATE_STATE = '22' AND T.ACCESS_TYPE = '1')
37 #BLKUP2: [1, 1, 192]; IDX_PRECOVER_ACTIVATE_ST_CUID(T)
38 #SSEK2: [1, 1, 192]; scan_type(ASC), IDX_PRECOVER_ACTIVATE_ST_CUID(t_activate as T), scan_range[var4,var4], is_global(0)
39 #SPL2: [1, 1, 192]; key_num(1), spool_num(0), is_atom(TRUE), has_var(1), sites(-)
40 #PRJT2: [1, 1, 192]; exp_num(1), is_atom(TRUE)
41 #SLCT2: [1, 1, 192]; (T.ACTIVATE_STATE = '22' AND T.ACCESS_TYPE = '1')
42 #BLKUP2: [1, 1, 192]; IDX_PRECOVER_ACTIVATE_ST_CUID(T)
43 #SSEK2: [1, 1, 192]; scan_type(ASC), IDX_PRECOVER_ACTIVATE_ST_CUID(t_activate as T), scan_range[var3,var3], is_global(0)
改写后的SQL执行达到毫秒级，需要注意执行计划中SPL节点的has_var是否为1，has_var(1)的情况是将主查询的关联字段转为变量VAR，循环探测被驱动表T，利用T表关联字段上的索引即可让SQL变得更高效。
总结
通常来说标量子查询可以改写left join进行优化，但当多表连接不好自动选择连接顺序，并且主查询可以返回较少结果集的情况下，也可利用索引嵌套循环的思路来进行优化，因此还需结合实际情况具体分析。
评论
后发表回复
作者
文章
阅读量
获赞
扫一扫
联系客服
联系客服
鄂Copyright © 达梦国产化应用创新实验室
达梦在线服务平台社区用户管理规范
鄂ICP备18017926号-1
达梦在线服务平台社区用户管理规范
鄂ICP备18017926号-1