Re: Strange index sizing after import

Bruce, take a look at the dbms_space package(its hiding under $ORACLE_HOME/rdbms/admin), just grep for it to see which .sql it lives in dbmsutil.sql more than likely), anyways what you're looking at is allocated space versus used space.

in that package is a proc you can use to see truly used space, that info is not available with any views that exists today.

hth, joe
"Reardon, Bruce (CALBBAY)" wrote:
>
> 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
>

-- 
Joe Testa  http://www.oracle-dba.com