On a table with an index on order_date, a column of type TIMESTAMP, this query performs a full scan: SELECT order_id FROM orders WHERE EXTRACT(YEAR FROM order_date) = 2025; Which rewritten WHERE clause returns every 2025 order and allows the index to be used?
Select an answer to reveal the explanation.
Short Explanation
Wrapping an indexed column in a function is like asking the librarian to translate every book title before checking the shelf: the sorted index stops helping. You want the bare column compared to a range, and a half-open range at that. Watch the BETWEEN trap: it stops at midnight on December 31 and quietly drops that whole day.
Full Explanation
An index on order_date stores the column's values in sorted order, so the optimizer can seek directly to a range when the predicate compares the bare column with constant values. Applying a function such as EXTRACT to the column means the engine must compute the function for each row before comparing, which makes the predicate non-sargable and typically forces a scan. A half-open range, greater than or equal to the first instant of 2025 and strictly less than the first instant of 2026, returns every 2025 timestamp regardless of time of day and lets the index be used for a range seek. A BETWEEN that ends at midnight on December 31 is sargable, but because order_date carries time values, any order placed after 00:00 on the last day of the year is excluded, so results are incomplete. Casting the timestamp to a character string and taking a substring wraps the column in two functions, which prevents index use, and it also depends on the engine's string formatting of timestamps. Splitting EXTRACT into two comparisons still applies the function to the column on both sides, so the predicate remains non-sargable even though it returns the right rows. Exam caveat: some engines support function-based or computed-column indexes, but the general rule is to keep indexed columns unwrapped in predicates. Operational check: compare execution plans before and after the rewrite, confirm an index seek or range scan, and verify the row count matches the original query.