Re: Index rebuilding behavior

It seems you have two indexes of the same name. Try selecting the owner with the rest of the segment name to find out who owns the index that has been built in the V_IDS_INDEX tablespace.

Your Oracle block size appears to be 8K, and you won't see an extent fof 256K because Oracle usually rounds up to multiples of 5 blocks (and then may take a spare few) when allocating extents, so 280 K is the 'natural' size of an extent if you specify 256K with an 8K block size.

I am slightly puzzled by the 320K extent, because I would thought Oracle only grabbed extra fragments if this stopped a free extent of LESS THAN 5 blocks being left behind - it looks from your example as if that should be LESS THAN OR EQUAL TO 5 blocks - I'll have to check that.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

skubiszewski_at_Eisner.DECUS.Org wrote in message ...
>In article <37B19A59.2DDF145B_at_us.oracle.com>, Pete Sharman
<psharman_at_us.oracle.com> writes:
>>
>>Strange behaviour, this. I've yet to see an object that is not
partitioned
>>span tablespaces. Can you provide the statement you're running and the
output
>>from it?
>
>Sure can! I documented it as I went along. I'm just trying to
>understand the behavior of REBUILD. Here's how it went:
>
>Query 1 (Before REBUILD):
>select tablespace_name, initial_extent/1024 INIT_KB,
>next_extent/1024 NEXT_KB, pct_increase
>from dba_segments
>where segment_type = 'INDEX' and segment_name = 'FIELDPROPS_IDX_01';
>
>TABLESPACE_NAME INIT_KB NEXT_KB PCT_INCREASE
>--------------- ------- ------- ------------
>V_INDEX 64 240 1
>V_IDS_INDEX 64 64 1
>
>Query 2 (Before REBUILD):
>select tablespace_name, bytes/1024
>from dba_extents
>where segment_type = 'INDEX' and segment_name = 'FIELDPROPS_IDX_01';
>
>TABLESPACE_NAME BYTES/1024
>--------------- ----------
>V_IDS_INDEX 80 (1 of these)
>V_INDEX 80 (4 of these)
>V_INDEX 120 (5 of these)
>V_INDEX 160 (5 of these)
>V_INDEX 200 (5 of these)
>V_INDEX 240 (4 of these)
>(For a total of 24 extents, before rebuilding)
>
>
>Query to REBUILD:
>alter index apps.FIELDPROPS_IDX_01 rebuild
>storage (initial 256K next 256K pctincrease 0)
>tablespace V_INDEX;
>
>
>Query 1 (After REBUILD):
>select tablespace_name, initial_extent/1024 INIT_KB,
>next_extent/1024 NEXT_KB, pct_increase
>from dba_segments
>where segment_type = 'INDEX' and segment_name = 'FIELDPROPS_IDX_01';
>
>TABLESPACE_NAME INIT_KB NEXT_KB PCT_INCREASE
>--------------- ------- ------- ------------
>V_INDEX 256 256 0
>V_IDS_INDEX 64 64 1
>
>Query 2 (After REBUILD):
>select tablespace_name, bytes/1024
>from dba_extents
>where segment_type = 'INDEX' and segment_name = 'FIELDPROPS_IDX_01';
>
>TABLESPACE_NAME BYTES/1024
>--------------- ----------
>V_IDS_INDEX 80 (1 of these)
>V_INDEX 280 (4 of these)
>V_INDEX 320 (1 of these)
>(For a total of 6 extents, after rebuilding.)
>
>I was hoping to see all extents in V_INDEX, of size 256K.
>
>Rose
Received on Wed Aug 11 1999 - 15:46:18 CDT