How to Create an Index in Oracle - Types & Examples

How to Create an Index in Oracle

Oracle indexes dramatically reduce query response time by allowing the optimizer to locate rows without a full table scan. Choosing the right index type — B-tree, bitmap, function-based, or composite — is as important as creating the index itself. This guide covers all common index types with production-ready SQL.

Prerequisites

To create an index on another user’s table, you need the INDEX object privilege on that table, or the CREATE ANY INDEX system privilege.

-- Check your index privileges
SELECT * FROM session_privs WHERE privilege LIKE '%INDEX%';

-- Check existing indexes on a table
SELECT index_name, index_type, uniqueness, status,
       visibility, partitioned, num_rows, last_analyzed
FROM dba_indexes
WHERE table_name = 'ORDERS'
  AND owner = 'APP_SCHEMA'
ORDER BY index_name;

-- Check existing index columns
SELECT index_name, column_position, column_name, descend
FROM dba_ind_columns
WHERE table_name = 'ORDERS'
  AND table_owner = 'APP_SCHEMA'
ORDER BY index_name, column_position;

Step 1: Create a Basic B-Tree Index

B-tree indexes are Oracle’s default and work for high-cardinality columns used in equality, range, or ORDER BY operations.

-- Basic single-column index
CREATE INDEX orders_customer_id_idx
  ON app_schema.orders (customer_id)
  TABLESPACE app_idx;

-- Unique index (enforces uniqueness and provides an index)
CREATE UNIQUE INDEX orders_order_num_uk
  ON app_schema.orders (order_number)
  TABLESPACE app_idx;

-- Descending index (for ORDER BY ... DESC queries)
CREATE INDEX orders_created_desc_idx
  ON app_schema.orders (created_date DESC)
  TABLESPACE app_idx;

Step 2: Create a Composite Index

Composite (multi-column) indexes serve queries that filter on multiple columns. Column order matters — the leading column must appear in WHERE clauses for the index to be used.

-- Composite index: put the most selective column first
CREATE INDEX orders_status_date_idx
  ON app_schema.orders (status, order_date)
  TABLESPACE app_idx;

-- Good for queries like:
-- WHERE status = 'PENDING' AND order_date > SYSDATE - 30
-- WHERE status = 'PENDING'  (leading column — still usable)
-- NOT useful for: WHERE order_date > SYSDATE - 30 (skips leading column)

-- Composite covering index (includes all columns needed by the query)
CREATE INDEX orders_lookup_idx
  ON app_schema.orders (customer_id, status, order_date)
  TABLESPACE app_idx;

Step 3: Create a Function-Based Index

Function-based indexes (FBIs) support queries that apply functions to columns in the WHERE clause.

-- Case-insensitive search index
CREATE INDEX customers_email_upper_idx
  ON app_schema.customers (UPPER(email))
  TABLESPACE app_idx;
-- Supports: WHERE UPPER(email) = '[email protected]'

-- Date truncation index
CREATE INDEX orders_order_date_trunc_idx
  ON app_schema.orders (TRUNC(order_date))
  TABLESPACE app_idx;
-- Supports: WHERE TRUNC(order_date) = TRUNC(SYSDATE)

-- Computed expression index
CREATE INDEX orders_net_amount_idx
  ON app_schema.orders (unit_price * quantity * (1 - discount))
  TABLESPACE app_idx;
-- Supports: WHERE unit_price * quantity * (1 - discount) > 1000

For FBIs to be used, QUERY_REWRITE_ENABLED must be TRUE (default in Oracle 10g+).

Step 4: Create a Bitmap Index

Bitmap indexes suit low-cardinality columns (like STATUS, GENDER, REGION) in data warehouse workloads. Avoid them on OLTP tables with concurrent DML — they cause severe lock contention.

-- Bitmap index on a low-cardinality column
CREATE BITMAP INDEX orders_status_bmp
  ON app_schema.orders (status)
  TABLESPACE app_idx;

-- Bitmap index on a boolean-like column
CREATE BITMAP INDEX orders_processed_bmp
  ON app_schema.orders (is_processed)
  TABLESPACE app_idx;

Step 5: Create an Index Online

Online index creation allows DML (INSERT, UPDATE, DELETE) to continue during the build. Essential for production tables that cannot tolerate downtime.

-- Online index creation (allows concurrent DML)
CREATE INDEX orders_customer_id_idx
  ON app_schema.orders (customer_id)
  TABLESPACE app_idx
  ONLINE;

-- Online rebuild of an existing index
ALTER INDEX app_schema.orders_customer_id_idx REBUILD ONLINE;

-- Note: ONLINE requires extra temp space — monitor tablespace usage during build

Step 6: Build with Parallelism

Use parallel degree to speed up index creation on large tables.

-- Create index in parallel (uses multiple CPUs/processes)
CREATE INDEX orders_history_idx
  ON app_schema.order_history (order_date, customer_id)
  TABLESPACE app_idx
  PARALLEL 8
  NOLOGGING;    -- Skip redo for faster build (backup after creation!)

-- After creation, reset to NOPARALLEL and re-enable logging
ALTER INDEX app_schema.orders_history_idx NOPARALLEL;
ALTER INDEX app_schema.orders_history_idx LOGGING;

Advanced Examples

Invisible Index

Create an index the optimizer ignores until you explicitly enable it — useful for testing.

-- Create invisible index (optimizer does not use it by default)
CREATE INDEX orders_test_idx
  ON app_schema.orders (region, category)
  TABLESPACE app_idx
  INVISIBLE;

-- Test the index in your session without affecting others
ALTER SESSION SET optimizer_use_invisible_indexes = TRUE;
EXPLAIN PLAN FOR SELECT * FROM app_schema.orders WHERE region = 'WEST';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- Make the index visible when satisfied with results
ALTER INDEX app_schema.orders_test_idx VISIBLE;

Partial Index on a Partitioned Table

-- Index only some partitions (reduces index size for partition-pruned queries)
CREATE INDEX orders_part_idx
  ON app_schema.orders_partitioned (customer_id)
  TABLESPACE app_idx
  LOCAL           -- Partition-aligned (one index partition per table partition)
  INDEXING PARTIAL;  -- Honor INDEXING OFF partitions

-- For partitioned tables, LOCAL vs GLOBAL matters:
-- LOCAL: partition-aligned, easier partition management
-- GLOBAL: single index structure over all partitions, faster cross-partition queries

Compressed Index

-- Index key compression (reduces index size for composite indexes with repeated prefixes)
CREATE INDEX orders_region_status_idx
  ON app_schema.orders (region, status, customer_id)
  TABLESPACE app_idx
  COMPRESS 2;  -- Compress the first 2 columns (prefix compression)

Common Mistakes and Pitfalls

Indexing low-cardinality columns with B-tree — A B-tree on a YES/NO column will never be used for table scans — the optimizer prefers a full table scan when a query returns more than roughly 10-15% of rows.

Creating bitmap indexes on OLTP tables — A single DML statement on an indexed row locks the entire bitmap segment, causing concurrency disasters. Bitmap indexes belong in read-heavy data warehouses.

Building large indexes without NOLOGGING — Without NOLOGGING, large index builds generate enormous amounts of redo and slow down significantly. Use NOLOGGING and take a backup immediately after.

Forgetting to rebuild after NOLOGGING — NOLOGGING blocks are unrecoverable from archived logs. If you lose the datafile, you cannot recover those index blocks — take a backup immediately.

Missing statistics after index creation — A new index with no statistics may not be chosen by the optimizer. Gather statistics after creation.

Over-indexing OLTP tables — Every index on a table slows INSERT, UPDATE, and DELETE because all indexes must be maintained. Keep indexes focused on actual query patterns.

Verification Queries

-- Confirm index was created and is VALID
SELECT index_name, index_type, uniqueness, status,
       visibility, partitioned, compression
FROM dba_indexes
WHERE table_name = 'ORDERS'
  AND owner = 'APP_SCHEMA'
ORDER BY index_name;

-- Check index columns and order
SELECT ic.index_name, ic.column_position, ic.column_name, ic.descend
FROM dba_ind_columns ic
JOIN dba_indexes i ON ic.index_name = i.index_name AND ic.table_owner = i.owner
WHERE ic.table_name = 'ORDERS'
  AND ic.table_owner = 'APP_SCHEMA'
ORDER BY ic.index_name, ic.column_position;

-- Check index size
SELECT segment_name,
       ROUND(SUM(bytes)/1024/1024, 0) AS size_mb,
       tablespace_name
FROM dba_segments
WHERE segment_name = 'ORDERS_CUSTOMER_ID_IDX'
  AND owner = 'APP_SCHEMA'
GROUP BY segment_name, tablespace_name;

-- Verify the query plan uses the new index
EXPLAIN PLAN FOR
  SELECT * FROM app_schema.orders WHERE customer_id = 12345;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- Gather statistics so the optimizer has accurate data
EXEC DBMS_STATS.GATHER_INDEX_STATS('APP_SCHEMA', 'ORDERS_CUSTOMER_ID_IDX');

Related Topics

• How to Gather Statistics - Ensure the optimizer uses your index
• How to Create a Partitioned Table - Partitioned index strategies
• Performance Analysis Scripts - Index usage monitoring
• Oracle Index Tuning Guide - Advanced tuning techniques