Oracle Indexes: B-Tree, Bitmap, Function-Based and Invisible

Oracle DBA Certification 15 min min read Updated: Nov 16, 2026 Intermediate
Oracle Indexes: B-Tree, Bitmap, Function-Based and Invisible
Intermediate Topic 1 of 3

Why Indexes?

Without an index Oracle reads every block of a table to find your rows (a full table scan). An index is a small sorted structure that points directly to the rows, so lookups become very fast.

Simple example: To find 'Oracle' in a 500 page book you could read every page, or use the index at the back. The index costs a few extra pages but saves a huge amount of time.

Index Types

TypeBest forWatch out
B-tree (default)Columns with many different values, most OLTP queriesSlows down INSERT and UPDATE a little
CompositeQueries that filter on several columns togetherColumn order matters
Function-basedSearches like WHERE UPPER(name) = 'ASHA'Query must use the same expression
BitmapFew distinct values (gender, status) in reporting systemsBad for heavy concurrent DML. Enterprise Edition.
InvisibleTesting the effect of dropping an indexOptimizer ignores it by default

B-Tree and Composite Indexes

sql CREATE INDEX idx_orders_cust ON shop.orders (customer_id); CREATE INDEX idx_orders_cust_status ON shop.orders (customer_id, status); CREATE UNIQUE INDEX uq_cust_email ON shop.customers (email);
Column order: Put the column you filter by equality most often first. The index on (customer_id, status) helps queries on customer_id alone and on both columns, but not on status alone.

Function-Based Index

sql CREATE INDEX idx_cust_upper_name ON shop.customers (UPPER(full_name)); SELECT * FROM shop.customers WHERE UPPER(full_name) = 'ASHA RAO';

Bitmap Index

sql CREATE BITMAP INDEX bix_orders_status ON shop.orders (status);

Invisible Index: Test Before You Drop

sql ALTER INDEX shop.idx_orders_cust INVISIBLE; -- run your workload and watch for slow queries ALTER INDEX shop.idx_orders_cust VISIBLE;

If nothing gets slower with the index invisible, it is safe to drop. If something breaks, make it visible again instantly, with no rebuild.

Check Indexes

sql SELECT index_name, index_type, uniqueness, status, visibility FROM dba_indexes WHERE owner = 'SHOP'; SELECT index_name, column_name, column_position FROM dba_ind_columns WHERE table_owner = 'SHOP' AND table_name = 'ORDERS' ORDER BY index_name, column_position;

Rebuild an Index

sql ALTER INDEX shop.idx_orders_cust REBUILD ONLINE;
Do you need to rebuild? Not on a schedule. B-tree indexes balance themselves. Rebuild only after a large mass delete, after a table move on older versions or when an index is marked UNUSABLE.

Find Unused Indexes

sql SELECT name, total_access_count, last_used FROM dba_index_usage WHERE owner = 'SHOP' ORDER BY total_access_count;

When NOT to Index

  • Very small tables (a full scan is just as fast)
  • Columns that are rarely used in WHERE or JOIN
  • Tables with very heavy inserts where read speed does not matter
  • Columns with extremely low selectivity on OLTP tables
Foreign keys: Oracle does not index foreign key columns for you. An unindexed foreign key on a busy table often causes locking and slow joins. Index them.

Conclusion

Create indexes for the columns you search and join on, keep them few and test removal with invisible indexes.

Quick Summary: B-tree is the default, function-based matches expressions, invisible tests removal, index your foreign keys.

Get Newsletter

Subscibe to our newsletter and we will notify you about the newest updates on Edugators