1Z0-071 practice question 17 of 35

Which two statements about INVISIBLE indexes are true?

Full set: 326 questions
Answer options
A: an INVISIBLE Index consumes no storage
B: You can only create one INVISIBLE index on the same column list
C: The query optimizer never considers INVISIBLE Indexes when determining execution plans
D: You use AlTER INDEX to make an INVISIBLE Index VISIBLE Correct answer
E: All INSERT, UPDATE, and DELETE statements maintain entries in the index Correct answer
Correct Answer:
D. You use AlTER INDEX to make an INVISIBLE Index VISIBLE; E. All INSERT, UPDATE, and DELETE statements maintain entries in the index
Explanation

An invisible index is a standard B-tree index that is maintained during all DML operations (INSERT, UPDATE, DELETE), ensuring it is always current. This confirms option E is correct. However, by default, it is ignored by the query optimizer when generating execution plans. Its primary purpose is to test the performance impact of dropping an index without actually removing it. The visibility of an index can be toggled between VISIBLE and INVISIBLE using the ALTER INDEX statement, which makes option D correct.

Why Incorrect

A. An invisible index is a physical database object that is fully maintained and therefore consumes storage just like a visible index.
B. You cannot create two indexes, regardless of visibility, on the exact same list of columns in the same order.
C. The optimizer can be instructed to consider invisible indexes by setting the OPTIMIZERUSEINVISIBLEINDEXES parameter to TRUE.

References

1. Oracle Database SQL Language Reference, 19c, CREATE INDEX. "An invisible index is maintained by DML operations, but it is not used by the optimizer by default."

2. Oracle Database Administrator’s Guide, 19c, Chapter 5, "Managing Indexes", Section "Making an Index Invisible". "You can make an index invisible to the optimizer... The database maintains the invisible index during DML statements."

3. Oracle Database SQL Language Reference, 19c, ALTER INDEX. "Use this statement to... change the visibility of an index (VISIBLE | INVISIBLE)..."

Scroll to Top