sql - 连接具有数百万行的表

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 会将每个表的连接行存储在相同的 block 中，因此它不必花费很长时间来检索连接的部分，但这可以有一个如果您还经常单独访问其中一个表，则不利。

上下文

孤立地查看查询并不总能揭示最佳答案 - 当可能是错误的事情时非常快速地做某事无济于事，因此请查看上下文，即一旦返回，您将如何处理 35000 行，是否添加了这些行仅在今天，是否有一个表具有可以替代使用的子集？