Oracle Indexes   «Prev  Next»

Lesson 6 Altering an index
Objective Modify attributes of an index in Oracle 23ai

Altering Index Attributes

In relational theory, a table (a relation) is a logical construct—rows, columns, keys, and constraints. An index is different: it is a physical access structure that exists to improve performance. That separation is important because it explains why Oracle lets you alter index attributes without changing what the table “means” logically.

In the prior lesson you created indexes with CREATE INDEX. In this lesson, you modify an existing index with ALTER INDEX. The scope is narrower than ALTER TABLE because indexes have a focused purpose: to provide one or more efficient access paths to table data.

What You Can and Cannot Change

  • You can change attributes and operational state—for example name, tablespace placement, logging mode, degree of parallelism, visibility, partition maintenance, and rebuild behavior.
  • You cannot change the indexed column list with ALTER INDEX. If you need different columns (or a different column order), you drop and recreate the index (or create a new one and retire the old one after testing).

Types of Modifications

Common ALTER INDEX tasks include:

  1. Rename an index.
  2. Move the index by rebuilding it into a different tablespace.
  3. Change parallelism (useful during builds; reset afterward).
  4. Maintain partitioned indexes (rebuild partitions, adjust partition attributes).
  5. Control logging (for example during bulk rebuild operations).
  6. Change the index to REVERSE / NOREVERSE (reverse-key behavior).
  7. Rebuild or coalesce (covered more deeply in the next module).
  8. Make an index visible/invisible (safe, staged rollout or staged removal).
  9. Monitor usage to help identify candidate indexes for retirement.

Operational Mindset: Treat Index Changes as Production Changes

Index alterations can change performance instantly—sometimes for the better, sometimes dramatically for the worse. A safe approach is:

  1. Measure first (baseline response time, logical reads, physical reads, plan shape).
  2. Make a controlled change (invisible index, rebuild into new tablespace, or adjust attributes).
  3. Validate the execution plan (same query, same binds, same stats, compare plan + runtime).
  4. Promote or revert (make visible, keep invisible, or drop/rollback change).

Example: Rename an Index

Renaming is purely administrative. It does not change the access path or the storage layout—only the object name. 


ALTER INDEX bid_client_idx
RENAME TO bidder_client_idx;

Move an Index to a Different Tablespace

You cannot “move” an index in-place. The standard approach is to rebuild it into the target tablespace. This is also how you separate indexes from table data (previous lesson) and how you correct fragmentation or change storage policy. 


-- Rebuild into a new tablespace
ALTER INDEX bidder_client_idx
REBUILD TABLESPACE index_tbs;

-- Optional: online rebuild when supported/appropriate
ALTER INDEX bidder_client_idx
REBUILD ONLINE TABLESPACE index_tbs;

After large rebuilds, it is common to refresh optimizer statistics so the cost-based optimizer has accurate information. In modern Oracle environments, DBMS_STATS is the preferred approach to gathering statistics. 


BEGIN
  DBMS_STATS.GATHER_INDEX_STATS(
    ownname => USER,
    indname => 'BIDDER_CLIENT_IDX'
  );
END;
/

Logging and NOLOGGING

Logging controls how much redo is generated for certain operations. DBAs sometimes reduce logging overhead during large index builds or rebuilds. The trade-off is operational: in some recovery scenarios (and especially in replicated environments), you must understand how reduced logging interacts with recovery and standby apply. 


-- Rebuild with reduced logging overhead (policy-dependent)
ALTER INDEX bidder_client_idx
REBUILD NOLOGGING;

-- Restore the default behavior if your standards require it
ALTER INDEX bidder_client_idx LOGGING;

Parallelism: Speed Up Builds, Then Reset

Parallelism can make index creation/rebuild faster by using multiple parallel execution servers. A common best practice is: enable parallel for the build, then return the index to NOPARALLEL so you don’t surprise the system later. 


ALTER INDEX bidder_client_idx PARALLEL 8;
ALTER INDEX bidder_client_idx REBUILD;
ALTER INDEX bidder_client_idx NOPARALLEL;


Reverse-Key Index Behavior

Reverse-key indexes were historically used to reduce contention when many sessions insert monotonically increasing key values (for example sequence-based primary keys). Oracle stores the index key bytes in reversed order, spreading inserts across the B-tree. The trade-off is that reverse-key indexes are generally not useful for range scans on the indexed column. 


-- Rebuild as a reverse-key index
ALTER INDEX bidder_client_idx REBUILD REVERSE;

-- Rebuild back to normal key order
ALTER INDEX bidder_client_idx REBUILD NOREVERSE;

Creating and Using Invisible Indexes

The presence of a new index may alter application behavior (execution plans). Often that is the goal, but you may want a safer rollout. An invisible index is maintained during DML (it still costs space and still adds DML overhead), but it is ignored by the optimizer unless you explicitly allow invisible indexes or you reference the index by name via hints. 


ALTER INDEX ba$title_author INVISIBLE;

With the index in an invisible state, you can test impact without changing the “default” plans for the entire application. To validate invisible-index behavior in a controlled test session: 


ALTER SESSION SET optimizer_use_invisible_indexes = TRUE;

When you are satisfied with the results, make the index visible: 


ALTER INDEX ba$title_author VISIBLE;

Invisible indexes are also a clean way to test removing an index before dropping it: make it invisible, observe performance and monitoring data, then decide whether it is safe to drop.

Automatic Indexing Connection

If your environment uses Automatic Indexing, Oracle can evaluate candidate indexes in a non-disruptive way by keeping them invisible during testing, then making validated indexes visible when you switch to an implementation mode. This mirrors the manual “invisible → validate → visible” workflow.

Monitoring Index Usage

A practical question in real systems is: “Is this index ever used?” Oracle provides index usage monitoring to help answer that. You enable monitoring per index, then query the usage view to see if it was used during the monitoring window. 


ALTER INDEX bidder_client_idx MONITORING USAGE;

In modern releases, use USER_OBJECT_USAGE (and DBA_OBJECT_USAGE for broader visibility) to inspect results: 


SELECT index_name,
       table_name,
       monitoring,
       used,
       start_monitoring,
       end_monitoring
FROM   user_object_usage
WHERE  index_name = 'BIDDER_CLIENT_IDX';

When you are done, stop monitoring: 


ALTER INDEX bidder_client_idx NOMONITORING USAGE;

Usage monitoring is a decision aid, not a verdict. “NOT USED” during a monitoring window might still mean: quarterly jobs, month-end batch, rare audit queries, incident-response scripts, or a regression scenario you haven’t hit yet.

Index Compression via Rebuild

If an index consumes significant space, rebuild-based compression can reduce storage and sometimes improve cache efficiency. Compression is not “free”—it changes CPU vs I/O trade-offs—so treat it as a measured tuning change. 


-- Rebuild with prefix/key compression
ALTER INDEX bidder_client_idx
REBUILD COMPRESS;

Partitioned Index Maintenance

For partitioned tables, indexes may be local or global. Maintenance often occurs at the partition level: rebuild a single index partition after data maintenance, or adjust attributes consistently across partitions. 


-- Example pattern (names depend on your partitioning strategy)
ALTER INDEX sales_date_ix
REBUILD PARTITION p2026_01;

Partition-aware index maintenance is one reason DBAs adopt partitioning: you can repair or rebuild smaller physical pieces instead of touching a massive global structure every time.

Related Hints (Use Sparingly)

Table-Access Hints: FULL and ROWID

Two commonly discussed table-access hints are FULL and ROWID.

FULL: suggest a full table scan.


SELECT /*+ FULL(emp) */ *
FROM   emp
WHERE  empno = 499;

ROWID: encourage a table lookup by rowid after an index step. Practically, ROWID access is what makes B-tree indexing fast: the index finds candidate rowids, and Oracle fetches those rows efficiently.

If you want blocks to remain in memory longer for repeated scans, you will sometimes see caching hints used with full scans in specialized scenarios (use only when you understand buffer-cache implications in your environment): 


SELECT /*+ FULL(worker) CACHE(worker) */ *
FROM   worker;

The index lesson takeaway is simple: hints exist, but your first tuning tools should be good indexing strategy, up-to-date statistics, and a schema design that matches the workload.

Legacy Note: LONG and LONG RAW Columns

Oracle still supports LONG and LONG RAW for compatibility, but these types are legacy and restrictive. If you find indexing or query patterns constrained by LONG, plan a migration to LOB types: LONG → CLOB/NCLOB and LONG RAW → BLOB. Once migrated, you have modern feature support (SecureFiles, better tooling, and broader API support).

This matters in indexing discussions because several modern indexing and search strategies (including Oracle Text, function-based indexing on extracted content, and LOB-aware processing) are far easier to implement on LOB types than on LONG types.

The next lesson continues index lifecycle management by focusing on rebuilding (and related maintenance operations) in a disciplined way.


SEMrush Software