All apps dba Blog is the blog contributed by Doyensys Employees, With the view to share the knowledge out of their experience.
Doyensys Is a Fast Growing Oracle Technology Based Solutions Company Located in the US And Offshore Delivery Centers in India. With rich experience In Oracle E-Business, Oracle Database, Oracle Application Express (APEX), Oracle J Developer, Oracle OBIEE and E-Business Suite
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.
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_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 making p1_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.
(Optional) 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/AP1_I_EMP_ENAME UNUSABLEP2_I_EMP_ENAME USABLE
(Optional) 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",