Upgrade to Oracle Database 12c — Question 33

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) 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. The optimizer evaluates index access from both the indexes before deciding on which index to use for a query execution plan.
• B. Both the indexes are created: however, only ORD_CUSTOMER_IX2 index is visible.
• C. Only the ORD_CUSTOMER_IX1 index is created.
• D. Only the ORD_CUSTOMER_IX2 index is created.
• E. Both the indexes are updated when a new row is inserted, updated, or deleted in the ORDERS table.

Correct answer: B, E

Explanation

Statement B is correct because the ORD_CUSTOMER_IX1 index is created as invisible, making only the ORD_CUSTOMER_IX2 index visible for querying. Statement E is also correct since both indexes will be updated with changes made to the ORDERS table, regardless of the visibility of the first index. The other options are incorrect as they either misrepresent the visibility of the indexes or the creation status.