深入理解Oracle索引(9)：不要过分依赖 HINT

很 多应用开发人员、特别是技术大拿、喜欢通过 HINT 强制Oracle CBO按自己的意志选择执行计划
Rocky 窃以为、这种策略在某种程度上其实是把双刃剑
使用 Hint 指定 Index 的时候需要慎重、如果不合适或者无效、可能画蛇添足、效率更低

摘自官方文档：

If the INDEX hint specifies no indexes, then the optimizer considers the cost of a scan on each available index on the table and 
then performs the index scan with the lowest cost. The database can also choose to scan multiple indexes and merge the results, 
if such an access path has the lowest cost. The optimizer does not consider a full table scan.

如果我们使用 HINT 指定了一个无效的 Index、CBO 会扫描表中所有可以使用的 Index
然后选择 cost 最小的 Index 或者复合索引、而不会选择全表扫描

测试：

① 创建一个表、含有 Bitmap Index 和 B-tree Index

sys@ORCL> create table rocky as select object_name,object_id from dba_objects;

Table created.

sys@ORCL> create index b_tree_rocky on rocky (object_id);

Index created.

sys@ORCL> create bitmap index bitmap_rocky on rocky (object_name);

Index created.

sys@ORCL> exec dbms_stats.gather_table_stats(USER,'ROCKY',cascade=>true);

PL/SQL procedure successfully completed.

② 不使用 HINT

sys@ORCL> set autot trace exp stat
sys@ORCL> select object_id from rocky;

50319 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1770888108

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       | 50319 |   245K|    55   (2)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| ROCKY | 50319 |   245K|    55   (2)| 00:00:01 |
---------------------------------------------------------------------------


Statistics
----------------------------------------------------------
        138  recursive calls
          0  db block gets
       3603  consistent gets
          0  physical reads
          0  redo size
     728242  bytes sent via SQL*Net to client
      37279  bytes received via SQL*Net from client
       3356  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
      50319  rows processed

这里因为 object_id　可能有 NULL 、所以不会使用 b_tree_rocky 索引、意料之中

③ 指定 B-tree Index

sys@ORCL> select /*+ INDEX(rocky b_tree_rocky)*/object_id from rocky;

50319 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 105445584

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              | 50319 |   245K|   782   (1)| 00:00:10 |
|   1 |  TABLE ACCESS BY INDEX ROWID | ROCKY        | 50319 |   245K|   782   (1)| 00:00:10 |
|   2 |   BITMAP CONVERSION TO ROWIDS|              |       |       |            |          |
|   3 |    BITMAP INDEX FULL SCAN    | BITMAP_ROCKY |       |       |            |          |
---------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      27546  consistent gets
          0  physical reads
          0  redo size
    2145896  bytes sent via SQL*Net to client
      37279  bytes received via SQL*Net from client
       3356  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      50319  rows processed

因为 object_id 有 NULL、所以不会走 b_tree Index
但就算不会使用b_tree_rocky index也不应该会使用BITMAP_rocky index、毕竟使用这个的cost会大于全表扫描

④ 指定一个 无效的 Index

sys@ORCL> select /*+ INDEX(rocky ooxx)*/object_id from rocky;

50319 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 105445584

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              | 50319 |   245K|   782   (1)| 00:00:10 |
|   1 |  TABLE ACCESS BY INDEX ROWID | ROCKY        | 50319 |   245K|   782   (1)| 00:00:10 |
|   2 |   BITMAP CONVERSION TO ROWIDS|              |       |       |            |          |
|   3 |    BITMAP INDEX FULL SCAN    | BITMAP_ROCKY |       |       |            |          |
---------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      27546  consistent gets
          0  physical reads
          0  redo size
    2145896  bytes sent via SQL*Net to client
      37279  bytes received via SQL*Net from client
       3356  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      50319  rows processed

因为我们 HINT 指定 b_tree_rocky index的时候、因为 object_id 可能有值为空(列没定义为not null)
所以不能使用该 index、从而也就是相当于一个无效的 index、从而扫描该表的其他可以使用的index、导致使用了位图索引(该类型index不排除null)、而非全表扫描