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未生效,语法格式没问题,这是什么错?

这个隐藏的问题,其实就是索引的内容,因为索引不包含空值,换句话说,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生效了,

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

1. 使用表名,

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

INDEX HINT无效,

2. 使用别名,

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

INDEX HINT生效,

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

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

显示未用索引,

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

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

扫码关注云+社区

领取腾讯云代金券