在同一个表上的左连接-耗时太长

Question

这个查询需要6-7个小时，有什么方法可以提高性能吗？

SELECT  
    SUM(INV1.cur_book_bal),
    SUM(inv2.cur_book_bal),
    SUM(INV3.CUR_BOOK_BAL),
    SUM(INV4.CUR_BOOK_BAL)  
FROM
    table1 inv1 
LEFT JOIN
    table1 inv2 ON inv1.CIF_KEY = inv2.CIF_KEY
LEFT JOIN
    table1 inv3 ON inv1.CIF_KEY = inv3.CIF_KEY
LEFT JOIN
    table1 inv4 ON inv1.CIF_KEY = inv4.cif_key
                AND inv1.date = '30-JUN-2014' 
                AND inv2.date = '30-juN-2015' 
                AND INV3.dATE = '30-JUNE-2016' 
                AND INV4.DATE = '30-JUNE-17'

Answer 1

尝试在进行连接之前过滤数据，如下所示：

with TABLE_DATA as    
(    
select  CIF_KEY ,date, sum(cur_book_bal)cur_book_bal from table1  where Date in ('30-JUN-2014','30-juN-2015','30-JUNE-2016','30-JUNE-17')    
group by CIF_KEY,date    
)    
SELECT  INV1.cur_book_bal ,inv2.cur_book_bal,INV3.CUR_BOOK_BAL,INV4.CUR_BOOK_BAL      
from TABLE_DATA inv1     
left  join TABLE_DATA inv2    
on inv1.CIF_KEY= inv2.CIF_KEY    
left join TABLE_DATA inv3    
on inv1.CIF_KEY= inv3.CIF_KEY    
left  join TABLE_DATA inv4    
on inv1.CIF_KEY= inv4.cif_key    

where inv1.date = '30-JUN-2014' and inv2.date = '30-juN-2015' AND INV3.dATE = '30-JUNE-2016' AND INV4.DATE = '30-JUNE-17'

Answer 2

编写此查询的最佳方法是使用条件聚合。我很确定这会得到你想要的结果：

SELECT SUM(CASE WHEN date = '2014-06-30' THEN curr_book_bal else 0 end),
       SUM(CASE WHEN date = '2015-06-30' THEN curr_book_bal else 0 end),
       SUM(CASE WHEN date = '2016-06-30' THEN curr_book_bal else 0 end),
       SUM(CASE WHEN date = '2017-06-30' THEN curr_book_bal else 0 end),
FROM table1 inv1 
WHERE date in ('2014-06-30', '2015-06-30', '2016-06-30', '2017-06-30');

有了table1(date, curr_book_bal)上的索引，这应该既快又准确。

请注意标准日期格式的使用。在查询中编写日期常量时，强烈建议您这样做。