Examine this statement which has executed successfully:

Question

Exhibit

Options

• AExecution performance can be improved by using like instead of RLIKE.
• BThe statement takes advantage of index description_idx.
• CExecution performance can be improved by, using a composite index with column description as
• DNo index will improve statement performance.
• EExecution performance can be improved by adding an index on column description.

• A
  63% (40)
• B
  5% (3)
• C
  19% (12)
• D
  10% (6)
• E
  3% (2)

Explanation

Option A is correct because RLIKE (regex matching) cannot use B-tree indexes - the database engine must perform a full table scan to evaluate each row against the regular expression pattern. Switching to LIKE with a leading-literal pattern (e.g., LIKE 'abc%') allows the optimizer to perform an index range scan, dramatically reducing rows examined.

Why the distractors are wrong:

• B is wrong because even though description_idx exists, RLIKE bypasses it entirely - the index is present but unused.
• C is wrong for the same reason: a composite index still can't rescue a regex scan; the bottleneck is the operator, not the index structure.
• D is wrong because it assumes no index can help at all - but an index would help once the operator is changed to LIKE.
• E is wrong because an index on description likely already exists (description_idx); adding another won't fix an operator that ignores indexes.

Memory tip: Think of it as "RLIKE = Regex = Row-by-row" - all three start with R. Any operator that requires inspecting the full value of every row (regex, functions like LOWER(), SUBSTRING()) defeats index lookups. LIKE with a leading literal is the index-friendly alternative.

No community discussion yet for this question.