SQL 在 Oracle 中强制使用索引
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1838094/
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
Force index use in Oracle
提问by Vijay
I encountered this question in an interview and had no clue how to answer:
我在面试中遇到了这个问题，不知道如何回答：
There is a table which has a index on a column, and you query:
有一个表，在列上有一个索引，您查询：
select * from table_name where column_having_index="some value";
The query takes too long, and you find out that the index is not being used. If you think the performance of the query will be better using the index, how could you force the query to use the index?
查询花费的时间太长，您发现索引没有被使用。如果您认为使用索引查询的性能会更好，您如何强制查询使用索引？
回答by Rene
You can use optimizer hints
您可以使用优化器提示
select /*+ INDEX(table_name index_name) */ from tableetc...
select /*+ INDEX(table_name index_name) */ from table等等...
More on using optimizer hints: http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/hintsref.htm
有关使用优化器提示的更多信息：http: //download.oracle.com/docs/cd/B19306_01/server.102/b14211/hintsref.htm
回答by Guru
There could be many reasonsfor Index not being used. Even after you specify hints, there are chances Oracle optimizer thinks otherwise and decide not to use Index. You need to go through the EXPLAIN PLAN part and see what is the cost of the statement with INDEX and without INDEX.
不使用 Index 的原因可能有很多。即使在您指定了提示之后，Oracle 优化器也有可能会不这么想并决定不使用 Index。您需要通过 EXPLAIN PLAN 部分，看看有 INDEX 和没有 INDEX 的语句的成本是多少。
Assuming the Oracle uses CBO. Most often, if the optimizer thinks the cost is high with INDEX, even though you specify it in hints, the optimizer will ignore and continue for full table scan. Your first action should be checking DBA_INDEXES to know when the statistics are LAST_ANALYZED. If not analyzed, you can set table, index for analyze.
假设Oracle 使用 CBO。大多数情况下，如果优化器认为 INDEX 的成本很高，即使您在提示中指定它，优化器也会忽略并继续进行全表扫描。您的第一个操作应该是检查 DBA_INDEXES 以了解统计信息何时为 LAST_ANALYZED。如果没有分析，你可以设置表，索引分析。
begin
DBMS_STATS.GATHER_INDEX_STATS ( OWNNAME=>user
, INDNAME=>IndexName);
end;
For table.
为表。
begin
DBMS_STATS.GATHER_TABLE_STATS ( OWNNAME=>user
, TABNAME=>TableName);
end;
In extreme cases, you can try setting up the statisticson your own.
在极端情况下，您可以尝试自行设置统计信息。
回答by David Aldridge
If you think the performance of the query will be better using the index, how could you force the query to use the index?
如果您认为使用索引查询的性能会更好，您如何强制查询使用索引？
First you would of course verify that the index gave a better result for returning the complete data set, right?
首先，您当然会验证索引是否为返回完整数据集提供了更好的结果，对吗？
The index hint is the key here, but the more up to date way of specifying it is with the column naming method rather than the index naming method. In your case you would use:
索引提示是这里的关键，但最新的指定方法是使用列命名方法而不是索引命名方法。在您的情况下，您将使用：
select /*+ index(table_name (column_having_index)) */ *
from table_name
where column_having_index="some value";
In more complex cases you might ...
在更复杂的情况下，您可能...
select /*+ index(t (t.column_having_index)) */ *
from my_owner.table_name t,
...
where t.column_having_index="some value";
With regard to composite indexes, I'm not sure that you needto specify all columns, but it seems like a good idea. See the docs here http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements006.htm#autoId18on multiple index_specs and use of index_combine for multiple indexes, and here http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements006.htm#BABGFHCHfor the specification of multiple columns in the index_spec.
关于复合索引，我不确定您是否需要指定所有列，但这似乎是一个好主意。请参阅此处的文档http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements006.htm#autoId18关于多个 index_specs 和对多个索引的 index_combine 的使用，以及此处的http://docs.oracle。 com/cd/E11882_01/server.112/e26088/sql_elements006.htm#BABGFHCH用于index_spec中多列的规范。
回答by Egor Rogov
There is an appropriate index on column_having_index, and its use actually increase performance, but Oracle didn't use it...
You should gather statisticson your table to let optimizer see that index access can help. Using direct hint is not a good practice.
column_have_index 上有一个合适的索引，它的使用实际上提高了性能，但 Oracle 没有使用它...
你应该收集你的表的统计信息，让优化器看到索引访问可以提供帮助。使用直接提示不是一个好习惯。
