Oracle不走索引hint,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