Oracle Database 12c: Installation and Administration — Question 215

On your Oracle Database, you issue the following commands to create indexes:
SQL > CREATE INDEX oe.ord_customer_ix1 ON oe.orders (customer_id, sales_rep_id) INVISIBLE;
SQL> CREATE BITMAP INDEX oe.ord_customer_ix2 ON oe.orders (customer_id, sales_rep_id);
Which two statements are true? (Choose two.)

Answer options

• A. Only the ORD_CUSTOMER_IX1 index created.
• B. Both the indexes are updated when a row is inserted, updated, or deleted in the ORDERS table.
• C. Both the indexes are created: however, only ORD_CUSTOMERS_IX1 is used by the optimizer for queries on the ORDERS table.
• D. The ORD_CUSTOMER_IX1 index is not used by the optimizer even when the OPTIMIZER_USE_INVISIBLE_INDEXES parameters is set to true.
• E. Both the indexes are created and used by the optimizer for queries on the ORDERS table.
• F. Both the indexes are created: however, only ORD_CUSTOMERS_IX2 is used by the optimizer for queries on the ORDERS table.

Correct answer: B, F

Explanation

Statement B is correct because both indexes are updated automatically when rows in the ORDERS table change. Statement F is also correct, as the optimizer utilizes the BITMAP index (ORD_CUSTOMER_IX2) for query execution, while the INVISIBLE index (ORD_CUSTOMER_IX1) is not used. The other options are incorrect because they misrepresent the behavior of the indexes in relation to the optimizer and their presence.