alter index rebuild - where sorted ?
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: alter index rebuild - where sorted ?

  1. #1
    Join Date
    Jan 2004
    Posts
    11

    alter index rebuild - where sorted ?

    hi,

    i often ran into a situation to rebuild large indices (approx. 4GB) -and sometimes i ran into ora-01652.

    TBS is 6GB overall, 3.7GB are used, TEMP is about 7.6GB, 3GB are used.

    my question is how to determine in general a percentage of free space (e.g. 50%) to 'alter index rebuild' or a decision to prefer to drop and re-create them ?

    thanks in advance

  2. #2
    Join Date
    Jan 2003
    Location
    india
    Posts
    175
    hi,

    how much of RAM you have?

    if you have large RAM space you can set
    the sort_area_size to high.

    if the sort_area_size is not enough then the temp
    tablespace is used for sorting.

    -Raja

  3. #3
    Join Date
    Jan 2004
    Posts
    11

    Question still confused...

    hi Raja,

    << how much of RAM you have?
    6144 real/1866 free

    you mean sort_area_size parameter is mostly
    responsible for the 'alter index rebuild' operation ?

    not to sure because an unavailable extent raises the
    ora-01652 - and i know i need to increase TEMP for sure,
    but, since rebuilding of those indices takes a long time,
    i need to know exactly how to determine if available
    space should be sufficiant or not - imagine i need to
    reorganize those large indices in the evening and it's not
    really amusing to see the DDL was canceled in the early
    morning due to that error.

    thanks anyway

  4. #4
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    Hi,
    Since rebuilding of indexes does sorting operation, and most of the sorting operation happens in TEMP tablespace.You can avoid this sorting in TEMP tablespace as this will cause lot of I/O and take more time and also you might end up in "UNABLE TO ALLOCATE EXTENT" error.To avoid soriting in TEMP tablespace, set your sort_area_size=32MB, since this is happening in the memory, I/O would be less and rebuilding/ or creating of indexes would be faster.

    regards
    anandkl
    anandkl

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by anandkl
    Hi,
    Since rebuilding of indexes does sorting operation, and most of the sorting operation happens in TEMP tablespace....
    Rebuilding indexes does not require any sorting at all, or it might require only very small amount of sorting in case of online rebuild. At least in case when index is not in unusable state. Old index is allready sorted, so oracle only have to traverse all the leaf blocks and rewrite the keys into newly allocated extents.

    To the original poster: what are your reasons for rebuilding indexes? Maybe you should rather consider coalescing the index - in this case you won't run into ORA-1652, that's for sure.
    Last edited by jmodic; 01-06-2004 at 06:56 AM.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    From Metalink:
    As an index is being rebuilt, it uses a segment which is defined as a temporary segment for the rebuild. Once this segment is fully populated, the allocation of the old index is set to temporary and the populated temporary segment is redefined as a permanent segment with the index name.
    Now if the storage clause (next extent for example) for the existing index is set to a very high number (64MB or so) and you don't specify a storage clause with the rebuild command Oracle will use the storage clause of the existing index to allocate the space for the temporary segments.
    http://metalink.oracle.com/metalink/...&p_id=120360.1

    HTH
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by jmodic
    Rebuilding indexes does not require any sorting at all, or it might require only very small amount of sorting in case of online rebuild. At least in case when index is not in unusable state. Old index is allready sorted, so oracle only have to traverse all the leaf blocks and rewrite the keys into newly allocated extents.

    To the original poster: what are your reasons for rebuilding indexes? Maybe you should rather consider coalescing the index - in this case you won't run into ORA-1652, that's for sure.
    Using the "compute statistics" option on the index rebuild command causes the index to be rebuilt from the table, rather than from the old index structure, and I expect that would increase tempspace usage.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    Jan 2004
    Posts
    11

    thanks everyone

    hi all,

    thanks for all your proposals - think i'm on the way
    right now the problem is those indices were built
    by a thirdparty software called 'fazi' which
    provides phonetic searches using bitmap indices.

    because of extensive daily production those indices
    are to be rebuilded at least once a week, better twice

    but, all (administrative) operations are done by a (poor)
    GUI which is anything but stable and personally not
    trusted at all because of it's poor functionality so that
    a command-line related administration seems to be more
    preferable to me

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Are there inserts/updates/deletes going on against the bitmap indexed tables?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  10. #10
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by jmodic
    or it might require only very small amount of sorting in case of online rebuild.
    It do require quite a significant amount of sorting, as the data is read from TABLE for online...
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

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