Every index costs something even when no query uses it: each INSERT, UPDATE, and DELETE has to maintain it, it takes space, and it adds work to backups and statistics gathering. Oracle tracks index usage automatically and publishes the results in the DBA_INDEX_USAGE view, which makes it possible to find the indexes no query has touched. This guide runs a workload against a table with four indexes, reads DBA_INDEX_USAGE, compares it with the older MONITORING USAGE method, and removes an unused index safely, by making it invisible first.
What You Need
DBA_INDEX_USAGE and V$INDEX_USAGE_INFO need the SELECT_CATALOG_ROLE role or SELECT privileges on them. Index usage tracking is on by default; nothing has to be enabled.
Sample Table and Workload
A table of 100,000 orders gets four indexes besides its primary key. Two of them also get the older MONITORING USAGE switch, to compare the two methods later:
Example:
create table demo_orders (
order_id number constraint demo_orders_pk primary key,
customer_id number not null,
status varchar2(10) not null,
order_date date not null,
amount number(10,2) not null);
insert into demo_orders
select level, mod(level * 7, 5000) + 1,
decode(mod(level, 3), 0, 'NEW', 1, 'SHIPPED', 'DELIVERED'),
date '2025-01-01' + mod(level, 600), mod(level * 13, 500) + 10
from dual connect by level <= 100000;
commit;
create index demo_ord_cust_ix on demo_orders (customer_id);
create index demo_ord_date_ix on demo_orders (order_date);
create index demo_ord_amount_ix on demo_orders (amount);
create index demo_ord_status_ix on demo_orders (status);
exec dbms_stats.gather_table_stats(user, 'DEMO_ORDERS')
-- the older method, for comparison: usage monitoring on two of the indexes
alter index demo_ord_date_ix monitoring usage;
alter index demo_ord_amount_ix monitoring usage;Output:
Table DEMO_ORDERS created. 100,000 rows inserted. Commit complete. Index DEMO_ORD_CUST_IX created. Index DEMO_ORD_DATE_IX created. Index DEMO_ORD_AMOUNT_IX created. Index DEMO_ORD_STATUS_IX created. PL/SQL procedure successfully completed. Index DEMO_ORD_DATE_IX altered. Index DEMO_ORD_AMOUNT_IX altered.
The workload looks up orders by customer 1,000 times and by date 3 times. The indexes on AMOUNT and STATUS are never needed:
Example:
-- the application's queries: many lookups by customer, a few by date
declare
l_total number;
begin
for i in 1 .. 1000 loop
select sum(amount) into l_total from demo_orders where customer_id = i;
end loop;
for i in 1 .. 3 loop
select count(*) into l_total from demo_orders
where order_date = date '2026-03-01' + i;
end loop;
end;
/Output:
PL/SQL procedure successfully completed.
When the Usage Data Appears
Oracle collects usage in memory and writes it to DBA_INDEX_USAGE every 15 minutes. V$INDEX_USAGE_INFO shows how the data is collected and when it was last written:
Example:
select index_stats_collection_type as collection_type,
to_char(last_flush_time, 'YYYY-MM-DD HH24:MI:SS') as last_flush
from v$index_usage_info;Output:
COLLECTION_TYPE LAST_FLUSH
__________________ ______________________
1 2026-10-10 05:19:01The workload ran at 04:04, so the results were in the view after the next flush, at 04:19. COLLECTION_TYPE 1 means the default, sampled collection: Oracle records usage for a sample of executions, not for every one.
Read DBA_INDEX_USAGE
Example:
select name, total_access_count as accesses, total_exec_count as execs,
total_rows_returned as rows_returned,
to_char(last_used, 'YYYY-MM-DD HH24:MI') as last_used
from dba_index_usage
where owner = user
and name like 'DEMO_ORD%'
order by total_access_count desc;Output:
NAME ACCESSES EXECS ROWS_RETURNED LAST_USED ___________________ ___________ ________ ________________ ___________________ DEMO_ORD_CUST_IX 1434 1434 28680 2026-10-10 04:18
- An index appears in DBA_INDEX_USAGE once it has been used; an index with no row has no recorded use since tracking began.
- TOTAL_ACCESS_COUNT and TOTAL_EXEC_COUNT count the uses, TOTAL_ROWS_RETURNED the rows they returned, and LAST_USED the time of the latest use.
- With sampling the counts are approximate: the view shows 1,434 executions for the 1,000 lookups that ran.
- DEMO_ORD_DATE_IX is missing, although the three date queries used it. Its few executions were not in the sample.
List the Indexes Without Recorded Use
A left join from USER_INDEXES to DBA_INDEX_USAGE lists every index of the table, with the constraint it supports, if any:
Example:
-- indexes of the table with no recorded use, and whether a constraint needs them
select i.index_name,
nvl2(u.name, 'USED', 'NOT USED') as tracked,
(select max(c.constraint_type) from user_constraints c
where c.index_name = i.index_name) as constraint_type
from user_indexes i
left join dba_index_usage u on u.owner = user and u.name = i.index_name
where i.table_name = 'DEMO_ORDERS'
order by tracked, i.index_name;Output:
INDEX_NAME TRACKED CONSTRAINT_TYPE _____________________ ___________ __________________ DEMO_ORDERS_PK NOT USED P DEMO_ORD_AMOUNT_IX NOT USED DEMO_ORD_DATE_IX NOT USED DEMO_ORD_STATUS_IX NOT USED DEMO_ORD_CUST_IX USED
- DEMO_ORDERS_PK shows as not used, but it enforces the primary key (P): it must stay, whatever its usage. The same applies to unique keys, and to indexes on foreign key columns, which prevent locking problems when parent rows change.
- DEMO_ORD_AMOUNT_IX and DEMO_ORD_STATUS_IX are really unused.
- DEMO_ORD_DATE_IX is listed as not used, which is wrong: it is a rarely used index that sampling missed.
Confirm with MONITORING USAGE
ALTER INDEX ... MONITORING USAGE, the older method, records whether an index was used at all, at once and for every execution. USER_OBJECT_USAGE shows the result:
Example:
-- the older method: MONITORING USAGE records whether an index was used at all select index_name, monitoring, used, start_monitoring from user_object_usage where table_name = 'DEMO_ORDERS';
Output:
INDEX_NAME MONITORING USED START_MONITORING _____________________ _____________ _______ ______________________ DEMO_ORD_DATE_IX YES YES 10/10/2026 04:04:43 DEMO_ORD_AMOUNT_IX YES NO 10/10/2026 04:04:43
DEMO_ORD_DATE_IX was used, and DEMO_ORD_AMOUNT_IX was not. MONITORING USAGE gives no counts, only YES or NO, but it does not miss rare uses, which makes it a good check before removing an index that DBA_INDEX_USAGE reports as unused. ALTER INDEX ... NOMONITORING USAGE turns it off.
Remove an Unused Index Safely
An index that looks unused may still serve a monthly report or a year-end job. Make it invisible first: the optimizer stops using it, but DML still maintains it, so it can be made visible again at once if a query slows down:
Example:
-- before dropping, make the index invisible: -- the optimizer ignores it, but DML still maintains it alter index demo_ord_status_ix invisible; select index_name, visibility from user_indexes where table_name = 'DEMO_ORDERS' order by index_name;
Output:
Index DEMO_ORD_STATUS_IX altered. INDEX_NAME VISIBILITY _____________________ _____________ DEMO_ORDERS_PK VISIBLE DEMO_ORD_AMOUNT_IX VISIBLE DEMO_ORD_CUST_IX VISIBLE DEMO_ORD_DATE_IX VISIBLE DEMO_ORD_STATUS_IX INVISIBLE
After a period that covers the business cycle, with no problems, drop it:
Example:
-- after a period without problems drop index demo_ord_status_ix;
Output:
Index DEMO_ORD_STATUS_IX dropped.
Things to Know
- Judge over a long enough period: weeks or months, including month-end and other periodic jobs. An index without a row in DBA_INDEX_USAGE has not been used since tracking began, not necessarily never.
- Usage data reaches DBA_INDEX_USAGE only after a flush, up to 15 minutes later.
- Sampling can miss indexes that are used rarely, as here. Confirm with MONITORING USAGE, or make the index invisible and watch, before dropping it.
- Keep indexes that enforce primary keys and unique constraints, and indexes on foreign key columns, even if no query uses them.
Related Guides
Conclusion
DBA_INDEX_USAGE shows which indexes queries have used, with counts and the last use; indexes without a row are candidates for removal. Leave out those that enforce constraints, confirm rarely used ones with MONITORING USAGE because the default tracking is sampled, then make the index invisible before you drop it.
