I have an APEX application that would benefit from some more indexes. I read about dbms_auto_index and ultimately decided to turn it on, flipping the config from REPORT-ONLY to IMPLEMENT per this documentation
https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_AUTO_INDEX.html#GUID-93A19936-453A-4C62-8DFB-FB52AC70C838
I saw a few handfuls of indexes marked visible and valid a few hours later, and (without any real performance metrics) felt like queries were returning faster. At some point over the weekend, some of those indexes were marked UNUSABLE for whatever reason, and started causing errors in my APEX app. Rebuilding these auto-indexes caused the queries to work once again, and I've since flipped the config back to REPORT-ONLY.
The errors we were getting were variations of:
ORA-01502: index '{{SCHEMA_NAME}}.SYS_AI_8j55h3717xzqq' or partition of such index is in unusable state
I attempted to ask for help in the APEX community Slack, and was told by Jayson Hanes (Oracle APEX PM) that this is a database issue, not an APEX issue. I think I understand what causes indexes to become UNUSABLE, but I don't understand what would cause a query to try using an UNUSABLE index, and how can I prevent that from happening in the future?
Using 19c (19.23.0.1.0) through OCI