SQL> select * from test;
ID
----------
1
2
3
4
5
ID
----------
1
2
3
4
5
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
转载请注明出处
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
转载请注明出处
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28389881/viewspace-1266136/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28389881/viewspace-1266136/