Re: Mechanism of building index with parallel

David wrote:
> Hi all,
>
> COuld anyone provide me the information of how to handle the parallel
> option in building index in Oracle? For example, if I build an index with
> parallel 5, then 5 threads will do the job. Is 5 segments created (each
> doing a small part of data, and each is reading, sorting and writing), and
> then merge together? Or 5 threads each doing different part of job, such as
> one is reading the data, one is writing the data, one is sorting etc. I have
> the experience that when building index with parallel option, the file size
> need to build is larger (index tablespace, not temporary tablespace). When I
> build the index with noparallel, the index is created successfully without
> adding extra file on the index tablespace. Could anyone explain the reason
> behind this?
>

Hi,
IMHO 5 pairs of processes are aquired, a scanner and a sorter. The scanner operates on an rowid range.
The reason for the higher storage requirement is that for each parallel thread at least a extent is allocated, which is eventually shrinked when finished.
E.g:
The index requires 35 MB, thus using 2 20MB Extents when created sequentially. If created parallel(5), 5 50MB are allocated initially, which are then shrinked to ~5 MB each (if the work done by each thread is the same).

Stephan Received on Wed Feb 25 2004 - 07:38:54 CST