Re: changing initial extent size with hash partitioned index

samuels_at_red.seas.upenn.edu wrote:
> hi,
> i'm hoping someone can help me with this one.
> i have a hash partitioned index that i create with:
> create unique index uniq_order_num on order_num (customer_id)
> storage (initial 20m) pctfree 0
> local
> (partition h_order_1 tablespace index0,
> partition h_order_2 tablespace index0)
> /
>
>
>
> the underlying table is a hash partitioned table.
> my problem is, how do i change the initial extent size of the uniq_order_num index?
> do i have to drop completely and recreate?
> i've tried doing a rebuild (thinking that oracle would use the initial extent size default
> of the tablespace), but no luck here.
> thanks in advance.
> -maurice
> samuels_at_seas.upenn.edu

No version or platform, I see.

Why would you even *want* to alter a segment's initial extent? That's the first question.

The second question is, why are you mucking around with extent sizes when locally managed tablespace has made this sort of exercise a complete waste of time and effort?

But if there are no intelligent answers on those two, and you really, really insist on doing it, then yes, a drop and re-create is your only real option.

Had you mentioned you mentioned definitely that you were running 8i or above, I would perhaps have suggested an alter index X rebuild tablespace Y. That effectively is a 'move index' command. If you move something into a locally managed tablespace with particular uniform size settings, then the tablespace settings take precedence to some extent over the segment's settings.

In your case, your index is created with a 20M initial extent. If you were to 'move' that to an LMT with Uniform Size 60M, you would get a 60M initial extent. (On the other hand, if you were to move it to an LMT offering 1M extents, you would have been allocated 20 separate 1M extents).

Drop and re-create is clean and you know what you're getting. It's also bloody expensive, of course, and a complete waste of time if all you're going to do is re-create it with a 30M initial extent instead of its current 20M.

Regards
HJR Received on Wed May 19 2004 - 16:27:33 CDT