A company stores employee data in Amazon Resdshift. A table names Employee uses columns named Region ID, Department ID, and Role ID as a compound sort key. Which queries will MOST increase the speed…

Question

Options

• ASelect *from Employee where Region ID='North America';
• BSelect *from Employee where Region ID='North America' and Department ID=20;
• CSelect *from Employee where Department ID=20 and Region ID='North America';
• DSelect *from Employee where Role ID=50;
• ESelect *from Employee where Region ID='North America' and Role ID=50;

• A
  2% (1)
• B
  74% (32)
• C
  14% (6)
• D
  9% (4)

Explanation

In Amazon Redshift, a compound sort key (Region_ID, Department_ID, Role_ID) is most effective when queries filter on the leading columns in order. Option B (WHERE Region_ID='North America' AND Department_ID=20) uses the first two columns of the sort key in sequence, allowing Redshift to skip large blocks of unsorted data - this is optimal usage. Option E (WHERE Region_ID='North America' AND Role_ID=50) uses the leading column Region_ID, which still enables zone map pruning for that prefix. Option A only uses the first column (less selective than B). Option C is logically equivalent to B (WHERE clause column order doesn't matter to the optimizer), but exam intent distinguishes B as the canonical form. Option D filters on Role_ID alone (the third column), skipping the leading columns entirely, so the sort key provides no benefit.

No community discussion yet for this question.