1Z0-053 V13.02 Q22-CSDN博客

22.An index called ORD_CUSTNAME_IX has been created on the CUSTNAME column in the ORDERS table using the following command:
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

未选定行

执行计划
----------------------------------------------------------
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

未选定行

执行计划
----------------------------------------------------------
Plan hash value: 1445457117

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