sql - 由于日期过滤器需要优化的Oracle Query使其运行缓慢

Question

我有一个需要优化的查询，需要帮助。该查询花费的时间无法接受。

这是查询的一般形式（从EARLIER更新）：

WITH

    SQ_Filter_Date AS
    (
        SELECT DISTINCT
            Business_Day AS Filter_Business_Day
        FROM
            Table_A
        WHERE
            Load_Date BETWEEN TO_DATE( '2019-11-01', 'yyyy-mm-dd' ) AND TO_DATE( '2019-12-01', 'yyyy-mm-dd' )
    ),

    SQ_Table_A_Results AS
    (
        SELECT *
        FROM
            Table_A sr INNER JOIN SQ_Filter_Date sfd ON ( sr.Business_Day = sfd.Filter_Business_Day )
    ),

    SQ_Final AS
    (
        SELECT *
        FROM
            SQ_Table_A_Results a 
            JOIN Table_B b ON ( a.A_Source_Key = b.B_Source_Key )
            JOIN Table_C c ON ( a.A_Type_Key = c.C_Type_Key )
            JOIN Table_D d ON ( a.A_Business_Type_Key = d.D_Business_Type_Key )
    )

SELECT *
FROM
    SQ_Final

Table_A具有作为主键的索引列。 Table_A也按Business_Day进行分区。因此，如果我们在Business_Day上进行过滤，则没有问题。问题是我们需要过滤一个称为Load_Date的未索引列。我已经检查过，由于我无法控制的事情，我们不允许向该列添加索引。

那么如何修改此查询以使其运行更快？

Answer 1

请查看是否有帮助。

WITH 
SQ_Filter_Date AS
(
    SELECT 
        A_Business_Type_Key, 
        MIN(LOAD_DATE) as MIN_LOAD_DATE,
        MAX(LOAD_DATE) as MAX_LOAD_DATE
    FROM
        Table_A
    GROUP BY
        A_Business_Type_Key

),
SQ_Table_A_Results AS
(
    SELECT A_Business_Type_Key
    FROM
        SQ_Filter_Date
    WHERE
    *-- << 
    --   have the date check here - 
    --   i dont whether this check is complete 
    --   so change it according to your needs 
    -- >>* 
    MIN_LOAD_DATE BETWEEN TO_DATE( '2019-11-01', 'yyyy-mm-dd' ) AND TO_DATE( '2019-12-01', 'yyyy-mm-dd' )
    or MAX_LOAD_DATE BETWEEN TO_DATE( '2019-11-01', 'yyyy-mm-dd' ) AND TO_DATE( '2019-12-01', 'yyyy-mm-dd' )

),
SQ_Final AS
(
    SELECT *
    FROM
        SQ_Table_A_Results a 
        JOIN Table_B b ON ( a.A_Source_Key = b.B_Source_Key )
        JOIN Table_C c ON ( a.A_Type_Key = c.C_Type_Key )
        JOIN Table_D d ON ( a.A_Business_Type_Key = d.D_Business_Type_Key )
)

SELECT * FROM SQ_Final

谢谢