Performance Tuning: Plans and Indexes

Explain Plan

EXPLAIN PLAN writes the execution plan Oracle chose into a plan table, and DBMS_XPLAN.DISPLAY renders it readably.

See the Plan

EXPLAIN PLAN FOR
SELECT * FROM orders WHERE customer_id = 10;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Reading the Plan

A FULL TABLE SCAN on a large table is usually the problem. The winning plan is typically TABLE ACCESS BY INDEX ROWID backed by an INDEX RANGE SCAN.

Index Types

CREATE INDEX idx_orders_cust ON orders(customer_id);
CREATE BITMAP INDEX idx_orders_status ON orders(status);

B-tree indexes fit high-cardinality columns such as customer_id. Bitmap indexes fit low-cardinality columns such as status.

Key Points

• EXPLAIN PLAN plus DBMS_XPLAN shows the plan.
• Look for FULL TABLE SCAN first.
• B-tree indexes for selective columns.
• Bitmap indexes for low-cardinality columns.