1.将索引创建为不可见
SQL> create index test_idx1 on test(id) invisible;


Index created.

2.指示优化器考虑不可见索引
SQL> alter system set optimizer_use_invisible_indexes=true;


System altered.

3.查看执行计划
SQL> set autotrace trace explain
SQL> select id from test where id=2;


Execution Plan
----------------------------------------------------------
Plan hash value: 4100545912


------------------------------------------------------------------------------
| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           |     1 |    13 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| TEST_IDX1 |     1 |    13 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   1 - access("ID"=2)


Note
-----
   - dynamic sampling used for this statement (level=2)


4.不用的时候，让优化器忽略不可见索引
SQL> alter system set optimizer_use_invisible_indexes=false;


System altered.


SQL> select id from test where id=2;


Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020


--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    13 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |    13 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   1 - filter("ID"=2)


Note
-----
   - dynamic sampling used for this statement (level=2)

-----------------------------------
kingsql分享
hongzhuohui@kingsql.com
转载请注明出处