OCP-1Z0-052-V9 02-72题

最新推荐文章于 2026-02-01 13:18:42 发布

原创最新推荐文章于 2026-02-01 13:18:42 发布 · 199 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

探讨了Oracle数据库中不可用索引的状态，解释了其如何被创建、查询和维护。不可用索引不会被查询优化器使用，也不会在重建过程中影响性能。

分享一下我老师大神的人工智能教程！零基础，通俗易懂！http://blog.csdn.net/jiangjunshow

也欢迎大家转载本篇文章。分享知识，造福人民，实现我们中华民族伟大复兴！

72.Examine the following output:

SQL> SELECT index_name,status

FROM dba_indexes

WHERE status='UNUSABLE';

INDEX_NAME STATUS

EIND UNUSABLE

Which two statements about the above index are true? (Choose two.)

A. It is ignored by the query optimizer.

B. It is not used while the index is being rebuilt.

C. The index cannot be rebuilt, and has to be re-created.

D. The index is automatically rebuilt when used the next time.

Answer: AB

答案解析：

参考： http://docs.oracle.com/cd/E11882_01/server.112/e25494/indexes.htm#ADMIN12317

STATUS VARCHAR2(8) Indicates whether a nonpartitioned index is VALID or UNUSABLE

Creating an Unusable Index

When you create an index in the UNUSABLE state, it is ignored by the optimizer and is not maintained by DML. An unusable index must be rebuilt, or dropped and re-created, before it can be used.

If the index is partitioned, then all index partitions are marked UNUSABLE.

Beginning with Oracle Database 11g Release 2, the database does not create an index segment when creating an unusable index.

The following procedure illustrates how to create unusable indexes and query the database for details about the index.

To create an unusable index:

If necessary, create the table to be indexed.
For example, create a hash-partitioned table called hr.employees_part as follows:

sh@PROD> CONNECT hr

Enter password: **

Connected.

hr@PROD> CREATE TABLE employees_part

2    PARTITION BY HASH (employee_id) PARTITIONS 2

3    AS SELECT * FROM employees;

Table created.

hr@PROD> SELECT COUNT(*) FROM employees_part;

COUNT(*)

----------

       107
Create an index with the keyword UNUSABLE.
The following example creates a locally partitioned index on employees_part, naming the index partitions p1_i_emp_ename and p2_i_emp_ename, and makingp1_i_emp_ename unusable:

hr@PROD> CREATE INDEX i_emp_ename ON employees_part (employee_id)

2 LOCAL (PARTITION p1_i_emp_ename UNUSABLE, PARTITION p2_i_emp_ename);

Index created.
Optionally, verify that the index is unusable by querying the data dictionary.
The following example queries the status of index i_emp_ename and its two partitions, showing that only partition p2_i_emp_ename is unusable:

hr@PROD> SELECT INDEX_NAME AS "INDEX OR PARTITION NAME", STATUS

2 FROM   USER_INDEXES

3 WHERE INDEX_NAME = 'I_EMP_ENAME'

4 UNION ALL

5 SELECT PARTITION_NAME AS "INDEX OR PARTITION NAME", STATUS

6 FROM   USER_IND_PARTITIONS

7 WHERE PARTITION_NAME LIKE '%I_EMP_ENAME%';

INDEX OR PARTITION NAME        STATUS

------------------------------ --------

I_EMP_ENAME                    N/A

P1_I_EMP_ENAME                 UNUSABLE

P2_I_EMP_ENAME                 USABLE
Optionally, query the data dictionary to determine whether storage exists for the partitions.
For example, the following query shows that only index partition p2_i_emp_ename occupies a segment. Because you created p1_i_emp_ename as unusable, the database did not allocate a segment for it.

hr@PROD> COL PARTITION_NAME FORMAT a14

hr@PROD> COL SEG_CREATED FORMAT a11

hr@PROD> SELECT p.PARTITION_NAME, p.STATUS AS "PART_STATUS",

2         p.SEGMENT_CREATED AS "SEG_CREATED",

3 FROM   USER_IND_PARTITIONS p, USER_SEGMENTS s

4 WHERE s.SEGMENT_NAME = 'I_EMP_ENAME';

PARTITION_NAME PART_STA SEG_CREATED

-------------- -------- -----------

P2_I_EMP_ENAME USABLE   YES

P1_I_EMP_ENAME UNUSABLE NO