脑残设计-视图里包含order by和union

今天开发找到我说一条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等需要从业务的角度改写，在设计视图的时候也一定要避免