You have altered an index supporting a constraint to be invisible on a large read only data
warehouse table, to determine it Smart Scan operations will be fast enough to satisfy your
performance requirements.
Given the results of your testing, you consider dropping the index.
Which two statements are true?
A.
You must retain the index and set the constraint to DISABLE NOVALIDATE RELY to enforce
the constraint
B.
You may drop the index and use a constraint with the DISABLE NOVALIDATE RELYflags
C.
You must retain the index and make it visible again for the constraint to be enforced.
D.
You may drop the index and make the constraint invisible, because this is enough for the
constraint to be enforced.
E.
You may retain the index, and leave it as invisible, because this is enough for the constraint to
be recognized.
Explanation:
Note:
*You may have noticed that we introduced Invisible Indexes as an 11g New Feature. Their main
benefit is that we can test whether performance differs if we would drop an index without actually
dropping it. This is particular useful after an Exadata Migration because we expect that some
conventional indexes migrated are now obsolete and may be substituted by Storage Indexes.
*With making indexes invisible, we can easily check whether indexes are useful without having to
drop (and in case recreate) them actually. While this may be of interest for “ordinary” Oracle
Databases already, it is particular a useful feature for Exadata where we expect some
conventional indexes to become obsolete after a migration.
*DISABLE NOVALIDATE RELY means: “I don’t want an index and constaraint checking to slow
down my batch data loading into datawarehouse, but the optimizer can RELY on my data loading
routine and assume this constraint is enforced by other mechanism”. This information can greatly
help optimizer to use correct materialized view when rewriting queries. So if you don’t use
materialized views for query rewrite then you can put RELY for all your constraints (or NORELY
for all your constraits) and forget about it.
For me A E.
Let’s asssume that constraint is a PK. Than to enforce it we need to have an index (unique) , visible or not – invisible index is still maintained by oracle. This mean C is not correct but E is. When PK constraint is rely disable novalidate, index is dropped automatically, unless use keep index clause – A correct.
I’m totally confused. The question us about to drip the ibdexes, but the answer us to retain them?
A is right. DISABLE NOVALIDATE RELY will let query rewrite and MV know about the constraint but drop the index
B is right. a constrint with DISABLE NOVALIDATE RELY will have no index to support it but is known to query rewrite and MV
C is wrong. you don’t need that index
D is wrong. you can not make constraint invisible
E is wrong. you are dropping the index
So A.B. are correct