比如根据索引取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