oracle 如何强制oracle使用索引范围扫描？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2473614/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to force oracle to use index range scan?
提问by wsb3383
I have a series of extremely similar queries that I run against a table of 1.4 billion records (with indexes), the only problem is that at least 10% of those queries take > 100x more time to execute than others.
我对包含 14 亿条记录（带索引）的表运行了一系列极其相似的查询，唯一的问题是这些查询中至少有 10% 的执行时间比其他查询多 100 倍以上。
I ran an explain plan and noticed that the for the fast queries (roughly 90%) Oracle is using an index range scan; on the slow ones, it's using a full index scan.
我运行了一个解释计划并注意到快速查询（大约 90%）Oracle 正在使用索引范围扫描；在较慢的情况下，它使用完整索引扫描。
Is there a way to force Oracle to do an index range scan?
有没有办法强制 Oracle 进行索引范围扫描？
回答by WW.
I suggest the following approach:-
我建议采用以下方法：-
- Get an explain plan on the slow statement
- Using an INDEX hint, get an explain plan on using the index
- 获取关于慢语句的解释计划
- 使用 INDEX 提示，获取使用索引的解释计划
You'll notice that the cost of the INDEX plan is greater. This is why Oracle is not choosing the index plan. The cost is Oracle's estimate based on the statistics it has and various assumptions.
您会注意到 INDEX 计划的成本更高。这就是 Oracle 不选择索引计划的原因。成本是 Oracle 根据其拥有的统计数据和各种假设估算的。
If the estimated cost of a plan is greater, but it actually runs quicker then the estimate is wrong. Your job is to figure out why the estimate is wrong and correct that. Then Oracle will choose the right plan for this statement and others on it's own.
如果计划的估计成本更高，但实际上运行得更快，那么估计是错误的。你的工作是找出估计错误的原因并纠正它。然后 Oracle 将自行为该语句和其他语句选择正确的计划。
To figure out why it's wrong, look at the number of expected rows in the plan. You will probably find one of these is an order of magnitude out. This might be due to non-uniformly distributed column values, old statistics, columns that corelate with each other etc.
要找出错误的原因，请查看计划中的预期行数。您可能会发现其中一个超出一个数量级。这可能是由于非均匀分布的列值、旧的统计信息、相互关联的列等。
To resolve this, you can get Oracle to collect better statistics and hint it with better starting assumptions. Then it will estimate accurate costs and come up with the fastest plan.
为了解决这个问题，您可以让 Oracle 收集更好的统计数据并用更好的起始假设来暗示它。然后它将估算准确的成本并提出最快的计划。
If you post more information I might be able to comment further.
如果您发布更多信息，我可能会进一步发表评论。
回答by wolφi
To "force" Oracle to use an index range scan, simply use an optimizer hint INDEX_RS_ASC. For example:
要“强制” 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
Whether this will make your query actually run faster depends on many factors like the selectivity of the indexed value or the physical order of the rows in your table. For instance, if you change the query to WHERE b BETWEEN 10 AND <xxx>, the following costs appear in the execution plans on my machine:
这是否会使您的查询实际运行得更快取决于许多因素，例如索引值的选择性或表中行的物理顺序。例如，如果您将查询更改为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
If you change the query very slightly to not only select the indexed column b, but also other, non-index columns, the costs change dramatically:
如果您非常轻微地更改查询以不仅选择索引列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
回答by APC
If you want to know why the optimizer takes the decisions it does you need to use the 10053 trace.
如果您想知道优化器为何做出决定，您需要使用 10053 跟踪。
SQL> alter session set events '10053 trace name context forever, level 1';
Then run explain plans for a sample fast query and a sample slow query. In the user dump directory you will get trace files detailing the decision trees which the CBO goes through. Somewhere in those files you will find the reasons why it chooses a full index scan over an index range scan.
然后为示例快速查询和示例慢查询运行解释计划。在用户转储目录中，您将获得详细说明 CBO 所经过的决策树的跟踪文件。在这些文件中的某处，您会找到它选择索引范围扫描而不是完整索引扫描的原因。
I'm not saying the trace files are an easy read. The best resource for understanding them is Wolfgang Breitling's excellent whitepaper "A look under the hood of CBO" (PDF)
我并不是说跟踪文件很容易阅读。了解它们的最佳资源是 Wolfgang Breitling 出色的白皮书“CBO 幕后的一瞥”(PDF)
回答by mermerkaya
you can use oracle sql hints. you can force to use specific index or exclude index check the documentation
您可以使用 oracle sql 提示。您可以强制使用特定索引或排除索引检查文档
http://psoug.org/reference/hints.htmlhttp://www.adp-gmbh.ch/ora/sql/hints/index.html
http://psoug.org/reference/hints.htmlhttp://www.adp-gmbh.ch/ora/sql/hints/index.html
like select /*+ index(scott.emp ix_emp) */ from scott.emp emp_alias
比如 select /*+ index(scott.emp ix_emp) */ from scott.emp emp_alias
回答by exiter2000
I have seen hint is ignored by Oracle.
我已经看到提示被 Oracle 忽略了。
Recently, our DBA uses "optimizer_index_cost_adj" and it utilized the index. This is Oracle parameter but you could use it as session level.
最近，我们的 DBA 使用了“optimizer_index_cost_adj”，它利用了索引。这是 Oracle 参数，但您可以将其用作会话级别。
100 is default value and we used 10 as the parameter.
100 是默认值，我们使用 10 作为参数。
