Understanding UNUSABLE Index
- Performance Tuning
- November 1, 2020
- 8 Comments
By default an index is USABLE.The Oracle optimizer will pick only USABLE index.
- Oracle indexes can go into a UNUSABLE state after maintenance operation on the table or if the index is marked as UNUSABLE with an ALTER INDEX statement.
- A direct path load against a table or partition will also leave its indexes UNUSABLE.
Suppose you have a batch processing job which runs daily from midnight till it gets complete to perform some operations on a table which has one index defined in it.If you are certain that not many users will query the table during night,then in that case,you can make the index UNUSABLE and let the job completes which will be faster because no index maintenance need to be done in the background.Post job completion, you can REBUILD the index to make the index USABLE.
In case of UNUSABLE index,the index segment gets dropped.
Queries and other operations against a table with UNUSABLE indexes will generate errors :-
ORA-01502: index ‘string.string’ or partition of such index is in unusable state
SQL> CREATE TABLE sales
2 (
3 id INT,
4 order_id INT,
5 product_code VARCHAR2(3),
6 amount INT,
7 date_of_sale DATE
8 );
Table created.
SQL> INSERT INTO sales
2 SELECT ROWNUM,FLOOR(DBMS_RANDOM.VALUE(90,9000)),
3 DBMS_RANDOM.STRING('U',3),FLOOR(DBMS_RANDOM.VALUE(90,9000)),
4 TRUNC(SYSDATE) - FLOOR(DBMS_RANDOM.VALUE(10,900))
5 FROM dual
6 CONNECT BY LEVEL <= 100000;
100000 rows created.
SQL> COMMIT;
Commit complete.
SQL> CREATE UNIQUE INDEX idx_sales_id ON sales(id);
Index created.
SQL> ANALYZE INDEX idx_sales_id VALIDATE STRUCTURE;
Index analyzed.
SQL> SELECT segment_name,segment_type,tablespace_name,blocks,extents,initial_extent,next_extent FROM user_segments WHERE segment_name = 'IDX_SALES_ID';
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BLOCKS EXTENTS INITIAL_EXTENT NEXT_EXTENT
---------------- ------------------ ------------------------------ ---------- ---------- ---
IDX_SALES_ID INDEX SYSTEM 256 17 65536 1048576
SQL> SET AUTOTRACE ON EXPLAIN
SQL> SELECT * FROM sales WHERE id = 1000;
ID ORDER_ID PRO AMOUNT DATE_OF_S
---------- ---------- --- ---------- ---------
1000 8794 ZQR 2282 22-JAN-18
Execution Plan
----------------------------------------------------------
Plan hash value: 1439439247
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 51 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| SALES | 1 | 51 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | IDX_SALES_ID | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=1000)
SQL> ALTER INDEX idx_sales_id UNUSABLE;
Index altered.
SQL> SELECT segment_name,segment_type,tablespace_name,blocks,extents,initial_extent,next_extent FROM user_segments WHERE segment_name = 'IDX_SALES_ID';
no rows selected
SQL> SELECT * FROM sales WHERE id = 1000;
ID ORDER_ID PRO AMOUNT DATE_OF_S
---------- ---------- --- ---------- ---------
1000 8794 ZQR 2282 22-JAN-18
Execution Plan
----------------------------------------------------------
Plan hash value: 781590677
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 337 | 17187 | 115 (1)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| SALES | 337 | 17187 | 115 (1)| 00:00:02 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=1000)
SQL> ALTER INDEX idx_sales_id REBUILD;
Index altered.
SQL> SELECT segment_name,segment_type,tablespace_name,blocks,extents,initial_extent,next_extent FROM user_segments WHERE segment_name = 'IDX_SALES_ID';
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BLOCKS EXTENTS INITIAL_EXTENT NEXT_EXTENT
---------------- ------------------ ------------------------------ ---------- ---------- ---
IDX_SALES_ID INDEX SYSTEM 256 17 65536 1048576
SQL> SELECT * FROM sales WHERE id = 1000;
ID ORDER_ID PRO AMOUNT DATE_OF_S
---------- ---------- --- ---------- ---------
1000 8794 ZQR 2282 22-JAN-18
Execution Plan
----------------------------------------------------------
Plan hash value: 1439439247
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 51 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| SALES | 1 | 51 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | IDX_SALES_ID | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=1000)