SQL>CREATE INDEX ord_custname_ix ON orders(custname);
The ORDERS table is frequently queried using the CUSTNAME column in the WHERE clause. You want to check the impact on the performance of the queries if the index is not available. You do not want the index to be dropped or rebuilt to perform this test.
Which is the most efficient method of performing this task?
A. disabling the index
B. making the index invisible
C. making the index unusable
D. using the MONITORING USAGE clause for the index
Answer: B
题意是，如何不通过删除和重新创建索引的方法，测试索引不可用时，对查询的性能影响。
SQL> conn hr/hr
已连接。
SQL> select table_name,index_name,visibility,status from user_indexes;
TABLE_NAME INDEX_NAME VISIBILIT STATUS
------------------------------ ------------------------------ --------- --------
EMPLOYEES EMP_EMAIL_UK VISIBLE VALID
EMPLOYEES EMP_MANAGER_IX VISIBLE VALID
EMPLOYEES EMP_NAME_IX VISIBLE VALID
EMPLOYEES EMP_EMP_ID_PK VISIBLE VALID
EMPLOYEES EMP_DEPARTMENT_IX VISIBLE VALID
EMPLOYEES EMP_JOB_IX VISIBLE VALID
SQL> set autot on
SQL> select * from employees where email='abc';
未选定行
执行计划
----------------------------------------------------------
Plan hash value: 2693862462
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 69 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 69 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMP_EMAIL_UK | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMAIL"='abc')
SQL> alter index EMP_EMAIL_UK invisible;
索引已更改。
SQL> select table_name,index_name,visibility,status from user_indexes;
TABLE_NAME INDEX_NAME VISIBILIT STATUS
------------------------------ ------------------------------ --------- --------
EMPLOYEES EMP_EMAIL_UK INVISIBLE VALID
EMPLOYEES EMP_MANAGER_IX VISIBLE VALID
EMPLOYEES EMP_NAME_IX VISIBLE VALID
EMPLOYEES EMP_EMP_ID_PK VISIBLE VALID
EMPLOYEES EMP_DEPARTMENT_IX VISIBLE VALID
EMPLOYEES EMP_JOB_IX VISIBLE VALID
SQL> select * from employees where email='abc';
未选定行
执行计划
----------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 69 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 69 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMAIL"='abc')
SQL> alter index EMP_EMAIL_UK visible;
索引已更改。
SQL> alter index EMP_EMAIL_UK unusable;
索引已更改。
SQL> select table_name,index_name,visibility,status from user_indexes;
TABLE_NAME INDEX_NAME VISIBILIT STATUS
------------------------------ ------------------------------ --------- --------
EMPLOYEES EMP_EMAIL_UK VISIBLE UNUSABLE
EMPLOYEES EMP_MANAGER_IX VISIBLE VALID
EMPLOYEES EMP_NAME_IX VISIBLE VALID
EMPLOYEES EMP_EMP_ID_PK VISIBLE VALID
EMPLOYEES EMP_DEPARTMENT_IX VISIBLE VALID
EMPLOYEES EMP_JOB_IX VISIBLE VALID
SQL> select * from employees where email='abc';
未选定行
执行计划
----------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 69 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 69 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMAIL"='abc')
SQL> alter index EMP_EMAIL_UK rebuild online;
索引已更改。
SQL> select * from employees where email='abc';
未选定行
执行计划
----------------------------------------------------------
Plan hash value: 2693862462
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 69 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 69 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMP_EMAIL_UK | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMAIL"='abc')
从测试结果来看，invisible和unusable都能禁用索引，但INVISIBLE可以直接VISIBLE，而UNUSABLE没有USABLE，必须重建索引。
SQL> alter index EMP_EMAIL_UK unusable;
索引已更改。
SQL> alter index EMP_EMAIL_UK usable;
alter index EMP_EMAIL_UK usable
*
第 1 行出现错误:
ORA-02243: ALTER INDEX 或 ALTER MATERIALIZED VIEW 选项无效
SQL> alter index EMP_EMAIL_UK rebuild online;
索引已更改。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/81227/viewspace-2121525/ ，如需转载，请注明出处，否则将追究法律责任。
