sql - 为什么当我使用临时表时，我在 oracle 中的查询需要更长的时间？

Question

在 ORACLE DB 中使用内部查询时，我遇到了一个特殊问题。我正在从一个有大量记录的表中获取数据。我正在使用的查询包含一个内部查询。

• 当我直接在内部查询中提供值时更快。
• 但是当我使用另一个(临时)表中的完全相同的值时通过内部查询或 JOIN，花费的时间太长。

下面是查询:

更快的性能

SELECT   assembly_item_id menuItemId,
         location_id restId,
         bill_sequence_id,
         bill_config_id
  FROM   zil_ibat_resolve_bmi_ai_max_v
 WHERE   assembly_item_id = 8321
         AND location_id IN (82, 85, 116, .........)    

---

在内部部分使用选择查询时性能低下

没有加入

SELECT   assembly_item_id menuItemId,
         location_id restId,
         bill_sequence_id,
         bill_config_id
  FROM   zil_ibat_resolve_bmi_ai_max_v
 WHERE   assembly_item_id = 8321
         AND location_id IN (SELECT   temp_id FROM global_temp_ids)

---

使用 JOIN

SELECT   assembly_item_id menuItemId, location_id restId, bill_sequence_id, bill_config_id 
FROM   zil_ibat_resolve_bmi_ai_max_v t1
join global_temp_ids t2
on t1.location_id = t2.temp_id
WHERE   t1.assembly_item_id = 8321

---

注意:zil_ibat_resolve_bmi_ai_max_v是一个 View 。

这个查询有什么问题？为什么我查询表而不是直接将 ID 放在内部部分时要花这么多时间？有替代方案吗？

解释计划

usedSelectQueryInInnerSection.png

usedJoin

enterNumbersInInnerQuery

Answer 1

由于 View 结果和临时表之间的 NESTED LOOP 连接，第二个和第三个查询很慢。将其更改为 HASH 连接(可能通过更好的优化器统计信息或 USE_HASH 提示)应该可以加快查询速度。

问题

这部分在执行计划的顶部:

NESTED LOOPS
    zil_ibat_resolve_bmi_ai_max_v
    global_temp_ids

类似于这个伪代码:

for each row of zil_ibat_resolve_bmi_ai_max_v
    search index of global_temp_ids

根据图像， View 的执行计划在查询之间不会改变，这部分必须相对较快。而且临时表的查找使用了唯一索引搜索，那也必须很快。但只一次 很快。我们可以从 Cardinality 1 中得知，Oracle 优化器认为它只会执行一次连接的内部部分。

嵌套循环在连接少量行时非常有用。在连接大量行时，HASH JOIN 的效果要好得多。

解决方案

有很多方法可以改变连接方式，这里先尝试两种:

1。收集统计信息。 更好的优化器统计信息将改进基数估计，这通常会改进执行计划。收集统计数据的方法有很多种，但通常默认设置是最好的。在这种情况下，可以通过运行如下过程来收集它们:exec dbms_stats.gather_schema_stats('SMART'); 对模式 ZILADMIN 和 XCBAIRAG 重复该操作。如果统计信息丢失或过时，调查默认统计信息收集作业未运行的原因也是一个好主意。

2。提示。通常应避免在生产代码中使用提示，但它们至少仍然有助于诊断问题。使用提示 SELECT/*+ USE_HASH(t1 t2) */... 运行查询，看看是否有所改进。如果可行，您可以保留提示或考虑使用其他形式的计划管理。例如，SQL Profile 可以更简洁的方式解决这个问题和其他问题。与其他开发人员或 DBA 核实，了解哪些类型的计划管理功能在您的系统中很常见。