Re: Index rebuilding behavior

From: <skubiszewski_at_Eisner.DECUS.Org>
Date: Wed, 11 Aug 1999 19:09:26 GMT
Message-ID: <FGBEJr.3KD@news.decus.org>

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 - 14:09:26 CDT