INDEX和NO_INDEX提示的作用和使用方法

提示INDEX指导优化器，使用索引来访问相关表。在指定索引时，可以通过指定索引名称，也可以指定希望在哪个列上使用索引（即使用包含指定列的索引）。
提示NO_INDEX则正好相反，其指导优化器，避免使用相应的索引。

• 提示INDEX的使用语法如下：

其中：
tablespec表示目标表的名称或别名（当有别名时，必须用别名）。但不要加入表的属主（SCHEMA）名，哪怕在SQL中，明确写了属主，提示中也不能写。

indexspec表示要使用哪个索引，或者要在哪个列上使用索引。其语法图如下所示：

queryblock表示查询块。优化器在为一条SQL制定执行计划时，会将该SQL中涉及的子查询和视图，拆分为相应的查询块。分别为每一个查询块制定执计划。

• 提示NO_INDEX的使用语法如下图所示：

其相关内容与INDEX相同，这里不再赘述。需要强调的是，优化器只是不考虑使用提示中指定的索引（或指定列上的索引），未指定的索引，仍然会考虑，但最终是否使用，要看COST是否是最低的。

下面，我们通过实验，来加深对上述概念和用法的理解。

测试验证

• 提示INDEX
  首先，我们创建一个测试表和一个索引。

如果此时，我们发出一条内容为 ”select * from testtab where object_id>0"的查询，优化器会倾向于不使用Object_id列上的索引。原因是这个object_id>0这个条件，没有什么过滤性，会返回表上绝大部分，甚至全部的记录。如下图所示：

如上所示，优化器没有使用索引，而是使用全表扫描的访问方法。

但是，如果我们就是希望使用object_id列上的索引，可以通过以下两种提示方法来指导优化器来使用索引：

提示方法1： /*+ index(testtab ind_testtab_object_id) */

如上图所示，通过该提示，我们让优化器选择了使用索引的访问方法。“/*+ index(testtab ind_testtab_object_id) */”中的testtab是表名，ind_testtab_object_id是索引的名称。

提示方法2：/*+ index(testtab (object_id)) */

如上图所示，这种写法，也是可以让优化器来使用指定列上的索引的。但要注意的是，指定列名时，要用括号括起来。

下面，我们再来看看几种错误的HINT使用方法。

错误1：SQL中指定了表的别名，但HINT中，却引用了表的名称，而非别名，会导致提示无效。

如上图所示，SQL中已经为testtab表指定了别名t，但提示中，未引用别名，却使用了表名。导致提示失效。但如果使用别名，则提示会生效，如下图所示：

错误2：为表名指定属主（SCHEMA)名，会导致提示无效。

如上所示，无论SQL中是否带上了表的属主（SCHEMA)名，提示中均不要加。错误3：通过列名的方式来指定索引时，列名没有用括号括起来，会导致提示无效。

对于通过在提示中指定索引列来指定索引的方法，如果指定的列上有多个索引时，优化器会如何选择呢？优化器会选择可用索引中，COST最低的。下面，我们仍通过实验来理解。

首先，我们在object_id列上再创建一个包含它的索引：

目前，在object_id列上有两个索引，一个是在object_id列上的单列索引 ind_testtab_object_id，一个是在object_id列和owner列上的组合索引ind_testtab_object_id_owner。

现在我们通过提示/*+ index(testtab (object_id)) */来指导优化器，使用object_id列上有的索引，看看优化器会如何选择？

如上图所示，选择的是object_id列上的单列索引ind_testtab_object_id。

如果我们指定使用组合索引ind_testtab_object_id_owner，看看此时的COST值是多少？

如上图所示，我们可以看到使用组合索引时，其COST是1738，而使用OBJECT_ID列上的单列索引时，COST是1665，所以，当我们通过列名来指导优化器来选择索引时，优化器会选择成本最低的那一个。

• 提示no_index
  对于SQL"select * from testtab where object_id=10"，由于Ojbect_id列上无没重复值，所以，这时，最多只会返回1行，因此，走object_id列上的索引，是COST最低的。如下图所示：

下面我们通过no_index提示，来指定优化器，不使用特定的索引。

通过提示 /*+ no_index(testtab ind_testtab_object_id) */来避免使用索引ind_testtab_object_id，如下图所示：

如上图所示，我们可以看到优化器如我们所期望的那样，并没有使用索引ind_testtab_object_id，而是使用了另一个Object_id列和owner列上的组合索引。

知识总结

1、提示INDEX指导优化器，使用索引来访问相关表；提示NO_INDEX则正好相反，其指导优化器，避免使用相应的索引。
2、可以指定索引名称，也可以指定索引所在的列。当指定了多个索引，或者指定列上有多个索引可用时，会选择COST最低的使用。
3、当SQL中指定了表的别名，则提示中必须使用别名。
4、提示中不可以指定表的属主（SCHEMA)名。

参考文档

《Oracle® Database SQL Language Reference》