On your Oracle 12c database, you Issue the following commands to create indexes
SQL > CREATE INDEX oe.ord_customer_ix1 ON oe.orders (customers_id, sales_rep_id)
INVISIBLE;
SQL> CREATE BITMAP INDEX oe.ord_customer_ix2 ON oe.orders (customers_id, sales_rep_id);
Which two statement are correct?
A.
Both the indexes are created; however, only the ORD_COSTOMER index is visible.
B.
The optimizer evaluates index access from both the Indexes before deciding on which index to
use for query execution plan.
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.
Explanation:
11G has a new feature called Invisible Indexes. An invisible index is invisible to the
optimizer as default. Using this feature we can test a new index without effecting the execution
plans of the existing sql statements or we can test the effect of dropping an index without dropping
it.
The answer is right: A & E.
However, the wording of option A should be:
“Both the indexes are created; however, only the ORD_COSTOMER_IX2 index is visible.”
A – this is TRUE – in O12c – this is possible if 2 indexs different type like B-tre and bitmap
B – FALSE – optimizer dont take invisible index if OPTIMIZER_USE_INVISIBLE_INDEXES is not set to TRUE
C – FALSE
D – FALSE
E – this is TRUE – DEFAULT is false nad Invisible indexes will not be considered by the optimizer but will still be maintained by DML operations.
Right explation:
The answer is right: A & E.
However, the wording of option A should be:
“Both the indexes are created; however, only the ORD_COSTOMER_IX2 index is visible.”
AE is final answer.
AE
This is repeat question.
A, E
Yeah AE are correct if A – should have said “ord_customer_ix2” visible.
It is possible to have 2 indexes in 12c on same set of column – but only one can be visible. There should differences between index type , uniqueness.
AE
A: miss invisible index ORD_COSTOMER_IX2