Re: Parallel index rebuilds: strange results?

Kevin Brand wrote:
>
> I've been re-building indexes with 'alter index rebuild' and have noticed
> that when I use a parallel clause, the final storage characteristics of the
> index are somewhat odd.
>
> The following statement should re-create the given index with 10 extents,
> each of which are 10 MB in size:
>
> alter index myindex rebuild storage ( initial 10M next 1024k ) parallel (
> degree 10 );
>
> While I get 10 extents, they are not 10M each. Here's the break down:
>
> ext_id bytes blocks
> 0 10485760 2560
> 1 1970176 481
> 2 1265664 309
> 3 1323008 323
> 4 1814528 443
> 5 1904640 465
> 6 2007040 490
> 7 1126400 275
> 8 1417216 346
> 9 1912832 467
>
> Looking at INDEX_STATS shows that btree_space is equal to the exact size
> requirements of the index ( I calculate this value by creating the index
> without any bells and whistles, then adding up the extents ) assuming
> pctfree=0, used_space = btree_space ( very close ), and pct_used = 100.
>
> The total size of the index is around 16M ( an initial extent around 1700k
> will work much better, but doesn't show the ?problem?), and the extents
> above add up to 24M. OK, but when I execute dbms_space.unused_space() on
> the index, I get 0 bytes. I also tried to deallocate unused space from the
> index and was informed that there were no bytes beyond the high water mark.
>
> Why did the parallel query coordinator not evenly distribute the extents as
> specified in the storage clause and where, in this example, is the lost
> space.
>
> Thanks
>
> -Kevin

Hi Kevin,

    Your extents show 1 extent at 10 meg, the remainding in the 1Meg per your allocations. When loading in parallel, each process will use the init storage you want (10meg) but for temp space, when it merges the outcome would be like you allocated. *10,1 * Received on Sun Apr 19 1998 - 21:43:35 CDT