Oracle database index failure

There is a table in the Oracle database. Using PLSQL to check that the index of the table is not dropped, but the data query on the table is very slow (the query time is about three times the original time ).

There is a table in the Oracle database. You can use PL/SQL to check that the index of the table is not dropped, but the data query on the table is very slow (the query time is about three times the original time ).

There is a table in the Oracle database. You can use PL/SQL to check that the index of the table is not dropped, but the data query on the table is very slow (the query time is about three times the original time ), then I rebuilt the index. Why is there a similar index loss?

It may be that your table is updated frequently, with too many fragments, and the index takes up too much space. The optimizer does not think it is necessary to use an index to scan the entire table. You can re-create an index, I just sorted out the fragments. Of course I used indexes again.

There are many cases where the index fails. For example, if the function is used on the left and the table is not analyzed, the cost of the index scan is higher than that of the full table scan, and the table is small. Specific analysis is required. You can determine based on the execution plan.

The following situations may cause index failure:
1) Direct import:
Imp with SKIP_UNUSABLE_INDEXES = Y
Or sqlldr with SKIP_INDEX_MAINTENANCE

2) ORA-1652/1653 error during index maintenance:
Sqlldr DIRECT = Y failes with ORA-1652 or 1653

3) The ROWID changes due to partition maintenance:
ALTER TABLE MOVE PARTITION
ALTER TABLE TRUNCATE PARTITION
ALTER TABLE SPLIT PARTITION

Index failure solution:
1) cause:
Indexes will be maintained during SQL * LOADER loading. Due to the large amount of data, exceptions may occur during SQL * LOADER loading, leading to ORACLE's inability to maintain indexes, leading to index failure, query and loading are affected.
Exceptions include killing the SQL * LOADER process during loading, restarting, and insufficient table space.

2) solution:
Re-Indexing
3) How to recreate an index
A) view the index type
Select t1.index _ name, t1.partitioned from Dba_Indexes t1
Where t1.Table _ Name = upper ('ccb _ COGNOS_PROD_BALANCE_AA ')
Whether the index name is a partitioned Index
GNOS_PROD_BALANCE_AA_N1 NO
B) Non-partition Index
Re-indexing: alter index cin. CCB_COGNOS_PROD_BALANCE_AA_N1 rebuild Nologging
C) Partition Index
Find the invalid partition index:
Select t. Index_Name, t. Partition_Name, t. Tablespace_Name, t. Status
From Dba_Ind_Partitions t
Where t. Index_Name = 'cmz _ LOCAL_IDX_2'

Rebuild all indexes in the unusable status
Alter index name
Rebuild partition name
TABLESPACE name
NOLOGGING

ORACLE index failure Solution

Recently, we encountered the following problem: an index was added to the PROD_PARTS table:

Create index IDX_PT_DV_ID on PROD_PARTS (DEVICE_ID );

However, when the DEVICE_ID field is used for query, it is found that the index is not used:

SELECT * FROM PROD_PARTS WHERE device_id = 122511619;
Execution Plan:
TABLE ACCESS FULL

After asking DBA, I found it was a data statistics problem. The specific solution is to execute the following statement:

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;