Skip to content
DBAExperts
Oracle Performance

How to create an index in Oracle and when to use bitmap

An index speeds up lookups but costs space and slows down writes. Choosing the right type matters as much as creating it.

  1. 1

    Create a B-tree index

    This is the default index and works for most equality or range queries.

    CREATE INDEX idx_pedidos_cliente ON pedidos (cliente_id);
  2. 2

    Composite indexes for combined filters

    Order the columns based on how the query filters; the most selective one, or the one used in equality, usually goes first.

    CREATE INDEX idx_pedidos_cli_fecha ON pedidos (cliente_id, fecha);
  3. 3

    Use bitmap only on low cardinality

    Bitmap is a good fit for columns with few distinct values and read-only or DWH workloads. In OLTP with concurrent writes it causes serious locking.

    CREATE BITMAP INDEX idx_pedidos_estatus ON pedidos (estatus);
  4. 4

    Gather statistics

    The optimizer needs fresh statistics to decide whether to use the index.

    EXEC DBMS_STATS.GATHER_TABLE_STATS('VENTAS','PEDIDOS');
  5. 5

    Verify the index is used

    Check the execution plan. An index nobody uses only weighs on writes.

    EXPLAIN PLAN FOR SELECT * FROM pedidos WHERE cliente_id = 100;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

// common mistake

A bitmap index on an OLTP table with concurrent inserts or updates locks entire ranges of rows and degrades performance.

// when it's worth an expert

If you have many indexes and do not know which ones help, or queries are still slow despite indexing, an analysis pays off. At dba.mx we tune indexing at a fixed price.

Book an assessment — from USD $550

This guide is for reference and uses example commands. In production, adapt to your version and test in a safe environment first.