Re: Determining size of existing index

Andy Yun <ayun_at_amgen.com> wrote:

>Chuck Hamilton wrote:
>>
>> What's the best way to determine the size of an existing index?
 

>The following statement will give you total size taken up by the index
>(remember that some of that may not be used yet due to extent sizes and
>PCT_FREE parameters):
 

>SELECT sum(bytes)
>FROM user_extents
>WHERE segment_name = 'PUT YOUR INDEX NAME IN HERE';

Maybe I didn't word the original question correctly. What I'm looking for is the amount of DATA space that's being using by the index. I want to use this in a small test database to determine what % of the size of the tables their primary key indexes represent. Then I can use that % to project what the size of the indexes need to be be on the production database for each table. In other words, if table X's pk index is 15% of the size of the entire table on a 10k table, it should also be 15% for a 5g table.

As you stated, the above example just gives me what was allocated including unused space in my extents. Since the indexes were all created with INITIAL extents much larger than needed, it isn't going to help me.

Isn't there somethig I can do like I do with tables? With a table I can ANALYZE it and then multiply NUM_ROWS by AVG_ROW_LEN (from the USER_TABLES view) and get a pretty close estimate of the size of the data in the table. What I really need is something similar to this for indexes. The test database tables aren't sufficiently large enough to get a reasonable estimate based on the number of blocks. An 8k table with a 1k PK index is going to show up as 2 blocks for the table (4k blocks) and 1 block for the index, a 2:1 ratio when it's actually 8:1.

--
Chuck Hamilton
chuckh_at_ix.netcom.com

Incoming fire has the right of way!