Oracle 19c introduced **Automatic Indexing** – a game changer for developers and DBAs dealing with slow queries.
How to Enable & Monitor
-- Enable Automatic Indexing
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');
-- Check status
SELECT * FROM DBA_AUTO_INDEX_CONFIG;
-- Monitor auto indexes created
SELECT owner, table_name, index_name, status
FROM dba_indexes
WHERE index_type = 'FUNCTION-BASED NORMAL'
AND index_name LIKE 'SYS_AI%';
Developer Benefit: No more manual index creation for ad-hoc queries. Oracle automatically creates, monitors, and drops unused indexes.
Oracle 19c | Automatic Indexing | Performance