Oracle unusable index and unvisible index

1 Visibility

The visibility of the index (visibility) refers to whether the index is visible to the CBO optimizer, that is, whether the CBO optimizer considers the index when it generates the execution plan, and can be considered an attribute of the index. If one of the index visibility attributes is: invisible. By default, the CBO optimizer does not consider the index when it generates the execution plan, that's all. As with visible indexes, the database maintains the indexed data appropriately when a DML operation is performed. However, for the local index, we cannot set an invisible property for a partition alone, only the entire index can be set to an invisible property.

• Sets index visibility properties that can be specified when an index is created or later adjusted.

  --Specify an invisible property when creating an index CREATE INDEX index_name on table_name (column_list) invisible;--late adjustment visibility alter
   Index index_name invisible;alter index index_name visible; 

• related parameters   optimizer_use_invisible_indexes   This parameter controls whether invisible index is considered when the optimizer generates the execution plan. The parameter value is type bool, and when set to true, the CBO will consider using invisible index. The default is False. This parameter can be set at the session or at the system global or instance level.

  sql> select Isses_modifiable,issys_modifiable,isinstance_modifiable from V$parameter where name= ' optimizer_use_invisible_indexes ' isses issys_mod isins-------------------TRUE IMMEDIATE Truealter Session Set Optimizer_use_invisible_indexes=true;alter system set optimizer_use_invisible_indexes=true sid= ' * ' Scope=both; 

• when SQL optimization is performed, it is unclear whether an index's creation or deletion has an effect on the system, at which point we can set the index to the inbisible state, then test the observation and, if consistent with the expectation, set the index to the main visible, or delete it. The invisible index is a difficult way for us to anticipate.

2 Availability

Index availability (usable), under normal circumstances, the index is available. When an index is unavailable (unusable), Oracle splits the index metadata (that is, the basic information contained in the creation statement) from the actual physical data, and the relevant data blocks can be reused. Whether the index segment has been created (dba_indexes.segment_created) This property will change to no (normally, yes). Unusable index, to be reused, there is only one way: rebuild. Of course, you can drop it and create it first.

• Generally, the CBO does not consider an index that uses the unusable state, including a partitioned table.
• When an index is unavailable, Oracle clears the index data and no longer maintains the
• Local index, you can set the index of a partition to unusable.  
• related parameters   skip_unusable_indexes

  parameters	controls non-unique indexes on unusable, whether or not index data is maintained in DML operations
  	true | false

Note

In the

case of a unique index, when DML is used, Oracle verifies index availability and cannot guarantee data uniqueness if the index is not available.

Oracle unusable index and unvisible index