index rebuild

I have an index named idx_1 which i want to rebuilding.
First I used the query "Select * from dba_indexes where index_name='idx_1'; ". It returned blank row. Then I gave these 2 stms "Analyze index idx_1 compute statistics; Analyze index idx_1 validate structure; ". After that I again checked the status in dba_indexes where the row was now populated. After that I gave the cmd to rebuild "Alter index idx_1 rebuild". Now when I checked the status in dba_indexes the row returned the same values before rebuilding. Why so. Who will I know that the index is rebuild.

What is the purpose of Analyze compute statistics & Analyze validate structure here.

go RTFM

tahiti.oracle.com

Hi,

The purpose of "Analyze index" and "validate structure" is collecting statistics on indexes like actual usage of blocks within an index.
After "Analyze index" you can query dba_indexes to collect the statistics and after "validate structure" you can query index_stats.
To check if index is rebuilt ,you don't have to issue those commands.
You can examine the rebuilding of the index by quering from dba_segments after changing its storage or changing its tablespace.
e.g:
set linesize 1000
col seg_nam format a15
col part_name format a10
col type format a15
col tbs format a12
col EXTS format 99999
col blks format 999999
col next format 9999 heading 'next|(in Mb)'
col ext format 9999
col int format 9999 heading 'int|(in Mb)'
col MIN format 99
col MAX format 9999
select SEGMENT_NAME "seg_nam",partition_name "part_name",
SEGMENT_TYPE "type",TABLESPACE_NAME "tbs",
EXTENTS "exts",blocks "blks",INITIAL_EXTENT/1024/1024 "int",
NEXT_EXTENT/1024/1024 "next",
MIN_EXTENTS "min",MAX_EXTENTS "max"
from dba_segments
where owner='&owner'
and SEGMENT_NAME = '&index_name';

Now rebuild the index with different storage:
Alter index rebuild tablespace storage (initial xxx next yyy);

Now issue again the above query to see all the changes.

If the index was unusable ,after the rebuilding examine its status:
select status
from dba_indexes
where index_name='';

The result before the rebuilding: UNUSABLE
The result after the rebuilding should be: VALID

Regards,
Nir

What if I am rebuilding the index for deleted row entries & not for storage or ts. Than how will I check the status if the index is rebuild. Why the status in dba_indexes remains same (i.e, as when you analyzed the index) even after rebuilding it.

When the STATUS will be UNUSABLE in the dba_indexes.

http://www.peasland.net/Knowing%20wh...%20indexes.zip

... and for another point of view, try this thread.

Don't get confused between rebuild and coalesce. See Oracle documentation for details.

The (rather rare) situation described in the peasland article would be well addressed either by coalescing the index, or by using a reverse index (which would be my own preferred method).