如何强制oracle使用索引范围扫描? [英] How to force oracle to use index range scan?

问题描述

我对一组14亿条记录(带有索引)的表运行了一系列极为相似的查询，唯一的问题是，这些查询中至少有10％的执行时间比其他查询多100倍.

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.

我运行了一个解释计划，并注意到对于快速查询(大约90％)，Oracle正在使用索引范围扫描.在速度较慢的服务器上，它使用的是全索引扫描.

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.

是否有一种方法可以强迫Oracle进行索引范围扫描?

Is there a way to force Oracle to do an index range scan?

推荐答案

要强制" Oracle使用索引范围扫描，只需使用优化程序提示 INDEX_RS_ASC .例如:

To "force" Oracle to use an index range scan, simply use an optimizer hint INDEX_RS_ASC. For example:

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

这是否会使查询实际运行更快取决于许多因素，例如索引值的选择性或表中行的物理顺序.例如，如果将查询更改为 10 AND< xxx> 之间的位置，则以下费用将出现在我的计算机上的执行计划中:

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:

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 ，还选择其他非索引列，则成本将发生巨大变化:

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 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

这篇关于如何强制oracle使用索引范围扫描?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！