深入理解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 statsys@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)、而非全表掃描