1Z0-027 · Question #36
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…
The correct answer is A. You must retain the index and set the constraint to DISABLE NOVALIDATE RELY to enforce the constraint C. You must retain the index and make it visible again for the constraint to be enforced. 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…
Question
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?
Options
- AYou must retain the index and set the constraint to DISABLE NOVALIDATE RELY to enforce the constraint
- BYou may drop the index and use a constraint with the DISABLE NOVALIDATE RELY flags
- CYou must retain the index and make it visible again for the constraint to be enforced.
- DYou may drop the index and make the constraint invisible, because this is enough for the constraint to
- EYou may retain the index, and leave it as invisible, because this is enough for the constraint to be recognized.
How the community answered
(40 responses)- A78% (31)
- B13% (5)
- D3% (1)
- E8% (3)
Explanation
- 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.
Topics
Community Discussion
No community discussion yet for this question.