SIZING INDEXES

Eventually cobbled this together for anyone who's interested !

spool c:\sql\index_size.lst
set pagesize 1000
col segment_name format a50
select segment_name, ROUND(sum(bytes)/1024/1024, "IDX SIZE in M"
from dba_segments
where segment_type = 'INDEX'
and tablespace_name = 'INDEX_TBSP'
group by segment_name
order by 2 DESC,1 ASC
/
spool off

Cheers Again
Eddy

If yoy are confident that Index does not have corrupted block, then you can speed up the index creation process by running "ALTER INDEX ... REBUILD" command with COMPUTE STATISTICS. You do not need to run analyze command after index is rebuilt.
Also examine parallelism with NOLOGGING option on a large indexes.
If possible, turn off archive process.

First of all, you do not need to rebuild all the 650 indexes in one night. You can do it in 4 or 5 nights.

FYI for those using the script - this one is correct with respect to syntax (missing a right parens and had a comma):

select segment_name, ROUND(sum(bytes))/1024/1024 "IDX SIZE in M"
from dba_segments
where segment_type = 'INDEX'
and tablespace_name = 'INDEX_TSP'
group by segment_name
order by 2 DESC,1 ASC

Eddy, 650 indexes is a lot of rebuilding. As you were advised, why don't you rebuild within a week, not within a night? If I have to do your job, bacause of possible corrupted blocks, I would drop and rebuild but first I would turn off archivelog.

Cheers Stecal - can't understand why it dropped the parens and added the comma, as all I did was cut and paste it from the SQL buffer where it was working fine - sure it would have barfed if it didn't - ODD !

I was planning on rebuilding over a few nights - the majority of the indexes are 3M and under. But the two main offenders are 200M and 1.5GB respectively. What would be the best wat to tackle a 1.5GB index rebuild - I suspect this will be the index that exponentially ballooned out of control when the patch was added and could ne only a very small in reality. If this is the case would it be preferable to drop and the create as opposed to rebuilding in terms of speed to rebuild it anyone ?

Thanks in advance
Eddy

Hi Tim,

So u hv rebuilt all the indexes in one go ?

I a running SAPR3. DB size is 200 GB.

I also need to rebuild the indexes.

What process u hv adopted :

- HV to dropped the indexes and rebuild those

or straightway u hv rebuilded those.

Please guide me on this ?

Thanks
Vijay