Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Parallel index rebuilds: strange results?

Parallel index rebuilds: strange results?

From: Kevin Brand <kbrand_at_gtetel.com>
Date: 19 Apr 1998 15:58:54 GMT
Message-ID: <01bd6bab$04c2d920$fc7f3f8f@khome.gtetel.com>

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 Received on Sun Apr 19 1998 - 10:58:54 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US