调优/重写带有许多左外部联接和重表的sql查询
我有四个-五个表，它们的大小都很大，它们使用下面的查询进行外部连接。是否有任何方法可以重写，以使性能得到改善？
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：6434323t2：6934562t3：9141420t4：11515192t5：3797768
大多数用于联接的列都有索引。在解释计划中，最常用的部分是与t4的外部连接，这是在最后发生的。我只想知道是否有任何方法来重写这个来提高性能。
回答 2
Stack Overflow用户
发布于 2014-11-02 20:48:55
假设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;此重写不要求创建额外的、但在其他方面无用的索引。
Stack Overflow用户
发布于 2014-11-02 21:23:59
我想说的是，您的问题是，您正在执行许多左联接，最后的结果集在应用所有这些联接之后变得太大了。此外，不能以这种方式使用索引来以最快的方式计算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之间的连接仍然存在问题。如果t1和t4之间存在1:1的关系，那么最好在第二行中写这样的东西：
(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，如果交叉应用程序和外部应用程序工作在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 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使服务器将所有数据加载到内存中，然后从非索引数据中计算MIN或MAX，这需要很长时间，因为它对I/O操作有很高的要求。在使用MIN或MAX时，索引使用不当可能导致出现这样的情况，即所有历史表数据都缓存在内存中，除了MIN或MAX计算之外，其他任何东西都不需要它。
如果没有交叉应用部分的查询，服务器将需要将来自t5的所有单个日期加载到内存中，并从整个加载的结果集中计算最大值。
标记这个MIN函数在适当的索引表上的行为类似于顶部1 ORDER BY，这是非常快的。这样你就可以立即得到你的结果。
交叉应用在Oracle12C中是可用的，否则您可以使用流水线函数。
检查此SQL Fiddle，特别是执行计划的差异。
https://stackoverflow.com/questions/26671290
复制相似问题
腾讯云开发者
