-
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
-
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
-
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
-
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
-
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 07:56 AM.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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"
-
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.
-
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
-
Are there inserts/updates/deletes going on against the bitmap indexed tables?
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|