Oracle 11G - PK索引未用于Join，采用完全扫描

Question

我知道Oracle有时会“判断”执行全表扫描比执行索引扫描更好，然而，由于仍处于“学习阶段”，我只是想更好地理解oracle何时确定最佳路径。例如，我有一个简单的查询：

Select *
  FROM GLMV_JOURNAL_LOGS JLOG 
       INNER JOIN GLMV_Transact_Details TDTL 
          ON TDTL.TR_REF_NO = JLOG.TR_REF_NO 
         AND TDTL.SCAT_KEY = JLOG.Scat_key 
         AND TDTL.CASE_KEY = JLOG.CASE_KEY 
         AND TDTL.TR_CD = JLOG.TR_CD 
       INNER JOIN FUND_DESC FDDC 
          ON FDDC.FD_DESC_ID = TDTL.FD_DESC_ID  
       INNER JOIN FD_RATES FDRT 
          ON  FDRT.FDRT_KEY = TDTL.FDRT_KEY
       INNER JOIN BEN_TYPES BNTP 
          ON BNTP.BNTP_KEY = FDRT.BNTP_KEY 
 WHERE JLOG.JRNL_CD  = '0' 
   AND JLOG.SRC_CD = '2' 
   AND JLOG.MKEY_FD_NUM <> 0 
   AND NVL(JLOG.TMOV_KEY, -1) > 0 
   AND NVL(JLOG.ORIG_SCAT_KEY, 1) = 1 
   AND TDTL.STAT_CD <> '4' 
   AND NVL(TDTL.ORIG_SCAT_KEY, 1) = 1 

FD_RATES上的join是PK值上的join，我还在GLMV_Transact_Details上创建了相应的索引，因为我认为全表扫描将被阻止，然而，根据下面的解释计划，事实并非如此，即使在我执行了索引重建和聚集表统计之后，结果仍然是一样的：

​

​

现在，如果我进入查询并添加以下where子句：

AND  FDRT.FDRT_KEY = 100

索引当然会起作用，但我想我很好奇为什么在进行内连接时不起作用……有什么建议吗？

Answer 1

优化器估计在FD_RATES上进行全表扫描的成本为106。这个join的估计基数为416，另一个行源也是如此。如果我们将散列连接替换为嵌套循环，对每一行执行唯一索引(PK)查找，则循环的每次迭代的成本将至少为1，可能是2或3，我们认为将有416次迭代，因此这将是至少416次的成本，可能是该成本的两倍或三倍，这远远超过执行全表扫描的估计成本。

现在，估计可能是错误的。根据我的经验，主要要看的是计划中显示的基数。如果这些数据相当准确，那么Oracle很有可能选择了相当有效的连接顺序和访问路径--不一定是最有效的，但很接近。

如果您想尝试强制索引扫描以查看其执行情况，我相信您想要的提示是：

Select /*+ INDEX(fdrt) */ *
...

Answer 2

我将忽略您的示例，只尝试回答您的FTS与Index问题:)

通常，使用索引的原因是最小化为满足查询而需要读取的数据块。这在很大程度上取决于数据在表中的物理存储方式。选择多少百分比的行并不重要，重要的是使用索引而不是全表扫描可以避免多少块。例如，如果您有一个跨越3000个块的3000万行的表，并且您希望选择15,000行(或.5%)，那么您应该使用索引吗？好吧，如果所有15,000行都在最后200个块中，那么索引绝对是有意义的。但是，如果查询必须从每个数据块中获取5行才能获得15,000行，则全表扫描更有意义，因为无论如何都必须接触所有块。

Cary Millsap使用了一个很好的示例，将Oracle索引视为书中的索引。如果您有一本关于oracle的书，并且查找"Partition“，它可能会指出在相对较小的一组页面中出现了许多事件。因此，在这种情况下，使用索引是一个好主意。但是，如果您查找"Row"，它可能与"Partition“具有相同的出现次数，但它们将分布在大多数页面中。在这种情况下，最好是“全扫描”，只按顺序阅读每一页，而不是在页面和索引之间来回切换。

Oracle在索引中存储有关表中数据的物理存储方式的近似值，并在确定选择哪条路径时使用该信息。显然，优化器中的功能比这多得多(而且您可能会因为糟糕的统计数据或参数设置而毁了它)，但这应该是您入门的起点。

Answer 3

我没有足够的信息来回答完整的问题，但这里有一些备注：

，

• ，我不认为像你这样的5表连接很简单。可以省略表吗？
• 只有在表很大的情况下，全表扫描才是不好的。对于较小的表，与基于索引的访问没有区别。
• 您可以使用提示(嵌入到注释中)来指示优化器使用特定的访问路径。