resizing index

[panjub_raj]

I have an index whose size is 10 gig(when I quried bytes from dba_segments).
The index has currently used more than 300 extents right now. It has an initial extent of 50 mg and next_extent of 1 mg

I want to drop it and rercreate with an initial extent of 10gig and next extent of 10 gig. Will this be an over head problem by alloacting the next_extent to be 10 gig? In other words will this be a problem, period?

[pando]

you can create a 10GB extent if you have 10GB contiguos freespace in your tablespace however the nuber of extents doesnt affect that much the perfomance of the database though.
But I doubt your OS supports file size over 2GB? An extent cannot spam over several datafiles
10gb extent size would be definitely a crazy idea IMHO

[jdorlon]

You will probably not gain any performace by rebuilding this index if the only reason that you are rebuilding it is because of the number of extents. Why don't you just increase the next extent size?

If you still want to rebuild it...

It will take a long tme to drop and recreate an index this big. If you have 10G of free space in your tablespace, you can save some time by doing an 'alter index ... rebuild storage(initial 10240 M next 10240M)';

-John

[panjub_raj]

I thought a lot of extents like I have now fragments the indexes

[jdorlon]

Yes it does but that does not mean performance will suffer.

Think of it this way - even if your index is in one big extent,
what are the odds that this contiguous extent is actually contiguous on disk?

Unless you have so many extents in your tables and indexes
that it actually makes Oracle's data dictionary grow by a significant amount, it just doesn't matter.

A few years ago there was a lot of talk about how having all
your objects in once nice extent would help performance, but
the truth is, nobody really noticed any difference.

that is not to say that all fragmentation is something we can
ignore. Fragmentation at the tablespace level can lead to
wasted space, but now we have locally mananged tablespaces
to help with that problem.

-John