SQL语句执行速度异常缓慢

Question

select TABLE1.FIELD1, 
       TABLE1.FIELD2, 
       TABLE1.FIELD3, 
       TABLE1.FIELD4, 
       TABLE1.FIELD5, 
       TABLE2.FIELD6, 
       TABLE2.FIELD7
  from TABLE1,
       TABLE2
 where TABLE1.FIELD8 = 'value' 
   and TABLE2.FIELD6 = TABLE1.FIELD6;

我正在查找来自2个不同表的数据。 （Oracle数据库-其中字段都被索引） 以上查询需要500毫秒才能执行。 当我分别搜索相同字段的表时， 它们每个表都可以在不到20毫秒内完成。

我可以在TABLE1中搜索我需要的数据（+ FIELD6）， 然后使用FIELD6在TABLE2中搜索其余部分。

我的问题是，为什么连接表时速度会慢这么多？ 我是否做错了什么？

编辑：添加Oracle的解释计划。

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
| Id  | Operation                    |  Name                   | Rows  | Bytes | Cost  |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                         |  6318 |   586K|   620 |
|   1 |  HASH JOIN                   |                         |  6318 |   586K|   620 |
|   2 |   TABLE ACCESS BY INDEX ROWID| TABLE1                  |  6318 |   450K|     2 |
|   3 |    INDEX RANGE SCAN          | INDEX_TABLE1_FIELD8     |  2527 |       |     1 |
|   4 |   TABLE ACCESS FULL          | TABLE2                  |   430K|  9242K|   508 |
----------------------------------------------------------------------------------------

Note: cpu costing is off, 'PLAN_TABLE' is old version

- Stefanos Kargas

---

你的表中有多少条记录？你尝试过使用ANSI连接语法进行重写吗？ - diagonalbatman

你能展示一下Oracle对于"解释计划"的输出吗？ - Walter Mitty

TABLE1有600000个值，10个字段，TABLE2有5000个值，500个字段（请不要评论这个，我知道它很糟糕，这不是我的数据库）。 ANSI连接语法具有相同的结果。 - Stefanos Kargas

你应该避免使用隐式连接，而是使用 inner join table2 on (table1.field6 = table2.field6)，并且只在 Where 子句中留下过滤器。（这与你的问题无关，不过还是提一下）。 - Benoit

Answer 1

如果在TABLE1中有25条记录满足field8='value'条件，如果需要20ms来执行select ... from table2 where field6=???，那么500ms就是预期时间范围。

因此，仅仅说每个查询需要20ms是毫无意义的，你还需要说明有多少记录满足TABLE1的field8条件，以及平均有多少记录满足TABLE2.FIELD6的条件。

但为了消除所有猜测，你应该让Oracle解释查询，并在此处显示（或发布）解释计划以进行进一步分析。

编辑：由于标准之间似乎存在1:1的关系（并且查询随后返回1条记录），因此不应该期望500ms。在这种情况下，我真的强调需要解释查询。如果您不熟悉此操作，可以这样做：

explain plan for
   select .... <your entire select statement goes here>
;

select * from table(dbms_xplan.display);

然后发布结果。这将使我们更好地帮助您。

- René Nyffenegger

---

每个案例只有一条记录。 - Stefanos Kargas

Answer 2

比什么慢？RDBMS是为了连接而制定的。如果你认为通过逐行处理（使用游标循环或类似方法）能够获得更好的响应时间，99.9％的情况下你都是错的。

我猜你正在比较返回所有行（甚至是前500行，如果使用FIRST_ROWS）的连接和手动逐个返回少量记录的响应时间。这就像苹果和橙子一样不可比较。

- tbone

Answer 3

你应该分析一下表格...在你的情况下，完整的表格访问和哈希连接没有意义。

begin
  dbms_stats.gather_table_stats('YOURUSERNAME', 'TABLE1');
  dbms_stats.gather_table_stats('YOURUSERNAME', 'TABLE2');
end;

- Erich Kitzmueller

Answer 4

你应该知道查询计划是什么，可以使用报告工具或提示语，比如/*+ gather_plan_statistics */。在搜索引擎上查找这些信息。

当优化器连接两个表时，它可能会使用笛卡尔积、排序合并连接、哈希连接等方法...尝试使用SELECT /*+ USE_HASH(table1 table2) */ ...。

此外，如果优化器选择了错误的计划，你可能需要重新计算统计数据，例如使用DBMS_STATS.GATHER_SCHEMA_STATS过程来处理不具代表性的统计数据。这是糟糕的优化器选择的主要原因。

- Benoit

---

如果表像Stefanos所说的那样索引，我就明确不想使用哈希。只有当它们没有被完全索引时，我才会使用哈希。 - René Nyffenegger

@René Nyfenegger：是的，在这种情况下，可能更好的选择是使用/*+ INDEX */提示。 - Benoit

Answer 5

我看不出你的查询有什么问题，在其他答案中给出的（好的）建议将帮助你详细了解发生了什么。

然而，在概念上，你必须牢记每个表中数据的顺序以及数据是分组还是分散。虽然说“只需要20毫秒就可以找到”，但匹配这两个数据集需要多长时间呢？

如果两个数据集已知的顺序相同，RDBMS 可以相对快速地对齐它们。但是 RDBMS 只能从索引中知道这一点。

如果在 Table1 上有一个按 Field8 然后按 Field6 排序的索引，则所有“值”都将被聚集在一起，然后按 Field6 排序。然而，如果 Field6 索引优先于 Field8，则感兴趣的记录将被排序但分散在索引中。最后，如果这些字段没有建立索引，则所有内容将随机排序并分散在各处。

根据这些类型的因素，RDBMS 可以用几十种方式完成查询。为获得最佳性能，需要了解 RDBMS 需要做什么，然后提供索引让它尽可能轻松地完成。

- MatBailie

---

好的，问题解决了。我的索引有问题，但我真的不知道是什么问题。TABLE2中有两个索引，一个是FIELD6的，另一个是没有在这里提到的另一个字段的。我删除了它们并重新为FIELD6创建了一个索引。现在连接语句只需要15毫秒。甚至比其中一个单独语句还要快。我接受这个答案作为最接近的答案。谢谢大家。 - Stefanos Kargas

如果在table1.field8上添加索引，你能告诉我们查询的时间吗？只是好奇。 - ypercubeᵀᴹ

我只在商业领域使用过一次Oracle，而且索引损坏的频率非常高。重建它们（或删除并重新创建它们）通常会产生巨大的差异。因此，纯粹地推测，该索引可能已经损坏了。 - MatBailie

TABLE1.FIELD8 已经被索引了。如果没有它，速度就不会那么快。 - Stefanos Kargas