Strange index sizing after import

From: Reardon, Bruce (CALBBAY) <Bruce.Reardon_at_comalco.riotinto.com.au>
Date: Mon, 18 Dec 2000 04:36:50 -0000
Message-Id: <10713.124798@fatcity.com>

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 Received on Sun Dec 17 2000 - 22:36:50 CST