今天开发找到我说一条SQL执行的特别慢。SQL里面有一张视图,视图单拿出来跑,需要十几分钟才能出结果(从这里基本上可以定位是视图的问题了)。
SQL和 视图的定义 如下
WITH
"VVVVSUM_TABLE0" AS
(
SELECT
......
FROM
"ANNE_APPS".VVVVSUM_TABLE "VVVVSUM_TABLE"
WHERE
"VVVVSUM_TABLE"."ORG_L1" = 'T024' AND
("VVVVSUM_TABLE"."AS_OF_DATE" = '201703' AND
"VVVVSUM_TABLE"."DATA_TYPE" = 'B' AND
'T01' = 'CNY' AND
"VVVVSUM_TABLE"."CURRENCY_CD" = 'CNY' OR
'T01' = 'R01' AND
"VVVVSUM_TABLE"."CURRENCY_CD" <> 'CNY' OR
'T01' = 'T01' AND
1 = 1)
),
"XXXXX" AS
(
SELECT
......
FROM
VVVVSUM_TABLE0
),
"ORG4" AS
(
SELECT
"DIM_TREE_3"."LEVEL_01_CODE" AS "LEVEL_01_CODE",
"DIM_TREE_3"."LEVEL_02_CODE" AS "LEVEL_02_CODE",
"DIM_TREE_3"."LEAF_CODE" AS "LEAF_CODE"
FROM
"ANNE_APPS"."VW_DIM_TREE_3" "DIM_TREE_3"
WHERE
"DIM_TREE_3"."LEVEL_01_CODE" = 'T024'
),
"XXXX" AS
(
SELECT
"ORG4"."LEVEL_01_CODE" AS "XXXXX",
"ORG4"."LEAF_CODE" AS "XXXXX"
FROM
"ORG4"
)
SELECT
SUM(XXXXXX)
FROM
XXX
WHERE ......
)
GROUP BY
"XXXX"."XXXXXX"
CREATE OR REPLACE VIEW VVVVSUM_TABLE AS
SELECT
...很多个列...
from AAAAAAAT.RRR_RRRRRRRTABLE
union
SELECT
...很多个列...
from AAAAAAAT.RRR_RRRRRRRTABLE_Y
order by AS_OF_DATE desc
视图里面的表数据量如下:
SELECT COUNT(1) FROM AAAAAAAT.RRR_RRRRRRRTABLE_Y; ---14754409
SELECT COUNT(1) FROM AAAAAAAT.RRR_RRRRRRRTABLE; ---10726835
视图里面有order by 这是其一坑
视图里面有union 这是其二坑
union 自带order by作用,这里面居然在结尾加了一个order by ,首先从语意上说这个order by没有任何意义
视图里面有order by本身也是脑残设计,如果确实需要order by可以拿到视图外面去order by
视图里面有order by 。每当访问这个视图的时候,视图都需要先order by【order by的主要消耗来自于table access full】再返回数据。不管外面SQL是否需要order by
比如你需要视图随机返回10行数据where rownum<=10 ,视图内部会先table access full两个表返回2000w行数据进行order by,再返回10行
所以坑不言而喻
同理 :union 比order by还要坑,union 首先需要排序两个大表,然后再去重!!!!
所以到这里我们基本上就有思路了
1.把order by去掉 【任何视图里面都不应该有order by】
2.union改为union all 当然这样改,逻辑就变了。
但是对整个查询没有影响。因为view中union 上下部分别有'A' as DATA_TYPE, 'B' as DATA_TYPE,这种打标可见想对表是单独访问
我把猜想和开发确认,确实如此,页面上有下拉框限制只访问一个表。
修改完后,原SQL 1s响应
总结:视图中有 order by、union、distinct、rownum等需要从业务的角度改写,在设计视图的时候也一定要避免