Which technique speeds up queries on a table with city demography data for both range and equality searches?

• A. Create a materialized view.
• B. Turn on search optimization for the table.
• C. Create a secure view for each search pattern.
• D. Cluster the table.

Answer: (B)

The technique being tested focuses on accelerating predicate-based searches using a specialized indexing feature. Turning on search optimization for a table makes Snowflake maintain a lightweight search index that speeds up predicates, especially for both range queries (for example, filtering by a range of population or year) and exact-match lookups (such as filtering by city name or code). This optimization helps the query engine prune irrelevant micro-partitions more effectively, so queries can locate matching rows quickly without scanning the entire table. It’s particularly advantageous for large city demography tables where you run a mix of range and equality filters.

While clustering and materialized views have their uses, they address different patterns or require more manual tuning and maintenance. Secure views don’t inherently improve performance, and clustering requires careful design of cluster keys and ongoing maintenance. Search optimization provides a targeted, often easier-to-manage speedup for the combined range and exact-match searches described.