新增一個索引，而不影響其他應用

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
轉載請註明出處