how to determine space gain before Index rebuild

Hi Friends,

Is there a way to determine the space I may gain by rebuillding an index before I really rebuild it.

Thanks
VB

Use this query before and after rebuilding index.

select bytes, extents, initial_extent, next_extent from dba_segments where segment_name = 'your_index_name';

Sanjay

Thanks Sanjay, but I was wondering if there is a way to find out the space I may gain before I rebuild the index.

Vb

Hi,

Gaining of space will depend on following

- no of deleted rows
- height

After validating the index you can check its space stats, and can calculate space used by per row. than by using some maths u can get the IDEA of the space that u'll gain after rebuilding.

Exact match is not possible....


Any other answers from gurus.....

Thanks Vijay

This poses the question "WHY?", if the index is fragmented, re-build it. You can view the space gains in OEM by looking at the space used for that tablespace before and after.

Are you re-building to regain space or to correct fragmentation?

MH

About estimate size of index extent after rebulid:

In first: u have to know type of indexes
-- compressed or not compressed
-- b+ or bit map
In second: (and this is more interesting thing) u have to know
HOW ROWS WITH SOME INDEX KEY VALUES DISTRIBUTED IN REAL TABLE.
Why:
index segment (not compressed) can keep 2 kind of entry:
1) key value + rowid
2) key value + (rowid_start, rowid_end)

In this case we have (NOTE with the same # of rows in 1 table block)

1 variant (in 1 db block):
1 row -- key1
2 row -- key1
3 row -- key2
4 row -- key2
5 row -- key1
6 row -- key2
7 row -- key1
8 row -- key2
9 row -- key2

in index we will have --> 6 entries

2 variant (in 1 db block):
1 row -- key1
2 row -- key1
3 row -- key1
4 row -- key2
5 row -- key2
6 row -- key2
7 row -- key2
8 row -- key2
9 row -- key1

in index we will have --> 3 entries

So field cluster_factor from all_indexxes in this case doesn't work.
This is one of the main problem with estimation of size of index extents.



[Edited by Shestakov on 09-04-2002 at 11:38 AM]