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?
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.
Explanation:
Not A: Both indexes are created fine.
B: The invisible index ORD_CUSTOMERS_IX1 and the bitmap index are both updated by DML operations on
the Orders table.F: Since ORD_CUSTOMERS_IX1 is invisible only ORD_CUSTOMERS_IX2 is used by the query optimizer.
Not C,Not D,Not E:
* ord_customer_ix1 is an invisible index and is therefore not used by the optimizer.
* VISIBLE | INVISIBLE Use this clause to specify whether the index is visible or invisible to the optimizer. An
invisible index is maintained by DML operations, but it is not be used by the optimizer during queries unless you
explicitly set the parameter OPTIMIZER_USE_INVISIBLE_INDEXES to TRUE at the session or system level.
Note: Specify BITMAP to indicate that index is to be created with a bitmap for each distinct key, rather than
indexing each row separately. Bitmap indexes store the rowids associated with a key value as a bitmap. Each
bit in the bitmap corresponds to a possible rowid. If the bit is set, then it means that the row with the
corresponding rowid contains the key value. The internal representation of bitmaps is best suited for
applications with low levels of concurrent transactions, such as data warehousing.