Oracle Database / Intermediate: application SQL and evidence / Lesson 11

Indexes, statistics and plan evidence

Connect access paths to selective predicates and diagnose estimation mistakes before proposing an index.

About 5 min readContent reviewed 2026-10-28

On this page

Lesson goals

Reason about composite and function-based indexes.
Separate estimated plans from evidence about an executed cursor.

An index exchanges read work for maintenance work

A B-tree index can locate a small matching population without reading every table block, but each insert and relevant update must maintain it. Selecting most of a large table may favor a full scan, particularly when table access through many row identifiers becomes scattered. “Index used” is therefore not a universal performance goal. Begin with the response-time requirement, exact SQL, parameter types, row counts and workload frequency. A ten-millisecond improvement on a rare report may not justify additional write cost on every booking. Keep storage and operational complexity in the decision alongside elapsed time.

Order keys from a concrete predicate

For repeated queries by customer with a booking-time range and stable identifier ordering, a candidate composite index begins with customer_id and then booked_at. Adding shipment_id can support ties, but its benefit depends on the selected columns and plan. The example is a proposal, not an executed change or a guarantee. An index on (customer_id, booked_at) is not identical to two independent indexes. Likewise, predicates on only the second column may not gain the expected efficient range access. Oracle can choose other strategies, so avoid reducing all plan behavior to a simplistic left-prefix slogan.

sql Illustrative technical example; logical results are reasoned, not engine-tested.

CREATE INDEX ix_shipments_customer_time
ON shipments (customer_id, booked_at, shipment_id);

SELECT shipment_id, booked_at FROM shipments
WHERE customer_id = :customer_id
  AND booked_at >= :from_time AND booked_at < :to_time
ORDER BY booked_at, shipment_id;

A candidate access path for a reviewed lab workload. Creation is DDL and must not be run as a casual diagnostic on an existing service.

The predicate expression must match the design

Applying UPPER to tracking_code can prevent an ordinary index from supplying the intended expression lookup. A function-based index on UPPER(tracking_code) can serve a case-normalized query when that normalization is the correct business rule. It also adds maintenance and requires consistent expression semantics. Do not use an uppercase rule for identifiers whose case carries meaning. Ordinary B-tree indexes omit entries where all indexed key columns are NULL, which matters for IS NULL searches; adding a meaningful non-null key or choosing a different design requires a specific workload justification rather than a universal recipe.

Cardinality mistakes propagate through joins

The optimizer estimates how many rows each operation will produce using statistics and assumptions about predicates. A large estimate error early in a plan can lead to an unsuitable join method or memory expectation later. Skewed customer populations, correlated columns and implicit conversions are useful hypotheses. Statistics changes can alter many plans, so collecting or changing them belongs to a controlled procedure rather than an automatic first reaction. Compare representative parameter sets, including small and large customers. One fast execution after warming caches does not establish a reliable improvement under the actual workload.

Say what the plan actually proves

EXPLAIN PLAN records an estimated plan in a plan table; it does not execute the target query and can differ from the actual cursor environment. DBMS_XPLAN can display plans and, when appropriately collected and authorized, cursor execution statistics. The illustrative display query below reads an existing explained plan rather than inventing measured runtime rows. Before using AWR, ASH, SQL tuning advisors or related facilities, check edition and management-pack entitlements for the exact offering. Availability in a menu or package is not authorization to use a separately licensed feature. Preserve these boundaries when sharing tuning advice.

sql Illustrative technical example; logical results are reasoned, not engine-tested.

SELECT plan_table_output
FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'BASIC +PREDICATE'));

Requires a relevant existing plan-table entry. It displays estimates and predicates, not proof of measured elapsed time or actual row counts.

Common errors and fixes

An added index does not improve the report

Why it happens: The query selects many rows or its predicate expression differs from the index keys.
How to fix it: Inspect selectivity, predicates and actual authorized plan evidence.
Prevent it next time: Evaluate representative reads and write overhead before adopting the index.

Estimated rows differ greatly from observed rows

Why it happens: Statistics or predicate assumptions misrepresent the relevant data distribution.
How to fix it: Investigate skew, correlations and conversions before controlled statistics work.
Prevent it next time: Capture parameter types and cardinalities in performance review evidence.

A plan screenshot is presented as a runtime benchmark

Why it happens: An estimated plan was confused with executed-cursor measurements.
How to fix it: Label the source and limits of every plan and timing claim.
Prevent it next time: Use a reproducible authorized experiment before asserting improvement.

Check your understanding

1. Should a query returning ninety percent of shipments always use an index?

Show worked solution

No. A full scan can be cheaper than scattered table accesses through an index. Compare the actual workload, selected columns and authorized evidence rather than treating access-path names as grades. Account for write maintenance and test several representative parameter populations before accepting a new index.

2. What does a clean EXPLAIN PLAN establish about a ten-second production query?