问为什么我的查询在强制全表扫描后运行得更快？
EN

Database Administration用户

提问于 2012-10-30 01:23:58

回答 2查看 3.2K关注 0票数 3

我有一个MERGE语句，它花费了很长时间来执行select，因为它正在运行的表(SUB_DATA)相当大(阅读1.4TB，7亿行)。我的SELECT看起来像这样。

SELECT MIN(TRUNC(DATETIME,'DD'))    AS FIRST_ACTIVITY,
       MAX(TRUNC(DATETIME,'DD'))    AS LAST_ACTIVITY,
       NVL(PHONE_NUMBER, '-1')      AS PHONE_NUMBER,
       MAX(NVL(PHONE_ID, '-1')) KEEP (DENSE_RANK LAST ORDER BY DATETIME) AS LAST_PHONE_ID,
       MAX(TYPE_ID) KEEP (DENSE_RANK LAST ORDER BY DATETIME)             AS TYPE_ID
FROM SUB_DATA   AD,
     SUB_PROF   ICP
WHERE DATETIME BETWEEN minDate AND maxDate
AND   PROFILE_ID = ICP.ICP_SEQID
AND   REF_RT_ID in (13, 63) 
AND   DURATION > 3 
AND   PHONE_NUMBER like '123%' 
GROUP BY NVL(PHONE_NUMBER, '-1')

表上有两个INDEXES，一个在PHONE_NUMBER上，另一个在没有在这里使用的列上。

我很难让查询运行得更快，工作中一个更资深的人建议我强制使用FULL TABLE SCAN。我做了这件事，效果很好。

尽管它有效，但它违背了一般的概念，即全表扫描是不好的。

有人能解释一下为什么会这样吗？

oracle-11g-r2

performance

query-performance

回答 2

Database Administration用户

回答已采纳

发布于 2012-10-30 05:17:07

... --它违背了一般的概念，即全表扫描是不好的。

这有例外，就像所有的一般概念一样。一个完整的表扫描可以比索引扫描后跟rowid的表访问更便宜--有时要便宜得多。

记住：

除了表的块之外，还需要读取索引块。如果索引很大(表大小的相当大的百分比)，SGA (和关联锁)的压力可能是显而易见的。
如果索引的排序顺序与实际表中存储数据的方式不匹配，则完成查询所需的逻辑I/O的数量可能比完成整个表扫描所需的LIOs数多(可能更多)。(索引的聚类因子是可以用来估计这一点的指标之一。)

我发现本文中的数字Oracle聚类_因素提示很好地解释了这个问题。

本质上，如果通过索引进行扫描使数据块I/O请求跳转到整个表中，则总体成本将高于可以进行大规模顺序读取的成本。FTS更可能使用多块直接路径读取，完全绕过SGA，这也是潜在的好-没有“高速缓存冲击”，更少的锁存。

如果你有一个覆盖指数，这很可能会击败一个完整的表扫描所有时间。如果不是，这将取决于实际块(数据+索引)中需要处理的百分比(索引对该查询的选择性)，以及它们之间的“物理排序”情况。

至于为什么优化器在这里为您选择“错误”的路径:很难说。索引或表上的陈旧统计数据可能是一个问题，通常情况下，基于LIKE计算的估计值对于某些模式来说可能是不正确的，实例参数可能对索引有太大的偏爱，……如果这是唯一有问题的查询，并且您的统计数据是最新的，那么使用/*+ full */提示听起来并不太糟糕。

票数 5