how to determine space gain before Index rebuild

[Nugpot]

Hi Friends,

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

Thanks
VB

[SANJAY_G]

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

[Nugpot]

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

[vijay8282]

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.....

[Nugpot]

Thanks Vijay

[Mr.Hanky]

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

[Shestakov]

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]