SnowPro Advanced: Architect free question 9 / 10 · Domain 4: Performance Optimization
The table SALES is defined with CLUSTER BY (sale_date), and the table EVENTS has no clustering key. To check the clustering state and error handling, the following four statements (A to D) were run. Which two of them produce errors? A: SELECT SYSTEM$CLUSTERING_INFORMATION('sales'); B: SELECT SYSTEM$CLUSTERING_INFORMATION('sales', '(region_id)', 5); C: SELECT SYSTEM$CLUSTERING_INFORMATION('events'); D: SELECT SYSTEM$CLUSTERING_INFORMATION('events', '(event_type)');
- A and D, because each leaves out or supplies the columns in the opposite way from what is expected
- A and B, because both are called against the table that has a key
- C and D, because both are called against the table that has no clustering key
- B and C, because B gives both columns and a count and C omits the columns on a table without a key
Show the answer and explanation
Answer: D
Why this is correct
The second argument of SYSTEM$CLUSTERING_INFORMATION takes either a list of columns or expressions or a number of clustering errors to return, but not both. The documentation says both cannot be included in a single call, so B, which lists a column and a count, produces an error. For a table without a clustering key, the column argument is required and omitting it returns an error, so C, which omits it on EVENTS (which has no key), also produces an error.
Why the other options are wrong
"A and B, because both are called against the table that has a key" … B produces an error, but A does not. SALES in A has a clustering key, and for a table with a key the column argument can be omitted; the information is returned for the defined key (sale_date). The other error is C, which omits the columns on EVENTS, a table without a key.
"C and D, because both are called against the table that has no clustering key" … C produces an error, but D does not. The column argument can be used with any column of a table whether or not a key is defined, and the documentation says it can be used to decide future clustering. D, which passes the column in parentheses, returns information even for EVENTS. The other error is B, which passes both arguments.
"A and D, because each leaves out or supplies the columns in the opposite way from what is expected" … A omits the column on a table that has a key, and D passes a column on a table that has no key; both return information as the rules allow. This reads the rules about keys and arguments backward in both cases.
Key point
Put the column specification in parentheses even for a single column. If the count is omitted, the clustering_errors field of the returned JSON holds the most recent ten. By passing a list of candidate columns, you can check how well data clusters on them before you change the key.