标量子查询相关解析
在Oracle-SQL中，当一个子查询介于select和from之间时，这种子查询就叫做标量子查询，一个范例如下：
select a.name, a.sex,
(select b.salary from salary b where b.deptno = a.deptno) salary
from user a;标量子查询类似一个天然的嵌套循环，而且驱动表固定为主表。由于嵌套循环的被驱动表的连接列必须包含在索引中，所以标量子查询中的表连接也必须包含在索引中。
但实际工作中，应该尽量避免使用标量主查询。如果主表返回了大量数据，主表的连接列基数很高，那么子查询中的表也会被多次扫描，从而严重影响SQL的性能。如果主表的数据量很小，或者主表的连接基数很低，那么这个时候就可以使用标量主查询，但需要给子查询中的连接列建立索引。
当SQL里面有标量子查询时，可以将标量子查询改写为外连接，从而使它们可以进行Hash连接。之所以要把标量子查询改写为外连接而不是内连接的原因，是因为标量子查询是一个传值的过程，如果主表传值给子查询，子查询没有查询到数据，这个时候会显示NULL。此时将标量子查询改写为内连接，会丢失没有关联上的数据。下面是一个标量子查询：
select a.name, a.loc,
(select max(b.sal) from table_2 b where b.deptno = a.deptno) max_sal
from table_1 a;可以将其等价改写为外连接：
select a.name, a.loc, b.max_sal
from table_1 a
left join (select max(sal) max_sal, deptno from table_2 group by deptno) b
on a.dept_no = b.deptno;案例一
问题概述
某客户日常巡检中发现存在高耗sql:
insert into T_XXXX
...
select ...
from (select B.CLJG CLJG,
B.CLJGMC CLJGMC,
B.ZQMJ,
SUBSTR(TO_CHAR(TRUNC(B.WFSJ, 'MM'), 'YYYYMMDD'), 0, 4) || '01' ||
SUBSTR(TO_CHAR(TRUNC(B.WFSJ, 'MM'), 'YYYYMMDD'), 5, 2) YF,
count(1) ZS,
0 CXS,
(select count(1)
from (select *
from T_XXXX T
right join (select trim(A.BH) BH,A.AJLX,A.SPZT,A.HPHM,A.DSRXM,A.YY,A.SHSJ
from T_XXXX A where A.AJLX = '简易处罚' and A.SPZT in ('变更')) T1
on T1.BH = T.JDSBH
where 1 = 1 and T.JDSBH is not null and T.XXLY = '1' and T.JDSLB = '1'
and T.WFSJ >= TO_DATE('20190101', 'YYYYMMDD')) C
where C.CLJG = B.CLJG
and C.ZQMJ = B.ZQMJ
and C.CLJGMC = B.CLJGMC
and TRUNC(C.WFSJ, 'MM') = TRUNC(B.WFSJ, 'MM')) BGS,
0 ZFS,0 ZCSJLRS,0 FZCSJLRS,0 CXL,0 BGL,0 ZFL,0 FZCSJLRL,'JGZX044' as YJDID,
(select C.JGBJCXX
from T_XXXX C
where C.JCDZJ = 'JGZX044') as YJDMC,
'ZFJD_FXD_WSGL_JYCFJDSBG' SJLXBH,
'ZFJD_CSYCSJL' UPPER_SJLXBH,
'2' BZ
from (select *
from T_XXXX T
where T.XXLY = '1'
and T.JDSLB = '1'
and T.WFSJ >= TO_DATE('20190101', 'YYYYMMDD')) B
group by B.CLJG, B.CLJGMC, B.ZQMJ, TRUNC(B.WFSJ, 'MM'));SQL的执行情况:
LAST_CALL SESS_SERIAL STATUS BLOCK_SESS RUN CLIENT
I EVENT SEQ# OSPID STATE COM SQL_ID INST:SESS TIME OSUSER_MACHINE_PRO
-- ------ ---------------------- ------------------ ---------- --- ------------------ --------------- ----- ------------------------
3 [CPU]: U_xxx|5.4W|3.2W 25297,53503 110224 A.S.2040MS INS C.2zh5k710dh8y6:0 2.52W oracle@zdb03@110224.SYSU
CPU(Sec) ELA(Sec) DISK(Phy) GET(Buff) ROW_PROC ROWS APPLI(MS) CONCUR(MS) CLUSTER(Sec) IO_WAIT(Sec) PLSQL JAVA
EXEC PRE EXEC PRE EXEC PRE EXEC PRE EXEC PRE EXEC PRE FETC PER EXEC PER EXEC PER EXEC PER EXEC PER EXEC PER EXEC SQL_PROFILE
---------- ---------- ---------- ------------- ------------- -------- -------- --------- ---------- ------------ ------------ -------- -------- ---------------
1 28252.5 28470.6 20,5419 8,9566,8525 1.8W 1.8W 0 232 106.7 37.4 0 0
PLAN CHI USER CPU(Sec) ELA(Sec) DISK(Phy) GET(Buff) ROW_PROC ROWS APPLI(MS) CONCUR(MS) CLUSTER(Sec) IO_WAIT(Sec) FIRST_LOAD_TIME
EXEC HASH VALUE NUM NAME PRE EXEC PRE EXEC PRE EXEC PRE EXEC PRE EXEC PRE FETC PER EXEC PER EXEC PER EXEC PER EXEC LAST_LOAD_TIME
---- ---------- ---- ------------ ---------- ---------- ------------- ------------- -------- -------- --------- ---------- ------------ ------------ ----------------------
1 3502319164 0 USER_NAME 28252.5 28470.6 20,5419 8,9566,8525 1.8W 1.8W 0 232 106.7 37.4 08-31/08:0.08-31/08:0SQL执行没有阻塞，执行2.52W秒没有结束(约7个小时)
SQL执行历史信息中，平均执行1次2.8W秒(约8个小时)，逻辑读消耗8亿，反回处理数据行数1.8W；每2天执行1次。
问题原因
查看SQL执行计划:
Plan hash value: 3502319164
---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | | 145K(100)| | | |
| 1 | LOAD TABLE CONVENTIONAL | | | | | | | | |
| 2 | SORT AGGREGATE | | 1 | 133 | | | | | |
|* 3 | HASH JOIN | | 1 | 133 | | 18717 (1)| 00:03:45 | | |CPU(10)(.04%) |
| 4 | PARTITION RANGE ITERATOR | | 1 | 96 | | 18687 (1)| 00:03:45 | 367 |1048575|CPU(134)(.53%) |
|* 5 | TABLE ACCESS BY LOCAL INDEX ROWID| T_XXXX | 1 | 96 | | 18687 (1)| 00:03:45 | 367 |1048575|gc cr grant 2-way(21)(.08%) |
| |db file sequential read(33)(.13%) |
| |gc cr request(2)(.01%) |
| |CPU(1566)(6.25%) |
| |gc current block 3-way(31)(.12%) |
| |gc current block 2-way(45)(.18%) |
|* 6 | INDEX RANGE SCAN | IDX_XXX1 | 48787 | | | 13152 (2)| 00:02:38 | 367 |1048575|CPU(23204)(92.55%) |
|* 7 | TABLE ACCESS FULL | T_XXXX | 346 | 12802 | | 30 (0)| 00:00:01 | | |CPU(7)(.03%) |
|* 8 | TABLE ACCESS FULL | T_XXXX | 1 | 45 | | 3 (0)| 00:00:01 | | |
| 9 | SEQUENCE |SQ_XXX | | | | | | | |CPU(2)(.01%) |
| 10 | VIEW | | 2134K| 999M| | 145K (1)| 00:29:03 | | |
| 11 | HASH GROUP BY | | 2134K| 162M| 196M| 145K (1)| 00:29:03 | | |CPU(2)(.01%) |
| 12 | PARTITION RANGE ITERATOR | | 2134K| 162M| | 105K (1)| 00:21:08 | 367 |1048575|
|* 13 | TABLE ACCESS FULL | T_XXXX | 2134K| 162M| | 105K (1)| 00:21:08 | 367 |1048575|gc current block 2-way(1)(0%) |
| |gc cr multi block request(2)(.01%) |
| |CPU(11)(.04%) |
---------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T"."JDSBH"=TRIM("A"."BH"))
5 - filter(("T"."ZQMJ"=:B1 AND "T"."CLJGMC"=:B2 AND "T"."CLJG"=:B3 AND "T"."XXLY"='1' AND "T"."JDSLB"='1' AND "T"."JDSBH" IS NOT NULL))
6 - access("T"."WFSJ">=TO_DATE(' 2019-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) filter(TRUNC(INTERNAL_FUNCTION("T"."WFSJ"),'fmmm')=:B1)
7 - filter(("A"."AJLX"='简易处罚' AND "A"."SPZT"='变更'))
8 - filter("C"."JCDZJ"='JGZX044')
13 - filter(("T"."XXLY"='1' AND "T"."WFSJ">=TO_DATE(' 2019-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T"."JDSLB"='1'))执行计划中，第5步为标量子查询+聚合统计，主查询和标量子查询基本查询结果相同。
select count(*) from JGZX_DPZS_ZFJD_WFCLXX T
where T.XXLY = '1'
and T.JDSLB = '1'
and T.WFSJ >= TO_DATE('20190101', 'YYYYMMDD')但是主查询的返回记录数有400百万,在标量子查询中，当主查询返回一行数据时，所有的标量子查询就要执行一次，如果在连接列有索引时，标量子查询在主表返回的行很少的情况下，对性能影响不大。如果主查询返回的数据较多，而子查询中又没有高效的索引，关联列对应的主查询表又没有较多的重复值，那么执行成本是巨大的，此SQL就属于以上情况。
解决方案
标量子查询的执行步骤：首先走的是外部的主查询，然后取主表的返回每一行数据就去和子表去关联，如果符合则返回子查询的值，如果不符合则用null补充。当然这个时候还有个类似的filter去重的运算，对于主表重复的数据行不用再去和子表去关联，因此基本可以改写成主表和子表的左关联即可。对于本例子中的标量子查询部分:
select count(1)
from (select *
from T_XXXX T
right join (select trim(A.BH) BH,A.AJLX,A.SPZT,A.HPHM,A.DSRXM,A.YY,A.SHSJ
from T_XXXX A where A.AJLX = '简易处罚' and A.SPZT in ('变更')) T1
on T1.BH = T.JDSBH
where 1 = 1 and T.JDSBH is not null and T.XXLY = '1' and T.JDSLB = '1'
and T.WFSJ >= TO_DATE('20190101', 'YYYYMMDD')) C
where C.CLJG = B.CLJG
and C.ZQMJ = B.ZQMJ
and C.CLJGMC = B.CLJGMC
and TRUNC(C.WFSJ, 'MM') = TRUNC(B.WFSJ, 'MM')T表右外连接T1表，但是条件是T表为非空，实际上已经变为内连接。因此整体可以改写为:
select *
from (select T.CLJG,T.ZQMJ,T.CLJGMC,T.WFSJ
from T_XXXX T
WHERE T.XXLY = '1'
AND T.JDSLB = '1'
AND T.WFSJ >= TO_DATE('20190101', 'YYYYMMDD')) T
left join (select a.CLJG,
a.CLJGMC,
a.ZQMJ,
TRUNC(a.WFSJ, 'MM') SJ,
COUNT(1) CNT
FROM (SELECT T.CLJG, T.CLJGMC, T.ZQMJ, T.WFSJ
FROM T_XXXX T
JOIN (SELECT TRIM(A.BH) BH,
A.AJLX,
A.SPZT,
A.HPHM,
A.DSRXM,
A.YY,
A.SHSJ
FROM T_XXXX A
WHERE A.AJLX = '简易处罚'
AND A.SPZT IN ('变更')) T1
ON T1.BH = T.JDSBH
WHERE 1 = 1
AND T.XXLY = '1'
AND T.JDSLB = '1'
AND T.WFSJ >= TO_DATE('20190101', 'YYYYMMDD')) a
group by a.CLJG, a.CLJGMC, a.ZQMJ, TRUNC(a.WFSJ, 'MM')) B
on (T.CLJG = B.CLJG and T.ZQMJ = B.ZQMJ and T.CLJGMC = B.CLJGMC and
TRUNC(T.WFSJ, 'MM') = B.SJ)
group by T.CLJG, T.CLJGMC, T.ZQMJ, TRUNC(T.WFSJ, 'MM');SQL的主查询和标量子查询基本查询结构和查询条件都相同，因此最终可以改写成如下:
select *
from (
select
T.CLJG CLJG,
T.CLJGMC CLJGMC,
T.ZQMJ,
SUBSTR(TO_CHAR(TRUNC(T.WFSJ, 'MM'), 'YYYYMMDD'), 0, 4) || '01' || SUBSTR(TO_CHAR(TRUNC(T.WFSJ, 'MM'), 'YYYYMMDD'), 5, 2) YF,
count(distinct WFBH) ZS,
0 CXS,
count(T1.BH) BGS,
0 ZFS,0 ZCSJLRS,0 FZCSJLRS,0 CXL,0 BGL,0 ZFL,0 FZCSJLRL,'JGZX044' as YJDID,
(select C.JGBJCXX from JGZX_JCDMS C where C.JCDZJ = 'JGZX044') as YJDMC,
'ZFJD_FXD_WSGL_JYCFJDSBG' SJLXBH,
'ZFJD_CSYCSJL' UPPER_SJLXBH,
'2' BZ，
ZFJD_TJ.NEXTVAL PKID
from T_XXXX T
left join
( select trim(A.BH) BH,A.AJLX,A.SPZT,A.HPHM,A.DSRXM,A.YY,A.SHSJ
from T_XXXX A where A.AJLX = '简易处罚' and A.SPZT in ('变更')
) T1
on T1.BH = T.JDSBH
where T.XXLY = '1'
and T.JDSLB = '1'
and T.WFSJ >= TO_DATE('20190101', 'YYYYMMDD')
group by T.CLJG, T.CLJGMC, T.ZQMJ, TRUNC(T.WFSJ, 'MM')
) ;由于T表和T1表关联条件非唯一，因此取外部表的COUNT计数时，使用外部表的PK主键去重。
查看改写后的优化效果:
SYS@(db): 1> alter session set current_schema=U_XXXX;
SYS@(db): 1> create table sys.enmo_tmp as select * from U_XXXXX.T_XXXX where 1=2;
Table created.
SYS@(db): 1> set timing on
SYS@(db): 1>
SYS@(db): 1>
SYS@(db): 1> insert into sys.enmo_tmp
2 (CLJG,CLJGMC,ZQMJ,YF,ZS,CXS,BGS,ZFS,ZCSJLRS,FZCSJLRS,CXL,BGL,ZFL,FZCSJLRL,YJDID,YJDMC,SJLXBH,UPPER_SJLXBH,BZ,PKID)
3 select * from (SELECT T.CLJG,
4 T.CLJGMC,
5 T.ZQMJ,
6 SUBSTR(TO_CHAR(TRUNC(T.WFSJ, 'MM'), 'YYYYMMDD'), 0, 4) || '01' ||
7 SUBSTR(TO_CHAR(TRUNC(T.WFSJ, 'MM'), 'YYYYMMDD'), 5, 2) YF,
8 count(distinct WFBH) ZS,
9 0 cxs,
10 count(t1.bh) BGS,
11 0 ZFS,
12 0 ZCSJLRS,
13 0 FZCSJLRS,
14 0 CXL,
15 0 BGL,
16 0 ZFL,
17 0 FZCSJLRL,
18 'JGZX044' AS YJDID,
19 (SELECT C.JGBJCXX FROM JGZX_JCDMS C WHERE C.JCDZJ = 'JGZX044') AS YJDMC,
20 'ZFJD_FXD_WSGL_JYCFJDSBG' SJLXBH,
21 'ZFJD_CSYCSJL' UPPER_SJLXBH,
22 '2' BZ
23 FROM T_XXXX T
24 LEFT JOIN (SELECT TRIM(A.BH) BH,
25 A.AJLX,
26 A.SPZT,
27 A.HPHM,
28 A.DSRXM,
29 A.YY,
30 A.SHSJ
31 FROM T_XXXX A
32 WHERE A.AJLX = '简易处罚'
33 AND A.SPZT IN ('变更')) T1
34 ON T1.BH = T.JDSBH
35 WHERE 1 = 1
36 and T.XXLY = '1'
37 AND T.JDSLB = '1'
38 AND T.WFSJ >= TO_DATE('20190101', 'YYYYMMDD')
39 group by T.CLJG, T.CLJGMC, T.ZQMJ, TRUNC(T.WFSJ, 'MM'));
20670 rows created.
Elapsed: 00:00:08.33
Execution Plan
----------------------------------------------------------
Plan hash value: 1541347568
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 3951K| 1850M| | 242K (1)| 00:48:35 | | |
| 1 | LOAD TABLE CONVENTIONAL | ENMO_TMP | | | | | | | |
|* 2 | TABLE ACCESS FULL | T_XXXX | 1 | 45 | | 3 (0)| 00:00:01 | | |
| 3 | SEQUENCE | SQ_XXXX | | | | | | | |
| 4 | VIEW | | 3951K| 1850M| | 242K (1)| 00:48:35 | | |
| 5 | HASH GROUP BY | | 3951K| 504M| 551M| 242K (1)| 00:48:35 | | |
|* 6 | HASH JOIN RIGHT OUTER | | 3951K| 504M| | 124K (1)| 00:24:59 | | |
|* 7 | TABLE ACCESS FULL | T_XXXX | 346 | 12802 | | 30 (0)| 00:00:01 | | |
| 8 | PARTITION RANGE ITERATOR| | 3951K| 365M| | 124K (1)| 00:24:58 | 367 |1048575|
|* 9 | TABLE ACCESS FULL | T_XXXX | 3951K| 365M| | 124K (1)| 00:24:58 | 367 |1048575|
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("C"."JCDZJ"='JGZX044')
6 - access("T"."JDSBH"=TRIM("A"."BH"(+)))
7 - filter("A"."AJLX"(+)='简易处罚' AND "A"."SPZT"(+)='变更')
9 - filter("T"."XXLY"='1' AND "T"."WFSJ">=TO_DATE(' 2019-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"T"."JDSLB"='1')
Statistics
----------------------------------------------------------
1074 recursive calls
6690 db block gets
460160 consistent gets
3 physical reads
5417584 redo size
1137 bytes sent via SQL*Net to client
2629 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
20670 rows processed
-- 两个表的统计结束完全匹配（ sys.enmo_tmp01为原SQL执行结果的临时表）
select * from sys.enmo_tmp minus select * from sys.enmo_tmp01;
未选定行.
select * from sys.enmo_tmp01 minus select * from sys.enmo_tmp;
未选定行.
改写之后，SQL查询结果一致，和原SQL没有差异。逻辑读由之前的8亿降低到了46万，执行时间只需要8秒。
案例二
一、SQL文本
select count(*) sl,
count(case
when exists (select 1
from J2_NSXY.nsxy_dbrw_plblmxjl mx,
J2_NSXY.nsxy_dbrw_plbljl jl
where mx.task_id = t.task_id
and jl.pcxh = mx.pcxh
and jl.zxzt_dm = '01') then
1
else
null
end) plblsl
from J2_NSXY.wf_task t
where exists (select *
from J2_NSXY.nsxy_mdb md
where md.md_dm = t.ywgldm
and md.pd_nd = '2021'
and md.ypbz = 'N'
and md.tz_lc = '0');二、SQL执行情况
执行计划
每次执行的平均逻辑读为235,066,880，物理读为2,928，平均返回1条数据，耗时8756秒未出结果。
三、问题分析及优化思路
通过分析执行计划，结合参与运算的表体积，不应该产生这么多的逻辑读，那SQL中的标量子查询就很可疑。如何验证标量子查询有问题？很简单，删除标量子查询部分，运行主表部分即可，结果主表返回1562537条数据，做HJ消耗也不大，这样就可以验证是标量子查询的问题。
优化标量子查询方法一般有两种，一是创建合适的索引，二是改SQL。这个场景明显更适用于改SQL，让相关表全部走HJ。
四、优化方案
（一）、改写SQL（以下改写仅供参考，最终目的是为了让标量子查询中的表和主表做HJ）
with mx as (
select mx.task_id
from J2_NSXY.nsxy_dbrw_plblmxjl mx,
J2_NSXY.nsxy_dbrw_plbljl jl
where jl.pcxh = mx.pcxh
and jl.zxzt_dm = '01'
)
select count(*) sl,
count(case
when t.task_id is not null then
1
else
null
end) plblsl
from J2_NSXY.wf_task t
where exists (select /*+ hash_sj*/ *
from J2_NSXY.nsxy_mdb md
where md.md_dm = t.ywgldm
and md.pd_nd = '2021'
and md.ypbz = 'N'
and md.tz_lc = '0')
and exists (select *
from mx
where mx.task_id = t.task_id
)
;五、优化效果对比
通过确认关键表数据量，制定执行计划，可以减少每次查询的逻辑读和物理读，提高SQL执行性能。
（一）、优化后的执行计划
（二）、优化前后资源消耗对比
逻辑读 |
物理读 |
执行时间(秒) |
|
优化前 |
235,066,880 |
2,928 |
8756 |
优化后 |
171646 |
90763 |
32 |