How does Oracle determine whether an index is invalid?

In Oracle, you can use the "select status from user_indexes where index_name='index name';" statement to determine whether the index is invalid; if the returned result is VALID, it means that the index is not invalid, otherwise it means that the index is invalid.

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

How oracle determines whether the index is invalid

The syntax is as follows:

select status from user_indexes where index_name='索引名称'；

If the return result is VALID, it means that the index is valid!

The example is shown in the figure:

Extended knowledge:

Solution to index failure

1. Choose a suitable Oracle optimizer

There are three types of Oracle optimizers:

a. RULE (rule-based) b. COST (cost-based) c. CHOOSE (selective) ).

Set the default optimizer through various declarations of OPTIMIZER_MODE parameters in the init.ora file, such as RULE, COST, CHOOSE, ALL_ROWS, FIRST_ROWS. Of course you also override it at the SQL statement level or session level.

In order to use the cost-based optimizer (CBO, Cost-Based Optimizer), you must run the analyze command frequently to increase the accuracy of object statistics in the database.

If the optimizer mode of the database is set to selective (CHOOSE), then the actual optimizer mode will be related to whether the analyze command has been run. If the table has been analyzed, the optimizer mode will automatically become CBO. Otherwise, the database will use the RULE form of the optimizer.

(Analyzing table

analyze table PROD_PARTS compute statistics;
ANALYZE TABLE PROD_PARTS COMPUTE STATISTICS FOR ALL INDEXED COLUMNS;
analyze table PROD_PARTS compute statistics for table for all indexes for all indexed columns;

) [After an index failure, after consulting the DBA, I found that it was a problem with data statistics. The specific solution is to execute the above statement]

By default, Oracle uses the CHOOSE optimizer. In order to avoid unnecessary full table scans, you must try to avoid using the CHOOSE optimizer and directly use a rule-based or cost-based optimizer.

2.‍Rebuild the index

‍alter index 索引名 rebuild 【online】

3. Force index

After adding hint to the statement, force it to use the 'RECORD_ENTITYID' index

The sql statement becomes like this

Quote

select /*+ index(record,record_entityid) */ *
from RECORD
where entityId='24' and entityType='blog';

/* index(record,record_entityid) */ In, index means that index is forced to be used, record is the table name, and record_entityid is the index name. The execution plan is consistent with the test database, both using the 'RECORD_ENTITYID' index, and the logical read and write is also 4.

After testing, after analyzing the table and the two indexes without adding hints, the 'RECORD_ENTITYID' index can also be used. However, because the table is updated quite frequently, I don’t know how long it will take to analyze it again

Recommended tutorial: "Oracle Video Tutorial"

The above is the detailed content of How does Oracle determine whether an index is invalid?. For more information, please follow other related articles on the PHP Chinese website!