Data Engineering Optimizing Query Performance 5

Question 1

A correlated subquery executed once per outer row is best optimized by:

Accepted Answer

Rewriting it as a join or window function

Answer

Adding more subqueries

Answer

Removing the WHERE clause

Answer

Increasing the LIMIT

Question 2

EXPLAIN ANALYZE differs from plain EXPLAIN because it:

Accepted Answer

Actually runs the query and reports real timings

Answer

Only shows the table schema

Answer

Disables the optimizer

Answer

Caches the result permanently

Question 3

When estimated rows differ wildly from actual rows in a plan, the fix is usually to:

Accepted Answer

Update/refresh table statistics

Answer

Drop the table

Answer

Add a LIMIT clause

Answer

Switch to row storage

Question 4

Z-ordering / data clustering on commonly filtered columns helps by:

Accepted Answer

Co-locating related values so more files can be skipped

Answer

Encrypting the data

Answer

Forcing a full scan

Answer

Removing partition keys

Question 5

A query joining on columns of mismatched data types (e.g., string vs int) often slows down because:

Accepted Answer

Implicit casting prevents index use

Answer

It returns duplicate rows

Answer

It always broadcasts

Answer

It disables the WHERE clause

Question 6

Reducing shuffle volume in a distributed aggregation is best achieved by:

Accepted Answer

Pre-aggregating (combiner/map-side) before the shuffle

Answer

Increasing the number of output columns

Answer

Disabling partitioning

Answer

Sorting the final output twice

Question 7

An OR condition across two different columns sometimes prevents index use; a common rewrite is:

Accepted Answer

Splitting into a UNION of two indexable queries

Answer

Wrapping both columns in functions

Answer

Adding a cross join

Answer

Removing both predicates