Oracle Database / SQL Performance & Memory
Check whether an index is valid and visible to the optimizer
A query may stop using an expected index because of its state, visibility, column order or changed selectivity. Inspect metadata before rebuilding.
Environment and inputs: Oracle Database 19c baseline · SQL*Plus-compatible SQL unless RMAN, DGMGRL, ADRCI or a host shell is specified. Check release, patch level, edition and container first.
Start with this diagnostic
Use the stated SQL, RMAN, DGMGRL or host shell and replace sample names with approved targets. DBA and V$ views need authorized catalog access in the correct container. Do not grant broad privileges for an example.
SELECT owner, index_name, status, visibility, uniqueness FROM all_indexes WHERE table_owner = 'APP_OWNER' AND table_name = 'APP_ORDERS';Read the result
Status and visibility describe potential availability, not whether the index is the best path. Partitioned index health needs partition-level inspection as well.
Resolve and verify
Compare indexed columns, predicates and the actual cursor plan. Repair an unusable component or query defect through a tested maintenance change.
Operational precautions
Do not rebuild every index because a query scans a table; a full scan can be the correct plan.