1Z0-060 · Question #167
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…
The correct answer is B. Both the indexes are created: however, only ORD_CUSTOMER_IX2 index is visible. E. Both the indexes are updated when a new row is inserted, updated, or deleted in the ORDERS. An invisible index is created in the data dictionary and maintained by DML but hidden from the optimizer, while the visible bitmap index is used normally - both indexes are kept current on every row change.
Question
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.)
Options
- AThe optimizer evaluates index access from both the indexes before deciding on which index to
- BBoth the indexes are created: however, only ORD_CUSTOMER_IX2 index is visible.
- COnly the ORD_CUSTOMER_IX1 index is created.
- DOnly the ORD_CUSTOMER_IX2 index is created.
- EBoth the indexes are updated when a new row is inserted, updated, or deleted in the ORDERS
How the community answered
(29 responses)- A17% (5)
- B72% (21)
- C7% (2)
- D3% (1)
Why each option
An invisible index is created in the data dictionary and maintained by DML but hidden from the optimizer, while the visible bitmap index is used normally - both indexes are kept current on every row change.
The optimizer does not evaluate invisible indexes for access path decisions unless OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE, so only ORD_CUSTOMER_IX2 is considered - not both.
The INVISIBLE keyword causes ORD_CUSTOMER_IX1 to exist in the data dictionary but be hidden from the optimizer unless OPTIMIZER_USE_INVISIBLE_INDEXES is set to TRUE, while ORD_CUSTOMER_IX2 is a standard visible bitmap index; both are created but only ORD_CUSTOMER_IX2 is considered by the optimizer.
Both indexes are created successfully; the INVISIBLE clause does not prevent index creation, it only hides ORD_CUSTOMER_IX1 from the optimizer.
Both indexes are created; the INVISIBLE clause on ORD_CUSTOMER_IX1 only affects optimizer visibility, not whether the index itself is built and stored.
Oracle maintains all indexes, including invisible ones, during INSERT, UPDATE, and DELETE operations on the base table so they remain consistent and are immediately usable if visibility is later changed.
Concept tested: Oracle invisible index visibility and DML maintenance behavior
Source: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/admin/managing-indexes.html
Topics
Community Discussion
No community discussion yet for this question.