> For the complete documentation index, see [llms.txt](https://www.pranaypourkar.co.in/the-programmers-guide/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://www.pranaypourkar.co.in/the-programmers-guide/database/platform-specific-features/oracle/indexing-and-optimization/unusable-index.md).

# Unusable Index

## Problem Statement

During runtime of select query with indexed column search, the following Oracle error was encountered:

{% code overflow="wrap" %}

```
ORA-01502: index 'CUSTOM_LOG.PK_LOG_ENTRY_IDX' or partition of such index is in unusable state
```

{% endcode %}

This error means that the index or one of its partitions is marked as **UNUSABLE** and cannot be used by the Oracle optimizer. Queries that rely on it may fail or degrade in performance.

{% code overflow="wrap" %}

```log
2025-03-24T19:46:10.856Z WARN  --- o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 1502, SQLState: 72000
2025-03-24T19:46:10.856Z ERROR --- o.h.engine.jdbc.spi.SqlExceptionHelper : ORA-01502: index 'CUSTOM_LOG.PK_LOG_ENTRY_IDX' or partition of such index is in unusable state
```

{% endcode %}

## Root Cause

The index became **UNUSABLE** because **some partitions were dropped** earlier. This broke the structural integrity of the index, especially if it's partitioned or dependent on the dropped segments.

Common causes include:

* Dropping or truncating table partitions.
* Moving or altering the base table.
* Bulk inserts using `APPEND` hint.
* Manual index-related DDL failures.

## Resolution: Rebuild the Index

### Rebuild the Index

Since this was a full index and not just one partition, we attempted a full rebuild:

```sql
ALTER INDEX CUSTOM_LOG.PK_LOG_ENTRY_IDX REBUILD;
```

This command:

* Recreates the index.
* Marks its state as `VALID`.
* Makes it available again for queries.

### Verification Steps

If we don't have access to `DBA_INDEXES`, we use **user-level views**.

#### 1. Check Index Status (Non-partitioned or Global Index)

```sql
SELECT INDEX_NAME, STATUS 
FROM USER_INDEXES 
WHERE INDEX_NAME = 'PK_LOG_ENTRY_IDX';
```

* `VALID` = the index is usable.
* `UNUSABLE` = the index is still broken.

#### 2. If Partitioned, Check Each Partition

```sql
SELECT INDEX_NAME, PARTITION_NAME, STATUS 
FROM USER_IND_PARTITIONS 
WHERE INDEX_NAME = 'PK_LOG_ENTRY_IDX';
```

If any rows return with `STATUS = 'UNUSABLE'`, rebuild that specific partition:

```sql
ALTER INDEX CUSTOM_LOG.PK_LOG_ENTRY_IDX REBUILD PARTITION <partition_name>;
```

### Confirm Index Usage in Queries

We can validate that the index is now used by Oracle in actual query plans:

```sql
EXPLAIN PLAN FOR 
SELECT * FROM CUSTOM_LOG WHERE <indexed_column> = 'some_value';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
```

Look for terms like:

* `INDEX RANGE SCAN`
* `INDEX UNIQUE SCAN`


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://www.pranaypourkar.co.in/the-programmers-guide/database/platform-specific-features/oracle/indexing-and-optimization/unusable-index.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.