Examine these commands that execute successfully: What must be done to allow the index to be used?

Question

Exhibit

Options

• AUse tublespace encryption instead of column encryption.
• BAdd the first_name column to the ix_employee Index to Improve its selectivity.
• CCreate a SQL baseline to preserve the execution plan from before the encryption.
• DEnable encryption hardware acceleration on the CPUs of the machine.

• A
  5% (1)
• B
  5% (1)
• C
  15% (3)
• D
  75% (15)

Explanation

Enabling hardware encryption acceleration (AES-NI CPU instructions) allows the database engine to decrypt encrypted column values at hardware speed during index lookups, making the cost acceptable to the query optimizer - without it, the decryption overhead makes index access more expensive than a full table scan, causing the optimizer to abandon the index entirely.

Why the distractors are wrong:

• A is wrong because tablespace encryption encrypts blocks on disk but decrypts them transparently in the buffer cache, so it doesn't solve the per-value decryption overhead that prevents index use in column-level encryption scenarios.
• B is wrong because selectivity is irrelevant here - the index can't be used due to encryption cost, not because it's a poor candidate statistically.
• C is wrong because a SQL plan baseline would preserve a plan that was using the index, but the root cause (CPU overhead making the index prohibitively expensive) remains unaddressed, and the optimizer would still not follow it.

Memory tip: Think of it as a toll road - the index is the fast route, but encryption is the toll. Without a hardware E-ZPass (AES-NI), the toll cost makes drivers (the optimizer) take the slow road (full scan) instead. Hardware acceleration makes the toll essentially free.

No community discussion yet for this question.