how to determine space gain before Index rebuild
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: how to determine space gain before Index rebuild

  1. #1
    Join Date
    Feb 2001
    Posts
    128
    Hi Friends,

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

    Thanks
    VB

  2. #2
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    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

  3. #3
    Join Date
    Feb 2001
    Posts
    128
    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

  4. #4
    Join Date
    May 2001
    Location
    Delhi
    Posts
    340
    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 ....

  5. #5
    Join Date
    Feb 2001
    Posts
    128
    Thanks Vijay

  6. #6
    Join Date
    Jan 2001
    Posts
    3,131
    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.

  7. #7
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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
  •  


Click Here to Expand Forum to Full Width