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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31404823/viewspace-2717761/，如需轉載，請註明出處，否則將追究法律責任。