如何强制oracle使用索引范围扫描？

Question

我有一系列极其相似的查询，我对14亿条记录（带有索引）的表执行，唯一的问题是至少有10％的查询比其他查询执行时间多100倍以上。

我运行了一个解释计划，并注意到对于快速查询（大约90％），Oracle正在使用索引范围扫描;在慢速的情况下，它使用完整的索引扫描。

有没有办法强制Oracle进行索引范围扫描？

Answer 1

我建议以下办法： -

• 获取慢速声明
• 使用索引提示的解释计划，得到一个解释计划使用索引

你注意INDEX计划的成本更高。这就是为什么Oracle不选择索引计划。成本是Oracle根据其具有的统计数据和各种假设估计的。

如果计划的估计成本更大，但实际运行速度更快，那么估算是错误的。你的工作是找出为什么估计是错误的，并纠正这一点。然后，甲骨文将选择适合本声明的正确计划和其他人自己的计划。

要找出错误原因，请查看计划中预期行数。你可能会发现其中之一是一个数量级。这可能是由于不均匀分布的列值，旧统计信息，彼此互相牵连的列等。

要解决此问题，您可以让Oracle收集更好的统计信息并使用更好的开始假设来提示它。然后，它会估计准确的成本，并提出最快的计划。

如果您发布更多信息，我可以进一步评论。

Answer 2

我看到提示被Oracle忽略。

最近，我们的DBA使用“optimizer_index_cost_adj”并使用索引。 这是Oracle参数，但您可以将其用作会话级别。

100是默认值，我们使用10作为参数。

Answer 3

如果你想知道为什么优化器需要做出决定，你需要使用10053跟踪。

SQL> alter session set events '10053 trace name context forever, level 1'; 

然后运行示例快速查询和示例慢速查询的解释计划。在用户转储目录中，您将获得详细描述CBO经历的决策树的跟踪文件。在这些文件中的某处，您可以找到选择索引范围扫描的完整索引扫描的原因。

我不是说跟踪文件是一个容易阅读。了解它们的最佳资源是Wolfgang Breitling出色的白皮书"A look under the hood of CBO" (PDF)

Answer 4

您可以使用oracle sql提示。您可以强制使用特定指数或排除指数 查看文档

http://psoug.org/reference/hints.html http://www.adp-gmbh.ch/ora/sql/hints/index.html

像 选择/ * +索引（scott.emp ix_emp）*/from scott.emp emp_alias

Answer 5

要“强制”Oracle使用索引范围扫描，只需使用优化程序提示INDEX_RS_ASC。例如：

CREATE TABLE mytable (a NUMBER NOT NULL, b NUMBER NOT NULL, c CHAR(10)) NOLOGGING; 

INSERT /*+ APPEND */ INTO mytable(a,b,c) 
SELECT level, mod(level,100)+1, 'a' FROM dual CONNECT BY level <= 1E6; 

CREATE INDEX myindex_ba ON mytable(b, a); 
EXECUTE dbms_stats.gather_table_stats(NULL,'mytable'); 

SELECT /*+ FULL(m)   */ b FROM mytable m WHERE b=10; -- full table scan 
SELECT /*+ INDEX_RS_ASC(m) */ b FROM mytable m WHERE b=10; -- index range scan 
SELECT /*+ INDEX_FFS(m) */ b FROM mytable m WHERE b=10; -- index fast full scan 

这是否会使你的查询实际运行速度取决于许多因素，如索引值的选择性或表中的行的物理顺序。举例来说，如果你改变了查询WHERE b BETWEEN 10 AND <xxx>，下列费用出现在我的机器上执行计划：

b BETWEEN 10 AND 10  20  40  80 
FULL    749 750  751 752 
INDEX_RS_ASC  29 325  865 1943 
INDEX_FFS   597 598  599 601 

如果更改了查询非常轻微不但选择索引列b，还包括其他，非索引列，成本变化很大：

b BETWEEN 10 AND 10  20  40  80 
FULL    749 750  751 754 
INDEX_RS_ASC  3352 40540 108215 243563 
INDEX_FFS   3352 40540 108215 243563