Oracle DBA

Index management has traditionally been one of the most time-consuming tasks for DBAs. Oracle 19c introduces Automatic Indexing, an intelligent feature that monitors SQL workloads, creates or drops indexes automatically, and continuously improves performance — all without manual intervention.

With Automatic Indexing, you can shift from reactive index management to a proactive, self-tuning database.

How Automatic Indexing Works

Automatic Indexing uses the following workflow:

1. Monitor: Oracle monitors SQL workload in the background.
2. Analyze: The optimizer identifies potential candidate indexes.
3. Test: New indexes are created as “invisible” and tested in the workload.
4. Validate: If performance improves, the index becomes “visible.”
5. Drop: Unused or ineffective indexes are automatically dropped.

Automatic Indexing Workflow

SQL Workload → Candidate Index Identification → Invisible Index Creation → Validation → Visible Index → Continuous Monitoring

By default, automatic indexing is off. You can enable it with the following command:

ALTER SYSTEM SET AUTO_INDEX_MODE = IMPLEMENT;

Modes available:

• OFF – Automatic indexing is disabled.
• REPORT – Generates recommendations but does not create indexes.
• IMPLEMENT – Actively creates, validates, and drops indexes.

Checking Automatic Indexing Status

SELECT parameter_name, parameter_value 
FROM dba_autotask_parameters 
WHERE client_name = 'auto_index';

-- or using DBA view
SELECT * FROM dba_auto_index_config;

Viewing Automatic Indexes

-- List all automatic indexes
SELECT index_name, status, auto, visibility 
FROM dba_indexes 
WHERE auto = 'YES';

-- Check automatic indexing tasks
SELECT task_id, task_type, status 
FROM dba_auto_index_task;

Example: Automatic Indexing in Action

CREATE TABLE sales (
  sale_id NUMBER,
  product_id NUMBER,
  sale_date DATE,
  amount NUMBER
);

-- Insert sample data
BEGIN
  FOR i IN 1..100000 LOOP
    INSERT INTO sales VALUES (i, MOD(i, 1000), SYSDATE - MOD(i, 365), DBMS_RANDOM.VALUE(10, 500));
  END LOOP;
  COMMIT;
END;
/

-- Run a query without index, this query might take several seconds without indexing

SET TIMING ON;
SELECT * FROM sales WHERE product_id = 500;

--Enable Automatic Indexing (REPORT mode first)
ALTER SYSTEM SET AUTO_INDEX_MODE = REPORT;

-- Oracle will start analyzing the workload and generate index recommendations.

-- Review Index Recommendations
SELECT * FROM dba_auto_index_report;

-- Switch to IMPLEMENT mode
ALTER SYSTEM SET AUTO_INDEX_MODE = IMPLEMENT;

-- Verify performance improvement, by re-running the query, the query should be significantly faster

SELECT * FROM sales WHERE product_id = 500;

Best Practices

• Start in REPORT mode in non-production environments to analyze recommendations before applying.
• Monitor automatic index tasks regularly using DBA_AUTO_INDEX_TASK.
• Combine Automatic Indexing with SQL Plan Management (SPM) for maximum stability.
• Exclude specific schemas if needed:

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'HR', 'OFF');

Managing Automatic Indexes

-- Disable Automatic Indexing:
ALTER SYSTEM SET AUTO_INDEX_MODE = OFF;

-- Drop all automatic indexes:
EXEC DBMS_AUTO_INDEX.DROP_AUTO_INDEXES;

-- Report-only mode for analysis:
ALTER SYSTEM SET AUTO_INDEX_MODE = REPORT;

Automatic Indexing in Oracle 19c is a game-changer for performance tuning. Instead of spending hours analyzing execution plans and creating indexes manually, DBAs can leverage Oracle’s built-in intelligence to handle the heavy lifting.

💡 Pro Tip: Start with REPORT mode, analyze the recommendations, then move to IMPLEMENT to let Oracle manage indexing autonomously.