HINT无效的几个场景

碰巧看见了dba-oracle上的一个问题，算是基础性问题，

http://www.dba-oracle.com/t_oracle_index_hint_syntax.htm

Question: I added an index hint in my query, but the hint is being ignored. What is the correct syntax for an index hint and how do I force the index hint to be used in my query?

问题就是某些检索中，即使指定了INDEX HINT，可能无效。

Answer: Oracle index hint syntax is tricky because of the index hint syntax is incorrect it is treated as a comment and not implemented.

回答是，如果这个INDEX HINT的语法格式错误，就会只将他看作一个注释，不会应用这个HINT。

IN

INDEX HINT的标准用法是/*+ index(table_name, index_name) */，其中的","可以省略，换成空格。

举出了几个正确的使用，测试表和数据，

SQL> select * from customer;     ID A---------- ---------------     1 a     2 b     3 c     4 d     5 efrom customer; 
    ID A
---------- ---------------
     1 a
     2 b
     3 c
     4 d
     5 e

一开始，只是为这个id创建了索引，

SQL> create index pk_customer on customer(id);Index created.on customer(id);
Index created.

此时执行INDEX HINT的SQL，

SQL> select /*+ index(customer, pk_customer) */ * from customer;/*+ index(customer, pk_customer) */ * from customer;

发现这个HINT未生效，语法格式没问题，这是什么错？

640?wx_fmt=png

这个隐藏的问题，其实就是索引的内容，因为索引不包含空值，换句话说，id列可能为空，因此索引中就可能为空，CBO认为HINT会导致错误结果，那么这个HINT就会被忽略，所以选择了全表扫描。

解决方案就是设置这个id非空约束，为了测试，直接将其设置为主键，这藏着另一个知识点，之前在摩天轮中看见个问题，如何创建主键，这两种操作，都是正确的，区别就是第一种可以设置主键约束的名称，第二种会由系统自动创建一个名称，例如SYS_C000000，从标准的角度看，建议第一种，

SQL> alter table customer add constraint pk_customer primary key(id);Table altered.SQL> alter table customer add primary key (id);Table altered.
Table altered.

SQL> alter table customer add primary key (id);
Table altered.

回到主题上，此时执行INDEX HINT的SQL，

SQL> select /*+ index(customer, pk_customer) */ * from customer;/*+ index(customer, pk_customer) */ * from customer;

此时使用了索引全扫描，说明这个HINT生效了，

640?wx_fmt=png

如果检索的表设置了别名，INDEX HINT就需要使用别名，不能是这个表名，

1. 使用表名，

SQL> select /*+ index(customer, pk_customer) */ * from customer c;/*+ index(customer, pk_customer) */ * from customer c;

INDEX HINT无效，

640?wx_fmt=png

2. 使用别名，

SQL> select /*+ index(c, pk_customer) */ * from customer c;/*+ index(c, pk_customer) */ * from customer c;

INDEX HINT生效，

640?wx_fmt=png

如果多个HINT冲突了，HINT无效，

SQL> select /*+ full(customer) index(customer pk_customer) */ * from customer;/*+ full(customer) index(customer pk_customer) */ * from customer;

显示未用索引，

640?wx_fmt=png

HINT还是有很多知识点的，需要继续梳理，还是需要夯实基础啊，有些问题，一看就应该知道，说明原理通了，有迟疑，说明概念模糊，没看出问题，说明知识有缺口，继续学习了。