CSE Roadmap

Core tier · about 2 hours · 6 parts

Part 6 of 6 · 4 min read

Exercises

Easy

Easy 1: Identify the operation (10 minutes)

A plan shows TABLE ACCESS FULL with 10,000 rows and a WHERE clause matching only 3 rows. What does this tell you about whether an index exists on the filtered column?

Done when: you conclude that no usable index exists on that column, since the engine had to read every row.

Hint

a full scan with no selective result strongly suggests the filtered column has no usable index.

Easy 2: Read the predicate information (10 minutes)

A plan's INDEX RANGE SCAN step shows predicate access("REGION"='EAST'), and the step above it shows filter("STATUS"='ACTIVE'). Which condition is applied during the index lookup, and which is applied after fetching the row?

Done when: you correctly identify REGION as applied during the index lookup and STATUS as applied afterward on the fetched row.

Hint

"access" predicates are applied during the index lookup itself; "filter" predicates are applied afterward on the fetched row.

Easy 3: Compare two costs (10 minutes)

Plan A has cost 30; Plan B, which the optimizer rejected, has cost 475. Which plan will the optimizer choose, and does a lower cost always mean the plan is actually faster in reality?

Done when: you state the optimizer picks Plan A by its own estimate, but note that the estimate can be wrong if statistics are stale.

Hint

the optimizer always picks the lower estimated cost, but a wrong estimate from stale statistics can still mislead it.

Medium

Medium 1: Explain a slow indexed query (20 minutes)

A query filters on last_name and subsidiary_id, but the only relevant index covers subsidiary_id alone. For a subsidiary with 1000 employees, explain why this indexed plan can be slower than a full table scan.

Done when: your explanation names both ingredients: the index returns 1000 candidate rows, and each one must be individually fetched before last_name can be checked.

Hint

point to the combination of a wide index match and many individual row fetches as the two ingredients of a slow index.

Medium 2: Force the alternate plan (20 minutes)

Write the SQL for the slow query in Medium 1, including a hint that forces the engine to avoid the subsidiary_id index, so its plan can be compared against the indexed version.

Done when: your query includes a correctly placed NO_INDEX-style hint naming the table and index to avoid.

Hint

the hint syntax names the table and the specific index to avoid, directing the optimizer to the alternate full-scan plan.

Medium 3: Diagnose from a row-count mismatch (20 minutes)

An execution plan estimates an index range scan will return 40 rows, but the actual subsidiary has 1000 employees. What is the most likely cause of this gap, and what fixes it?

Done when: you name stale or missing statistics as the likely cause and refreshing statistics as the fix.

Hint

a forty-row estimate against a thousand real rows points directly at outdated statistics, fixed by re-running the statistics-gathering step.

Hard

Hard 1: Walk the full diagnosis (25 minutes)

A telephone-directory query slowed down right after a primary key index was changed to start with subsidiary_id. Walk through the steps you would take to confirm the index change is the cause, in order.

Done when: your steps include reading the current plan's predicate information, forcing the old plan with a hint for comparison, and checking whether statistics accurately reflect the table's row distribution.

Hint

confirm cause before proposing a fix, in this order: read the new plan, compare against the old plan via a hint, then check statistics accuracy.

Hard 2: Decide whether to keep the index (25 minutes)

The same subsidiary_id-first index now helps one set of queries but hurts another, the one from Hard 1. Propose two different ways to resolve this conflict without simply reverting the index.

Done when: you propose at least two options, such as adding a second, more specific composite index covering both columns in the right order, or maintaining two separate indexes for the two different query patterns.

Hint

a composite index ordered to match each query's own leftmost filter, or simply maintaining two separate indexes, are both reasonable resolutions.

Hard 3: Predict the effect of table growth (25 minutes)

A full table scan currently costs 478 for a 106-row table. Predict roughly how that cost changes if the table grows to 1,060 rows, and explain why an index range scan's cost would not grow the same way for a highly selective query.

Done when: you predict the full scan's cost grows roughly proportionally with table size, and explain that a selective index lookup's cost depends on how many rows match the filter, not the total table size.

Hint

a full scan's cost tracks table size roughly linearly, while a selective index lookup's cost tracks the number of matching rows, which does not necessarily grow with the whole table.