Examine these statements which execute successfully: Which two changes will improve this query performance?

Question

Exhibit

Options

• ACREATE INDEX 1X7 ON users (user_name) USING HASH;
• BCREATE INDEX 1X4 ON Locations (site_id, loc_shared);
• CCREATE INDEX IX1 ON locations (loc_shareci) ;
• DCREATE INDEX 1X6 ON users (user_name);
• ECREATE INDEX 1X3 ON locations <loc_site_id) ;
• FCREATE INDEX 1X2 ON locations (loc_mapping) USING HASH; fH
• GCREATE INDEX 1X5 ON users (loc_id);

• A
  77% (27)
• B
  11% (4)
• E
  6% (2)
• F
  3% (1)
• G
  3% (1)

Explanation

Options A and D are correct because both create indexes on the user_name column of the users table - the column the underlying query most likely filters or joins on. Option D creates a standard B-tree index (the default, best for range and equality searches), while Option A adds a HASH index on the same column, optimizing equality lookups specifically. Together they address the most performance-critical access path in the query.

The distractors fail for distinct reasons: E has a clear syntax error (< instead of ( before the column name), so it would not execute successfully. C references a misspelled column (loc_shareci), making it invalid against the actual schema. B uses site_id, a column that doesn't exist in Locations (the correct column, based on other options, is loc_site_id). F indexes loc_mapping with HASH, which targets a column unrelated to the query's filter or join conditions. G attempts to index loc_id on the users table, but loc_id belongs to the locations table - a clear column-to-table mismatch.

Memory tip: Filter distractors in two passes - first eliminate anything with syntax errors or misspelled column names (they'd never run), then eliminate indexes on the wrong table or an irrelevant column. What's left should point directly to the column(s) the query actually touches.

No community discussion yet for this question.