A customer lookup table holds hundreds of millions of rows, and a support application issues many highly selective queries that filter on an indexed-style equality predicate against a high-cardinality account_id, each returning only a handful of rows. The clustering key is already set on a different reporting column that the team will not change. Which feature best accelerates these selective point lookups without altering the existing physical clustering?
- ARe-cluster the table by replacing the clustering key with account_id so automatic clustering co-locates each account's rows and the lookups prune effectively
- BEnable the search optimization service on the table so its persistent search access paths prune to the few micro-partitions holding each selective account_id lookup Correct
- CBuild a materialized view keyed on account_id so the support queries read precomputed lookup results instead of scanning the large base customer table directly
- DTurn on the query acceleration service for the warehouse so extra serverless compute scans the large table faster for each selective account_id lookup
Why A is wrong: Changing the clustering key to account_id could help, but the stem fixes the existing clustering column, and re-clustering reorganises the whole table rather than leaving the physical layout untouched.
Why B is correct: Search optimization builds and maintains search access paths that make selective equality and point-lookup queries scan only the relevant micro-partitions, accelerating them without touching the clustering key.
Why C is wrong: A materialized view precomputes a query's results and adds storage and refresh cost, but it is not designed to serve arbitrary selective point lookups across many distinct account_id values.
Why D is wrong: Query acceleration offloads large scan-heavy work to serverless compute, which suits big scans, not tiny selective lookups that already return only a handful of rows.