Truncate Table Solve Unusable Indexes

The usual way to solve an index that has been unusable state is to perform a rebuild of the index. Another less common way is to perform a truncate of the table that is associated index, of course,  we can not always truncate table.

An example;

 

TEST*ANR>  create table anr222 as select * from dba_objects;

Tabla creada.

 

TEST*ANR> create index anr222_inx on anr222 (object_id);

Índice creado.

 

TEST*ANR> select status from dba_indexes where index_name =’ANR222_INX’;

STATUS

________

VALID

 

TEST*ANR> SELECT COUNT (*) FROM  anr222;

COUNT(*)

__________

59168

 

TEST*ANR> ALTER INDEX ANR222_INX UNUSABLE;

Índice modificado.

 

TEST*ANR>  select status from dba_indexes where index_name =’ANR222_INX’;

STATUS

________

UNUSABLE

 

TEST*ANR> TRUNCATE TABLE anr222;

Tabla truncada.

 

TEST*ANR> SELECT COUNT (*) FROM  anr222;

COUNT(*)

__________

0

 

TEST*ANR> select status from dba_indexes where index_name =’ANR222_INX’;

STATUS

________

VALID

 

 

HTH – Antonio NAVARRO

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s