-
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.....
--------------------------
The Time has come ....
-
-
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
I remember when this place was cool.
-
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]
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|