Understanding UNUSABLE Index

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)

Shoumadip Das

Hi Folks, I am Shoumadip Das hailing from Kolkata, India welcoming you to explore my blog www.oraclemasterpiece.com. I am a self motivated and successful professional working in a leading IT Giant for more than 10 years.My area of expertise includes –

  • Oracle Database Programming (SQL & PL/SQL)
  • Perl Programming
  • Unix Programming

Read More

Leave a Reply

Your email address will not be published. Required fields are marked *