Re: changing initial extent size with hash partitioned index

there's no need to be rude.
version is 8.1.7.4.0, platform is aix5.1

my question has more to do with the fact that when rebuilding a non-partitioned or range partitioned index, an initial extent size is allowed, and with a hash partitioned index it is not (apparently).

if you can't post a well-mannered constructive follow-up, then you shouldn't at all. -maurice
samuels_at_seas.upenn.edu

Howard J. Rogers (hjr_at_dizwell.com) wrote:
: 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 - 18:29:29 CDT