DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: index extent

  1. #1
    Join Date
    Nov 2000
    Posts
    416
    Some indexes have several extent in my prod DB ( extent > 100 ) how can I get rid off these extents?

    An ounce of prevention is worth a pound of cure

  2. #2
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    once option is to drop and recreate the index with proper storage parameter.This will also enable u to rebuild the index which will increase the performance of the database

    cheese
    anandkl
    anandkl

  3. #3
    Join Date
    Feb 2002
    Posts
    27
    The only possible work arond is

    drop the index and create with larger INITIALEXTENT and
    PCTINCREASE ( decide PCTINCREASE only after considering space in ur INDEX tablespace)


  4. #4
    Join Date
    Feb 2001
    Posts
    389
    What does it matter , what is the height of the index,is it unbalanced and do u have large number of del_lf_rows,
    If yes , then u rebuild them.

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Instead of drop and create, use ALTER INDEX ..MOVE .
    This is simpler and faster than any other method.

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    The above is of course a typo. With indexes you use ALTER INDEX ... REBUILD ... , not MOVE as with tables.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Nov 2000
    Posts
    416
    If I use rebuild command then I can't change INIALEXTENT I think? Also do I have to use nologging and also can you iron out what do you mean by : decide PCTINCREASE only after considering space in ur INDEX tablespace ? what is the criteria for this.
    An ounce of prevention is worth a pound of cure

  8. #8
    Join Date
    Feb 2001
    Posts
    290
    select 'alter index '||owner||'.'||segment_name||' rebuild unrecoverable tablespace '||tablespace_name||
    ' storage ( '||chr(10)||' initial '||bytes||' next '||bytes / 4||' pctincrease '||pct_increase||
    ' minextents 1 maxextents 100) pctfree 0 ;'
    from dba_segments
    where owner=upper('&SchemaOwner') and segment_type='INDEX' ;


    The Above script will generate a another script , which can be used to rebuild all the indexes in a schema , and you can also use it for single INDEX in a particular schema.

    And the Initial extent size would be the present size of the index and with this you will never have extents problem.

    And your doubt regarding about the initial extentent is also solved.

    test this script in u r R&D box and you can safely implement in production..

    Hope this will help you out..

    thanks,
    Madhu

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by farrokhp
    If I use rebuild command then I can't change INIALEXTENT I think?
    You can change *all* storage parameters when using index rebuild, including INITIAL.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  10. #10
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763
    Could you free the free blocks in the index with the rebuild command too?

    I have some free block under the HMW, the i tried to rebuild the index, but after this it continues to have very free blocks. Is it mandatory for this to DROP and RECREATE the index?

    Regards

    Angel

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