- Which statement best explains why this design can help the range queries?
Answer: Clustering stores rows with nearby time keys near one another, allowing a range to be read more sequentially.
When you see a question about clustered indexes and query performance, focus on the physical storage model: a clustered index dictates the actual order in which rows are stored on disk, not just how they're looked up.
Because the log table is clustered on event_time, rows with similar timestamps are physically adjacent on disk. When an analyst requests events between two timestamps, the database engine can locate the starting point via the index and then read consecutive disk pages sequentially until it reaches the end of the range. This sequential I/O is dramatically faster than random I/O, which would be required if matching rows were scattered across the disk. That's exactly what A describes — and it's the correct answer.
B is wrong because clustered indexes don't precompute or cache result sets for queries. That would describe something closer to a materialized view, which is an entirely different structure that must be explicitly created.
C is wrong because clustering has no effect on row uniqueness or duplicate elimination. A clustered index can absolutely contain duplicate key values (unless a unique constraint is added separately). Returning one row per timestamp is a uniqueness concern, not a storage-order concern.
D is wrong because clustered indexes involve disk organization, not memory persistence. Keeping rows "permanently in memory" describes a caching or in-memory database concept, not how clustered indexes function. Pages may be cached temporarily by the buffer pool, but nothing is guaranteed to stay in memory indefinitely.
As a study tip, always distinguish between how data is physically stored (clustered index), how it's looked up (non-clustered index), and how results are cached (materialized views, buffer pools) — exam questions frequently mix these concepts as distractors.
- Which explanation best accounts for the reduction in page reads?
Answer: The index provides a searchable key structure with references to rows, reducing the need to scan unrelated pages.
When a question asks why an index reduces page reads, you should think about what an index actually is: a separate, ordered data structure that maps key values to row locations, letting the database engine jump directly to relevant rows instead of scanning everything.
A nonclustered index on customer_id builds a balanced tree (B-tree) of customer ID values, each paired with a pointer to the actual row's location in the table. When a query filters by customer_id, the engine traverses this compact structure, finds only the matching row pointers, and fetches just those pages — skipping millions of unrelated rows entirely. That's exactly what C describes, and it's the correct answer.
A is wrong because an index does not store the full table in memory, nor does it eliminate disk access. It simply makes disk access more targeted. Memory caching is a separate mechanism (like a buffer pool) and isn't what the index itself provides.
B is wrong because a nonclustered index does not physically reorder table rows — that's what a clustered index does. The underlying table pages remain in their original order; only the index structure is sorted by customer_id. This is a classic trap: confusing clustered and nonclustered behavior.
D is wrong because indexes are not query result caches. They don't store previous search results or reuse them across queries. That describes a caching or memoization layer, not an index.
As a study tip: always distinguish between clustered indexes (change physical row order), nonclustered indexes (separate lookup structure with row pointers), and caches (store results). Exam questions frequently blur these three concepts.
- How can the index improve the join without changing the report's logical result?
Answer: It can let the DBMS locate orders for each selected customer without repeatedly scanning all orders.
When a query joins two tables, the DBMS needs a strategy to find matching rows efficiently. Questions like this test whether you understand that indexes are access path optimizers — they change how data is retrieved, never what data is logically returned.
Here's the core idea behind A: when your query first narrows down to a small set of customers, the DBMS then needs to find each customer's matching orders. Without an index on Orders.customer_id, the engine must scan every row in Orders for each customer — potentially millions of reads. With the index, it can jump directly to only the relevant rows for each customer, like using a book's index instead of reading every page. The logical result — which customers match which orders — is identical either way. A is correct because it accurately describes this index lookup (or nested-loop index join) behavior.
B is wrong because indexes never modify or delete data from tables — they can't remove customers from Customers before a join runs. That would change the logical result, which the question explicitly rules out.
C describes materialized views, not indexes. An index doesn't store a completed join result; it stores a sorted pointer structure to speed up lookups within a single table.
D confuses an index with a constraint. An index on Orders.customer_id doesn't enforce a one-order-per-customer rule — only a UNIQUE constraint would do that, and even then it wouldn't guarantee any order exists.
Study tip: When evaluating index questions, always ask: does this change performance (access path) or results (data returned)? Indexes only ever affect the former.
- What property of the index most directly permits this behavior?
Answer: The index contains all columns needed for filtering and output, so it can cover the query.
When a query can be satisfied entirely from an index — without ever touching the base table — you're dealing with a covering index. Any time you see a scenario where the optimizer skips the base table, ask yourself: does the index contain every column the query needs, both for filtering and for output?
That's exactly what's happening here. The index includes customer_id (used in the WHERE clause) and status (returned in the SELECT). Because the index holds all the data the query requires, the database engine never needs to follow a pointer back to the actual table rows. This is the definition of a covering index, and A correctly identifies this property — the index covers the query by containing all referenced columns.
B is wrong because indexes don't cache query results at all. Result caching is a separate database feature; an index is a sorted data structure, not a result store. C is wrong because a covering index says nothing about uniqueness or duplicate elimination — a single customer could have multiple statuses, and the index would still cover the query just fine. D is a complete fabrication: indexes don't convert filters into constraints, and they certainly don't delete rows from a table. That confuses index behavior with something like a CHECK constraint or a DELETE statement.
A practical tip: when you see "optimizer skips the base table" or "index-only scan," immediately think covering index — an index that includes every column touched by the query. On SQL exams, this concept is often tested by asking why the table isn't read, and the answer is always about column coverage, not caching or constraints.
- What performance benefit can this index provide even though the query does not filter any rows?
Answer: It can make sorting unnecessary if the optimizer reads entries in the required index order.
When thinking about index performance, don't limit yourself to filtering — indexes also help the database avoid work it would otherwise do. A B-tree index stores rows in sorted key order, which means the database can walk the index leaf pages sequentially and retrieve rows already arranged the way the query needs them.
This is exactly what makes B correct. When your query orders by last_name, first_name and the index is defined on those same columns in that same order, the optimizer can perform an index scan and return rows in sorted sequence without ever invoking a separate sort step. Sorting is expensive — it often requires writing data to temporary storage — so eliminating it is a genuine performance gain even when zero rows are filtered out.
A is wrong because an index on last_name, first_name only stores those two columns plus the row pointer. If the SELECT clause includes other columns (like salary or email), the database must still fetch the full employee rows — the index alone cannot supply all the output data. A covering index would need to include every selected column.
C describes something that simply doesn't happen. A B-tree index doesn't collapse or deduplicate rows; it preserves every row individually. Adjacent keys may share the same last name, but the database returns all of them.
D is a fabrication. Index ordering has no inherent connection to parallel execution. Parallel plans are chosen based on cost estimates and optimizer settings, not whether the index is sorted.
Study tip: Remember the two index superpowers — filtering (reducing rows) and ordering (avoiding sorts). Exam questions often isolate one to see if you recognize the other.
- Why is the revised predicate often more likely to benefit from the index?
Answer: Direct boundaries can identify a contiguous key range in the index, while a function applied to the column may obscure the stored key values and prevent an efficient seek.
Whenever you see a question about index usage and query predicates, focus on how the database engine navigates a B-tree: it seeks by comparing stored key values directly. A B-tree stores order_date values in sorted order, so the engine can efficiently jump to a starting key and scan a contiguous range — but only when the predicate exposes those raw stored values.
When you wrap a column in a function like YEAR(order_date) = 2023, the database must evaluate that function for every row before comparing the result. The stored keys are no longer directly comparable to your predicate, so the optimizer typically cannot perform an index seek — it may resort to a full index or table scan instead. The revised approach, using order_date >= '2023-01-01' AND order_date < '2024-01-01', speaks directly in terms of stored values. The optimizer can locate '2023-01-01' in the B-tree and scan forward to '2024-01-01' — a classic, efficient range seek. This is why C is correct: direct boundaries expose a contiguous key range, while a function obscures stored values and may prevent an efficient seek.
A is wrong because DBMSs do not swap a B-tree for a temporary hash index per query — that's not how indexing works. B is wrong and backwards: applying a function doesn't simplify lookups; it complicates them by hiding the original key values. D is wrong because boundary comparisons don't force the optimizer to avoid a table scan regardless of selectivity — if a predicate matches most rows, a table scan may still be chosen.
Remember this pattern: functions on indexed columns break index seeks. Rewrite predicates to isolate the raw column on one side of the comparison whenever possible.
- Which factor most directly explains the decrease in write throughput?
Answer: Each data change may also require changes to one or more index structures and their pages.
When you see a question about index performance, think about the hidden work that indexes create behind the scenes. An index isn't a passive lookup table — it's a data structure (like a B-tree) that must stay synchronized with the actual table data at all times.
Every time a row is inserted or updated, the database engine doesn't just write to the table — it must also locate the correct position in each index and update those structures accordingly. If you add five indexes to a heavily written table, each write operation now triggers up to five additional index page modifications. This multiplied I/O and structural maintenance is precisely why A is correct: data changes require changes to one or more index structures, directly reducing write throughput.
B describes something that doesn't happen at all — indexes don't cause the engine to re-execute the insert for every existing row. That would be O(n) behavior per insert, which would make indexes completely unusable in practice. C is equally fictional; indexes store the actual column values (or derived keys) in their native data types, not converted text. There's no text-conversion step triggered by indexing. D confuses the direction of the relationship entirely — inserting new data doesn't cause the database to rerun old read queries. Read queries benefit from indexes at query time, not in reverse when writes occur.
A useful mental model: think of each index as a separate "shadow copy" of certain columns that must be kept up to date. More indexes = more maintenance work per write. When diagnosing index tradeoffs on an exam, always ask yourself what write-side overhead each index introduces.
- Why can the table scan be less expensive even though an applicable index exists?
Answer: Retrieving most rows through index entries can require more work than reading the table sequentially.
Whenever you see a question about query optimization, think about cost, not just capability. The optimizer doesn't ask "can I use this index?" — it asks "should I?"
An index speeds up lookups by letting the database jump directly to matching rows instead of reading everything. But here's the catch: index-based retrieval isn't free. For each matching entry, the database must follow a pointer from the index to the actual data page on disk — a process called a random I/O. When 90% of rows match your filter, you're essentially jumping to 90% of the data pages anyway, but in a scattered, random order. Sequential reads (a full table scan) are far more efficient because the database reads pages in order, minimizing disk seeks. At high selectivity like 90%, the index actually creates more work, not less. That's why the optimizer correctly chooses the full scan — answer B.
A is wrong because indexes are frequently used for ranges, patterns, and multi-row matches. The idea that an index only works for single-row lookups is a misconception with no basis in how indexes function.
C is wrong because Boolean columns can be indexed — there's no rule against it. The issue here is selectivity, not data type. An index on a Boolean column is simply low-selectivity when one value dominates.
D is wrong because no such rule exists. The optimizer makes cost-based decisions dynamically; creating an index never "disables" scanning behavior.
As a study tip, remember: low selectivity kills index usefulness. When a predicate matches a large percentage of rows, always suspect the optimizer will prefer a full scan.
- Which assessment of the application's assumption is correct?
Answer: The assumption is incorrect because a regular index can contain duplicates; uniqueness must be enforced separately.
When working with database indexes, you need to clearly separate two distinct concepts: query performance and data integrity enforcement. A regular index is a performance tool — it creates a sorted data structure that speeds up lookups, but it makes no promises about the uniqueness of the values it indexes.
A standard (non-unique) index happily stores duplicate key values. If two customers register with the same email address, the index will index both rows without complaint. The database engine simply records both entries and points to their respective rows. Because the index itself has no uniqueness constraint, no error is raised and the duplicate is silently accepted. That's exactly why D is correct — the application's assumption is broken, and uniqueness must be enforced through a separate mechanism, either a UNIQUE index or a UNIQUE constraint on the column.
A reflects a common misconception — confusing a regular index with a unique index. Only a UNIQUE index requires every key value to be distinct; the word "unique" must be explicitly specified. B is nonsense from a database standpoint; where rows are physically stored on data pages has no bearing on whether duplicates are allowed. C goes too far in the opposite direction. It's true that a regular index doesn't enforce uniqueness, but the claim that indexes never support uniqueness rules is false — UNIQUE indexes do exactly that.
Your study tip: whenever a question mentions an index, ask yourself what type. A plain index = performance only. A UNIQUE index = performance plus uniqueness enforcement. That distinction is a frequent exam trap.
- Which statement most accurately describes the likely usefulness of this index for locating the qualifying rows?
Answer: It may not support an efficient date seek because entries are ordered first by customer_id, then by sale_date.
When you see a question about composite indexes, the key concept to recall is the leftmost prefix rule: a B-tree index on (customer_id, sale_date) sorts rows first by customer_id, and only within each customer_id group are rows sorted by sale_date. This physical ordering is what determines whether the index can efficiently locate rows matching a given predicate.
Because the index entries are interleaved across all customers before being ordered by date, filtering only on sale_date means the matching rows are scattered throughout the entire index structure — there's no contiguous range the database can seek to. The optimizer will typically skip the index entirely and perform a full table scan instead. This makes D the correct answer: the index may not support an efficient date seek precisely because customer_id is the leading key.
A is wrong because columns in a composite index do not independently serve as leading keys — only the leftmost column(s) do. B goes too far in the opposite direction; composite indexes aren't universally useless when some columns are omitted. If you filter on customer_id alone (the leading column), the index remains useful. The flaw is requiring all columns in the predicate — that's simply not the rule. C describes behavior that doesn't exist; B-tree indexes don't reorganize themselves or create alternate entry points when the leading column is absent.
Study tip: Remember "leftmost prefix" as your mental model for composite indexes. Ask yourself: "Does my query predicate include the first column of the index?" If not, expect the index to be skipped or used only inefficiently.
