oracle不走hint原因1:依据hint会出现错误结果

比如根据索引取count(*),如果该列没有规定是非null的，那么根据索引取行数就会出现错误结果。如下： 1 创建一张包含null的表test，并在上面创建索引 SQL> create table test (a varchar2(10)); Table created. SQL> insert into test values ('a'); 1 row created. SQL>   insert into test values ('b'); 1 row created. SQL>   insert into test values (null); 1 row created. SQL>   insert into test values ('d'); 1 row created. SQL> commit; Commit complete. SQL> select * from test; A ---------- a b d SQL> create index ind_t on test(a); Index created. 2 查看执行计划，并添加hint后对比。可以看到，默认情况下，走的是全表，然后指定hint /*+index(test,ind_t)  */让查询走索引，但是没有用。改造sql，加上条件where a is not null，然后查看，发现走了索引。因为index不能存储空值，所以如果没有帅选条件直接走索引，获取的值是空值，这样结果就是有问题的。 SQL> set autot on SQL> select count(*) from test;   COUNT(*) ----------          4 Execution Plan ---------------------------------------------------------- Plan hash value: 1950795681 ------------------------------------------------------------------- | Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     | ------------------------------------------------------------------- |   0 | SELECT STATEMENT   |      |     1 |     2   (0)| 00:00:01 | |   1 |  SORT AGGREGATE    |      |     1 |            |          | |   2 |   TABLE ACCESS FULL| TEST |     4 |     2   (0)| 00:00:01 | ------------------------------------------------------------------- Note -----    - dynamic statistics used: dynamic sampling (level=2) Statistics ----------------------------------------------------------           5  recursive calls           0  db block gets           8  consistent gets           0  physical reads           0  redo size         550  bytes sent via SQL*Net to client         387  bytes received via SQL*Net from client           2  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)           1  rows processed SQL> l   1* select count(*) from test SQL> select /*+index(test,ind_t)  */count(*) from test   2  ;   COUNT(*) ----------          4 Execution Plan ---------------------------------------------------------- Plan hash value: 1950795681 ------------------------------------------------------------------- | Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     | ------------------------------------------------------------------- |   0 | SELECT STATEMENT   |      |     1 |     2   (0)| 00:00:01 | |   1 |  SORT AGGREGATE    |      |     1 |            |          | |   2 |   TABLE ACCESS FULL| TEST |     4 |     2   (0)| 00:00:01 | ------------------------------------------------------------------- Hint Report (identified by operation id / Query Block Name / Object Alias): Total hints for statement: 1 (U - Unused (1)) ---------------------------------------------------------------------------    2 -  SEL$1 / TEST@SEL$1          U -  index(test,ind_t) Note -----    - dynamic statistics used: dynamic sampling (level=2) Statistics ----------------------------------------------------------           4  recursive calls           0  db block gets           8  consistent gets           0  physical reads           0  redo size         550  bytes sent via SQL*Net to client         412  bytes received via SQL*Net from client           2  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)           1  rows processed SQL> select /*+index(test,ind_t)  */count(*) from test where a is not null;   COUNT(*) ----------          3 Execution Plan ---------------------------------------------------------- Plan hash value: 938330370 -------------------------------------------------------------------------- | Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------- |   0 | SELECT STATEMENT |       |     1 |     7 |     1   (0)| 00:00:01 | |   1 |  SORT AGGREGATE  |       |     1 |     7 |            |          | |*  2 |   INDEX FULL SCAN| IND_T |     3 |    21 |     1   (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    2 - filter("A" IS NOT NULL) Note -----    - dynamic statistics used: dynamic sampling (level=2) Statistics ----------------------------------------------------------           5  recursive calls           0  db block gets           6  consistent gets           0  physical reads           0  redo size         550  bytes sent via SQL*Net to client         431  bytes received via SQL*Net from client           2  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)           1  rows processed