RE: Strange index sizing after import

Hi Bruce,

try 'validate index your_index'; This command populates table INDEX_STATS. The table contains only one row for most recent command. Compare INDEX_STATS.DEL_LF_ROWS for your indexes. It will show you how much your index was sparse.

Ed

>
>
> Hi,
>
> I am running Oracle 81511 on NT 4.
>
> I exported our production database and imported into a
> freshly created test
> instance.
>
> In doing so, I found that 1 index took up twice as much
> space in the test
> instance as it did in production.
>
> The query I used to look at the occupied space was:
>
> select
> 2 segment_name , segment_type , bytes , blocks , extents ,
> tablespace_name
> 3 from
> 4 dba_segments
> 5 where
> 6 segment_name ='CHSE_ELMN_FK_I'
> 10 order by
> 11 tablespace_name , bytes
> 12 ;
>
> In production:
>
> SEGMENT_NAME SEGMENT_TYPE BYTES BLOCKS
> EXTENTS TABLESPACE_NAME
> -------------------- ------------------ ---------------- ----------
> ---------- --------------------
> CHSE_ELMN_FK_I INDEX 134,963,200 16475
> 1 INDEXD
>
>
> whereas in test:
>
>
> SEGMENT_NAME SEGMENT_TYPE BYTES BLOCKS
> EXTENTS TABLESPACE_NAME
> -------------------- ------------------ ---------------- ----------
> ---------- --------------------
> CHSE_ELMN_FK_I INDEX 261,144,576 31878
> 7 INDEXD
>
>
> The export was taken with compress=no (and anyway I got more
> extents after
> the import compared to in production.
>
> In both cases, the degree of the index is set to default.
>
> I checked the base table in both instances and it contained
> basically the
> same number of rows (production has been in use since the
> export and now has
> a few more rows) - this is around 5,172,976.
>
> Initially I had analyzed the test schema with an estimate of 30% and
> dba_indexes showed the number of rows 10,363,732 whereas
> after I did a
> compute analyze the number of rows showed correctly in dba_indexes.
>
> A validate structure on both the index and table showed no problems.
>
> What else should I look at?
>
> Thanks,
> Bruce Reardon
>

mailto:bruce.reardon_at_comalco.riotinto.com.au

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Reardon, Bruce (CALBBAY)
  INET: Bruce.Reardon_at_comalco.riotinto.com.au

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may