使用许多左外连接和重表调整/重写sql查询

Question

我有四个五表，它们的大小非常大，并且使用下面的查询将它们留在外部。有什么办法可以改写它，以便提高性能？

SELECT t1.id, 
    MIN(t5.date) AS first_pri_date, 
    MIN(t3.date) AS first_pub_date, 
    MAX(t3.date) AS last_publ_date, 
    MIN(t2.date) AS first_exp_date 
FROM t1 
    LEFT JOIN t2 ON (t1.id = t2.id) 
    LEFT JOIN t3 ON (t3.id = t1.id) 
    LEFT JOIN t4 ON (t1.id = t4.id) 
    LEFT JOIN t5 ON (t5.p_id =t4.p_id) 
GROUP BY t1.id 
ORDER BY t1.id; 

记录计数：

• t1：6434323
• t2：6934562
• t3：9141420
• t4：11515192
• t5：3797768

大多数用于连接的列都有索引。解释计划中最耗时的部分是最后发生的t4的外连接。我只是想知道是否有任何方法来重写这个来改善性能。

Answer 1

假设id是t1主键，写入时您的查询可能（也可能没有，取决于你的Oracle的PGA的设置）更好地运行如下：

SELECT --+ leading(t1) use_hash(t2x,t3x,t45x) full(t1) no_push_pred(t2x) no_push_pred(t3x) no_push_pred(t45x) all_rows 
    t1.id, 
    t45x.first_pri_date, 
    t3.first_pub_date, 
    t3.last_publ_date, 
    t2.first_exp_date 
FROM t1 
    LEFT JOIN (
     SELECT t2.id, 
      MIN(t2.date) AS first_exp_date 
     FROM t2 
     GROUP BY t2.id 
    ) t2x 
     ON t2x.id = t1.id 
    LEFT JOIN (
     SELECT t3.id, 
      MIN(t3.date) AS first_pub_date, 
      MAX(t3.date) AS last_publ_date 
     FROM t3 
     GROUP BY t3.id 
    ) t3x 
     ON t3x.id = t1.id 
    LEFT JOIN (
     SELECT --+ leading(t5) use_hash(t4) 
      t4.id, 
      MIN(t5.date) AS first_pri_date 
     FROM t4 
      JOIN t5 ON t5.p_id = t4.p_id 
     GROUP BY t4.id 
    ) t45x 
     ON t45x.id = t1.id 
ORDER BY t1.id; 

此重写不会强加任何需要用于创建额外的，否则无用的索引。

Answer 2

我会说你的问题是你正在做很多左连接，并且最终的结果集在应用所有这些连接后变得太大。此外，索引不能以这种方式以最快的方式计算MIN或MAX。充分利用索引，您应该能够快速计算MIN或MAX。

我会写的查询，而像这样：

SELECT t1.id,  
(SELECT MIN(t5.date) FROM t5 JOIN t4 ON t5.p_id = t4.p_id WHERE t4.id = t1.id) AS first_pri_date, 
(SELECT MIN(date) FROM t3 WHERE t3.id = t1.id) AS first_pub_date, 
(SELECT MAX(date) FROM t3 WHERE t3.id = t1.id) AS last_publ_date, 
(SELECT MIN(date) FROM t2 WHERE t2.id = t1.id) AS first_exp_date 
FROM t1 
ORDER BY t1.id; 

为了更好的服务表现上(id, date)或(p_id, date)创建索引。 所以，你的指数会是这样的：

CREATE INDEX ix2 ON T2 (id,date); 
CREATE INDEX ix3 ON T3 (id,date); 
CREATE INDEX ix5 ON T5 (p_id,date); 
CREATE INDEX ix4 ON T4 (id); 

但仍然存在问题t4和t5之间的连接。 但如果是1：1个t1和t4之间的关系，也可能是更好写这样的事情在第二行：

(SELECT MIN(t5.date) FROM t5 WHERE t5.p_id = (SELECT p_id FROM t4 WHERE t4.id=t1.id)) AS first_pri_date, 

如果是1：N并且如果CROSS APPLY和OUTER APPLY您的Oracle版本的工作，你可以重写第二行是这样的：

(SELECT MIN(t5min.PartialMinimum) 
FROM t4 
CROSS APPLY 
(
    SELECT PartialMinimum = MIN(t5.date) 
    FROM t5 
    WHERE t5.p_id = t4.p_id 
) AS t5min 
WHERE t4.id = t1.id) 
AS first_pri_date 

所有这一切的目的是最小或最大的计算期间尽可能使用索引。 所以整个SELECT可以改写这样的：

SELECT t1.id,  
(SELECT MIN(t5min.PartialMinimum) 
FROM t4 
CROSS APPLY 
(
    SELECT TOP 1 PartialMinimum = date 
    FROM t5 
    WHERE t5.p_id = t4.p_id 
    ORDER BY 1 ASC 
) AS t5min 
WHERE t4.id = t1.id) AS first_pri_date, 
(SELECT TOP 1 date FROM t2 WHERE t2.id = t1.id ORDER BY 1 ASC) AS first_exp_date, 
(SELECT TOP 1 date FROM t3 WHERE t3.id = t1.id ORDER BY 1 ASC) AS first_pub_date, 
(SELECT TOP 1 date FROM t3 WHERE t3.id = t1.id ORDER BY 1 DESC) AS last_publ_date 
FROM t1 
ORDER BY 1; 

这是因为我相信最优化的方式如何从历史数据表中获取MIN或MAX。

问题是，使用MIN与很多非索引值使服务器加载所有数据到内存中，然后从非索引数据计算MIN或MAX，这需要很长时间，因为它对I/O操作。使用MIN或MAX时索引使用不当可能会导致出现这种情况，即将所有历史表数据缓存到内存中，而不需要MIN或MAX计算以外的任何其他数据。

如果没有查询的CROSS APPLY部分，服务器将需要从t5加载到所有单独日期的内存，并从整个加载结果集计算MAX。

标记正确索引表上的MIN函数的行为与TOP 1 ORDER BY类似，非常快。这样你就可以立即得到你的结果。

CROSS APPLY在Oracle 12C中可用，否则您可以使用pipelined functions。

检查这个SQL Fiddle，特别是执行计划的差异。