oracle 如何使用 LEFT JOIN 编写查询以获得更高的性能

How to write queries with LEFT JOIN for more performance

提问by user1881712

I try to understand how to correct write big queries with a lot of JOIN clause. Are this queries has the same performance?

我试图了解如何使用大量 JOIN 子句来纠正编写大查询。此查询是否具有相同的性能？

/*Sql 1*/
SELECT G.ID, T1.QUANTITY, T2.QUANTITY
 FROM GOODS G
LEFT JOIN
/*First subquery*/
(SELECT SUM(QUANTITY) QUANTITY, GOOD_ID
FROM MY_TABLE_1
GROUP BY GOOD_ID
) T1 ON G.ID = T1.GOOD_ID
LEFT JOIN
/*Second subquery*/
(SELECT SUM(QUANTITY) QUANTITY, GOOD_ID
GROUP BY GOOD_ID
FROM MY_TABLE_2
) T2 ON G.ID = T2.GOOD_ID
/*and so on....Next same subqueries*/
WHERE G.ID IN (1, 2, 3, 4);

In the next query same WHERE clause set in all joining subqueries and outside LEFT JOIN clause replace on INNER CLAUSE. Is it a good solution? Or oracle optimizer automatic make such things?

在下一个查询中，所有连接子查询中设置的相同 WHERE 子句和外部 LEFT JOIN 子句替换 INNER CLAUSE。这是一个好的解决方案吗？或者oracle优化器自动做出这样的事情？

/*Sql 2*/
SELECT G.ID, T1.QUANTITY, T2.QUANTITY
 FROM GOODS G
INNER JOIN
/*First subquery*/
(SELECT SUM(QUANTITY) QUANTITY, GOOD_ID
FROM GOODS G
LEFT JOIN MY_TABLE_1 M ON M.GOOD_ID = G.ID
WHERE G.ID IN (1, 2, 3, 4);
GROUP BY GOOD_ID
) T1 ON G.ID = T1.GOOD_ID
INNER JOIN
/*Second subquery*/
(SELECT SUM(QUANTITY) QUANTITY, GOOD_ID
FROM GOODS G
LEFT JOIN MY_TABLE_2 M ON M.GOOD_ID = G.ID
WHERE G.ID IN (1, 2, 3, 4);
GROUP BY GOOD_ID
) T2 ON G.ID = T2.GOOD_ID
/*and so on....Next same subqueries*/
WHERE G.ID IN (1, 2, 3, 4);

回答by Curt

Query performance will depend on a lot of things including:

查询性能将取决于很多因素，包括：

• The relative size of the tables involved
• The presence or absence of indices covering join columns and filter criteria
• The currency of the table statistics in the database
• The way that the query is written.

• 所涉及表格的相对大小
• 是否存在覆盖连接列和过滤条件的索引
• 数据库中表统计的币种
• 编写查询的方式。

It is absolutely untruethat OUTER joins are faster than INNER joins (although there might be some queries for which that is true). So it is not a great use of your time to try to convert queries to use more OUTER joins based on that theory.

OUTER joins 比 INNER joins 快是绝对不正确的（尽管可能有一些查询是这样的）。因此，尝试将查询转换为基于该理论使用更多 OUTER 连接并不是很好地利用您的时间。

There are entire books written on performance optimization, but some general rules include

有很多关于性能优化的书，但一些一般规则包括

• In your query, do table joins and WHERE criteria in most-selective to least-selective order (i.e. if you have a join that will reduce the size of the return set by 80%, put it up near the top of your query). The optimizer should theoretically be able to re-order the join these criteria based on table statistics, but I have found that giving it that extra help sometimes makes the difference.

• Learn to read SQL execution plans. They will tell you exactlyhow the database went about fulfilling your request, and you can learn whether it missed and index (or if the database is missing an index that might be useful for performance), when it was able to use ranges, direct index hits, or full table scans, and the like.

• It is sometimes useful, with very long-running queries, to divert some preliminary results into a temporary table, index that table appropriately, and then do the rest of your querying against that table. That is particularly true when you needto join or filter by some computed criteria that would force a table scan of a large table when you can find some strategy to pre-filter part of your result by more direct criteria.

• 在您的查询中，以从最有选择到最无选择的顺序执行表连接和 WHERE 条件（即，如果您的连接将返回集的大小减少 80%，请将其放在查询的顶部附近）。优化器理论上应该能够根据表统计信息对这些条件重新排序，但我发现给予它额外的帮助有时会有所不同。

• 学习阅读 SQL 执行计划。他们会确切地告诉您数据库是如何满足您的请求的，您可以了解它是否遗漏了索引（或者数据库是否遗漏了可能对性能有用的索引）、何时能够使用范围、直接索引命中，或全表扫描等。

• 对于运行时间很长的查询，将一些初步结果转移到临时表中，适当地索引该表，然后对该表执行其余查询，这有时很有用。当您需要通过某些计算条件进行联接或过滤时尤其如此，当您可以找到一些策略来通过更直接的条件预过滤部分结果时，这些条件会强制对大表进行表扫描。

回答by AngelWarrior

For best results, cut down your first table as fast as possible so that oracle doesn't create lots of extra rows to throw away. Then add that filter inside the subqueries, not after they complete. I'd try:

为获得最佳结果，请尽快减少您的第一个表，这样 oracle 就不会创建大量要丢弃的额外行。然后在子查询中添加该过滤器，而不是在它们完成之后。我会尝试：

SELECT G.ID, T1.QUANTITY, T2.QUANTITY
  FROM GOODS G
  JOIN DUAL ON G.ID IN (1, 2, 3, 4)
  LEFT JOIN (SELECT SUM(QUANTITY) AS QUANTITY, GOOD_ID
          FROM MY_TABLE_1 T
         GROUP BY GOOD_ID
        ) T1 ON T1.GOOD_ID = G.ID
  LEFT JOIN (SELECT SUM(QUANTITY) AS QUANTITY, GOOD_ID
          FROM MY_TABLE_2 T
         GROUP BY GOOD_ID
        ) T2 ON T2.GOOD_ID = G.ID

回答by Mangoose

Optimizer takes care of most of the things. You should do following Try to join on primary key If not possible then provide indexes for columns involved in join If possible Filter results using where clause to reduce rows in source table before join

优化器会处理大部分事情。您应该执行以下操作 尝试连接主键 如果不可能，则为连接中涉及的列提供索引 如果可能，在连接前使用 where 子句过滤结果以减少源表中的行