Prepare and test your skills

Prepare and test your skills

Free sample question with answers and detailed explanation below. The Professional Cloud Database Engineer practice bank has an additional 769 practice questions.Try 10 free realistic questions: instant score, no credit card, and no account required.
You manage an e-commerce application backed by Cloud Spanner (GoogleSQL) and observe rising CPU utilization and compute costs. Using Query Insights and SPANNER_SYS introspection tables, you identify a high-frequency checkout query with the following characteristics:
OR predicates, preventing execution plan reuse.Orders table (returning very few rows) with a large Customers table using JOIN@{JOIN_METHOD=HASH_JOIN}, with Customers placed first in the FROM clause.Which refactoring approach should you implement to reduce CPU consumption and optimize query execution?
APPLY_JOIN and SCAN_METHOD=BATCH DoAn APPLY_JOIN iterates through rows from the left-hand (outer) input and executes a lookup against the right-hand (inner) input for each row. SCAN_METHOD=BATCH forces Cloud Spanner to read data in blocks of rows rather than one row at a time, which is designed for large scans over static or infrequently updated datasets.
APPLY_JOIN outer table: Like HASH_JOIN, Spanner's APPLY_JOIN picks the left-hand table as the outer input. Placing the large Customers table first forces Spanner to iterate over millions of customer rows and probe Orders repeatedly instead of starting from the small filtered Orders set.SCAN_METHOD=BATCH applies to the entire query, including selective lookups and high-churn transactional data (such as active checkout orders), where row-oriented scanning actually performs better.OR predicates with parameterized IN UNNEST(@IdList), so plan compilation overhead persists.LIMIT clauses, point lookups, small scans, or data where more than roughly 10% of rows are frequently updated do not benefit from batch-oriented processing.SCAN_METHOD=BATCH at the statement level overrides Spanner's automatic heuristics across every table in the query, potentially degrading transactional read latency.You were executing a large analytical scan across an infrequently updated table and needed to enforce block processing at a specific table level while keeping the smallest dataset as the outer join input.
In Cloud Spanner, query parameters substitute dynamic values in SQL statements instead of hardcoding literals into query strings. Combining a single array parameter with the UNNEST operator flattens an input list of keys into selectable rows, while join ordering controls which table acts as the build input versus the probe input during a HASH_JOIN.
@IdList as a parameter allows Cloud Spanner to compile the execution plan once, cache it, and reuse it across invocations, eliminating repeated compilation overhead and lowering CPU costs.OR predicates: Using WHERE t.Key IN UNNEST(@IdList) evaluates a single parameterized array rather than parsing variable-length OR chains, simplifying query composition.HASH_JOIN build phase: In Cloud Spanner, HASH_JOIN always uses the left-hand table as the build table and the right-hand table as the probe table. Placing the heavily filtered Orders table first (and enforcing the order with FORCE_JOIN_ORDER=TRUE if the optimizer reorders it) ensures the in-memory hash table is built from the smallest row set.Orders dataset minimizes memory consumption during join execution.Unlike BETWEEN range filters, IN UNNEST(@IdList) only fetches the exact non-adjacent keys requested without scanning intermediate rows. Furthermore, unlike latency-focused hints that increase CPU saturation, parameterizing the query and minimizing the hash build input directly reduce both compute usage and processing overhead.
auto_parameter and USE_ADDITIONAL_PARALLELISM DoThe auto_parameter hint instructs Cloud Spanner to convert hardcoded literal values inside a SQL string into internal parameters even when the optimal query plan might depend on literal values. The USE_ADDITIONAL_PARALLELISM=TRUE hint directs Spanner to maximize parallel execution across table splits to minimize query latency.
USE_ADDITIONAL_PARALLELISM=TRUE optimizes for lowest latency, not lower compute cost; it actively consumes more resources in parallel and can saturate the CPU on database splits.OR chain inefficiency: Keeping dynamically concatenated OR predicates with varying numbers of elements still produces different SQL statement structures as list lengths change, limiting plan reuse compared to a single IN UNNEST(@IdList) parameter.Customers as the left-hand input in the HASH_JOIN leaves the large table as the build side.auto_parameter with concatenated user or application strings does not provide the security and escaping benefits of native application-level query parameters.You were running an infrequent, latency-sensitive batch or LIMIT scan where maximizing split parallelism was more important than conserving CPU capacity.
Using BETWEEN @min AND @max filters a column across a continuous range between two parameterized bounds. Placing the larger table on the left side of a join is a common optimization pattern in distributed analytics engines (such as BigQuery) to trigger a broadcast join where the smaller right-hand table is sent to every worker.
BETWEEN @min AND @max predicate forces Cloud Spanner to scan and evaluate every key value falling between the lowest and highest IDs, dramatically increasing I/O and CPU consumption.HASH_JOIN build table: In Cloud Spanner's HASH_JOIN implementation, the left-hand table is always selected as the build table and the right-hand table as the probe table. Keeping the large Customers table first forces Spanner to load the massive table into memory as the hash build input rather than the heavily filtered Orders table.BETWEEN only improves efficiency when keys in the list are consecutive or densely packed.HASH_JOIN requires the smaller, more selective table on the left.The item IDs formed a strictly contiguous sequence (such as {1, 2, 3, 4, 5}) and you were optimizing a BigQuery broadcast join rather than a Cloud Spanner HASH_JOIN.
Answer them yourself and get your score the moment you finish. No account needed.
Full access includes
Keep the momentum going with these hand-picked practice scenarios
Last updated Validated