DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: INDEX REBUILD PROBLEM

Hybrid View

  1. #1
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865
    When I use
    ANALYZE INDEX index_name VALIDATE STRUCTURE;

    and then query INDEX_STATS, it shows the following...

    PRD > select pct_used, btree_space, used_space from index_stats;

    PCT_USED USED_SPACE BTREE_SPACE
    -------------- ---------------- ----------------
    97 3145821 3262400


    So, I REBUILD the index using the following command, as the MAX EXTENTS was 50.

    ALTER INDEX index_name REBUILD STORAGE (MAXEXTENTS 75);


    Now, when I query index_stats, after VALIDATE STRUCTURE, I get ...

    PCT_USED USED_SPACE BTREE_SPACE
    ------------- ---------------- ----------------
    90 3146383 3510276

    This is not the case always, some indexes when I follow the same REBUILD to change the storage settings, it does not reflect any change.

    Do you know why?

    Please help. Thanks

    With regards.

    [Edited by ggnanaraj on 07-19-2001 at 09:12 AM]

  2. #2
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865
    No reply?


  3. #3
    Join Date
    Jul 2001
    Location
    Sweden
    Posts
    2
    Hi,

    I have no idea why you get different figures after your rebuild. Maybe there are some types of holes in the data that get filled whe you rebuild. Maybe it's stored in a more sufficent way.

    But if you just want to change the maxextents you don't have to rebuild the index, just type

    ALTER INDEX index_name
    STORAGE (MAXEXTENTS 75);

    This should not show a change in the figures.
    And if you rebuild the index you should put all data in the first extent (if you like you can set new_next_extent=old_next_extent):

    ALTER INDEX index_name
    REBUILD
    STORAGE ( INITIAL (nr_of_extents-1)*old_next_extent+old_initial_extent NEXT new_next_extent PCTINCREASE 0 MAXEXTENTS 75);

    /Pontus

  4. #4
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865

    Question

    Originally posted by Pontus
    Hi,

    And if you rebuild the index you should put all data in the first extent (if you like you can set new_next_extent=old_next_extent):
    /Pontus

    Thanks for the reply.

    Can you please explain further, why I have to put all data in the first extent when I REBUILD the index?

    With regards.


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