Rebuild of Indexes not sort fragmentation
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Rebuild of Indexes not sort fragmentation

  1. #1
    Join Date
    Nov 2000
    Posts
    205
    Hello there,

    I used OEM and did a tablespace map, then I used the analysis of objects for it to show me the fragmentation. It listed a few indexes and I issued the alter index xxx rebuild.

    The rebuild completed and I checked via OEM and it showed it as still fragmented, directly after.

    What should I do?


    Please help as this is urgent..

    Thanks in advance,
    Nirasha

    [Edited by nirasha on 03-07-2001 at 12:20 PM]

  2. #2
    Join Date
    Nov 2000
    Posts
    205
    Please help with this..

    Nirasha

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    If you rebuild with ALTER INDEX xxx REBUILD there are very good chances you will get a new index consisting of the same number and same sizes of extents as your original index have had. Yo have to provide new storage parameters for an index if fragmentation is what you are fighting against.

    Use something like:
    ALTER INDEX xxx STORAGE (INITIAL xM NEXT xM PCTINCREASE0) REBUILD NOLOGGING;
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Nov 2000
    Posts
    205
    Thanks for that.

    If I extract the DDL for what I have now it looks like:
    Alter Index FNDRYR0200.CAT_ID_NAME_IX Rebuild
    pctfree 10
    initrans 2
    maxtrans 255
    tablespace INDX01
    storage (
    initial 587202560
    next 10485760
    pctincrease 0
    maxextents 2147483645
    )

    This is what the storage is at present. Any pointers as to what I should use to determine what the correct initial and next should be?

    Thanks in advance,
    Nirasha

  5. #5
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    Use SAFE(Simple Algorithm for Fragmentation Elimination) rules for your indexes also to avoid fragmentation. You can change your initial extent size for index without dropping and recreating it unlike for tables. Give appropriate size based on the size of the index tablespace for initial and next with PCTINCREASE 0 for Index tablespace. I hope you have read SAFE rules to avoid fragmentation. Search for PDF documnet here in this forum. You have bunch of posts on that.
    Reddy,Sam

  6. #6
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    [url]http://metalink.oracle.com/cgi-bin/cr/getfile_cr.cgi?239049[/url]
    Reddy,Sam

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