In-depth understanding of Oracle indexes (9): Do not rely too much on hint

VeryMultiple application developers, especially tech danale, prefer to use hint to force Oracle CBO to select execution plans as per their own will
Rocky believes that this strategy is a double-edged sword to some extent.
Exercise caution when using hint to specify the index. If it is inappropriate or invalid, it may be superfluous and inefficient.

From official documents:

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.

If we use hint to specify an invalid index, the CBO will scan all indexes available in the table.
Select the smallest index or composite index of the cost instead of full table scan.

Test:

① Create a table with bitmap index and 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.

② Do not use 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

In this case, object_id may be null, so B _tree_rocky indexes are not used. Expected

③ Specify 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

Because object_id is null, B _tree index is not used.
However, even if B _tree_rocky index is not used, bitmap_rocky index should not be used. After all, the cost with this function will be larger than the full table scan.

④ Specify an invalid 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

Because when we use hint to specify B _tree_rocky index, the object_id value may be null (the column is not defined as not null)
Therefore, you cannot use this index, which is equivalent to an invalid index, scan other available indexes of the table, and use Bitmap indexes (null is not excluded for this type of index) instead of full table Scanning