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;

我正在从两个不同的表中搜索一些数据。 （Oracle 数据库 - wherefields 为两个表建立了索引） 上述查询的执行时间为 500 毫秒。 当我单独搜索表中的相同字段时 它们每个都在 20 毫秒内完成。

我可以在 TABLE1 中搜索我需要的数据 (+FIELD6) 然后使用 FIELD6 在表 2 中搜索其余内容。

我的问题是。为什么我加入表时速度慢了很多。 我做错了什么吗？

编辑：添加 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

Answer 1

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

因此，说每个查询需要 20ms 是没有意义的，您还必须说明有多少记录满足 TABLE1 中的 field8 条件，以及平均有多少记录满足 TABLE2.FIELD6 上的条件。

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

编辑：由于条件之间似乎存在 1:1 关系（并且查询随后返回 1 条记录），因此预计不会有 500 毫秒。在这种情况下，我真的会强调解释查询的必要性。如果您不熟悉它，您可以这样做：

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

select * from table(dbms_xplan.display);

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

Answer 2

比什么慢？ RDBMS 是为连接而设计的。如果您认为通过程序执行（使用游标 for 循环或类似操作逐行执行）可以获得更好的响应时间，那么 99.9% 的情况下您都错了。

我的猜测是，您正在比较返回所有行（甚至是前 500 行左右，如果使用 FIRST_ROWS）的连接与按程序（或手动）返回的少量记录的响应时间。苹果和橙子。

Answer 3

我看不出您的查询有任何问题，其他答案中给出的（好的）建议将帮助您详细了解发生的情况。

然而，从概念上讲，您必须记住每个表中数据的顺序，以及数据是分组还是分散。说得好“只需要20ms就能找到”，但是匹配两个数据集需要多长时间呢？

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

如果 Table1 上有一个索引，即 Field8，然后是 Field6，则所有“值”将集中在一起，然后按 Field6 排序。但是，如果您有 Field6 和 Field8 的索引，则您感兴趣的记录将按顺序排列，但分布在索引中。最后，如果您在这些字段上没有索引，则所有内容都将随机排序并分散。

根据这些类型的因素，RDBMS 可以通过多种方式完成查询。为了获得最佳性能，需要了解 RDBMS 需要做什么，然后为其提供索引以使其尽可能简单。

Answer 4

您应该使用报告工具或诸如 /*+ Gather_plan_statistics */ 之类的提示来了解查询计划是什么。在搜索引擎上查找此信息。

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

此外，如果优化器选择了错误的计划，您可能希望使用 DBMS_STATS.GATHER_SCHEMA_STATS 过程重新计算不具有代表性的统计信息。这是优化器选择不当的主要原因。

Answer 5

您可能应该分析表......完整的表访问和散列连接在您的情况下没有意义。

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