DEA-C02 · Question #103
A Data Engineer is working to optimize the performance of end-users' queries about automobile shipment data for a portal web application that generates dashboards for different clients. An internal te
The correct answer is B. SELECT SYSTEM$CLUSTERING_DEPTH('shipments', '(client_id, warehouse, C. SELECT SYSTEM$CLUSTERING_DEPTH('shipments', '(client_id,. B and C are correct because SYSTEM$CLUSTERING_DEPTH with an explicit column expression is the right tool for comparing how well a specific combination of columns clusters a table - and both B and C include client_id (the required anchor column) alongside other candidate columns f
Question
A Data Engineer is working to optimize the performance of end-users' queries about automobile shipment data for a portal web application that generates dashboards for different clients. An internal team queries the information using order_date as a where condition; the ingestion and natural clustering are performed in this column. However, the application recovers data using the client_id, warehouse, product_id, and destination_city. The Engineer needs to create materialized views with different cluster keys, and must choose two columns combined with client_id to optimize the first cluster key. Which statements will provide all the information needed to select the MOST effective combination of columns to cluster? (Choose two.)
Options
- ASELECT SYSTEM$CLUSTERING_DEPTH('shipments');
- BSELECT SYSTEM$CLUSTERING_DEPTH('shipments', '(client_id, warehouse,
- CSELECT SYSTEM$CLUSTERING_DEPTH('shipments', '(client_id,
- DSELECT SYSTEM$CLUSTERING_DEPTH('shipments', '(warehouse, product_id,
- ESELECT SYSTEM$CLUSTERING_INFORMATION('shipments', 3);
How the community answered
(15 responses)- A7% (1)
- B73% (11)
- D13% (2)
- E7% (1)
Explanation
B and C are correct because SYSTEM$CLUSTERING_DEPTH with an explicit column expression is the right tool for comparing how well a specific combination of columns clusters a table - and both B and C include client_id (the required anchor column) alongside other candidate columns from the application's query pattern. By running both statements and comparing their average depth values, the engineer can determine which two-column pairing with client_id yields the lowest depth (lower = better clustering, faster queries).
Why the distractors fail:
- A calls
SYSTEM$CLUSTERING_DEPTHwithout specifying any columns, so it evaluates the natural clustering key (order_date) - useless for comparing new candidate keys. - D omits
client_identirely from the column expression, ignoring the requirement thatclient_idmust anchor every candidate cluster key. - E misuses
SYSTEM$CLUSTERING_INFORMATIONby passing a number (3) as the second argument; the function expects a column expression string, so this call is syntactically invalid.
Memory tip: Think of SYSTEM$CLUSTERING_DEPTH as your ruler - pass it the exact column combo you're testing, compare the measurements, and pick the shortest depth. If client_id must be in the key, it must be in the function call too. SYSTEM$CLUSTERING_INFORMATION is a different tool that gives a broader health report, not a head-to-head comparison.
Topics
Community Discussion
No community discussion yet for this question.