On your Oracle 12c database, you issue the following commands to create indexes: SQL> CREATE INDEX oe.ord_customer_ix1 ON oe.orders (customer_id, sales_rep_id); SQL> CREATE BITMAP INDEX…

Question

Options

• AOnly the ORD_CUSTOMER_IX1 index is created and it is visible.
• BBoth the indexes are created and both of them are visible.
• CBoth the indexes are created; however, only the ORD_CUSTOMER_IX1 index is visible.
• DBoth the indexes are created; however, only the ORD_CUSTOMER_IX2 index is visible.

• A
  15% (4)
• B
  11% (3)
• C
  4% (1)
• D
  70% (19)

Explanation

Oracle permits both a standard B-tree index and a bitmap index to exist on the same column set, so both ORD_CUSTOMER_IX1 and ORD_CUSTOMER_IX2 are physically created. However, Oracle enforces a visibility rule to prevent ambiguity and optimizer confusion: when a bitmap index is created on a column combination that already has a B-tree index, Oracle automatically marks the pre-existing B-tree index as INVISIBLE. An invisible index still exists in the data dictionary and is maintained by DML operations, but the query optimizer ignores it by default unless explicitly hinted. The newly created bitmap index (ORD_CUSTOMER_IX2) is visible and will be considered by the optimizer. This behavior makes choice D correct - both indexes exist, but only the bitmap index (ORD_CUSTOMER_IX2) is visible. Choice A is wrong because both indexes are created. Choice B is wrong because the B-tree index is not visible. Choice C has the visibility backwards - it is the bitmap index that is visible, not the B-tree index.

No community discussion yet for this question.