HINT无效的几个场景

墨蓝 2021-11-22 04:36 161阅读 0赞

碰巧看见了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。

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][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][640_wx_fmt_png 1]

如果检索的表设置了别名，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][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][640_wx_fmt_png 2]

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

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

显示未用索引，

![640?wx\_fmt=png][640_wx_fmt_png 3]

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

[640_wx_fmt_png]: /images/20211122/5d3aef0bb26d497f801543e78f1637b4.png
[640_wx_fmt_png 1]: /images/20211122/f4ec8ed431c44d66a4defdb70df415a5.png
[640_wx_fmt_png 2]: /images/20211122/c7a83a93335b4a8c9872ddd27bedcb34.png
[640_wx_fmt_png 3]: /images/20211122/3ecb190110544f8892f64c76f082fb84.png