Re: How to calculate the size of an index?

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 12 Jan 2004 21:32:37 GMT
Message-ID: <btv3pk$bi09s$1@ID-82536.news.uni-berlin.de>

> Rene Nyffenegger <rene.nyffenegger_at_gmx.ch> wrote in message news:<btudr2$bd78h$1_at_ID-82536.news.uni-berlin.de>...


>> > To calculate the size of a table, I usually use num_rows *

>> > avg_row_len. But how to I calculate the size of an index? Do I use

>> > avg_data_blocks_per_key * distinct_key * db_block_size of the

>> > database?

>> >
>> > Please advice. Thank you.
>>
>>
>> Imho, avg_row_len * num_rows gives you quite a meaningless figure if you
>> want to know the size of a table; if this isn't the case for you, please
>> elaborate further why this figure is interesting.
>>
>> Equally imho, better would be to calculate the size for a table like
>>
>> select bytes from user|all_segments where segment_name = 'TABLE_NAME'
>> [and owner = 'OWNER']
>>
>> The same query can then also be used to find the size for an index.
>>
>> hth
>> Rene

> 
> That is the physically used size of the segment, but not the size of
> the _business_ data it contains, to which I think the OP may be
> referring.  It may be rebuilt much smaller if the INITIAL_EXTENT of
> the table/index is ridiculously large.

Steve,

I am well aware of the fact that this is the physically used size that gets calculated like this. If someone relays on the 'business size' only, he is heading for trouble.

Rene

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch

Received on Mon Jan 12 2004 - 15:32:37 CST