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)
Good day! Would you mind if I share your blog with my twitter group? There’s a lot of folks that I think would really appreciate your content. Please let me know. Cheers
Thank you for you response.
Please share your twitter account so that i can start sharing my content, also requesting you to please ask your friend & colleagues to subscribe my blog site for all latest updates.
Thank You…
You could definitely see your enthusiasm in the work you write. The world hopes for more passionate writers like you who are not afraid to say how they believe. At all times follow your heart. brist på järn lleme.prizsewoman.com/map11.php
I am enjoying every second I am on this website.
נערות ליווי בנצרת עילית
Great post
I have read somewhere similar point of view and I totally agree with what you said. However, there are also some other things could be mentioned on this topic, but overall I like what you described.
In this website there is also a lot of interesting and useful information:
Great post
Nice site I’m glad I found it