What is the outcome of the above command?

View the Exhibit for some of the parameter settings. You start a session and issue the
following command:
SQL>CREATE INDEX emp_ename ON emp(ename)
TABLESPACE users INVISIBLE;
What is the outcome of the above command?
Exhibit:

View the Exhibit for some of the parameter settings. You start a session and issue the
following command:
SQL>CREATE INDEX emp_ename ON emp(ename)
TABLESPACE users INVISIBLE;
What is the outcome of the above command?
Exhibit:

A.
The index is not used by the optimizer but is maintained during DML operations.

B.
The index is not used by the optimizer and is not maintained during DML operations.

C.
The index is used by the optimizer only if a hint is specified in the query statement and is
maintained during DML operations.

D.
The index is used by the optimizer only if a hint is specified in the query statement but is
not maintained during DML operations.

Show Hint

Leave a Reply 2

Your email address will not be published. Required fields are marked *

Jake from SF

Jake from SF

Actually, A is still correct:

https://oracle-base.com/articles/11g/invisible-indexes-11gr1

Oracle 11g allows indexes to be marked as invisible. Invisible indexes are maintained like any other index, but they are ignored by the optimizer unless the OPTIMIZER_USE_INVISIBLE_INDEXES parameter is set to TRUE at the instance or session level. Indexes can be created as invisible by using the INVISIBLE keyword, and their visibility can be toggled using the ALTER INDEX command.

In the question, the diagram has OPTIMIZER_USE_INVISIBLE_INDEXES = FALSE. Thus, optimizer will not use this index.

Reply