Temp Segment Created Not In Temp Tbs During Creating Index Online

[mike9]

Originally posted by reydp
But come to think of this: If the datafile that is being written for the index uses the same disk where the data of the table is also written, there must be an I/O bottleneck during reading of the table and writing of the index? plus, what is claimed to be the temp segments written in INDEX tbs too.
That is why the best practice is to separate the TBS(datafile/disk) for TABLE and INDEX segments, which will also help performance for DMLs.

Out of the follwoing statment you should notice that there will not be such a bottlneck when creating an Index.

Originally posted by jmodic
And btw, that temporary segment that you observed in the INDEX tablespace was not used for sorting while building the index. The sorting is done in memory and if that isn't sufficient the intermediate sort batches are written down to another temp segment in TEMPORARY tablespace.

The temp sefgment in your INDEX tablespace is used only after all sorting is done and when oracle builds the actual index.

[slimdave]

Originally posted by reydp
But come to think of this: If the datafile that is being written for the index uses the same disk where the data of the table is also written, there must be an I/O bottleneck during reading of the table and writing of the index? plus, what is claimed to be the temp segments written in INDEX tbs too.
That is why the best practice is to separate the TBS(datafile/disk) for TABLE and INDEX segments, which will also help performance for DMLs.
But I am sure you are very much aware of this.

Think about the process here ... the data is read, the index entries are sorted, then the new index segment is written. The read of data and write of index do not occur at the same time, hence no bottleneck.

You will get the best performance by spreading both table and index over the maximum number of disks available, and maybe by considering a parallel index creation operation.

[reydp]

hi mike & dave,
Are you telling me guys that the sorted output is done in a single process only? No partial segments written? So if I have an index with size of about 2Gig, I should have also the same size with temporary tbs and/or memory to accomodate the requirements of writing the whole sorted segments before written into index?

regards.