标量子查询改写优化

最新推荐文章于 2024-04-14 23:19:14 发布

ciqiyuan9775

最新推荐文章于 2024-04-14 23:19:14 发布

阅读量186

点赞数

文章标签：大数据

1.简单版:

SELECT empno,
ename,
sal,
deptno,
(SELECT d.dname
FROM dept d
WHERE d.deptno = e.deptno)
AS dname
FROM emp e;

改写后:

SELECT e.empno,
e.ename,
e.sal,
e.deptno,
D.DNAME AS dname
FROM emp e left join dept d on (d.deptno = e.deptno)

2.带聚合函数的标量子查询

SELECT d.department_id,
d.department_name,
d.location_id,
NVL ( (SELECT SUM (e.salary)
FROM hr.employees e
WHERE e.department_id = d.department_id),
0)
AS sum_sal
FROM hr.departments d;

改写后:

SELECT d.department_id,
d.department_name,
d.location_id,
NVL (e.salary, 0) AS sum_sal
FROM hr.departments d
LEFT JOIN ( SELECT e.department_id, SUM (e.salary) salary
FROM hr.employees e
GROUP BY e.department_id) e
ON (e.department_id = d.department_id)

3.行转列优化标量子查询

SELECT
(SELECT ROUND(v,2) FROM F2 where F2.UMITM=F4101.IMITM AND m='1' and F2.UMUM=F4101.IMUOM1) AS M3,
(SELECT ROUND(v,2) FROM F2 where F2.UMITM=F4101.IMITM AND m='2' and F2.UMUM=F4101.IMUOM1) AS KG,
(SELECT ROUND(v,2) FROM F2 where F2.UMITM=F4101.IMITM AND m='3' and F2.UMUM=F4101.IMUOM1) AS KN,
(SELECT ROUND(v,2) FROM F2 where F2.UMITM=F4101.IMITM AND m='4' and F2.UMUM=F4101.IMUOM1) AS MH,
(SELECT ROUND(v,2) FROM F2 where F2.UMITM=F4101.IMITM AND m='5' and F2.UMUM=F4101.IMUOM1) AS ML,
(SELECT ROUND(v,2) FROM F2 where F2.UMITM=F4101.IMITM AND m='6' and F2.UMUM=F4101.IMUOM1) AS MW,
(SELECT MAX(BPUPRC)/10000 FROM F4106 WHERE TRIM(BPMCU) = 'ZX10' and BPCRCD='CNY' AND F4106.BPLITM = F4101.IMLITM) BPUPRC
FROM F4101, F4102
WHERE F4101.IMITM = F4102.IBITM
AND F4101.IMLITM = F4102.IBLITM
AND IBMCU <> 'ZX10'
AND IMDSC1 NOT LIKE '%CANCEL%'
AND ( IMSRP5 IN ('1', '6')
OR IMSRP3 IN ('37', '38')
OR IBLITM IN ('506000040') AND TRIM (IBMCU) = 'SF10')

改写后:

WITH f0
AS (SELECT IMITM, IMUOM1, IMLITM
FROM F4101, F4102
WHERE F4101.IMITM = F4102.IBITM
AND F4101.IMLITM = F4102.IBLITM
AND IBMCU <> 'ZX10'
AND IMDSC1 NOT LIKE '%CANCEL%'
AND ( IMSRP5 IN ('1', '6')
OR IMSRP3 IN ('37', '38')
OR IBLITM IN ('506000040') AND TRIM (IBMCU) = 'SF10'))
SELECT F2.M3 AS M3,
F2.KG AS KG,
F2.KN AS KN,
F2.MH AS MH,
F2.ML AS ML,
F2.MW AS MW,
F4106.BPUPRC AS BPUPRC
FROM f0
LEFT JOIN ( SELECT UMITM,
UMUM,
MAX (CASE WHEN m = '1' THEN ROUND (v, 2) END) AS M3,
MAX (CASE WHEN m = '2' THEN ROUND (v, 2) END) AS KG,
MAX (CASE WHEN m = '3' THEN ROUND (v, 2) END) AS KN,
MAX (CASE WHEN m = '4' THEN ROUND (v, 2) END) AS MH,
MAX (CASE WHEN m = '5' THEN ROUND (v, 2) END) AS ML,
MAX (CASE WHEN m = '6' THEN ROUND (v, 2) END) AS MW
FROM F2
GROUP BY UMITM, UMUM) F2
ON (F2.UMITM = f0.IMITM AND F2.UMUM = f0.IMUOM1)
LEFT JOIN ( SELECT BPLITM, MAX (BPUPRC) / 10000 BPUPRC
FROM F4106
WHERE TRIM (BPMCU) = 'ZX10' AND BPCRCD = 'CNY'
GROUP BY BPLITM) F4106
ON (F4106.BPLITM = f0.IMLITM)

4.不等连接的标量子查询改写(一)

SELECT a.licenceid,
a.data_source,
a.street,
(SELECT MIN (contdate)
FROM ct
WHERE ct.licenceid = a.licenceid
AND ct.data_source = a.data_source
AND TRUNC (contdate) >= a.opensaledate)
AS mincontdate,
(SELECT MIN (buydate)
FROM ct
WHERE ct.licenceid = a.licenceid
AND ct.data_source = a.data_source
AND TRUNC (buydate) >= a.opensaledate)
AS minbuydate
FROM a

改写后:

with ct2 as
(
select rowid as rid,
min(case when TRUNC (contdate) >= a.opensaledate then contdate end),
min(case when TRUNC (buydate) >= a.opensaledate then buydate end)
from ct , a
where ct.licenceid = a.licenceid
AND ct.data_source = a.data_source
group by rowid
)
SELECT a.licenceid,
a.data_source,
a.street,
ct2.mincontdate AS mincontdate,
ct2.minbuydate AS minbuydate
FROM a left join ct2 on (ct2.rid = a.rowid)

5.不等连接的标量子查询改写(二)

SELECT s.stkcode,
t.mktcode,
t.stype,
t.sname,
(SELECT SUM (c.hs1)
FROM c
WHERE c.stkcode = s.stkcode
AND c.mktcode = t.mktcode
AND c.calcdate BETWEEN TO_CHAR (
TO_DATE (s.tdate, 'yyyymmdd') - 365,
'yyyymmdd')
AND s.tdate)
AS f1,
(SELECT DECODE (COUNT (c.calcdate),
0, NULL,
SUM (c.hs1) / COUNT (c.calcdate))
FROM c
WHERE c.stkcode = s.stkcode
AND c.mktcode = t.mktcode
AND c.calcdate BETWEEN TO_CHAR (
TO_DATE (s.tdate, 'yyyymmdd') - 365,
'yyyymmdd')
AND s.tdate)
AS f2,
s.tdate
FROM s, t
WHERE s.stkcode = t.scode
AND t.status = 1
AND t.stype = 2
AND s.tdate >= TO_NUMBER (TO_CHAR (SYSDATE - 3, 'yyyymmdd'));

改写后:
(1).

with t0 as
(select rownum as sn, s.*,t.*
FROM s, t
WHERE s.stkcode = t.scode
AND t.status = 1
AND t.stype = 2
AND s.tdate >= TO_NUMBER (TO_CHAR (SYSDATE - 3, 'yyyymmdd'))
)
SELECT t.stkcode,
t.mktcode,
t.stype,
t.sname,
ha.f1 AS f1,
ha.f2 AS f2,
t.tdate
FROM t0 t left join ( SELECT t0.sn,
SUM (c.hs1) f1,
DECODE (COUNT (c.calcdate), 0, NULL, SUM (c.hs1) / COUNT (c.calcdate))
f2
FROM c, t0
WHERE c.stkcode = t0.stkcode
AND c.mktcode = t0.mktcode
AND c.calcdate BETWEEN TO_CHAR (TO_DATE (t0.tdate, 'yyyymmdd') - 365,
'yyyymmdd')
AND t0.tdate
GROUP BY t0.sn) ha on (ha.sn = t0.sn)

(2).

WITH t0
AS (SELECT ROWNUM AS sn, s.*, t.*
FROM s, t
WHERE s.stkcode = t.scode
AND t.status = 1
AND t.stype = 2
AND s.tdate >= TO_NUMBER (TO_CHAR (SYSDATE - 3, 'yyyymmdd')))
SELECT s.stkcode,
t.mktcode,
t.stype,
t.sname,
SUM (c.hs1) AS f1,
DECODE (COUNT (c.calcdate), 0, NULL, SUM (c.hs1) / COUNT (c.calcdate))
AS f2,
s.tdate
FROM t0 t LEFT JOIN c ON (c.stkcode = t.stkcode AND c.mktcode = t.mktcode)
WHERE c.calcdate BETWEEN TO_CHAR (TO_DATE (s.tdate, 'yyyymmdd') - 365,
'yyyymmdd')
AND s.tdate
GROUP BY t.sn,
t.stkcode,
t.mktcode,
t.stype,
t.sname,
s.tdate

6. 标量子查询DISTINCT

SELECT *
FROM (SELECT *
FROM t1
WHERE col4_1 = '00') a
LEFT JOIN
(SELECT col3_1,
col3_2 col1,
col2,
(SELECT DISTINCT col2_1
FROM t2
WHERE col2_2 = a.col2
AND col2_3 <= TO_DATE ( ('2012-09-30'), 'yyyy-mm-dd')
AND col2_4 > TO_DATE ('2012-09-30', 'yyyy-mm-dd'))
col3
FROM t3 a
WHERE col2_3 <= TO_DATE ('2012-09-30', 'yyyy-mm-dd')
AND col2_4 > TO_DATE ('2012-09-30', 'yyyy-mm-dd')) b
ON (a.col4 = b.col3_1)

改写后:

SELECT *
FROM (SELECT *
FROM t1
WHERE col4_1 = '00') a
LEFT JOIN
(SELECT col3_1,
col3_2 col1,
col2,
col3.col2_1 col3
FROM t3 a
LEFT JOIN
( SELECT col2_1, col2_2
FROM t2
WHERE col2_3 <=
TO_DATE ( ('2012-09-30'),
'yyyy-mm-dd')
AND col2_4 >
TO_DATE ('2012-09-30', 'yyyy-mm-dd')
GROUP BY col2_1, col2_2) col3
ON (col3.col2_2 = a.col2)
WHERE col2_3 <= TO_DATE ('2012-09-30', 'yyyy-mm-dd')
AND col2_4 > TO_DATE ('2012-09-30', 'yyyy-mm-dd')) b
ON (a.col4 = b.col3_1)

7.分析函数优化标量子查询(一)

当标量子查询中的表与主查询中的表一样，也就是有自关联的时候，常常可以改用分析函数直接取值。

未完。。。

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

转载于:http://blog.itpub.net/15412087/viewspace-2148428/

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
标量子查询改写优化

1.简单版: SELECT empno, ename, sal, deptno,...
复制链接

扫一扫

评论

被折叠的条评论为什么被折叠?

到【灌水乐园】发言

查看更多评论

添加红包

成就一亿技术人!

hope_wisdom

发出的红包

实付元

使用余额支付

点击重新获取

扫码支付

钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。