分享一下我老师大神的人工智能教程!零基础,通俗易懂!http://blog.csdn.net/jiangjunshow
也欢迎大家转载本篇文章。分享知识,造福人民,实现我们中华民族伟大复兴!
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 hrEnter password: **Connected.hr@PROD> CREATE TABLE employees_part2 PARTITION BY HASH (employee_id) PARTITIONS 23 AS SELECT * FROM employees;Table created.hr@PROD> SELECT COUNT(*) FROM employees_part;COUNT(*)----------107Create an index with the keyword
UNUSABLE
.The following example creates a locally partitioned index on
employees_part
, naming the index partitionsp1_i_emp_ename
andp2_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 partitionp2_i_emp_ename
is unusable:hr@PROD> SELECT INDEX_NAME AS "INDEX OR PARTITION NAME", STATUS2 FROM USER_INDEXES3 WHERE INDEX_NAME = 'I_EMP_ENAME'4 UNION ALL5 SELECT PARTITION_NAME AS "INDEX OR PARTITION NAME", STATUS6 FROM USER_IND_PARTITIONS7 WHERE PARTITION_NAME LIKE '%I_EMP_ENAME%';INDEX OR PARTITION NAME STATUS------------------------------ --------I_EMP_ENAME N/AP1_I_EMP_ENAME UNUSABLEP2_I_EMP_ENAME USABLEOptionally, 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 createdp1_i_emp_ename
as unusable, the database did not allocate a segment for it.hr@PROD> COL PARTITION_NAME FORMAT a14hr@PROD> COL SEG_CREATED FORMAT a11hr@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 s4 WHERE s.SEGMENT_NAME = 'I_EMP_ENAME';PARTITION_NAME PART_STA SEG_CREATED-------------- -------- -----------P2_I_EMP_ENAME USABLE YESP1_I_EMP_ENAME UNUSABLE NO
给我老师的人工智能教程打call!http://blog.csdn.net/jiangjunshow
![这里写图片描述](https://i-blog.csdnimg.cn/blog_migrate/2e19ffb3303a490d20ea5d00dd397156.gif)