Examine the output: SQL> SELECT index_name, status FROM dba_indexes WHERE status='UNUSABLE'; INDEX_NAME STATUS --------------------- ----------------- EINDUNUSABLE Which two statements about this…

Question

Options

• AIt is ignored by the query optimizer.
• BIt is not used while the index is being rebuilt.
• CThe index cannot be rebuilt, and has to be re-created.
• DThe index is automatically rebuilt when used the next time.

• A
  84% (21)
• C
  4% (1)
• D
  12% (3)

Explanation

B: as soon as index is rebuilt it becomes usable. But during the rebuild operation it is still not Unusable indexes An unusable index is ignored by the optimizer and is not maintained by DML. One reason to make an index unusable is to improve bulk load performance. (Bulk loads go more quickly if the database does not need to maintain indexes when inserting rows.) Instead of dropping the index and later re-creating it, which requires you to recall the exact parameters of the CREATE INDEX statement, you can make the index unusable, and then rebuild it. You can create an index in the unusable state, or you can mark an existing index or index partition unusable. In some cases the database may mark an index unusable, such as when a failure occurs while building the index. When one partition of a partitioned index is marked unusable, the other partitions of the index remain valid. An unusable index or index partition must be rebuilt, or dropped and re-created, before it can be used. Truncating a table makes an unusable index valid. When you make an existing index unusable, its index segment is dropped.

No community discussion yet for this question.