sql - 提高性能 : Very Slow Oracle SQL Join

Question

我是 SQL 查询的新手，我花了 3 个小时来获得加入 2 个查询的全部结果。经过研究，我专注于使用左连接并避免在 select 语句上使用子查询。但是它仍然非常慢。我没有足够了解 sql 的密友来解释问题所在或我应该采取的方法。我也是新来的，所以如果不允许这个问题，请通知我，我会立即将其删除。

这是查询的结构...第一个查询将获取成员详细信息。第二个查询将获取交易详细信息。关系是，一个产品有很多子计划，子计划有很多成员。一种产品也有许多交易，这些交易是在每个产品的基础上进行的。我需要显示所有交易并为每个成员复制每一行。我使用产品主键加入了查询。在加入之前，我测试了两个单独的查询，结果都很好。只需 1-2 秒，我就能得到结果。但加上这两者，我最终等待了 3 个小时。

SELECT
MPPFF.N_DX,
MPPFF.PM_A_P,
MPPFF.FEE1,
MPPFF.FEE2,
MPPFF.FEE3,
MPPFF.FEE4,
MPPFF.FEE11,
MPPFF.FEE12,
MPPFF.FEE5,
MPPFF.N_NO,
MPPFF.SETN_DX,
MPPFF.PRIME_NO,
MPPFF.SECN_NO,
MPPFF.COMM_A,
MPPFF.TYX_NO,
MPPFF.P_NAME,
MPPFF.B_BFX,
MPPFF.B_FM,
MPPFF.B_TO,
MPPFF.BB_NAME_P,
MPPFF.BB_NAME_S,
MPPFF.REVERSE_BFX,
MPPFF.TYX_REF_NO,
MPPFF.BB_NO_AX,
MPPFF.BB_NAME_AX,
MPPFF.DXC,
MPPFF.ST,
MPPFF.DAY,
MPPFF.CE_D_PRODUCT,
MPPFF.CE_H,
MPPFF.AS_C_E,
MPPFF.BCH,
MPPFF.RCPY_NO,
MPPFF.RE_BFX,
MPPFF.A_END,
MPPFF.PLACE,
MPPFF.MEMB_DX,
MPPFF.MBR_NO,
MPPFF.MBR_TR_BFX,
MPPFF.CE_D_TERM_CE,
MPPFF.MEMBER_AS,
MPPFF.C_USER,
MPPFF.C_BFX,
MPPFF.U_USER,
MPPFF.U_BFX

FROM (
        SELECT
        FF.N_DX,
        FF.PM_A_P,
        FF.FEE1,
        FF.FEE2,
        FF.FEE3,
        FF.FEE4,
        FF.FEE11,
        FF.FEE12,
        FF.FEE5,
        FF.N_NO,
        FF.SETN_DX,
        FF.PRIME_NO,
        FF.SECN_NO,
        FF.COMM_A,
        FF.TYX_NO,
        FF.P_NAME,
        FF.B_BFX,
        FF.B_FM,
        FF.B_TO,
        FF.BB_NAME_P,
        FF.BB_NAME_S,
        FF.REVERSE_BFX,
        FF.TYX_REF_NO,
        FF.BB_NO_AX,
        FF.BB_NAME_AX,
        FF.DXC,
        FF.ST,
        FF.DAY,
        FF.CE_D_PRODUCT,
        FF.CE_H,
        FF.AS_C_E,
        FF.RCPY_NO,
        FF.RE_BFX,
        FF.A_END,
        FF.BCH,
        MPP.MBR_NO,
        MPP.MBR_TR_BFX,
        MPP.CE_D_TERM_CE,
        MPP.C_USER,
        MPP.C_BFX,
        MPP.U_USER,
        MPP.U_BFX,
        MPP.PLACE,
        MPP.MEMBER_AS,
        MPP.TYX_DX,
        MPP.AS_DX,
        MPP.PRODUCT,
        MPP.POPL_DX,
        MPP.MEMB_DX,
        FF.TYX_DX

        FROM (
                SELECT
                MBR.MEMB_DX,
                MBR.MBR_NO,
                MBR.MBR_TR_BFX,
                MBR.CE_D_TERM_CE,
                MBR.C_USER,
                MBR.C_BFX,
                MBR.U_USER,
                MBR.U_BFX,
                MPP.PLACE,
                MPP.MEMBER_AS,
                MPP.TYX_DX,
                MPP.AS_DX,
                MPP.PRODUCT,
                MPP.POPL_DX

                FROM (
                        SELECT
                        MPP.PLACE,
                        MPP.MEMBER_AS,
                        MPP.TYX_DX,
                        MPP.AS_DX,
                        MPP.PRODUCT,
                        MPP.POPL_DX,
                        MMP.MEMB_DX

                        FROM(
                                SELECT
                                MPP.PLACE,
                                MPP.TYX_AS_DXC MEMBER_AS,
                                MPP.TYX_DX,
                                MPP.AS_DX,
                                MPP.POPL_DX,
                                RPT.PRODUCT

                                FROM
                                TABLE1 MPP

                                LEFT JOIN (
                                        SELECT
                                        SUBSTR(CE_D_PRODUCT,9) PRODUCT,
                                        AS_DX
                                        FROM
                                        TABLE6 RPT,
                                        TABLE7 PP
                                        WHERE
                                        PP.PRTY_DX = RPT.PRTY_DX
                                ) RPT
                                ON  MPP.AS_DX = RPT.AS_DX

                        ) MPP

                        LEFT JOIN (
                                SELECT
                                POPL_DX,
                                MEMB_DX
                                FROM
                                TABLE4
                        )MMP
                        ON MPP.POPL_DX=MMP.POPL_DX

                ) MPP,
                (
                        SELECT
                        MBR.MEMB_DX,
                        MBR.MBR_NO,
                        MBR.TERM_BFX MBR_TR_BFX,
                        MBR.CE_D_TERM_CE,
                        MBR.C_USER,
                        MBR.C_BFX,
                        MBR.U_USER,
                        MBR.U_BFX

                        FROM
                        TABLE8 MBR
                ) MBR
                WHERE
                MPP.MEMB_DX = MBR.MEMB_DX
        ) MPP
        INNER JOIN
        (
                SELECT
                FF.N_DX,
                ROUND(CB.FEE5 * FF.RATE,2) PM_A_P,
                CB.FEE1,
                CB.FEE2,
                CB.FEE3,
                CB.FEE4,
                CB.FEE11,
                CB.FEE12,
                CB.FEE5,
                FF.N_NO,
                FF.SETN_DX,
                FF.PRIME_NO,
                FF.SECN_NO,
                FF.COMM_A,
                FF.TYX_NO,
                FF.P_NAME_1||', '||FF.P_NAME_2||' '||FF.P_NAME_3 P_NAME,
                FF.B_BFX,
                FF.B_FM,
                FF.B_TO,
                FF.BB_NAME_1_P||', '||FF.BB_NAME_2_P BB_NAME_P,
                FF.BB_NAME_1_S||', '||FF.BB_NAME_2_S BB_NAME_S,
                CB.REVERSE_BFX,
                FF.TYX_REF_NO,
                FF.BB_NO_AX,
                FF.BB_NAME_1_AX||' '|| FF.BB_NAME_2_AX BB_NAME_AX,
                CASE 
                        WHEN FF.CE_D_ST IN ('A', 'B', 'C') THEN 'AC'
                        WHEN FF.DAY >1 THEN 'NEW'
                        ELSE 'AB'
                END DXC,
                FF.CE_D_ST ST,
                FF.DAY,
                FF.CE_D_PRODUCT,
                FF.CE_D_COMP CE_H,
                FF.AS_C AS_C_E,
                FF.RCPY_NO,
                FF.RE_BFX,
                ROUND(CB.A_S,2) A_END,
                FF.TYX_DX,
                MP.BCH

                FROM
                TABLE2 CB,
                TABLE3 FF

                LEFT JOIN (
                        SELECT
                        SUBSTR(CE_D_BCH_O,13) BCH,
                        TYX_DX
                        FROM
                        TABLE5 MP
                )MP
                ON MP.TYX_DX = FF.TYX_DX

                WHERE
                FF.SETN_DX = CB.SETN_DX AND
                EXTRACT( YEAR FROM FF.EFF_BFX) >=2013
        ) FF    
        ON MPP.TYX_DX = FF.TYX_DX

)MPPFF
;

Answer 1

使用 ROWNUM 防止优化器转换降低性能。

您遇到了一个常见问题 - 两个查询单独运行速度很快，但放在一起时运行速度很慢。 Oracle 不必按照编写的顺序运行查询。它可以合并 View 、推送谓词，并且通常完全重写查询以不同的顺序运行。通常这是一件好事，因为您不必担心连接表的物理顺序。但有时 Oracle 应用了错误的转换，结果是灾难性的。

有两种方法可以解决这些问题。

1. 查看表结构、语句、执行计划、SQL 监控或跟踪、统计信息等。尝试找出哪个操作慢，为什么(使用基数作为指导)，然后尝试修复它。此过程可能很容易花费数小时，甚至数天，但这是最好的学习方式。
2. 用一个简单的技巧阻止优化器组合查询。有几种方法可以做到这一点，但根据我的经验，最简单的方法是将伪列 ROWNUM 添加到任何您不想转换的内联 View 中。 ROWNUM 是一个特殊的列，它告诉 Oracle“这个查询 block 必须以特定的方式返回，不要对它做任何事情”。

改变这个:

--This is slow:
select ...
from
(
    --This is fast:
    select ...
) inline_view1
join
(
    --This is fast:
    select ...
) inline_view2
    on ...

为此:

--Now this is fast.
select ...
from
(
    --This is fast:
    select rownum /*add rownum to prevent slow transformations*/, ...
) inline_view1
join
(
    --This is fast:
    select rownum /*add rownum to prevent slow transformations*/, ...
) inline_view2
    on ...

在您的代码中，我相信要修改的两个内联 View 将是最外层的 MPP 和 FF。

---

附带说明一下，我不同意其他一些评论和答案。

• CTE 在这里无济于事，因为没有任何表格被使用两次。
• 您并不总是需要了解有关查询的一百万个详细信息才能对其进行优化。除非您有时间并且想提高自己的技能。
• 我认为您的整体查询结构很好。您正走在构建出色 SQL 语句的正确道路上。内联 View 是编写 SQL 的关键——构建小的代码单元，以简单的步骤组合它们，重复。将所有表放在一个大规模连接中是意大利面条代码的秘诀。尽管我同意其他人的意见，您应该避免使用老式的连接语法。查询将真正受益于一些注释和更有意义的名称。不要害怕将所有选择列表项放在一行中。拥有 500 列的行并不理想，但您希望专注于连接，而不是简单的列列表。