连接具有数百万行的表

Question

我有下面的 sql，它返回 35k 行，运行大约需要 10 分钟。两个表都有数百万行。我该如何改进这个sql？

SELECT /*+ index(T_DIRECTORY X_DIR) */ 
                 DIRx.dir_id ,                   
                 base.id       
                 FROM T_DIRECTORY DIRx,  T_PERSON base 
                 WHERE 
                   DIRx.id = 26463
                   and DIRx.PERSONID=  base.PERSONID

'| Id  | Operation                    | Name           |'
'-------------------------------------------------------'
'|   0 | SELECT STATEMENT             |                |'
'|   1 |  NESTED LOOPS                |                |'
'|   2 |   TABLE ACCESS BY INDEX ROWID| T_DIRECTORY |'
'|   3 |    INDEX RANGE SCAN          | X_DIRECTORY |'
'|   4 |   TABLE ACCESS BY INDEX ROWID| T_PERSON      |'
'|   5 |    INDEX UNIQUE SCAN         | I_PERSON      |'

Answer 1

首先，确保在 where 子句 (DIRx.id) 中的列和正在连接的表 (base.personid) 上有合适的索引，并且对这些索引进行分析，以便它们代表表中的数据 - 如果不进行分析，Oracle当可以使用索引时，可能会进行全表扫描。

SELECT INDEX_NAME, 
       NUM_ROWS, 
       LAST_ANALYZED 
  FROM DBA_INDEXES 
 WHERE TABLE_NAME IN ('T_DIRECTORY','T_PERSON');

此外，您还通过使用提示强制它使用索引，但如果一个表比另一个表小，则哈希联接可能是更好的解决方案，因此也许尝试完全删除提示并看看它是否有帮助。

并行查询

当此 SQL 运行时，您是否有多个 CPU，并且没有其他任何运行 - 即，它是批处理过程的一部分，还是可以同时调用多次的在线过程的一部分。如果是批处理并且您有多个 CPU，请尝试并行查询，但如果它是在线程序，请不要这样做（例如，使用并行查询的报告将尝试使用所有可用的 CPU，如果同时运行多次，性能可能会变得更糟或者，如果每个 CPU 核心运行的并行线程数量超过 2 个，

则实际上，每 4 个线程的并行线程执行时间大约为一半。

如果这些表

始终以这种方式连接，您可以考虑。聚簇表（Oracle 将在其中将每个表的连接行存储在相同的块中，这样就不必花费很长时间来检索连接的部分，但如果您还经常单独访问其中一个表

上下文

， 那么这可能会产生不利影响。孤立的查询并不总是能揭示出最好的答案 - 当可能是错误的事情时，做一些非常快的事情并没有帮助，所以看看上下文，即一旦返回，你将如何处理 35000 行，它们是只在今天添加的吗？具有子集的表可以用那个代替吗？

Answer 2

“我必须对表进行非规范化”

在 OLTP 数据库中，非规范化始终是坏消息。它会加快某些查询的速度，但会减慢其他查询的速度。最好的情况是它会对 DML 操作造成性能影响，最坏的情况可能会导致数据损坏。这无疑使我们的应用程序变得更加复杂。

@trevorNorth 对于上下文提出了一个有效的观点。数据分布非常重要。 T_DIRECTORY 中有多少行与该 ID 匹配？该结果集中有多少行在 T_PERSON 中具有匹配行？这些实际值与解释计划中的基数匹配吗？如果没有，刷新数据库统计信息可能会让数据库发现更好的计划（没有 INDEX 提示）。但如果您的统计数据是最新的，那么您需要不同的解决方案。

比如……什么？

调优是一项艰巨的任务，因为需要考虑的事情太多，而且细节确实很重要。有些顾问除了修复其他人代码中的性能错误之外什么都不做，过着非常好的生活。如果调整很简单，他们就无法做到这一点。

在缺乏确凿事实的情况下，这里有一些猜测。解决此问题的替代方法：

1. 消除表读取，也许还可以通过构建复合索引来获得散列连接：
   T_DIRECTORY(ID、PERSONID、DIR_ID)
T_PERSON(PERSONID,ID)
2. 在语句上构建快速刷新的物化视图，并允许查询重写来满足查询。

Answer 3

我必须对表进行非规范化。