Oracle Database index Expiration __ Database

There is a table in the database, using Pl/sql to see the index of the table has not been dropped, but the data on the table is slow (query time is about 3 times times the original), and then rebuilt the index is good, I would like to ask why, in what circumstances will be similar to the loss of index.

It may be that your tables are constantly being updated, too many fragments, too much space in the index, and the optimizer doesn't feel the need to use the index.
Just scan the entire table, ah, you re-build the index, you have to organize the pieces of Ah, of course, with the index

There are many cases of index invalidation, such as the function on the left , the table is not analyzed , the cost of index scan is higher than the whole table scan, the table is very small , and so on. Need specific analysis. You can judge according to the plan of execution.

******************************************

The following conditions can cause the index to fail:
1) Direct import:
Imp with Skip_unusable_indexes=y
or Sqlldr with Skip_index_maintenance

2 A ora-1652/1653 error occurred during the index maintenance process:
Sqlldr direct=y failes with ORA-1652 or 1653

3) Zoning maintenance causes rowID to change:
ALTER TABLE Move PARTITION
ALTER TABLE TRUNCATE PARTITION
ALTER TABLE SPLIT PARTITION

****************************************************
Index Expiration Problem Resolution:
1) Causes:
During the Sql*loader loading process, the index is maintained, because of the large amount of data, abnormal conditions occur during the Sql*loader loading process, which leads to the failure of Oracle to maintain the index, which causes the index to be in an invalid state, affecting query and loading.
Abnormal situation mainly include: in the loading process kill sql*loader process, restart, table space is not enough.

2) Solution:
Rebuilding indexes
3 How to rebuild the index
A) View index types
Select t1.index_name,t1.partitioned from Dba_indexes t1
where T1. Table_name=upper (' Ccb_cognos_prod_balance_aa ')
Index name is partition index
Gnos_prod_balance_aa_n1 NO
b) Non-partitioned index
Rebuild index: ALTER index CIN. CCB_COGNOS_PROD_BALANCE_AA_N1 Rebuild Nologging
c) Partitioned Index
Isolate the failed 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 with unusable status
ALTER Index Index name
REBUILD PARTITION Partition Name
Tablespace Table Space Name
Nologging

"Go" Oracle Index fail-through solution

A recent problem has been encountered: A new index was added to the Prod_parts table:

Create INDEX idx_pt_dv_id on prod_parts (device_id);

However, when querying using the device_id field, it was found that the index was not exploited:

SELECT * from prod_parts WHERE device_id = 122511619;
Execution Plan:
TABLE ACCESS Full

After consulting the DBA, found that the problem is data statistics, 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 to all indexed;