Oracle 提示经常出现的错误写法

Oracle 提示经常出现的错误写法

概述：

在 Oracle 中，可以通过为语句添加Hint (提示) 来实现干预优化器优化的目的。Hint的使用类似于注释，

/*+hint1 hint2 / 是在 * 后面加上一个 + 表示Hint 多个Hint键使用一个或者多个空格进行分割，另外如果Oracle发现Hint 语法有错误，Oracle不会报错，只是把 / * */ 里的内容当做注释而已。所以此处就列举一些Hint出错的情况，仅供参考。

情况：

1. “*” 和“+” 之间不能有空格

   -- 建立测试表
   create table test_tab(
   	userid number(6) not null
    	,name varchar2(8)
   );
   
   create index ind_userid on test_tab(userid);
   
   insert into test_tab values(1,'aa');
   insert into test_tab values(2,'bb');
   insert into test_tab values(3,'cc');
   insert into test_tab values(4,'dd');
   insert into test_tab values(5,'ee');
   insert into test_tab values(6,'ff');
   insert into test_tab values(7,'gg');
   insert into test_tab values(8,'hh');
   commit;
   

   如果我们希望查询使用全表扫描可以使用FUll这个 Hint强制全表扫描，如果 * 和 + 之间有空格，如下：

   select /* + FULL(test_tab) */ * from test_tab where userid = 3;
   

   如果这样写 Oracle 只能是认为/* 和 */ 之间的是注释，语句无法生效，查询执行计划中 Hint 没有生效；

2. 如果表指定了别名，那么Hint 中也要使用别名。

   仍然使用如上的测试表，测试语句如下：

   select /*+ FULL(test_tab) */ * from test_tab a where a.userid = 3;
   

   如果不使用别名，那么执行计划中Hint不会生效。

3. 使用嵌套查询时，内层的别名在外侧是无法使用的，所以如果Hint中使用了内层的别名，也是无效的。

   select /*+ FULL(b IDX_USERID1) */ a.userid from test_tab a where a.userid in (select b.userid from test_tab b where a.userid = b.userid);
   

4. 多个提示之间分割不能使用‘ , ’ 必须使用空格

   如下查询语句希望是走全表扫描，结果发现Hint失效

   select /*+ append,FULL(a) */ * from test_tab a where a.userid = 3;
   

   Oracle 在解析 Hint的时候，是从左到右进行，如果遇到一个词是 Oracle 关键字或者说是保留字，将忽略这个词以及之后的所有词，如果遇到的一个词既不是关键词也不是Hint，就忽略该词。如果遇到的一个词是有效的Hint，那么会保留该Hint。

   ​

   Oracle的保留字，可以通过视图 V$reserved_word 来查询。发现连 “ , ”(逗号) 也是一个关键词。这样，当Oracle解析时遇到 “ , ” 时，就忽略了之后所有的Hint。

   另外，一些很常见的Hint形式，比如：

    /*+ parallet(t,8) */ , /*+ index(t,t_idx) */ 
   

   虽然当前没有问题，但是标准的写法应该是：

    /*+ parallet(t 8) */ , /*+ index(t t_idx) */ 
   

5. 生产环境慎用APPEND提示

   使用Append提示格式如下：

   select /*+ append */ * into b select * from a;
   

   其主要作用是insert时不去判断表中delete留下的空隙，在表最后进行插入，这样比普通的insert会快一些，同时安排彭也能减少日志的产生，但是由于append不去填充空隙，所以表空间会越来越多。同时append会把整张表锁住，别的用户的insert也会被阻塞。