oracle：在select子句中调整相关子查询

在Oracle 10gR2上，给定以下查询，它需要永远运行。这是因为select子句中的所有相关子查询。必须有一个更好的方法。我认为将相关子查询重写为连接可以解决问题，或者构建查询的方式只需要一个相关的子查询，或者可能使用某种类型的分析函数，但到目前为止它已超出我的范围。任何帮助将非常感激。 这是查询： 
SELECT COL_1,
       TAB_1.COL_2                                 AS REPORT,
       (SELECT COL_3
        FROM   TAB_2
        WHERE  TAB_2.COL_1 = TAB_1.COL_1)          AS DEPOT,
       (SELECT COUNT(DISTINCT( TAB_3.COL_4 ))
        FROM   TAB_3
        WHERE  TAB_3.COL_9 = TAB_1.COL_1
               AND TAB_1.COL_2 = TAB_3.COL_6
               AND TAB_3.COL_5 IS NULL
               AND TAB_3.COL_8 = 'Parts Shortage') AS P_SHORTAGES,
       (SELECT COUNT(DISTINCT( Trim(COL_10) ))
        FROM   TAB_3
        WHERE  TAB_3.COL_9 = TAB_1.COL_1
               AND TAB_1.COL_2 = TAB_3.COL_6
               AND TAB_3.COL_5 IS NULL
               AND TAB_3.COL_8 = 'Parts Shortage') AS PARTS_AFFECTED,
       (SELECT COUNT(TAB_3.COL_7)
        FROM   TAB_3
        WHERE  TAB_3.COL_7 = 1
               AND TAB_3.COL_9 = TAB_1.COL_1
               AND TAB_1.COL_2 = TAB_3.COL_6
               AND TAB_3.COL_5 IS NULL
               AND TAB_3.COL_8 = 'Parts Shortage') AS PARTS_CATEGORY1,
       (SELECT COUNT(TAB_3.COL_4)
        FROM   TAB_3
        WHERE  TAB_3.COL_9 = TAB_1.COL_1
               AND TAB_3.COL_6 = TAB_1.COL_2
               AND TAB_3.COL_5 IS NULL
               AND TAB_3.COL_8 = 'Unsrv Asset')    AS U_SHORTAGES,
       (SELECT COUNT(TAB_3.COL_10)
        FROM   TAB_3
        WHERE  TAB_3.COL_9 = TAB_1.COL_1
               AND TAB_3.COL_6 = TAB_1.COL_2
               AND TAB_3.COL_5 IS NULL
               AND TAB_3.COL_8 = 'Unsrv Asset')    AS U_AFFECTED,
       (SELECT COUNT(DISTINCT( Trim(TAB_3.COL_7) ))
        FROM   TAB_3
        WHERE  TAB_3.COL_7 = 1
               AND TAB_3.COL_9 = TAB_1.COL_1
               AND TAB_3.COL_6 = TAB_1.COL_2
               AND TAB_3.COL_5 IS NULL
               AND TAB_3.COL_8 = 'Unsrv Asset')    AS UNSRV_CAT1,
       To_char(TAB_1.COL_11, 'MM/DD/YY')           AS REPORT_DATE
FROM   TAB_1;
对我而言，为了使事情变得不那么复杂，我试图重写以下内容，但我仍然不知所措： 
    SELECT COL_1,
       (SELECT COUNT(DISTINCT( TAB_3.COL_4 ))
        FROM   TAB_3
        WHERE  TAB_3.COL_9 = TAB_1.COL_1
               AND TAB_1.COL_2 = TAB_3.COL_6
               AND TAB_3.COL_5 IS NULL
               AND TAB_3.COL_8 = 'Parts Shortage') AS P_SHORTAGES
FROM   TAB_1;
    
2019-10-19 3 条评论
分享

没有找到相关结果

    已邀请:

    冲汉

    这是一个非常有问题的问题。这里没有足够的信息（例如执行计划，记录计数，可用索引，表中数据的物理位置等）以便能够正确地调整查询。任何人都可以做的最好的事情是猜测。那就是说..这是我最好的猜测： 
    select distinct
    tab_1.col_1,
    tab_1.col_2 AS report,
    tab_2.col_3 AS depot,
    COUNT (DISTINCT (case when tab_3.col_8 = 'Parts Shortage' then tab_3.col_4 end)) over (partition by tab_1.col_1, tab_1.col_2) AS p_shortages,
    COUNT (DISTINCT (case when tab_3.col_8 = 'Parts Shortage' then TRIM (tab_3.col_10) end)) over (partition by tab_1.col_1, tab_1.col_2)  AS parts_affected,
    COUNT (case when tab_3.col_8 = 'Parts Shortage'  and tab_3.col_7 = 1 then tab_3.col_7 end) over (partition by tab_1.col_1, tab_1.col_2)  AS parts_category1,
    COUNT (case when tab_3.col_8 = 'Unsrv Asset' then tab_3.col_4 end) over (partition by tab_1.col_1, tab_1.col_2)  AS u_shortages,
    COUNT (case when tab_3.col_8 = 'Unsrv Asset' then tab_3.col_10 end) over (partition by tab_1.col_1, tab_1.col_2)  AS u_affected,
    COUNT (DISTINCT (case when tab_3.col_8 = 'Unsrv Asset' and tab_3.col_7 = 1 then TRIM(tab_3.col_7) end)) over (partition by tab_1.col_1, tab_1.col_2)  AS unsrv_cat1,
    TO_CHAR(tab_1.col_11, 'MM/DD/YY') AS report_date
from tab_1
    left outer join tab_2
        on tab_2.col_1 = tab_1.col_1
    left outer join tab_3
        on     tab_3.col_9 = tab_1.col_1
        AND tab_3.col_6 = tab_1.col_2 
        AND tab_3.col_5 IS NULL;
    在仔细查看上面的查询以解释我做了什么后，我进一步修改了它。这是因为我按TO_CHAR（tab_1.col_11，'MM / DD / YY'）进行分组，但看到这不是相关子查询条件的一部分，因此必须更改（除非tab_1.col_1和col_2的组合是独一无二的）。 所以现在试着解释一下： 基本上，原始查询中发生的事情是，对于tab_1的每一行，您在tab_3上运行多个查询。因此，我将其更改为tab_3上的外部联接。由于我不知道数据，因此必须是外连接，因为相关子查询不会消除最终输出中的任何行，而内部连接可能是这样。我刚刚加入tab_3一次，因为所有子查询都使用相同的字段将tab_3连接回tab_1。我只是将每个子查询的特定逻辑移动到计数内的case语句中，这样如果不满足条件，则case将返回null（因此不会被计数）。这个最新版本使用聚合函数来使我的计算处于正确的级别（tab_1 col_1和col_2，这是原始子查询基于此加入的）。由于将子查询转换为外部联接可能会创建更多行（如果表之间没有一对一的匹配），我添加了distinct，以便只为tab_1中的每一行获取一行。 tab_1中每行的所有行都应相同。如果tab_1中已经存在重复项，则必须进行更深入的操作才能使记录数保持不变。 希望这是有道理的。如果您有任何疑问，请随时提出，我会尽力进一步解释。   ---------------------------更多解释 @shawno：有点，但我并没有把它想象成一个循环..使用你的简化例子，让我们假装你的表看起来像这样： 
    TAB_1:

col_1           col_2
--------        ---------
A               B
C               D

TAB_3:

col_9           col_6          col_4
--------        ---------      ---------
A               B              X
A               B              Y
A               B              Z
C               D              X
C               D              X
    使用子查询方法，您将查看tab_1的每一行，然后针对tab_3运行查询。所以你会这样做： 对于row col_1 = A，col_2 = B，在tab_3上运行select count（distinct（col_4）），其中col_9 = A且col_6 = B.这将返回值3，即子查询返回的值。 对于row col_1 = C，col_2 = D，在tab_3上运行select count（distinct（col_4）），其中col_9 = C且col_6 = D.这将返回值1，即子查询返回的值。 使用join方法，首先连接表，为您提供如下数据： 
    col_1           col_2          col_9           col_6          col_4
--------        ---------      --------        ---------      ---------
A               B              A               B              X
A               B              A               B              Y
A               B              A               B              Z
C               D              C               D              X
C               D              C               D              X
    所以现在你只需要根据那些数据进行查询，为col_1，col_2的每个值做一个Count（distinct（col_4））。如果你知道你的数据是什么样的，你可以创建一个更有效的查询，但这个想法保持不变。 希望这会让它更清晰一点！     
    2019-10-19 0 0
    分享

    埠仙俊

    调整核心子查询的最有效方法是在连接列时使用适当的索引。因此，我建议您在连接列上使用适当的索引。 Tab3上col9，col6的复合索引。 Tab1上col1和col2的复合索引。 Tab3.col5上的索引（如果它有大多数空值，否则不创建这个） tab3.col8
    create index <index_name> on tab3(upper(col8))
    上基于函数的索引 并更改查询以使用UPPER函数说..
    UPPER(TAB_3.COL_8) = 'PARTS SHORTAGE'
    ... 请记住在执行查询之前执行此语句，否则基于函数的索引无用。 
    alter session set query_rewrite_enabled=true
    （这使得优化者可以使用FBI）。 然后执行查询。如果查询正确使用所有索引，请检查解释计划...否则添加/ * + Index（）* / hints以通过查询正确使用索引。     
    2019-10-19 0 0
    分享
    为什么被折叠? 0 个回复被折叠

    要回复问题请先登录或注册

    或代码 OrCode.com 备案号：粤ICP备15020848号-1
    Escape time: 0.049365043640137