Disclaimer. The following is AI generated. Please be sure what to do or be informed by a DBA. These are guidelines.

Scope & rules (important)

  • Indexes are vendor-managed (EUCOP)

  • NOT allowed:

    • creating indexes

    • dropping indexes

    • changing index structure

  • Allowed (standard DBA maintenance):

    • updating statistics

    • index coalesce / reorganize / rebuild (when justified)

  • When in doubt → consult EUCOP

GENERIC FLOW (Oracle & SQL Server)

1. Start with the query (not the index)

  • Identify slow SQL

  • Check execution plan

  • Verify if index is used

2. Fix statistics FIRST

Most issues are caused by stale stats, not bad indexes

3. Check index usage

  • Used for reads? (good)

  • Only updated by writes? (potential overhead)

4. Check structure (fragmentation / bloat)

Only relevant for:

  • high delete activity

  • high churn tables

  • large indexes

5. Apply minimal maintenance

  • update stats

  • light maintenance first

  • rebuild only if justified

6. Validate

  • compare execution plan

  • check reads / performance improvement

ORACLE – GUIDELINES

Key point

Oracle does NOT use fixed % thresholds like SQL Server.

Decisions are based on:

  • deleted leaf rows

  • index growth vs table

  • actual performance impact

1. Update statistics (always first)

BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS(
ownname => ‘SCHEMA’,
cascade => TRUE
);
END;
/

2. Check execution plan

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, ‘ALLSTATS LAST’));

3. Monitor index usage

ALTER INDEX index_name MONITORING USAGE;

SELECT * FROM V$OBJECT_USAGE;

ALTER INDEX index_name NOMONITORING USAGE;

4. Analyze index structure

ANALYZE INDEX index_name VALIDATE STRUCTURE;

SELECT name, lf_rows, del_lf_rows
FROM index_stats;

👉 Guideline (no hard rule):

  • high DEL_LF_ROWS → consider maintenance

5. Maintenance

Preferred (light)

ALTER INDEX index_name COALESCE;

Heavy (only if justified)

ALTER INDEX index_name REBUILD ONLINE;

Use rebuild only when:

  • clear bloat / fragmentation

  • measurable performance issue

  • or EUCOP advises it

SQL SERVER – GUIDELINES

1. Update statistics

EXEC sp_updatestats;

2. Check index usage

SELECT *
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID();

3. Check fragmentation

SELECT *
FROM sys.dm_db_index_physical_stats(
DB_ID(), NULL, NULL, NULL, ‘LIMITED’
);

4. Maintenance rules

Fragmentation Action
< 5% Do nothing
5–30% REORGANIZE
> 30% REBUILD

5. Commands

Reorganize (light)

ALTER INDEX index_name ON table_name REORGANIZE;

Rebuild (heavy)

ALTER INDEX index_name ON table_name REBUILD WITH (ONLINE = ON);

STANDARD MAINTENANCE SCRIPTS

Oracle – scan ALL indexes (structure overview)

Yes, you can scan all indexes, but not in one single built-in view. You typically loop.

Generate analyze statements

SELECT ‘ANALYZE INDEX ‘ || owner || ‘.’ || index_name || ‘ VALIDATE STRUCTURE;’
FROM dba_indexes
WHERE owner = ‘SCHEMA’;

Then execute the output.

⚠️ Note:

  • INDEX_STATS only holds one index at a time

  • so this is typically used for targeted analysis, not full automation

Oracle – coalesce all indexes (safe baseline job)

BEGIN
FOR r IN (
SELECT owner, index_name
FROM dba_indexes
WHERE owner = ‘SCHEMA’
AND status = ‘VALID’
) LOOP
EXECUTE IMMEDIATE
‘ALTER INDEX ‘ || r.owner || ‘.’ || r.index_name || ‘ COALESCE’;
END LOOP;
END;
/

👉 Safe default:

  • low impact

  • no structure change

  • vendor-friendly

Oracle – rebuild all indexes (ONLY if approved)

BEGIN
FOR r IN (
SELECT owner, index_name
FROM dba_indexes
WHERE owner = ‘SCHEMA’
) LOOP
EXECUTE IMMEDIATE
‘ALTER INDEX ‘ || r.owner || ‘.’ || r.index_name || ‘ REBUILD ONLINE’;
END LOOP;
END;
/

⚠️ Only use:

  • during maintenance window

  • or with EUCOP approval

SQL Server – scan + maintain all indexes

Fragmentation overview

SELECT
OBJECT_NAME(i.object_id) AS table_name,
i.name AS index_name,
ps.avg_fragmentation_in_percent,
ps.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, ‘LIMITED’) ps
JOIN sys.indexes i
ON ps.object_id = i.object_id
AND ps.index_id = i.index_id
WHERE ps.page_count > 1000;

Automated maintenance (safe logic)

DECLARE @sql NVARCHAR(MAX) = ”;

SELECT @sql = @sql +
CASE
WHEN avg_fragmentation_in_percent > 30
THEN ‘ALTER INDEX [‘ + i.name + ‘] ON [‘ + OBJECT_NAME(i.object_id) + ‘] REBUILD WITH (ONLINE = ON);’ + CHAR(10)
WHEN avg_fragmentation_in_percent BETWEEN 5 AND 30
THEN ‘ALTER INDEX [‘ + i.name + ‘] ON [‘ + OBJECT_NAME(i.object_id) + ‘] REORGANIZE;’ + CHAR(10)
ELSE ”
END
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, ‘LIMITED’) ps
JOIN sys.indexes i
ON ps.object_id = i.object_id
AND ps.index_id = i.index_id
WHERE ps.page_count > 1000;

EXEC sp_executesql @sql;

Key takeaways (short)

  • Stats first, always

  • Oracle ≠ SQL Server → no % rules in Oracle

  • Coalesce/reorganize before rebuild

  • Rebuild only with reason

  • Vendor (EUCOP) owns index design

  • You maintain, not redesign