Re: index creation -- what temp space is used

Saad Ahmad (sahmad_at_mfa.com) wrote:
: When creating indexes, where does the temporary info
: go.
 

: I thought it would use the user's temporary tablespace
: but it looks like it also creates stuff in the tablespace
: that is being worked on for creating index...
: And when it is creating temporary segments what
: initial/next extents does it use

When creating an index, a segment that looks like a temporary segment is allocated on the tablespace that you specified to create the index. This temporary segment is actaully the index segment, but it is not made into such until the index creation is complete. Thus, if the index creation is interrupted and aborted, smon knows it is a temp segment and clean it up as such, instead of treating it as a half complete index segment. you will always see this temporary segment created when creating an index.

This is separate from the temporary segments that _may_ be created in the user's temp tbspc. These temp segments are created to temporarily hold subsets of sorts on the table to be indexed. These temporary segments will be created only if Oracle cannot do this sort for index creation completely in memory.

Johnny Chan
Indepedent Oracle Specialist Received on Tue Sep 05 1995 - 00:00:00 CEST