Part 5 of 6 · 3 min read
Confirming with accurate statistics
Confirming the diagnosis with accurate statistics
Think of confirming a diagnosis the way a doctor orders a second test. It compares what the body actually does against what an earlier, possibly outdated, chart predicted it would do.
In plain words: To confirm that an index change actually caused a slowdown, it helps to see the execution plan the engine would have used before that change, which many engines let a developer force by temporarily disabling a specific index for one query. Comparing the plan with the index against the plan without it can show the full table scan actually finishing with a lower cost estimate than the supposedly faster indexed plan. The underlying reason is often stale statistics: if the optimizer's stored row-count estimate for the index lookup is far too low, refreshing the statistics to reflect the data's real distribution corrects that estimate and raises the index plan's calculated cost to something closer to reality.
A worked example. Forcing the plan without the EMPLOYEES_PK index on the slow query from the previous part reveals a full table scan at cost 477, barely different from the indexed plan's cost of 30, yet it actually runs faster once row counts are accurate. Refreshing statistics raises the index plan's estimated rows from 40 to 1000 and its cost from 30 to 680, correctly reflecting the real cost of fetching a thousand rows individually.
The key lines.
SELECT /*+ NO_INDEX(EMPLOYEES EMPLOYEE_PK) */
first_name, last_name, subsidiary_id, phone_number
FROM employees
WHERE last_name = 'WINAND'
AND subsidiary_id = 30;
With accurate statistics:
---------------------------------------------------------------
|Id |Operation | Name | Rows | Cost |
---------------------------------------------------------------
| 0 |SELECT STATEMENT | | 1 | 680 |
|*1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 680 |
|*2 | INDEX RANGE SCAN | EMPLOYEES_PK | 1000 | 4 |
---------------------------------------------------------------
Optimizer hint
An optimizer hint is an instruction embedded in a query that overrides the optimizer's own choice for a specific operation, such as forcing it to avoid or prefer a particular index, used here only to compare plans rather than as a permanent fix.
Example: the NO_INDEX hint above forces the engine to produce the plan it would have used without the EMPLOYEES_PK index, for direct comparison against the plan that uses it.
Quick notes
- An execution plan's operations, rows, and cost columns, together with its predicate information, show exactly what the engine will do and which filter each step applies.
- A full table scan's cost scales with table size regardless of selectivity; an index range scan's cost depends on how many rows the indexed part of the filter actually matches.
- An index covering only part of a WHERE clause can still return many rows, each needing an individually expensive fetch from the table, sometimes costing more than a full scan.
- Stale or missing statistics can make the optimizer badly underestimate how many rows an index lookup returns, leading it to wrongly prefer a slower indexed plan.
- Temporarily disabling an index with a hint, and refreshing statistics, are two concrete ways to confirm why a specific plan was chosen.